Introduction to VBScript Connection Objects: Tutorial #12
In my previous VBS tutorial, we discussed on ‘Excel Objects’ in the VBScript. In this tutorial, I will be explaining about Connection Objects that are used in the VBScript.
VBScript supports different type of objects and Connection Objects are among those. Connection Objects are mainly referred 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.
What You Will Learn: [show]
Overview
Any Databases like SQL Server, PL/SQL, etc. needs to be installed on your computer to work with the Database Connections.
As such, there is not any straight-forward 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 the 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 to extract data from the database.
List of ADODB Connection Object properties are as follows:
#1) Connection string:
This is a very useful property which 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 the 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 in a while).
List of its properties are 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 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 maximum 20 rows at a time from the data then you can set this property as 20.
Let's now take a look at Methods:
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/recordset which is opened.
- Cancel: This is a recordset method which 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 which is used to move the pointer at a specified location inside a recordset on the basis of the count that is mentioned.
- count: This is a recordset method and is used to get the total number of fields present in the recordset 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 recordset.
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 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 recordset object by mentioning 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 recordset that is mentioning the index of the item where acct no. is located like item(0) in this case. Value of a field is then displayed with the help of a message box.
Note: Fields represent ‘columns’ and recordset represents ‘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 ‘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 of data using a combination of the database and QTP.
Let’s see 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 query and connection object.
- Next, the main task is performed to store the values of 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).
- 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 ‘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 recordset object by mentioning query and connection object.
- Then, name and age are written using writeline method of 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, connection object and recordset object are released by using ‘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 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.