VBScript : How to Connect to Database and Return Records

---

Welcome to this post!-“VBScript : How to Connect to Database and Return Records?”

If you are looking for latest HP UFT QTP interview Questions, then you are at right place. This post – “ VBScript : How to Connect to Database and Return Records?” consists of discussion on Connection Object, Connection String, RecordSet Object, Cursor Types, Cursor and Lock Enum, EOF, BOF and Complete Example.

Plus this post will give a broader perspective on the usage and implementation of ADO and vbscript options in UFT or QTP.

Usually these are assumed to be known in depth to check the logical solvency efficiency of the candidate and of course suitability for the project and company. Go ahead and enjoy reading…

 

This discussion will help you prepare well if you are going for the interview or you need the function to be used in your project.

In the interview usually, you might be questioned in different ways like:

1. How to get records from database using vbscript?
2. How can you connect to sql server database using vbscript?
3. Will you be able to use excel as your database and connect using vbscript?
4. How to fetch records from excel file?
5. Can you use connection strings in vbscript?
6. which type of cursor should you use to connect with database?
7. Which type of lock should you use while opening connection? And so on.

---

In this post, I will discuss the objects that are used for establishing connection, running your sql statements and returning results. For this complete process, you need to understand below concepts with respect to vbscript:

1. Connection Object
2. Connection String
3. RecordSet Object
4. Cursor Types
5. Cursor and Lock Enum
6. EOF
7. BOF
8. Complete Example

Let’s understand each of the main topics one by one and with examples.

---

Understanding Connection Objects

 

In ActiveX Data Object (ADO), a Connection object means an electronic or internal system connection between your vbscript and the targeted database. The Connection object communicates with ADO informing where the database can be found with opening parameters like user name and password and so forth.

 

Creating a new Connection object is very simple.

Dim Conn

Set Conn = CreateObject("ADODB.Connection")

 

When you’re done using your database connection (such as at the end of your script), you call the Connection object’s Close method.

'closing connection

Conn.Close

 

This ensures that all database files are properly closed, any server connections are released, and so forth.

 

---

Understanding Connection Strings

The connection string itself contains information about the database such that Connection object can directly utilize the same to build a connection to database. Connection strings provide more flexible connection option than DSNs. Unlike a DSN, a connection string doesn’t require any special configuration or changes on your computer where the script is supposed to execute.

The major advantage is that connection strings can utilize newer OLE DB database drivers (called providers).

Connection strings can vary widely depending on the types of databases.

Example for connection string:

---

The connection string to Microsoft Access is:

Syntax:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<db filename path>;

 

Example:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\msaccessdb\mydb.accdb;

 

---

The connection string to Microsoft Excel is:

Syntax:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<db filename path>;Extended Properties=Excel 8.0;

 

Example:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source c:\msexceldb\myxldb.xlsx;Extended Properties=Excel 8.0;

 

---

The connection string to text files (like CSV, which has header column) is:

Syntax:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<db path>;Extended Properties=Text;HDR=Yes;FMT=Delimited;

 

Example:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\myexceldb\mycsvdb.csv;Extended Properties=Text;HDR=Yes;FMT=Delimited;

 

---

The connection string to Microsoft SQL Server is:

Syntax:

Provider=SQLOLEDB;Data Source=server name;Initial Catalog=<database name>; [User ID=user name;Password=password]

 

Example:

Provider=SQLOLEDB;Data Source=SQLServer1999;Initial Catalog=myrealdb.mdb;User ID=user1999;Password=pass1999

 

Note: In case you don’t have username and password you can omit these.

 

Now you can pass the connection string as the argument of the Open method.

Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyrealDB.mdb;"

 

---

Understanding Recordset Objects

A database table is a set of rows, or records. A Recordset object represents part, or all, of a database table.

Suppose you have a database table named WebUsers that contains columns named UserID, UserName, and UserPassword.

 

Example 1

**querying all rows

Dim recordSetObj

Set recordSetObj = CreateObject("ADODB.Recordset")

Set recordSetObj = objConn.Execute("SELECT * From WebUsers")

 

Example 2

**querying some rows

Dim recordSetObj

Set recordSetObj = CreateObject("ADODB.Recordset")

Set recordSetObj = objConn.Execute("SELECT top 20 From WebUsers")

 

Similarly, you can associate various sql keywords, to obtain more meaningful results.

 

---

Cursors

There is an internal pointer called cursor keeps track of the current row. By default, the cursor points the first row.

There are various types of cursor available for a recordset use like:

• Forward-Only Cursors
• Static Cursors
• Keyset Cursors
• Dynamic Cursors

Similarly, there are different statements to move the pointer or cursor within recordset like

1. MoveNext
2. MovePrevious
3. MoveFirst
4. MoveLast

 

Forward-Only Cursor

 

Forward-only cursors are those cursors which can only move forward in your whole returned result set, in simple terms once you moved to next row you cannot go back. This is default cursor type.

In most of the cases you will use this cursor only. Even the Execute method (of connection object) returns the forward-only recordset cursor.

 

Dynamic Cursor

The most useful cursor type is dynamic. To open a dynamic cursor, you have to explicitly declare a new Recordset object and use its Open method.

Example:

Dim recordSetObj

Set recordSetObj = CreateObject("ADODB.Recordset")

recordSetObj.Open "SELECT Column FROM Table", objConn, 2

 

Note :

• The number 2 indicates that you want a dynamic cursor on recordset. The default is 0 which opens a forward-only cursor on recordset.
• Not all database providers support various cursor types. If you get an error trying to open one, try opening a forward-only cursor. If that works, your database provider doesn’t support other cursor types.

You can also modify column value using a dynamic cursor on the recordset.

**You can also modify column value using a dynamic cursor on the recordset.

Dim recordSetObj

Set recordSetObj = CreateObject("ADODB.Recordset")

recordSetObj.Open "SELECT salesdate FROM sales WHERE [customer_name] = 'John'", objConn, 2

recordSetObj("salesdate") = "05/23/18"

recordSetObj.Update

Please see the table below to follow which cursortype, locktype number or enum you should use in which situation:

 

CursorTypes Enums

CursorTypes have enums as shown below in the table:

Specifies the type of cursor used in a Recordset object.

You can find more details on below link :

Cursor Type Enums

 

LockType Enums:

Specifies the type of lock placed on records during editing.

You can find more details on below link :

Lock Type Enums

 

 

---

EOF

The Recordset object supports a property called EOF, which stands for end of file. This property is set to TRUE when the recordset pointer is positioned past the last row of data.

And hence to enumerate through each row in your recordset, use the EOF property in a loop.

Example 1

Do Until recordSetObj.EOF = True

recordSetObj.MoveNext

Loop

 

Example 2

To refer a specific column, you can use as shared below:

**To refer a specific column, you can use as shared below:

Do Until recordSetObj.EOF = True

strUser = recordSetObj("UserName")

recordSetObj.MoveNext

Loop

recordSetObj.Close

 

**When you’re finished using the recordset, it’s a good idea to close it, releasing any resources it had been using.

---

BOF

Like EOF there is also a BOF i.e. Beginning of file. BOF is only TRUE when the cursor or pointer is positioned on the first record. If both BOF and EOF are TRUE, the result set or output recordset is empty.

 

Example

you can check empty record count as shown below.

**you can check using following example:

Dim recordSetObj

Set recordSetObj = CreateObject("ADODB.Recordset")

recordSetObj.Open "SELECT top 10 FROM sales WHERE salescount>1000", objConn, 2

If recordSetObj.EOF And recordSetObj.BOF Then

Print “empty recordset”

End If

---

Complete Example- Connect to Database and Return Records

In this example, you will see how to Connect to Database and Return Records.

 

Objective:

There is table of sales containing columns like orderid, ordernumber,city,revenue,salemonth,etc. The query is to get the sales order number for those which happened in city Chicago and for more than 4000 dollars.

I have put comments before each statement to indicate what’s happening.

''VBScript : How to Connect to Database and Return Records

Dim Conn, recordSetObj, sqlQuery, serverName, fieldOrCol, allFieldsOrCols

'Declare the sqlQuery statement that will query the database

sqlQuery = "SELECT s.ordernumber as ORDERNO FROM sales s where s.city='Chicago' and s.revenue>4000"


'Create an instance of the ADO Conn and recordSetObj objects
Set Conn = CreateObject("ADODB.Conn")
Set recordSetObj = CreateObject("ADODB.recordSetObj")

'Open the Conn to the database
Conn.Open "Provider=SQLOLEDB;Data Source=SQLServer1999;Initial Catalog=myrealdb.mdb;User ID=user1999;Password=pass1999"

'Open the recordSetObj object executing the sqlQuery statement and return records, 
'since we do not require any specific cursor to work with we are opting here for defaults.

recordSetObj.Open sqlQuery,Conn

'Determine whether there are any records

If recordSetObj.EOF Then
	print "There are no records!!"
Else

'if there are records then loop through the fieldOrCols

 Do While NOT recordSetObj.Eof

     fieldOrCol = recordSetObj("ORDERNO")

	'Store all returned values into a variable
	if fieldOrCol <> "" then
    	allFieldsOrCols = chr(13) & fieldOrCol +allFieldsOrCols
	end if

	recordSetObj.MoveNext
  Loop
End If

print "all values::" & allFieldsOrCols

'Close the Conn and recordSetObj objects
recordSetObj.Close
Set recordSetObj=nothing
Conn.Close
Set Conn=nothing

 

---

Conclusion

Let’s conclude our discussion on “VBScript : How to Connect to Any Database and Return Records” with few good points to be noted :

1. Prior calling the procedures you must know which type of cursor and locks you should use.
2. If you need to pass only select query then do not think on cursor type or lock type. Let it be a default one.
3. If you need to use update, delete etc queries think of dynamic or static cursor. Both of them have their own pros and cons but works well when modification is required in recordset.

 

 

This brings us to the end of our discussion on “VBScript : How to Connect to Database and Return Records”.

I really hope you have enjoyed reading the post. If you have any doubt on this please feel free to add your comment below.

---

And if you like to read more on UFT or QTP Technical Interview Questions please follow below links:

UFT Technical Interview Questions – Set 1

UFT Technical Interview Questions – Set 2

How to Download Resource From QC\ALM?

How to Upload Resource To QC\ALM?

How to download file from Server via WinSCP using VBScript?

How to upload file from Server via WinSCP using VBScript?

Or if you prefer General Interview Questions please follow below links:

UFT General Interview Questions – Part 1

UFT General Interview Questions – Part 2

---

If you would like to keep track of further articles on UFT (QTP). I recommend you to SUBSCRIBE by Email and have new UFT articles sent directly to your inbox.