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..
- An ODBC DSN does not need to be configured
- Connection string can be created on the fly to allow
for more flexible scripts. (db names passed as parameter)
- Easier to transport to other machines.
Some draw backs
- Loss of ODBC features - such as, connection pooling.
- Easier to program.
- Loss of control over connection to database. (ODBC allows
you to configure Buffer size, Page Timeout, Threads, etc..)
// 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.