Inside Technique : Sokoban Level Designer : Client Data Access with HTML Applications
By Rajeev Hariharan

This is the last in a series of articles that discusses the implementation of the DHTML Sokoban game. In the first article, Client-side Data Access, we saw how IE's security model makes it difficult to implement a clean approach to handling data on the client. In this article, we will look at how we used an HTML Application or HTA, a new IE5-only technology, to simplify this thorny task. We will also present a library of common Client Data Access procedures, and take a quick tour of ActiveX Data Objects or ADO, the technology behind these procedures.

Surprisingly little has been written about HTAs, especially considering the fact that they were unleashed on the general public almost a year ago, in the June '98 Developer Preview of IE5. SBN Magazine has a (very) basic Introduction to HTAs, while the official Microsoft documentation contains a mandatory Overview, along with a detailed HTA Reference. If you are new to this technology, we recommend that you follow the links above before proceeding with the rest of this article.

In this article we will look at :

• How HTAs differ from HTML pages
• Using the ADO Connection object
• Using the Microsoft Text Driver
• The ADO Client Data Access procedures

We are releasing the DHTML Sokoban Level Designer along with this article. This is a companion software for the DHTML Sokoban game released earlier, and enables users to create their own gamepacks. This software has been implemented as an HTML Application, and will work only in Internet Explorer 5.

First of all, let us take a look at the basic differences between HTA Applications and HTML pages.

© 1997-2000 InsideDHTML.com, LLC. All rights reserved.

Inside Technique : Sokoban Level Designer : Client Data Access with HTML Applications : HTA v/s HTML

Even though HTAs share the same DOM and Scripting Languages with ordinary HTML pages, there are two very significant differences between them. The first is that HTAs ignore the users security settings, thus offering a safe platform for ActiveX controls. The second, and this one is a potential minefield, is that HTAs operate in their own window, which is a standard Application window, and not a browser-owned window. In this section we will look at how the following issues affect the design of an HTA :

• Using ActiveX Controls
• Error Handling
• Debugging HTAs
• Application Window
• The javascript: Protocol

Using ActiveX Controls

Using ActiveX controls has always been a thorny issue for Web developers. While there is no denying their ease of use in extending the somewhat limited functionality of a Web Application, OCXs are basically binary executables, and thus enjoy potentially unlimited access to the client machine, including disk and registry access. Microsoft has a rigorous code signing and authentication procedure in place, which - in theory at least - ensures that the users always know where an OCX has come from, and whether it is safe to use. Inspite of this, many users tend to be paranoid about permitting their browsers to initialize these controls, and thus turn off support for ActiveX in their security settings.

This means that developers have no guarantee that their code will function properly on all client machines, even if they are using controls marked as safe for scripting, for example the Common Dialog control, which we had covered in the first article of this series. And if the developer wants to use a control that has been marked as unsafe for scripting, like the ADO Connection object we will be discussing in the next section, then the chances that the user will prevent the control from initializing increase dramatically.

HTAs offer a perfect solution to the ActiveX dilemma - they simply ignore the users security settings, thus ensuring that the controls are initialized properly. HTAs run from the client disk behave exactly like normal Windows executables, the user sees no security prompt - not even the Open-or-Download prompt that they would normally see when attempting to access an HTA on-line. The Level Designer has been implemented as an HTML Application which is run from the client disk.

Error Handling

Another reason why HTAs are perfect for deploying applications that contain embedded ActiveX controls is that error handling is simpler. In the first article of this series, we used a VBScript subroutine to ensure that errors raised by the Common Dialog control were trapped. This was mainly because of the lack of any kind of exception handling in older versions of JScript. However, JScript version 5 implements the try...catch statement, so you need not resort to VBScript just for error handling.

Debugging HTAs

HTAs are difficult to debug, because, unlike HTML pages, they simply crash without warning when they encounter certain types of errors in the page script. This is why we recommend that you design and debug the page as an ordinary HTML page, then convert it to an HTA - by changing it's extension and adding the HTA tag - only when you're sure that your code is bulletproof. This approach is not without it's drawbacks, however, - just because your page runs fine as an HTML document does not guarantee that it will behave the same as an HTA. We will now discuss how you can take steps to ensure that there will be no nasty surprises awaiting you during this conversion process.

Application Window

As we had discussed earlier, HTAs run in their own window, which is an Application window, and not a browser-owned window. This distinction is important because HTAs behave differently from HTML pages when it comes to functions involving windows. A fairly common gotcha is that a browser window opened by an HTA cannot access the HTA using the caller property, because the caller is an application, not a Web page. Also, Anchor tags in HTAs pop up a new window rather than replacing the contents of the current page.

The javascript: Protocol

HTAs do not recognize the javascript: protocol within Anchor tags. This means that if you use <A> tags like this :

<A HREF="javascript:somefunction()">

...you will get a new window with "javascript:somefunction()" in the address bar!

We therefore recommend that instead of using the HREF property of the anchor tag, you instead call it's onclick handler, like this:

<A HREF="#" onclick="somefunction();return false">Some Function</A>.

Since our focus in this series of articles is on Client-side Data Access, we will not focus on other HTA issues like MDI applications, wizards, mouseless navigation, etc. Some of these will be covered in our next series, where we discuss the Explorer Solitaire game. Meanwhile, let us take a look at ADO, the technology that the Level Designer uses for implementing data access.

© 1997-2000 InsideDHTML.com, LLC. All rights reserved.

Inside Technique : Sokoban Level Designer : Client Data Access with HTML Applications : Accessing Text Data with ADO

If you have scripted Active Server Pages before, you will almost certainly have come across ADO. ADO stands for ActiveX Data Objects, and is a core Microsoft Data Access technology. ADO is a wrapper for OLE DB (a low-level API for data access), and provides the developer with various objects that allow high-level access to this API. Active Server Pages normally use the ADO Connection and Recordset objects to access databases on the server. In this section we will see how we can use this technology to manipulate text data files on the client. Specifically, we will be looking at:

• The ADO Connection object
• The Microsoft Text Driver
• Connecting to a Text Driver database
• Understanding the connection string
• Executing SQL statements

The ADO Connection object

The ADO Connection object is used to establish a connection to a database. To do this, you will first have to create an instance of the object like this:

ADOConn=new ActiveXObject("ADODB.Connection"); 

The next step is to open the connection. You do this by sending a connection string to the object. This string is usually of the form

"DSN=DataSourceName;UID=UserID;PWD=Password" 

Here DSN represents an ODBC database which has been defined using the ODBC Data Source Administrator in the Control Panel. UID and PWD hold security information that the ODBC driver requires in order to establish the connection.

The Microsoft Text Driver

So how do the Connection object and ODBC come into the picture when we are discussing text files? Well, among the various ODBC drivers which come packaged with Windows, is the Microsoft Text Driver. Using this driver, a developer can manipulate text files as database tables, using standard SQL statements. These files need to be in standard CSV format, which is the same format that the TDC uses for it's data files. This is not a coincidence, the TDC uses an ADO recordset internally. Although the Text Driver has many restrictions on the type of SQL statements it can execute, it is still powerful enough for the type of application we have in mind.

FYI: The Text Driver also supports the fixed-width format for data files, but we will not be covering this in the article.

Connecting to a Text Driver database

The connection string to access a Text Driver database looks like this:

ConnStr="DRIVER={Microsoft Text Driver (*.txt; *.csv)}; DBQ="+
	DataFilePath.substring(0,DataFilePath.lastIndexOf('\\'));

Phew! Looks like quite a handful, doesn't it? Also, it doesn't look anything like the standard connection string. Don't worry, we'll go through it step by step.

The first thing you need to understand is that the Text Driver treats files as database tables, and the directories that these files reside in as databases. To use a database in ODBC, you would normally first have to create an ODBC DSN that points to the location of the database. What this translates into while using the Text Driver is this - every time the user wants to manipulate files in a particular directory, he will first have to register that directory as an ODBC DSN in order to be able to access the files within it.

It is obvious that this is not an acceptable state of affairs. Fortunately, ODBC provides a mechanism whereby the developer can dynamically create a DSN, without having to force the user to mess with the Control Panel. A special type of connection string, known as DSN-less, is used to implement this mechanism. The string looks like this:

ConnStr="DRIVER={ODBCDriverName};DBQ=PathToDatabase"

Understanding the connection string

So now, hopefully, you should understand the Text Driver connection string. The ODBCDriverName in this case is:

Microsoft Text Driver (*.txt; *.csv)

You must type in this string EXACTLY as it is displayed here. One of the most common mistakes developers make is to omit the space just before the *.csv, causing the connection to bomb. The PathToDatabase is the directory in which the data file resides. We have used the JScript substring operator to extract the directory path from the DataFilePath variable, which holds the full path of the text data file we want to manipulate. You will have obtained this path from the Common Dialog control, which we discussed in part 1 of this series.

Executing SQL statements

Now that we've established a connection to the database (read directory), the next step is manipulate the data file. You do this by sending an SQL string to the connection object like this:

ADOConn.Execute('SQL String');

For a list of the limitations imposed on SQL statements by the Text Driver, check the ODBCJET.HLP file in your Windows System directory. For a general refresher course on SQL, check the JETSQLnn.HLP in the same directory.

In the next four sections, we will look at a library of functions used to implement common tasks like Open File, Create File, Save File, and Save File As. These functions require that you have 3 ActiveX controls on your page, the Common Dialog control (to help the user select a data file), the Tabular Data Control (which your application will use to manipulate the data), and the ADO Connection object(which the library will use to write data to the client disk ). We will assume that they are named CommonDialog1, TDC1, and ADOConn respectively. We have already covered the creation of these objects. Now we will look at them in action.

© 1997-2000 InsideDHTML.com, LLC. All rights reserved.

Inside Technique : Sokoban Level Designer : Client Data Access with HTML Applications : FileOpen function

The first function we will be covering is the FileOpen function. Since this is a fairly large block of code, we will be breaking it into 3 steps, followed by an explanation of these steps. Note that this function uses JScript version 5 constructs, and thus will only work in IE5.

Step 1 : Open the Common Dialog

function FileOpen(){

//Decide which directory in which to open CommDlg
DP=TDC1.dataURL;
if(DP=='')
  DataPath=location.pathname.substr(1)
else
  DataPath=DP.substring(0,DP.lastIndexOf('\\')

//Initialize CommDlg properties
CommonDialog1.DialogTitle="Select Data File";
CommonDialog1.Filter=
 "Data File (*.dat)|*.dat|All Files (*.*)|*.*";
CommonDialog1.InitDir=DataPath;

//Open CommDlg
try{
	CommonDialog1.ShowOpen();
	}

//An Error has occured
catch (exception){
	//Strip high word from error number
	errnum=exception.number & 0xFFFF; 
	//Check if user has pressed Cancel
	if(errnum==32755)
		return
	//Some other error, display Error Message
	//and return
	else{
		alert('ERROR:\n\n'+exception.description);
		return;
		}
	}
...continued...

The first thing this function does is decide in which directory to open the Common Dialog. If the user has previously opened a data file, the Common Dialog should open in the same directory as that data file. This code gets this directory by obtaining the DataURL property of the TDC, and stripping the filename from it. If, on the other hand, the user has not previously opened a data file, the Common Dialog should open in the same directory from which the user has loaded the page.

Following this, the function sets the Common Dialog's properties appropriately and proceeds to call the Open File dialog. This call is enclosed within a JScript try...catch error handler. The handler first strips off the high word of the Error number (This is because of a bug in the JScript engine...Microsoft has since fixed it, I think...still better safe than sorry). Then the handler checks if the error is a CdlCancel (covered in the first article of this series), and quietly returns if so. If some other error has occured, the handler displays the error message and returns.

Step 2 : Open the ADO Connection

//Set DataFile and DataFilePath
DataFile=CommonDialog1.FileTitle;
if(DataFile=="")
  return;
DataFilePath=CommonDialog1.FileName;


//Set DSN-less Connection String
DSNStr=
"DRIVER={Microsoft Text Driver (*.txt; *.csv)}; DBQ="+
DataFilePath.substring(0,DataFilePath.lastIndexOf('\\'));

//If Previous Connection is Open, Close it
if(ADOConn.State==1)
 ADOConn.Close();

//Open ADO Connection
try{
	ADOConn.Open(DSNStr);
	}
catch(exception){
	errnum=exception.number & 0xFFFF; 
	//ODBC Error - Path Not Found
	if(errnum==16389){
        alert('The path you have specified does not exist');
	  return;
	  }
	else{
	//Some other error
	 alert('ERROR:\n\n'+exception.description);
		return;
		}
	}
...continued...

The piece of code above first sets the DataFile variable to hold the name of the file, and the DataFilePath variable to hold the full path of the file that the user has selected. Both DataFile and DataFilePath are global variables, and will be used by the other functions in this library. The code then tests if the connection object is open by examining it's State property. If the value of State is 1 (Open), the connection object is closed. ADO Connection objects need to be closed before re-opening them with a new connection string.

Finally, the connection object is opened using a DSN-less connection string with the DBQ pointing to the directory in which the file that the user selected resides. The error handling code checks for a Path Not Found error, for which it displays a custom error message. If some other error has occured, it displays the default message for that error.

Step 3 : Open the file in the TDC

//Point the TDC to the Data File
TDC1.dataURL=DataFilePath;
try{
	TDC1.Reset();
	}
catch(exception){
	errnum=exception.number & 0xFFFF; 
	//Trap 'File Not Found' error
	if(errnum==65531){
		alert('Data File does not exist');
		return;
		}
	else{
		alert('ERROR:\n\n'+exception.description);
		return;
		}
	}
}//end function FileOpen()

The last task of the function is to open the data file in the TDC. It does this by setting the dataURL of the TDC to the full path of the selected file, previously stored in the DataFilePath variable, and then resetting the TDC. The error handler for the TDC Reset displays a custom error message if it detects the 'File Not Found' error, or the default error message if some other error has occured.

That's it for the FileOpen function. In the next section, we will look at the FileNew function.

© 1997-2000 InsideDHTML.com, LLC. All rights reserved.

Inside Technique : Sokoban Level Designer : Client Data Access with HTML Applications : FileNew function

In this section, we will be looking at the FileNew function. As in the previous section, because of the size of the function, we will be chopping it into steps, each followed by a detailed explanation of the code within these steps.

Step 1 : Open the Common Dialog

function FileNew(){

//Decide which directory in which to open CommDlg
DP=TDC1.dataURL;
if(DP=='')
  DataPath=location.pathname.substr(1)
else
  DataPath=DP.substring(0,DP.lastIndexOf('\\')

//Initialize CommDlg properties
CommonDialog1.DialogTitle="Create Data File";
CommonDialog1.Filter="Data File (*.dat)|*.dat";
CommonDialog1.DefaultExt="dat";
CommonDialog1.InitDir=DataPath;

//Open CommDlg
try{
	CommonDialog1.ShowSave();
	}

//An Error has occured
catch (exception){
	//Strip high word from error number
	errnum=exception.number & 0xFFFF; 
	//Check if user has pressed Cancel
	if(errnum==32755)
		return
	//Some other error, display Error Message
	//and return
	else{
		alert('ERROR:\n\n'+exception.description);
		return;
		}
	}
...continued...

The first step of this function is almost exactly the same as the first step for the FileOpen function, but for 2 important differences. The first is that the DefaultExt property of the Common Dialog is set to "dat", which means that if the user enters a file name without an extension, the ".dat" extension is automatically added by the Common Dialog. The other important difference is that instead of the ShowOpen method, we call the ShowSave method of the Common Dialog. This method displays the default Save File As dialog.

Step 2 : Open the ADO Connection

//Set DataFile and DataFilePath
DataFile=CommonDialog1.FileTitle;
if(DataFile=="")
  return;
DataFilePath=CommonDialog1.FileName;


//Set DSN-less Connection String
DSNStr=
"DRIVER={Microsoft Text Driver (*.txt; *.csv)}; DBQ="+
DataFilePath.substring(0,DataFilePath.lastIndexOf('\\'));

//If Previous Connection is Open, Close it
if(ADOConn.State==1)
 ADOConn.Close();

//Open ADO Connection
try{
	ADOConn.Open(DSNStr);
	}
catch(exception){
	errnum=exception.number & 0xFFFF; 
	//ODBC Error - Path Not Found
	if(errnum==16389){
        alert('The path you have specified does not exist');
	  return;
	  }
	else{
	//Some other error
	 alert('ERROR:\n\n'+exception.description);
		return;
		}
	}
...continued...

This is exactly the same as Step 2 in the FileOpen function

Step 3 : Write new Data File to Disk

//Build SQL CREATE TABLE string
CreateStr='CREATE TABLE '+DataFile+
  '(FLDA INTEGER,FLDB INTEGER,FLDC TEXT)'; 

//Execute CREATE TABLE command
try{
	ADOConn.Execute(CreateStr)
	}
catch(exception){
	errnum=exception.number & 0xFFFF;
	//File Already Exists error 
	if(errnum==3604){
		alert('Data File already exists!');
		return;
		}
	else{
		alert('ERROR:\n\n'+exception.description);
		return;
		}
	}
TDC1.DataURL=DataFilePath;
TDC1.Reset();

The code then builds an SQL Create Table string, using the file name previously obtained as the table name. The file name is followed by a sample schema consisting of 3 fields, you would replace this structure with your custom schema.

The SQL statement is then executed using the Execute method of the connection Object. The error handler for this statement first checks to see if the File Already Exists error occurred, in which case it displays a custom error message. Finally, if everything went smoothly, the TDC is reset to point to the newly created data file.

When the file is created, another file 'SCHEMA.INI' is also created along with it in the same directory. This file holds information about the structure of the data file, and is used internally by the Text Driver. However, you need not worry about it, because CSV tables don't require a schema file, and even if the user deletes this file, nothing will happen.

In the next section, we will look at the FileSave function.

© 1997-2000 InsideDHTML.com, LLC. All rights reserved.

Inside Technique : Sokoban Level Designer : Client Data Access with HTML Applications : FileSave function

The FileSave function is fairly short, so we will be covering it in one go.

function FileSave(){
//Check if there is a file to save
if(DataFile==''){
	alert('No Data File currently loaded');
	return;
	}

//Delete existing data file
ADOConn.Execute('DROP TABLE '+DataFile);

//Create Empty Data File
CreateStr='CREATE TABLE '+DataFile+'
  (FLDA INTEGER,FLDB INTEGER,FLDC TEXT)'; 
ADOConn.Execute(CreateStr);

//Save TDC filter
SaveFilter=TDC1.filter;
TDC1.filter='';
TDC1.Reset();

//Loop through TDC
while(!TDC1.recordset.eof){
	InsertStr='INSERT INTO '+DataFile+' VALUES ('+
	   TDC1.recordset.Fields("FLDA").value+','+
	   TDC1.recordset.Fields("FLDB").value+','+
	   '\''+levels.recordset.Fields("FLDC").value+'\')';
	TDC1.recordset.MoveNext();
	ADOConn.Execute(InsertStr);
	
	}

//Restore TDC filter
TDC1.filter=SaveFilter;
TDC1.Reset();
} //end function FileSave 

The first thing the function does is to see if there is a file to save, by checking whether the global DataFile variable is a null string. The functions next step is rather interesting - it deletes the existing file from the disk by executing a DROP TABLE command. This approach is used because the Text Driver does not support the SQL UPDATE command. Therefore, the table (file) needs to be dropped (deleted), then recreated from scratch.

The function recreates an empty table using the CREATE TABLE command, then saves the current TDC filter before setting it to a null string (No Filter). Then the function loops through the TDC, building an INSERT INTO statement based on the field values in the TDC, and finally executing the SQL statement. Needless to say, you will replace the sample fields shown here with your own schema. Also, remember to enclose the values of text fields (like FLDC above) in quotes, otherwise the INSERT INTO statement will bomb.

If you have been following this discussion closely, you might find it a little weird. The TDC points to the data file that has just been deleted. Yet even after the deletion, the TDC is still reading data from the deleted file! The explanation is simple - the TDC reads the data file just once - when the dataURL property changes. The file is then cached, and all subsequent reads are from the cache, not from the physical file. Thus the TDC is totally unconcerned whether or not the underlying file has been deleted or not.

Finally the function resets the filter of the TDC to it's original value and exits. In the last section, we will look at another short function, FileSaveAs.

© 1997-2000 InsideDHTML.com, LLC. All rights reserved.

Inside Technique : Sokoban Level Designer : Client Data Access with HTML Applications : FileSaveAs function

The last function we will be looking at in this library is the FileSaveAs function. This tiny function introduces no new concepts, it is just a combination of the FileNew and FileSave functions :

//Check if there is a file to save
if(DataFile==''){
	alert('No Data File currently loaded');
	return;
	}

//Save the path of the current file
SaveDataURL=TDC1.dataURL;

//Call FileNew to create the data file
FileNew();

//Check if FileNew has bombed
if(TDC1.dataURL==SaveDataURL)
   return;
//Save to new file
newURL=TDC1.dataURL;
TDC1.dataURL=SaveDataURL;
TDC1.Reset();
FileSave();
TDC1.dataURL=newURL;
TDC1.Reset();

The only thing of note in this function is that it checks whether the FileNew function has bombed (either because of a user Cancel or some other error) by comparing the values of TDC1.dataURL before and after the call to the FileNew function. If the values are the same, it means an error has occured, so the function quietly returns, otherwise it sets the dataURL back to it's original value , calls the FileSave function, the sets the dataURL to it's new value again.

On the last page, you can download complete the Sokoban Package for play on your machine. This package includes the Sokoban Game and Level Designer.

© 1997-2000 InsideDHTML.com, LLC. All rights reserved.

Inside Technique : Sokoban Level Designer : Client Data Access with HTML Applications : Download Sokoban Level Designer

We complete Sokoban package is available for download. Unzip all the files into a single directory. The Sokoban game is named sokoban.htm and the level designer is named leveldes.hta.

Some Windows 98 installations have experienced a script error accessing the common dialog control. If you get a script error trying to load a game pack please let us know. We are trying to understand why this sometimes occurs (it is fairly rare).

This brings our series on the DHTML Sokoban game and our focus on Client-side data access to a close. In the next series of articles, we will be presenting a new game - Explorer Solitaire. We hope you have enjoyed this series, and hopefully, learnt from it too. Till next time, goodbye.

The author wishes to thank Scott Isaacs for his invaluable advice and contributions during the development and testing of this series

© 1997-2000 InsideDHTML.com, LLC. All rights reserved.