DSN-Less Database Access




Normally we would access a database through ODBC and the Data Source Name (DSN) of the database to connect to and issue SQL queries to manipulate the data. This however, requires a DSN to be configuration on the machine that plans to use the database, which may not be available or convenient.

You can also connect directly to a database without using ODBC. You must write a few more lines of code and you give up the features that ODBC gives you.


The access to configure DSNs on a machine may not be an option such as on a remotely hosted web server. You are developing a low volume single concurrent user database application or script.

Some good points about direct connect off of the top of my head..


Some draw backs






// Create ADO Recordset

var dbrcd = new ActiveXObject( "ADODB.Recordset" );



// Create the connection string

var cString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=d:\\wsh\\db\\test.mdb";



// Create SQL Statement

var sql = "SELECT * from Phone";



// Open connection to database

dbrcd.Open( sql, cString, 1, 3 ); 



// Loop through all the records found

while( !dbrcd.EOF )

{

   // variable to store data

   record = "";

   

   // Loop through each field in this record 

   // and add contents to variable

   for( i = 1; i < dbrcd.Fields.Count; i++ )

       record += dbrcd(i) + "\n";

	

   // Display the contents in Popup

   WScript.Echo( record );

   

   Move to the next record and loop

   dbrcd.MoveNext();

}







var dbrcd = new ActiveXObject( "ADODB.Recordset" );

ADO provide us with a couple of ways to retrieve data from a database. In this example I am using the ADO Recordset object, which provides us with an easy way to manipulate records. You can also use the ADODB.Connection which allows more control over how we connect to the database.


var cString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=d:\\wsh\\db\\test.mdb" ;

This is really where the difference is between using ODBC and not. The connection string specifies which driver and where the database is located that we want to connect to. Normally this would have been stored in the DSN, if you were using ODBC. In this example, we are using the Microsoft Access Driver and the path to the database is d:\wsh\db\test.mdb. The syntax of this line is a little touchy so watch your curly braces and parentheses.


var sql = "SELECT * from Phone";

We need to ask the database which records that we want to return. We accomplish this by creating an SQL statement. In this example I want to return all the records and fields from the table Phone.


dbrcd.Open( sql, cString, 1, 3 );

Now that we created the Recordset and the SQL Statement that are needed, we can actually open a connection to the database. The Open method of the Recordset object takes up to 5 optional parameters.

Recordset.Open( source, ActiveConnection, CursorType, LockType, Options )
In this example,
Our SQL statement is our source sql
Our ActiveConnection is our Connection String cString
Our CursorType is adOpenKeyset 1
Our LockType is adLockOptimistic 3
No options

Please refer to Microsoft's ADO documentation for more information on using ADO.

If everything went OK, our Recordset dbrcd should contain the retrieved records from our database test.mdb.


while( !dbrcd.EOF ){ statements }

The recordset stores each record returned along with each field requested. In this example, I have request that all the fields should be return (The * is the sql statement).

We start by creating a loop to move through each record. The easiest way I have found to do this is to use a while loop and check for the "End Of File" Property to go true. More specifically, keep looping while we are not at the EOF.


for( i = 1; i < dbrcd.Fields.Count; i++ )
   record += dbrcd(i) + "\n";


Our while loop takes care of moving us through each record, we also need to get at the fields inside of each record. A quick way to get this down is to use a for loop.

Recordset are 0 based indexed. Which means the first field in a record is at index(0). The total number of fields can be found by retrieving the property dbrcd.Fields.Count

This loop starts at field index 1 and continues until the last field which is field index (Fields.Count - 1). I started at field (1) because the first field is an Autonumber Primary Key and I did not want to show it. I could have also constructed a slightly more difficult SQL statement to not even return this field.

Database table layout looks like this:
ID - Primary Key
FName = First Name
LName = Last Name
Phone = Phone Number

The value of each field is appended to the variable record plus a new line character (\n).


WScript.Echo( record );

Show the data stored in the variable record to the user.


dbrcd.MoveNext();

We need to advance to the next record contained in our Recordset object. This is important not to forget or you will have an infinite loop if you do not move.

Loop backup up and do it all over again.