IT Job search

Allow a Flash movie to receive dynamic information from a SQL database using ASP.©www.ciquery.com

Introduction --------------------------------------------- The purpose of this article is to outline the basic techniques that will allow a Flash movie to receive dynamic information from a SQL database using ASP. Also,I will outline the process in which this can be acheived.If you are not familiar with ActionScript - which is the scripting language of Flash refer to ActionScript tutorial which offers a very good breakdown (with downloadable source code). Flash does not communicate directly with a database.Therefore it's necessary to create the ASP scripting layer to allow the the Flash movie to send variables and receive variables. Through the use of Flash method: loadVariables() a call is made to an ASP script which deals with the database connection, query and recordset display .The data is then passed back into the Flash movie. BASICS Getting Started -------------------------------------------------- You will need to use the following technologies 1)MS SQL 7 or MS Access 2)Flash 5 Handling of variables: URL endoding querystrings ----------------------------------------------------- Flash takes URL-encoded text, such as a query string and instigates the variables within its environment.To pass variables from ASP to Flash, you must write a string back to the response in the following format. aspVar0=John+Smith&aspVar1=16/07/81&aspVar2=UK&aspVar3=ASP&aspVar4=VB&aspVar5=HTML The important things here are :1)the output is accurate , 2)the name/value output is formatted using the "=" sign and "&" 3)words within the value part have to use "+" as the separator. 4)The querystring has to be url-encoded. Primarily, this is to ensure that only characters with a certain range are used in the URL.In ASP the Server.URLEncode method will deal with preparing the querystring to be a valid url.An example would be: Server.URLEncode("animation1.asp?aspVar0=John Smith&aspVar1=16/07/81&aspVar2=UK&aspVar3=ASP&aspVar4=VB&aspVar5=HTML") Flash will have already initialised these variables and all is needed is to pass the querystring.The values of the querystring variables are returned within Flash, as name/value pairs.So the querystring above will allow the Flash movie to view them as: aspVar0 John Smith aspVar1 16/07/81 aspVar2 UK aspVar3 ASP aspVar4 VB aspVar5 HTML Flash deals automatically with the decoding.Therefore, in aspVar0 the querystring will have passed the value as "John+Smith" but the decoded value will output aspVar0 as "John Smith" Loading values from ASP to Flash ----------------------------------- To load variables from an ASP file, you should use the following action script: loadVariables ("animation1.asp","","") 1)The url argument i.e "animation1.asp" can be any legitimate url using either HTTP or HTTPS. 2)The location argument i.e "0" is the target in the movie. 3)the variables argument i.e GET defines the HTTP method, you can also use POST 4) loadVariables will only load variables of the MIME format application/x-www-urlformencoded hence the importance of the Server.URLEncode method. You can also use the "getUrl" action , but it does not use the "location" argument and we wanted to allow more flexibility for futrure Flash development Another aspect, if you're retrieving data from the ASP script is to set the Variable option. For Normal Mode select 'Don't Send' from the loadVaribles dialog box.For 'Expert' mode ,don't include in 'Post' or 'Get' option. Passing values from Flash to ASP ------------------------------------------- Flash to ASP, allows the added flexibility to send form data,for example a Flash form . To send data from Flash to an ASP script, use the following ActionScript code: loadVariables ("animation1.asp?item1_Flash=Red", 0, vars=GET) Sending Flash variables using GET,allows you to write into the query string. As such grab the data as: Request.QueryString("item1_Flash"). Alternatively,Flash variables can be sent using POST.Access them in the ASP script using one of two methods: Request.Form("item1_Flash") THE PROCESS Files --------------------------- Animation..(SQL db)-I am using SQL7 .It would be just as easy to use Access. It will depend on your requirements animation1.asp... (asp script) animation1.swf,animation2.swf ...(Flash movie) animation1.fla,animation2.fla ...(Flash file) Database ----------------------------- I've created a table within an existing SQL7 database called "t_animations" using the following SQL script CREATE TABLE [dbo].[t_animations] ( [animation_id] [int] NULL , [itm0] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [itm1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [itm2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [itm3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [itm4] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [itm5] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL Loading from the Flash file ------------------------------------------------------------------------ From within the Flash movie you will run the following ActionScipt: loadVariables ("animation1.asp","" ) The asp script -------------------------------- The Flash movie hits the animation1.asp , which runs the following ASP code.This creates the querystring that the loadVariables ("animation1.asp","" ) will call: 'establish a connection set dbconnection = Server.CreateObject("ADODB.Connection") dbconnection.open "ani","myUsername","myPassword" Set ani = Server.CreateObject("ADODB.Command") Set ani.ActiveConnection = dbconnection ani.CommandText = "select itm0,itm1,itm2,itm3,itm4,itm5 from t_animations" Set aniRs = Server.CreateObject("ADODB.Recordset") aniRs.Open ani, ,3,1 Dim aspVar(6), j, xcount j = 0 xcount = 6 if not aniRs.EOF then aspVar(0) = aniRs("itm0") aspVar(1) = aniRs("itm1") aspVar(2) = aniRs("itm2") aspVar(3) = aniRs("itm3") aspVar(4) = aniRs("itm4") aspVar(5) = aniRs("itm5") end if 'write results to asp page Do While j < xcount Response.Write "aspVar" & i & "=" & aspVar(j) & "&" j = j + 1 Loop This will output the following string onto your web page, which the request from the Flash movie will accept: aspVar0=aa&aspVar1=bb&aspVar2=cc&aspVar3=dd&aspVar4=ee&aspVar5=ff Conclusion ------------------------------------------------------------------------ These techniques should allow you to easily implement a Flash to ASP or ASP to Flash interchange. Even though this appears to be a long winded way of passing data, it is surprisingly quick. The 3 components - Flash,ASP,SQL - focus on their strengths and allow the data request and response to happen smoothly.

Digg This : Click to email a colleague Author details ------------------------------------------------------------------------ Jack Vamvas is a SQL Server Consultant. To contact him about this article , or any issues related to SQL Server register on www.ciquery.com and post a question. For SQL Server Consultancy of any SQL Server task - check SQL Server Specialists Make SQL Server go faster - click here