© Andy Wilson, 1999

Tips and Tricks: DataGrip

In my last article I showed how to connect Director to a database over the web. We achieved this using the built in netLingo Xtra, and we connected to a SQL database via Microsoft's Internet Information Server and ASP. I received a lot of mail subsequently asking me to write about how the same could be achieved using other databases and hosting environments; Access, Oracle, FileMaker, VBScript, Perl, PHP, and so on. Now, it seemed to me pointless to try to go through every permutation of database, hosting environment and scripting language. Most of what people wanted to achieve was very simple, and I thought instead that I would look this month at some of the database solutions for Director that claim to work also over the web. Spotting the need for such a solution, a number of the major database Xtra companies have launched, or intend to launch a web-enabled version of their products. <>I>Integration New Media have a web enabled version of the mighty V12-DBE due any day now - see their site at http://www.integration.qc.ca. I'll be writing about the online version of V12 just as soon as it's available. In the meantime, I want to write about the dataGrip Xtra (http://www.datagrip.com), a solution for Windows projectors and servers. DataGrip has a reputation as an excellent tool for connecting Director to Access databases, and now, with the web-aware version, dataGrip Net, that functionality has become available over the web. In fact, dataGrip will work over any network, so you can use it within your company LAN or WAN, as well as across the internet.

DataGrip works on a client-server model, which is to say that to use the Xtra you'll need to either install the dataGrip server that comes as part of the installation, or you'll need access to a server that has the dataGrip server software installed. Don't worry, though - the program comes with clear and simple instructions that walk you through the installation and the necessary configuration tweaks required to get the server up and running. Obviously, you'll want the server installed on a machine that's publicly accessible, on either your local network or on the internet as such, depending on who your audience is. Once the server component is installed you can effectively ignore it, except when adding a new database to it, as we'll see below. As for the databases on your server, technically speaking you can address any Microsoft Access compatible database (versions 1.0 through to Access 97), in fact, since Access can itself connect to any ODBC compliant database, dataGrip can do so too.

On the client side, all you need do is install the dataGrip Xtra. As usual, you do this simply by dropping the Xtra under the Xtras folder beneath the directory that Director is installed into, during development, or beneath the folder that contains your projector in the run-time environment. The Xtra extends Lingo to give you a set of commands for interacting with a remote database. The client-server operating environment, then, looks like this;

Fig 1: The DataGrip Environment

A High Score database

To demonstrate dataGrip's functions, I'll show how you might use it to control a database of high-scorers, so that the database maintains a list of the scores user's achieve when they play a game you have authored in Director. Let's assume that you want to record each user's name, email address and the score they achieve.

Start by creating an Access database on the server. Call it 'highScores'. Add a new table to the database, calling it 'Scores', and add the following fields to the table;

Fig 2: MS Access Table

You will have to register this new database with the dataGrip server, but fortunately this takes only a few clicks to achieve. When you do this, call the new database 'high scores' - you'll need to know how the database has been named within the dataGrip server environment so as to be able to access it from within Director.

Now, if you've placed the dataGrip Xtra into the correct folder on your development machine, you can open Director and start playing with the dataGrip Lingo commands. To open a connection within Director to the new database you have to issue the following commands;

set gNetID = DGN_Login("localhost")

Here we have opened a connection to the remote server by issuing the DGN_Login command, passing to it the name or address of the remote server we wish to connect to. In this case I have installed the dataGrip server on my development machine, so that it is hosting both the server and client halves of dataGrip's communication. Therefore, I was able to tell the method to connect to 'localhost', though I could have used the IP address of my local machine, or it's domain name. The DGN_Login method kicks off a network process and returned the ID of this process, which is assigned to the gNetID variable. All network communication in dataGrip is asynchronous, which means that when you issue a dataGrip network command, the command returns immediately even though the communication it has initiated has not yet completed. This asynchronous communication is controlled by making each network command return a number that identifies that particular instance of the command. We can then use this ID to track the status of the command by using this ID. It is this ID that is returned by the initial command and assigned to gNetID. The next thing to do is check whether the command has yet completed by calling the DGN_NetDone command, passing to it the ID of the process we wish to check the status of;

put DGN_NetDone(gNetID)
-- "1"

Now, depending on the speed of the connection between your client and server, this process could take some time to complete. Therefore there is no point in a real-world application of following the Login command immediately with a check call to NetDone. Instead, as we discussed last month, you would want to do something like maintaining a list off all pending net process ID's, and then using the on idle handler to keep checking the status of each process, and act accordingly when any completes. Still, in these examples I will continue to assume that you are typing each command into the message window and don't need any complex programming to track each process.

The next thing to understand is that the NetDone command returns a '1' only when the particular net command has been completed, whether or not it completed successfully. The next thing, then, is to check that the process completed successfully;

put DGN_NetError(gNetID)
-- 0

A return of 0 tells us that the process did indeed complete successfully. Any non-zero result signifies and error of some sort, and dataGrip has methods that let you find out more about the error in question, but I'll leave you to discover the secrets of error handling in dataGrip for yourself.

Now we have logged into the server, we need to open up a particular database registered with the dataGrip server;

set gNetID = DGN_OpenDB("high scores")
put DGN_NetDone(gNetID)
-- "1"
put DGN_NetError(gNetID)
-- 0

and then get dataGrip to return a handle that identifies the database in question so that we can refer to it in our following commands.

set gDBHandle = DGN_GetHandle(gNetID)
put DGN_NetDone(gNetID)
-- "1"
put DGN_NetError(gNetID)
-- 0

Having logged into the server we have now successfully opened the database we created earlier and made dataGrip return to us a handler we can use to identify it. Now we need to open a recordset from the database that we intend to manipulate. This is done by issuing an SQL command to the table. Since I am testing this in a development environment, I am going to return all of the records existing in the database table. This is done as follows;

set gNetID = DGN_OpenRS("SELECT * FROM Scores",¬
   gDBHandle)
put DGN_NetDone(gNetID)
-- "1"
put DGN_NetError(gNetID)
-- 0

Here, the SQL command SELECT * FROM Scores is used to select all of the records currently in the table Scores. This done, we now have to retrieve a handle for this recordset so that we can manipulate it in the commands that follow, just as we did with the database handle;

set gRSHandle = DGN_GetHandle(gNetID)

Now that we have the handle for the recordset stored in the gRSHandle variable, we can begin to use this to add a new record to the table. We add a new record as follows;

DGN_AddNew(gRSHandle)

and populate it thus;

DGN_SetFieldValue("name", "Andy Wilson", gRSHandle)
DGN_SetFieldValue("email", "andyw@dircon.co.uk",¬
   gRSHandle)
DGN_SetFieldValue("score", "20", gRSHandle)

Here I am setting the fields "name", "email" and "score", which we created earlier, with my own name, email address and the score I have achieved in my supposed game. I assume that in a real world application the values passed to the SetFieldValue handlers would be takes from editable fields or suchlike that you use to capture user information. Notice that these are not network operations - we are simply setting the values in the new record we created, which itself exists only in the memory space of the client machine. Nothing is written back to the server database until the changes made are actually committed. This is done with the following command;

set tNetID = DGN_Update(gRSHandle)
put DGN_NetDone(gNetID)
-- "1"
put DGN_NetError(gNetID)
-- 0

Now that we have successfully added our new record to the remote database, all that is left is to close the recordset and the database, and log off from the remote server;

gNetID = DGN_CloseRS(gRSHandle)
put DGN_NetDone(gNetID)
-- "1"
put DGN_NetError(gNetID)
-- 0
gNetID = DGN_CloseDB(gDBHandle)
put DGN_NetDone(gNetID)
-- "1"
put DGN_NetError(gNetID)
-- 0
DGN_LogOff()

And that really is it. In a few simple commands we have been able to read and modify a database on a remote server - which may be located in the next office or on a machine half way around the world. Of course, I have shown you only the basic dataGrip Net commands, but there are plenty of others I haven't mentioned that extend the functionality of dataGrip considerably. They will allow you to use cursors on a recordset and call query strings within a database. Combining these techniques with clever queries in the target database should give you the power to achieve most things you are likely to be called upon to do.

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.