22 Working with Databases
Concept: QTP does not support database, but VBS support(ADODB) to interact with DB.
22.1 ADODB has 4 primary objects:
Connection Object : to connect to the database |
RecordSet Object: to retrieve data from a database |
Command Object: executing commands to the database, the command text is a SQL statement |
Field Object: to retrieve a special column in aRecordset object. |
22.2 Connect to a DB
Set objConnection = CreateObject("ADODB.Connection") conStr = "DSN=Inventory;UID=user;PWD=password;DATABASE=mydatabasename" objConnection.Open conStr objConnection.Close Set objConnection = Nothing |
22.3 Building Connection Strings
A connection string is one property of ADODB to connect aDB. 2 ways to get the connection string:
Method 1: get the string from the DB document (very available) |
Method 2 : use a connection string builder wizard Step1: Create a file with name “mycon.udl”, click on the file to open Data Link Properties dialog Step2: select “Provider” Tab and choose “Microsoft OLE DB Provider for ODBC Drivers”, click “Next” button. Filling detail in the Column “Use connection string”, and click “Test Connection” button Step3: If test connection successful. Open “mycon.udl” file is the connection string. |
22.4 Check the state of a connection or recordSet
Set objRecordset=CreateObject("ADODB.recordset") Set adoCon = CreateObject("ADODB.Connection") Set objRecordset.ActiveConnection = adoCon print adoCon.State print objRecordset.State set adoCon = Nothing set objRecordset = Nothing ‘1 means open; 8 means fetching; 4 means executing; 2 means connecting; 0 means closed. |
22.5How can we execute query
Method 1: Set adoCon = CreateObject("ADODB.Connection") 'connection string for the database conStr = "DSN=DSNname;UID=user;PWD=password;DATABASE=mydatabasename" adoCon.Open conStr Set adoRecordSet = CreateObject("ADODB.Recordset")
'Set the active connection to be used for the recordset Set adoRecordSet.ActiveConnection = adoCon
'Cursor location can take two values adUseClient = 3 or adUseServer = 2. adoRecordSet.CursorLocation = 3 'adUseClient adoRecordSet.CursorType = 2 'adOpenDynamic
strSQL = "Select Val1, Val2 from Table2" adoRecordSet.Source = strSQL
'Run the SQL query adoRecordSet.Open
'Loop till we are not at the end of the record |
Method 2: 'Create the command object Set adoCommand = CreateObject("ADODB.Command") strSQL = "Select Val1, Val2 from Table2" adoCommand.CommandSource = strSQL adoCommand.CommandType = 1 'adCmdText 'Run the query through the command object and get the recordset Set adoRecordSet = adoCommand.Execute
'Loop till we are not at the end of the record |
Method 3: strSQL = "Select Val1, Val2 from Table2"
Set adoRecordSet = adoCon.Execute (strSQL)
'Loop till we are not at the end of the record |
Here is the code “Loop till we are not at the end of therecord”
While not adoRecordSet.eof()
'Access the values of the fields. We can either use the name 'of the column in the query or use the index print "Val1: " & adoRecordSet.Fields("Val1").value print "Val2: " & adoRecordSet.Fields("Val2").value
'Move on to the next record adoRecordSet.MoveNext Wend |
22.6 How can we determine the number of rows altered by an update or deletequery?
Determine if the last query altered any rows. E.g. if we rana syntactically correct update query which didn’t alter any rows:
strSQL = "DELETE from Table1 where Val1 in (2,3,4)"
dim rowsAffected
'Execute the query through the connection and get the affected rows adoCon.Execute strSQL, rowsAffected
If rowsAffected = 0 then Msgbox "No rows were deleted" Else Msgbox rowsAffected & " row(s) were deleted" End if |
22.7 Exporting the results of a query to data table.
‘adoRecordSet is result set from query For each fld in adoRecordSet.Fields outSheet.AddParameter Replace(fld.name," ","_") Next
While adoRecordSet.eof() 'Loop through all the columns of the row For each fld in adoRecordSet.Fields outSheet.GetParameter(Replace(fld.name," ","_")).value = fld.value Next
'Move on to the next record adoRecordSet.MoveNext Wend |
Note: to avoid is the spaces in the column name of the datatable.
22.8 Executing a Stored DB procedure.
Stored procedures cane be executed using the Command object.
E.g. authentication procedure:
Set adoCommand = CreateObject("ADODB.Command") With adoCommand .ActiveConnection = adoCon .CommandTimeout = 30 .CommandType = 4 'adCmdStoredProc .CommandText = "Authentication" Set adoPrm =.CreateParameter("retvalue", adInteger, adParamReturnValue, 5) .Parameters.Append adoPrm
Set adoPrm =.CreateParameter("empid", adInteger, adParamInput, 5, s_UserID) .Parameters.Append adoPrm
Set adoPrm =.CreateParameter("password", adVarChar, adParamInput, 50, s_Password) .Parameters.Append adoPrm
Set adoPrm =.CreateParameter("projid", adVarChar, adParamInput, 50, s_ProjID) .Parameters.Append adoPrm
'Execute the Stored Procedure adoCmd.Execute
Select Case adoCmd.Parameters("retvalue").Value Case 0 MsgBox "Succesfully Authenticated" Case 1 MsgBox "Invalid Password" Case 2 Msgbox "Employee Not Associated to Given Project" Case Else Msgbox "Some Error Unable To Authenticate" End Select End With Set adoCommand = Nothing |