© Andy Wilson, 1999

Tips and Tricks: Database Access Over the Web

With the growing popularity of the web there are more and more Director developers out there having to make the transition from creating projectors that stand alone on a CD-ROM to creating applications that work in the more complicated environment of the internet. For most of us the first step down this road meant simply recompiling projectors as Shockwave movies and embedding these in a page of HTML. Increasingly, however, we are required not simply to add a movie to a web page as a bit of animated, interactive fun, but to begin to make use of the distributed nature of the internet; importing media from other servers. This might mean sending messages over the net by email, creating projectors that call across the internet to access media or even import whole new sections, and so on. Increasingly, Director developers have to understand about distributed programming, the client server model, and other concepts normally thought of as the business of traditional programmers.

In the next few months I am going to talk about some of these issues and how to work with them in Director. To begin with I thought I’d address the question that developers first began to ask which involved understanding how to work over the internet, namely, how to create a high score database for use with Shockwave games. This question arose because so many of us started out by creating games that ran as Shockwave movies and then realised that the next step was to create a remote high score database. That way, the results of a game played by any user on the net could be stored centrally and accessed from the same game. With such a database it would be possible to have competitions, so that the highest scorers in a game could win a prize, or take part in some other scam dreamed up by the fevered imaginations in your marketing department. Hence the sudden demand on the various Director mailing lists to explain ‘how to create a high score database on the web’. It is possible to do database access directly from within Director using tools like the latest version of the dataGrip Xtra (see http://www.datagrip.com/) , but for all sorts of reasons I’m going to assume that you want to do this via a script on the internet which handles the database communication for you.

In this article I’m going to kick off by explaining some of the theory involved in such database access and then show you a real world example using ASP. In future articles I’ll be widening out from this to show you how to do the same thing using different technologies and looking at other techniques that allow you to really take advantage of Director’s potential once you have plugged it into the big, bad world of the net. I realise, of course, that some people will complain that this subject is not about Director or Lingo programming as such. That is precisely the point - the days are gone when you could get away with knowing only how to create a stand alone Director projector and get it to work across different platforms. To work as a Director developer these days you have to know more and more about many different programs and programming and hosting environments.

Remote Communication on the Web

Let’s start with some theory. When you use a web browser to view HTML over the web, your browser is simply a ‘client’ of the web server that delivers the HTML page up to it. The basic model looks like this;

Fig 1: Basic Client Server Model

As I say, in this model the web browser is the client and the web server is, surprisingly enough, the server. Of course, we all know that the client here doesn’t have to be a web browser as such – Netscape or Internet Explorer, for example – but can be any program capable of calling up a web server and retrieving data from it. Similarly, the data that is served doesn’t necessarily have to be a page of HTML, but can be any datatype the server is capable of delivering. A good example of an alternative client capable of calling up a server in this way and receiving the result would be - you guessed it - Director (either a stand alone projector or a Shockwave movie.) If you are using netLingo Xtra you can simply call up a web page and retrieve it as follows;

set netCallID = getnettext("http://localhost/")

The variable netCallID is used to identify the particular net access process you are kicking off. You will need this ID when you want to actually get the results of the call. The URL that you pass as the argument to the getNetText handler is simply the URL of the page of data you are trying to access. In this case I’m retrieving the home page of the web site on my local server. I could just as easily have pointed at any page, anywhere on the internet. For instance, if I want to get a particular page on the net, I simply pass the handler the full URL of the page in question. To actually retrieve the result I first have to wait until the process has finished (ie., the call is made to the remote server and the server has responded). I can find out if the process has completed when the following expression evaluates as true;

put netDone(netCallID)
-- 1

In the real world I wouldn’t be evaluating this in the message box in this way but in a handler called periodically, maybe in my on idle handler. Basically, I’d call this method repeatedly until it evaluated to true, and then I’d get the result using;

set callResult = nettextresult(netCallID)

At this point the callResult variable will hold the result of the net call. If the process was unsuccessful it will contain an appropriate error message (though you can also use the netError handler to test for an error.) If the call succeeded the variable will contain the text of the page you requested, which you can then use as you see fit.

In this case I’ve pointed Director at a traditional page of HTML. I could, of course, have pointed it at anything capable of being retrieved from the net. Since I used the getNetText handler I would be limited to text files. Nevertheless, it’s important to grasp that this text could be of any text MIME type – it doesn’t have to be HTML. Also, I can do anything Director is capable of doing with the result of the net call. For example, I could have a page that contained no HTML markup code whatsoever. To show you what I mean, imagine you want to pass a list of special offers to Director. You can put a file on your server that contains only the following text;

[["Offer 1", 1.00],["Offer 2", 2.00]]

I’ve put the file in the root directory of my local server and called it offers.htm. The file can be updated on the server simply by editing it. Now I can retrieve this information from within Director with the following code;

set netCallID = getnettext("http://andyw.zinc.co.uk/offers.htm")
put netDone(netCallID)
-- 1
set res = nettextresult(netCallID)
set res = value(res)
put res
-- [["Offer 1", 1.0000], ["Offer 2", 2.0000]]

Remember that I’m typing all this into the message window – in the real world you’ll have to use the idle handler, or whatever, to regularly check the netDone status and then retrieve the actual result of the call only when you know that the process is completed. The point is that here I’m not trying to retrieve HTML to display within my projector. Instead I’ve turned the text served up to me straight into a Director list which I can begin immediately to operate on just as if it were any other list I might create in Lingo. The difference is that the contents of this list are always going to be up to date. Even if your projector is distributed on a CD-ROM, as long as the client machine is connected to the internet it will be able to access and use the most current information.

Of course we are only on the first step of making our projector or movie truly interactive. Although the offers.htm file can be edited to keep the information up to date, it is still essentially static data. However, it is simple enough to use server-side scripting capabilities to make sure that the text returned to Director is genuinely dynamic. Whether you are doing Perl scripting on a UNIX box running Apache server, or ASP scripting on an NT box running Internet Information Server (IIS), the principles are the same. When the scripted file is requested, the script is run on the server and the code in the script is evaluated. The output of the script is an HTML file (or any other appropriate text MIME type – I’m sticking with HTML in these examples for the sake of simplicity) which is retrieved by Director exactly as before. In the examples that follow, for the sake of simplicity I’m going to assume the NT/IIS environment described above. The basic architecture of this situation is as follows;

Fig 2: Web Server and Script Engine

The client requests a page from the web server, which calls up the requested script and runs the script. The result is returned from the scripting engine to the server, and from there back to the requesting client. To give you an example of how this might work, if I create a file, moreDynamic.asp, in the root of my web site that contains nothing but the following ASP / VBS code;

This request was from 38.103.63.60

I get the following results when I call it from within Director;

set netCallID = getNetText("http://andyw.zinc.co.uk/moreDynamic.asp")
put netError(netCallID)
-- "OK"
put netDone(netCallID)
-- 1
put netTextResult(netCallID)
-- "This request was from 10.0.0.0"

Now we are getting close to the heart of the matter. The result of the call is totally dynamic. In this case it returns the IP address of the client making the request – something you would hardly need to call across the internet to discover – but the principle has been established. You can see how easy it is to write ASP code and call it from within Director. There really are no mysteries involved here, nothing special to do on the server – just install IIS on a networked computer, copy the one line of code above into a file in the web root, and you’re off.

The very last link in the chain involves connecting the ASP script to a database. Once again, the basic architecture is very simple;

Fig 3: Web Server, Script Engine and Database

Here we have introduced one last stage to the path. What happens is that the script makes it’s own call to an external database, which returns a result set to the script. The script then formats this as text / HTML before returning it to the web server which, once again, hands the result back to the requesting client.

For the sake of argument, let’s assume that you have created a little MS Access database that contains a simple table with fields to store a users name, email address and their score in some game you have created. Of course you could be running any database capable of working on your system – SQL Server, Oracle, whatever – but the principles would be the same. I am using Access in this example because it is the easiest database system to work with, and for most uses it will be powerful enough to cope. I am going to assume that;

1. The Access database file is called scoreResults.mdb and is placed in the same directory as the script that calls it
2. The table in question is called scoreResults
3. The fields are called UTxtName, UTxtAddress and UIntScore, where the first two are text fields and the last is an integer.
4. The script that you call to pass information to the database is called acceptHiScore.asp, and is in the root of the web site.

Let’s assume that you want to pass a new score to the database. From within Director you can pass the data to the script by simply appending it to the URL of the script as follows;

scriptURL = "http://andyw-int/acceptHiScore.asp"
args = URLEncode([#name: "Andy Wilson", #address: ¬
	"andyw@dircon.co.uk", #score: "99"])
fullURL = scriptURL & "?" & args
put fullURL
-- "http://andyw-int/acceptHiScore.asp?name=Andy+¬
	Wilson&address=andyw%40dircon.co.uk&score=20"
set netCallID = getNetText (fullURL)

In the second line of this code you are using the URLEncode method to convert a property list containing the values you want to pass to the database into a form than can be used over the internet. Each of the property-value pairs in the list represent one of the fields in your database and the value you want to assign it in the new record. All you need to do to construct the full URL is to append the URL-encoded result to the URL of your script, separating the two with a ‘?’ (fullURL = scriptURL & "?" & args). If you then call this full URL you are effectively passing all of the values you need to the target script, and we leave it to the script to handle the process of unpacking the values and passing them to the database. The script code will look something like this;

<%
set objConn = Server.Createobject("ADODB.Connection")
objConn.Open "Driver={Microsoft Access Driver (*.mdb)}; ¬
  DBQ=" & Server.MapPath("\") & "scoreResults.mdb;"

uName =  Request("name")
uAdd =   Request("address")
uScore = Request("score")

sql = "INSERT scoreResults (UTxtName, UTxtAddress, ¬
  UIntScore) VALUES (" & "'" & ¬
  uName & "', '" & uAdd & "', " & uScore & ")"
objConn.Execute(sql)
If (objConn.Errors.Count = 0) Then
  Response.Write ("OK")
Else
  Response.Write ("Error")
End If
%> 

Here the first two lines of code simply create a database connection object by using the ADO connection component that ships as standard as part of IIS. If you look at the code you will see that, first, the raw database connection object is created (set objConn = Server.Createobject("ADODB.Connection")). Next, a connection between the object and the actual database file is opened by calling Open method of the connection object, passing it the details of the required driver and the path to the Access file. How you actually connect to a database will depend on the type of database you want to use and also the type of connection. There are a number of choices for you to make here, but they are all easy to implement and are well documented in most ASP manuals.

The real work starts with the following code. The three values you passed to the script are picked up from the ASP Request Object in the following three lines of code and assigned to the VBScript variables uName, uAdd and uScore. Notice that, if you called one of the properties in your Lingo code propertyX, you can access it’s value in ASP by looking for the Request Object variable Request("propertyX"). Thus, the name property is accessed by examining the Request("name") value.

Next, our three variables are used to build up a standard SQL query string, which is assigned to the sql variable. This SQL query is then executed by calling the database object’s Execute method and passing the SQL string to it (objConn.Execute(sql)). Finally, the error count is checked and, if no errors occurred, the string ‘OK’ is passed back as text. Otherwise, the ‘Error’ string is returned. That means that, within Director, if you type in the Lingo code above to call the script, you should get the following results if your data was successfully written to the database;

put netDone(netCallID)
-- 1
put netTextResult(netCallID)
-- "OK"

Now all you have to do is check the database on the final day of the competition and send an email to the highest scorer, telling them that they have won an excellent T-Shirt branded with your clients’ logo.

Obviously I have missed out a lot of intermediary steps here. For instance, I haven’t told you how to code an engine to carry out all of this communication – issuing calls, checking for when the net process is done, handling errors, and so on – in a real world application. I haven’t compared the GET and POST methods, or warned you of the way that the GET method (using getNetText() as opposed to postNetText()) will truncate long URL’s when you use it from Director. I’ve shown you how to submit data remotely, but not how to read it and return the results. I haven’t handled the script file MIME types very elegantly. Also, I’ve stuck to ASP in my scripting examples. These are all issues you’ll have to face at some point. Nevertheless, if you’ve followed the article this far you should understand all you need to know about the basic techniques well enough to start building your own distributed, database-driven applications in Director. All you need now is a web server and a little pile of the relevant reference manuals – Lingo, ASP, SQL, Access. You’ll soon be flying.

Comments and suggestions for future articles can be mailed to me at mailto:andyw@dircon.co.uk. I’ll also remind you that, to join the UK Director User Group (DUG) mailing list, you simply have to fill in the form here.