Introduction to VBScript Connection Objects: Tutorial #12
In my previous VBS tutorial, we discussed ‘Excel Objects’ in the VBScript. In this tutorial, I will be explaining Connection Objects that are used in the VBScript.
VBScript supports different types of objects and Connection Objects are among those. Connection Objects are mainly referred to as the objects that provide support to the Coders to work and deal with the Database Connections.
This tutorial will take you through all the aspects of Connection Objects in the VBScript including its different properties and methods along with simple examples for your easy understanding.
Table of Contents:
Overview
Any Databases like SQL Server, PL/SQL, etc. need to be installed on your computer to work with the Database Connections.
As such, there is not any straightforward mechanism to get connected to the database in the QTP. But with the help of ADODB Objects, you can interact with the database and work with the SQL Queries to fetch the data from the database.
ADO stands for ActiveX Data Objects and this provides the mechanism to act as an intermediary between the QTP and the Database.
This topic formed the basis of working with the Database and it would be very helpful for you in the long run if you have a better understanding of it.
I will try to make you understand all the different codes, properties, and methods that are required to be written to work with the database in an easy manner so that you can easily write a piece of code on your own.
Now, let’s start with the Properties and Methods that are helpful in establishing a connection with the database.
Properties and Methods of Connection Objects
There are different properties and methods that support extracting data from the database.
The list of ADODB Connection Object properties is as follows:
#1) Connection string
This is a very useful property that is used for creating a database connection and includes connection details like the details of the Driver, Database Server Name, Username, and Password.
#2) Connection Timeout
This is used for defining the required time for waiting for a connection to get established.
#3) Provider
This property provides all provider-related details i.e. the name of the Connection Provider.
#4) State
This provides information about the state of the connection i.e. if the Connection is ON or OFF.
The above-mentioned ones are the properties of a Connection Object. However, there is a RecordSet Object also (will we discuss it in a while).
The list of its properties is as follows:
#1) BOF
This property is used to know the position of the current record. If the position of the current record is present before the first record of the recordset, then this property will return true.
#2) EOF
This is just the reverse of the above one. If the position of the current record is present after the last record of the recordset, then this property will return true.
Note: Values of EOF and BOF will be false when there are no records in the recordset. This is useful in the case of validating empty records i.e. when there are no records in the recordset.
#3) MaxCount
This is useful in setting the maximum values of rows/records that can be returned from the database i.e. if you want to fetch a maximum of 20 rows at a time from the data then you can set this property as 20.
Let’s now take a look at the Methods:
The list of ADODB Connection Object and RecordSet Object methods are as follows:
- Open: This method is used to open a database connection object/recordset object.
- Execute: This is used to execute a SQL Query that is provided.
- Close: This is used to close a database connection/record set that is opened.
- Cancel: This is a recordset method that is used for canceling the existing execution of the Database Connection.
- clone: This is a recordset method and is used for getting the clone of the existing recordset object.
- move: This is a recordset method that is used to move the pointer at a specified location inside a recordset based on the count that is mentioned.
- count: This is a recordset method and is used to get the total number of fields present in the record i.e. total number of columns in a table.
- item(i): This is a recordset method. This is used to get the item whose index is specified by the total number of fields present in a record.
These are the different properties and methods that you will use while dealing with the Connection related objects.
Let’s move on to the practical implementation to know the working of these objects.
Establishing a Database Connection using ADODB Connection Object
In this section, we will see the different steps involved in creating a database connection using the Connection Object mechanism in VBScript.
Following is the Code for creating a connection:
Set obj = createobject(“ADODB.Connection”) ‘Creating an ADODB Connection Object
Set obj1 = createobject(“ADODB.RecordSet”) ‘Creating an ADODB Recordset Object
Dim dbquery ‘Declaring a database query variable bquery
Dbquery=”Select acctno from dbo.acct where name = ‘Harsh’” ‘Creating a query
obj.Open“Provider=SQLQLEDB;Server=.\SQLEXPRESS;UserId=test;Password=P@123;Database =AUTODB” ‘Opening a Connection
obj1.Open dbquery,obj ‘Executing the query using recordset
val1 = obj1.fields.item(0) ‘Will return field value
msgbox val1 ‘Displaying value of the field item 0 i.e. column 1
obj.close ‘Closing the connection object
obj1.close ‘Closing the connection object
Set obj1=Nothing ‘Releasing Recordset object
Set obj=Nothing ‘Releasing Connection object
Let’s see how it works:
- Firstly, a Connection Object with the name ‘obj’ is created using ‘createobject’ keyword, and ADODB Connection in the parameter as you are creating a Database Connection Object is defined.
- Then a Recordset Object is created to access the records of a particular table from the database using ‘obj1’. Recordset object provides a way to access the records from the database.
- After that, a variable is declared for a query ‘dbquery’ to store the query to fetch the records from the database.
- Then a Connection Object that is created above is opened to start working with the object by providing all the details about the provider like SQL SERVER, server name, database name, user id, and password.
- A Query is then executed after opening the record object by mentioning the query and the connection object.
- Next, the main task is performed to fetch the value of acct no. of a particular person as per the query using fields of the record that mention the index of the item where acct no. is located like the item(0) in this case. The value of a field is then displayed with the help of a message box.
Note: Fields represent ‘columns’ and recordset represent ‘rows’ of a database table.
- The objects are then closed as the task has been completed.
- Finally, both the objects – obj and obj1 respectively are released by using the ‘Nothing’ keyword.
Note: It is a good practice to release the objects using ‘Set object name = Nothing’ after the completion of the task at the end.
We have seen all about establishing a connection with the database and reading & displaying data using a combination of the database and QTP.
Let’s see a few other scenarios using Connection Object.
Exporting Data to Excel File Using ADODB Connection Object
In this section, we will see the different steps involved in exporting the data to an excel file from the database using the Connection Object mechanism in VBScript.
Following is the Code for this scenario:
Set obj = CreateObject("ADODB.Connection") Set obj1 = CreateObject("ADODB.RecordSet ") Set obj2 = CreateObject("Excel.Application") Set obj3 = obj2.Workbooks.Open ("C:\Users\Riya.xlsx") Set obj4 = obj3.Worksheets(1) obj.Provider =("Microsoft.ACE.OLEDB.12.0") obj.Open "C:\Users\newdb.autodb" obj1.Open "Select name, age from person", obj obj4.cells(1, 1) = "Name" obj4.cells(1, 2) = "Age" row = 2 While obj1.EOF = False obj4.cells(row, 1) = obj1.Fields("Name") obj4.cells(row, 2) = obj1.Fields("Age") obj1.MoveNext row = row+1 Wend obj3.Save obj2.Quit obj1.Close obj.Close Set obj4 = Nothing Set obj3 = Nothing Set obj2 = Nothing Set obj1 = Nothing Set obj = Nothing
Let’s see how it works:
- Firstly, objects – Connection Object, RecordSet Object, and Excel Object with the name ‘obj, ‘obj1’ and ‘obj2’ respectively are created using ‘createobject’ keyword.
- Then, a workbook object – ‘obj3’ is created mentioning the location of the excel file and after that worksheet object (‘obj4’) is created to specify the sheet in the excel file where the data needs to be exported.
- Then a Connection Object that is created above is opened to start working with the object and the Provider details are mentioned.
- A Query is then executed after opening the recordset object by mentioning the query and connection object.
- Next, the main task is performed to store the values of the name and age of a particular person in the excel file as per the query using fields of the recordset in the cells of the sheet from the database. The loop will go on until the EOF is not false (we have already discussed EOF).
- The Excel workbook is then saved.
- Excel application will quit as the task has been completed.
- The objects are then closed as the task has been completed.
- Finally, all the objects are released by using the ‘Nothing’ keyword.
Exporting Data to Text File Using ADODB Connection Object
In this section, we will see the different steps involved in exporting data to a text file from the database using the Connection Object mechanism in the VBScript.
Following is the Code for this scenario:
Set obj = CreateObject("ADODB.Connection") Set obj1 = CreateObject("ADODB.RecordSet ") Set obj2 = CreateObject("Scripting.FileSystemObject") Set obj3 = obj2.OpenTextFile("C:\Users\Riya.xlsx") obj.Provider =("Microsoft.ACE.OLEDB.12.0") obj.Open "C:\Users\newdb.autodb" obj1.Open "Select name, age from person", obj obj3.WriteLine "Name Age" obj3.WriteLine "------" While obj1.EOF = False obj3.WriteLine obj1.Fields("Name") & “ “ & obj1.Fields("Age") obj1.MoveNext Wend obj3.Close Set obj3 = Nothing Set obj2 = Nothing obj1.Close obj.Close Set obj1 = Nothing Set obj = Nothing
Let’s see how it works:
- Firstly, objects – Connection Object, RecordSet Object, and File Object with the name ‘obj, ‘obj1’ and ‘obj2’ respectively are created using ‘createobject’ keyword.
- Then, a file object is opened mentioning the location of the text file where the data needs to be exported.
- Then a Connection Object that is created above is opened to start working with the object and the Provider details are mentioned.
- A Query is then executed after opening the record set object by mentioning the query and connection object.
- Then, the name and age are written using writeline method of a file system object.
- Next, the main task is performed to write the values of name and age in the text file of a particular person using the fields of the recordset in the cells of the sheet from the database. The loop will go on until the EOF is not false (we have already discussed EOF).
- A File object is then closed and the file-related objects are released.
- The objects are then closed as the task has been completed.
- Finally, the connection object and recordset object are released by using a ‘Nothing’ keyword.
These are some of the prime scenarios which are important in the proper understanding of the concept. They form the foundation to work and deal with the codes for handling different types of scenarios while dealing with Connection Objects in the script.
Now, let’s understand the implementation of these scenarios through a simple example.
Example:
<html> <head> <title>Let’s see implementation of Exporting data in files</title> </head> <body> <script language=”vbscript” type=”text/vbscript”> Function Exporttoexcelfile() ‘Function for exporting data to excel file Set obj = CreateObject("ADODB.Connection") Set obj1 = CreateObject("ADODB.RecordSet ") Set obj2 = CreateObject("Excel.Application") Set obj3 = obj2.Workbooks.Open ("C:\Users\Riya.xlsx") Set obj4 = obj3.Worksheets(1) obj.Provider =("Microsoft.ACE.OLEDB.12.0") obj.Open "C:\Users\newdb.autodb" obj1.Open "Select name, age from person", obj obj4.cells(1, 1) = "Name" obj4.cells(1, 2) = "Age" row = 2 If obj1.EOF = True Then Msgbox “No records found on the table!!” End If While obj1.EOF = False obj4.cells(row, 1) = obj1.Fields("Name") obj4.cells(row, 2) = obj1.Fields("Age") obj1.MoveNext row = row+1 Wend obj3.Save obj2.Quit obj1.Close obj.Close Set obj4 = Nothing Set obj3 = Nothing Set obj2 = Nothing Set obj1 = Nothing Set obj = Nothing End Function Function Exporttotextfile() ‘Function for exporting data to text file Set obj = CreateObject("ADODB.Connection") Set obj1 = CreateObject("ADODB.RecordSet ") Set obj2 = CreateObject("Scripting.FileSystemObject") Set obj3 = obj2.OpenTextFile("C:\Users\Riya.xlsx") obj.Provider =("Microsoft.ACE.OLEDB.12.0") obj.Open "C:\Users\newdb.autodb" obj1.Open "Select name, age from person", obj obj3.WriteLine "Name Age" obj3.WriteLine "------" If obj1.EOF = True Then Msgbox “No records found on the table!!” End If While obj1.EOF = False obj3.WriteLine obj1.Fields("Name") & “ “ & obj1.Fields("Age") obj1.MoveNext Wend obj3.Close Set obj3 = Nothing Set obj2 = Nothing obj1.Close obj.Close Set obj1 = Nothing Set obj = Nothing End Function Call Exporttoexcelfile() ‘Calling Function for exporting data to excel file Call Exporttotextfile() ‘Calling Function for exporting data to text file </script> </body> </html>
Note: For Output of an Example, you can go to the location of the files and check if data is exported or not in the respective files.
Conclusion
I am sure that by now you must have gained knowledge about the importance and effectiveness of using VBS ADODB Connection Objects.
Next tutorial #13: I will cover ‘File System Objects’ in my next tutorial.
Stay tuned and share your experience of working with Connection objects and let us know if you have any queries about this tutorial.