Database handling via VBScript is basically done with following steps:
1. Creating the object of ADODB
2. Define the Connection String for the database to connect
3. Opening the connection
4. Firing of the query
5. Accessing data with Record Set Object
6. Closing the connection
7. Release the memory occupied by the Objects.
We will go through each of the above stated steps with an appropriate example showing how the things work up in real life application.
Creating the object of ADODB –
Set db = CreateObject("ADODB.Connection")
Specifying the Connection String of the database to connect –
Connection String related to specific connection can be set either with or without DSN (Data Source Name).
In Case of DSN –
You create the DSN depending upon you want to fetch the data from SQL Server, Excel, Access etc. depending upon the drivers present in your system. Say for example DSN for Ms-Access is created with name as "MyDSN" for a pre specified database selected. You will write the command as:
Db.ConnectionString = "DSN = MyDSN"
In case you don't want to create a DSN-
Connection String will now contain the complete information of what is contained in DSN. It has a benefit over DSN, that the connection since it can contain the complete network path of the database, so can work on all systems. But in case of DSN, that has to be present in the machine when you are using it. Again taking that database is on MS-Access -
Db.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;"
Opening the connection
Once the connection string has been set, next step goes towards the opening of the connection. It basically setup the connection/pathway with the database being specified in the connection string. Command goes like:
Db.Open
Firing of the query
Next step goes to the writing of the SQL Query and executing it. We write a sql query in a variable and that query is executed. On executing the query, a recordset object is returned which contains the result set of the query executed.
Getting the SQL Query:
SQL = "Select * from table1"
Executing the SQL query and capturing the recordset object returned:
Set rec_ob = Db.execute (SQL)
Accessing data with Record Set Object
As per the example, rec_ob is the recordset object containing the result of the query which was being executed. Now we can capture each record in the recordset. In general for looping down till end through the recordset we use the following:
Do while rec_ob.EOF <> true
Operation on records
Loop
Various important methods/events/properties and collection supported recordset object with explanation:
Properties:
BOF – Returns true if the current record position is before the first record,
otherwise false.
EOF - Returns true if the current record position is after the last record,
otherwise false
State – Returns a value that describes if the Recordset object is open, closed,
connecting, executing or retrieving data
Methods:
Open – Opens a Recordset
Close - Closes the Recordset
MoveFirst - Moves the record pointer to the first record
MoveLast - Moves the record pointer to the last record
MoveNext - Moves the record pointer to the next record
MovePrevious - Moves the record pointer to the previous record
Save - Saves a Recordset object to a file or a Stream Object
Events:
The various events supported by ADODB Recordset object cannot be handled using VBScript or JSCript (Only VB, V C++ and V J++ can handle these events). So we are not going discuss these over here.
Collections:
Fields - Indicates the number of field objects in the Recordset object
Properties - Contains all the Property objects in the Recordset object
The Fields Collection's Properties:
Count - Returns the number of items in the fields collection. Starts at zero
Item (name/number) – Returns a specified item in the fields collection.
The Properties Collection's Properties:
Count - Returns the number of items in the properties collection. Starts at
Zero
Item (name/number) – Returns a specified item in the properties collection.
Closing the connection
Once all the activities have been carried out on Recordset object and no more database accessing is record, you need to close the connection established by the ADODB object. Command goes like:
Db.Close
Releasing the memory space occupied by the Objects
The final work is to free up the space occupied by all the objects which were created to reference the objects created. This is done for freeing up the memory so that there is no memory leak.
Set Db = nothing
Comments