DATABASE CONNECTION

To verify that information is entered into the database at runtime, you will need to add code to your script which will open a connection to the database, query the database, then close the connection. Here are the steps to do this (an example follows).

1. Create an “ADODB.Connection” object (the database object).
2. Set the connection string for the database object.
3. Open the connection to the database.
4. Execute a SQL statement.
5. Close the database connection.

The query will return the values as a tab delimited string. You can parse through the string and verify that the information is correct.

Note:
The exact structure of the connection string will depend on the type of database you are using. For example, if your database requires a password, you will need to include a password parameter in the connection string. For more information, refer to Example:

 

 

 

Dim flightnumber
Dim dbexample

‘ Create the conection object.
Set dbexample = CreateObject(“ADODB.Connection”)

‘ Set the connection string and open the connection
dbexample.ConnectionString = “DBQ=D:\Program Files\Mercury Interactive\WinRunner\samples\flight\app\flight32.mdb;DefaultDir=D:\Program Files\Mercury Interactive\WinRunner\samples\flight\app;Driver={Microsoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;”
dbexample.Open

‘ or use this method if a DSN entry was created.
‘dbexample.Open(“DSN=Flight32″)

flightnumber = 6195
‘ Get the recordset returned from a select query.
Set recordset = dbexample.Execute(“SELECT * from Orders WHERE Flight_Number = ” & flightnumber)

‘ Display the results of the query.
msgbox recordset.GetString

‘ Close the database connection.
dbexample.Close
Set dbexample = Nothing

If your query returns several columns, you can use the Fields method to retrieve data from specified columns in the returned record set.

Example:
‘ Connect to the Flight32 database
Set dbexample = CreateObject(“ADODB.Connection”)
dbexample.Open(“DSN=Flight32″)

‘ Perform a query
Set recordset = dbexample.Execute(“SELECT * from Orders”)

‘ Get the values from the Customer_Name column
while (NOT recordset.EOF)
MsgBox recordset.Fields(“Customer_Name”)

‘ Move to the next value in the record set
recordset.MoveNext
wend

‘ Close the database connection.
dbexample.Close
Set dbexample = Nothing

For more information on the Fields and MoveNext methods and working with the “ADODB.Connection” object, please refer to a VBScript reference.

Connecting to Access

sFileName=”D:\customer.mdb”
Set cnnAccess = CreateObject(“ADODB.Connection”)
cnnAccess.Open(“DRIVER={Microsoft Access Driver (*.mdb)};DBQ=” & sFileName)
If cnnAccess.State = 1 Then
ConnectDB = “open”
msgbox “done”
else
ConnectDB = “close”
msgbox “not done”
End if

‘Toget the data from the database
Set Rs=CreateObject(“ADODB.Recordset”)
Set Rs=cnnAccess.Execute(“Select * from orders”)

Do while not Rs.EOF
msgbox (Rs(0) & Rs(1) & Rs(2) & Rs(3))
Rs.MoveNext
Loop

‘To Update the records
cnnAccess.Execute(“update orders set customer_name=’kumar’ where order_number=10″)

‘Connect to SQLSErver using sqlserver authentication

Str_Connect=”Provider=SQLOLEDB;Data Source=pulse;Initial catalog=Master”

Set CnnSql=CreateObject(“ADODB.Connection”)
CnnSql.Open Str_Connect,”sa”,”"

If CnnSql.State = 1 Then
msgbox “done”
else
msgbox “not done”
End if

‘Sample code to connect to sqlserver using WinAuthentication
Str_Connect=”Provider=SQLOLEDB;Data Source=Pulse;Initial catalog=Master;Integrated Security=SSPI”

Set CnnSql=CreateObject(“ADODB.Connection”)
CnnSql.Open Str_Connect

If CnnSql.State = 1 Then
msgbox “done”
else
msgbox “not done”
End if

Connecting to Oracle

Dim Str_Connection

strDriver={Microsoft ODBC for oracle}
strServer=”server”
strUserID=”scott”
strPassword=”tiger”

Str_Connection = “DRIVER=” & strDriver & “;SERVER=” & strServer & “;UID=” & strUserID & “;PWD=” & strPassword
set con = createobject(“adodb.connection”)

con.open strConnectionString
If con.State = 1 Then
msgbox “done”
else
msgbox “not done”
End if