Details
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.