Topic: QTP use of Database Functions

Using Database Functions
The code below contains a set of useful functions that can be used in QuickTest Professional. 'Example of how to use functions.

''******************************************************************************************

' Example of how to use DSN created for the database of sample Flight application.

''******************************************************************************************

SQL="SELECT * FROM ORDERS"

connection_string="QT_Flight32"

isConnected = db_connect ( curConnection ,connection_string )

If isConnected = 0 then

       ' Execute the basic SQL statement

       set myrs=db_execute_query( curConnection , SQL )

       ' Report the query and the connection string

       Reporter.ReportEvent micInfo ,"Executed query and created recordset ","Connection_string is ==> " & connection_string & " SQL query is ===> " & SQL

       ' Show the number of rows in the table using a record set

       msgBox " Quantity of rows in queried DB ( db_get_rows_count )==> " & db_get_rows_count( myrs )

       ' Show the number of rows in the table using a new SQL statement

       msgBox " Quantity of rows in queried DB (db_get_rows_count_SQL ) ==> " & db_get_rows_count_SQL( curConnection , "SELECT COUNT(*) FROM ORDERS" )

       ' Change a value of a field in an existing row

       rc = db_set_field_value (curConnection, "ORDERS" , "Agents_Name" , "test", "Agents_Name", "AGENT_TESTER")

       ' Examples of how to retrieve values from the table

       msgBox "val row 0 col 0: " & db_get_field_value( myrs , 0 , 0 )

       msgBox "val row 0 col 1: " & db_get_field_value( myrs , 0 , 1 )

       msgBox "val row 1 col Name: " & db_get_field_value( myrs , 1 , "Agents_Name" )

       msgBox "val SQL row 1 col Name: " & db_get_field_value_SQL( curConnection , "ORDERS" , 1 , "Agents_Name" )

       db_disconnect curConnection

End If

''******************************************************************************************

' Database Functions library

''******************************************************************************************

'db_connect

' ---------------

' The function creates a new connection session to a database.

' curSession - The session name (string)

' connection_string - A connection string

' for example the connection_string can be "DSN=SQLServer_Source;UID=SA;PWD=abc123"

''******************************************************************************************

Function db_connect( byRef curSession ,connection_string)

       dim connection

       on error Resume next

       ' Opening connection

       set connection = CreateObject("ADODB.Connection")

       If Err.Number <> 0 then

              db_connect= "Error # " & CStr(Err.Number) & " " & Err.Description

              err.clear

              Exit Function

       End If

       connection.Open connection_string

       If Err.Number <> 0 then

              db_connect= "Error # " & CStr(Err.Number) & " " & Err.Description

              err.clear

              Exit Function

       End If

       set curSession=connection

       db_connect=0

End Function

''******************************************************************************************

' db_disconnect

' ---------------------

' The function disconnects from the database and deletes the session.

' curSession - the session name (string)

''******************************************************************************************

Function db_disconnect( byRef curSession )

       curSession.close

       set curSession = Nothing

End Function

''******************************************************************************************

' db_execute_query

' ---------------------------

' The function executes an SQL statement.

' Note that a db_connect for (arg1) must be called before this function

' curSession - The session name (string)

' SQL - An SQL statement

''******************************************************************************************

Function db_execute_query ( byRef curSession , SQL)

       set rs = curSession.Execute( SQL )

       set db_execute_query = rs

End Function

''******************************************************************************************

' db_get_rows_count

' ----------------------------

' The function returns the number of rows in the record set

' curRS - Variable, containing a record set, that contains all values that retrieved from the database by query execution

''******************************************************************************************

Function db_get_rows_count( byRef curRS )

       dim rows

       rows = 0

       curRS.MoveFirst

       Do Until curRS.EOF

              rows = rows+1

              curRS.MoveNext

       Loop

       db_get_rows_count = rows

End Function

''******************************************************************************************

' db_get_rows_count_SQL

' ------------------------------------

' The function returns the number of rows that are the result of a given SQL statement

' curSession - The session name (string)

' CountSQL - SQL statement

''******************************************************************************************

Function db_get_rows_count_SQL( byRef curSession ,CountSQL )

       dim cur_rs

       set cur_rs = curSession.Execute( CountSQL )

       db_get_rows_count_SQL = cur_rs.fields(0).value

End Function

''******************************************************************************************

' db_get_field_value_SQL

' -----------------------------------

' curSession - Variable that denotes the current active connection

' tableName - Name of the table, from which the value should be retrieved

' rowIndex - Row number

' colName - The column name

''******************************************************************************************

Function db_get_field_value_SQL( curSession , tableName , rowIndex , colName )

       dim rs

       SQL = " select " & colName & " from " & tableName

       set rs = curSession.Execute( SQL )

       rs.move rowIndex

       db_get_field_value_SQL = rs.fields(colName).value

End Function

''******************************************************************************************

' db_get_field_value

' --------------------------

' The function returns the value of a single item of an executed query.

' Note that a db_execute_query for (arg1) must called before this function

' curRecordSet - Variable, containing a record set, that contains all values retrieved from the database by query execution

' rowIndex - The row index number (zero-based)

' colIndex - The column index number (zero-based) or the column name.

' returned values

' -1 - Requested field index more than exists more than once in record set

''******************************************************************************************

Function db_get_field_value( curRecordSet , rowIndex , colIndex )

       dim curRow

       curRecordSet.MoveFirst

       count_fields = curRecordSet.fields.count-1

       If ( TypeName(colIndex)<> "String" ) and ( count_fields < colIndex ) then

              db_get_field_value = -1 'requested field index exists more than once in recordset

       Else

              curRecordSet.Move rowIndex

              db_get_field_value = curRecordSet.fields(colIndex).Value

       End If

End Function

''******************************************************************************************

' db_set_field_value

' ---------------------------

' The function changes the value of a field according to a search criteria.

' We search for a certain row according to a column name and the desired vale, then we change a value in that row according

' to a desired columns

' curConnection - The session name (string)

' tableName - Name of the table from which the value should be retrieved

' colFind - The column which to search for the criteria

' colFindValue - The value for which to search in the column

' colChange - The column in which we want to change the value

' colChangeValue - The new value

' returned values

' -1 - Requested field index that does not exist in the recordset

''******************************************************************************************

Function db_set_field_value(curConnection, tableName , colFind , colFindValue, colChange, colChangeValue)

       dim curRow

       dim updateSQL

       dim checkSQL

       checkSQL = "select * from Details"

       set myrs1 = db_execute_query( curConnection , SQL )

       myrs1.MoveFirst

       count_fields = myrs1.fields.count

       If ( TypeName(colFind)<> "String" ) or ( TypeName(colChange)<> "String" ) then

              db_set_field_value = -1 'requested field index that does not exists in the record set

       Else

              updateSQL = "UPDATE " & tableName & " SET " & colChange & "='" & colChangeValue & "' WHERE " & colFind & "='" & colFindValue & "'"

              set myrs1 = db_execute_query( curConnection , updateSQL )

              db_set_field_value = 1 'operation suceeded

       End If

End Function

''******************************************************************************************

' db_add_row

' -----------------

' The function adds a new row to the desired table

' curConnection - Variable, containing a recordset, that contains all the values to be retrieved from the database by query execution

' tableName - Name of the table, from which the value should be retrieved

' values - Array that contains values to be entered in a new row to the table

' Note: The function must receive values for all the columns in the table.

' returned value.

' -1 - The number of values to be entered to the table does not match the number of columns

' 1 - Execution of the query succeed and the data was entered to the table

''******************************************************************************************

Function db_add_row(curConnection, tableName , byRef values)

       dim i

       dim updateSQL

       dim myrs1

       updateSQL = "INSERT INTO " & tableName & " VALUES ("

       arrLen = UBound (values) - LBound (values) + 1

       set myrs1=db_execute_query( curConnection , SQL )

       myrs1.MoveFirst

       count_fields = myrs1.fields.count

       ' Check whether the number of values match the number of columns

       If arrLen <> count_fields then

              db_add_row = -1

       Else

              For i = 0 to arrLen-1

                     updateSQL = updateSQL & values (i)

                     If i <> arrLen-1 then

                            updateSQL = updateSQL & ","

                     End If

              Next

              updateSQL = updateSQL & ")"

              set myrs1 = db_execute_query( curConnection , updateSQL )

              db_add_row = 1

       End If

End Function

''******************************************************************************************

' represent_values_of_RecordSet

' ---------------------------------------------

' The function reports all the values of fields in a record set

' curRS - Variable, containing the recordset, that contains all the values that were retrieved from the database by the query execution

''******************************************************************************************

Function represent_values_of_RecordSet( myrs)

       dim curRowString

       myrs.MoveFirst

       reporter.ReportEvent 4,"Fields quantity" , myrs.fields.count

       count_fields = myrs.fields.count-1

       curRow=0

       Do Until myrs.EOF

              curRowString= ""

              curRow = curRow+1

              For ii=0 to count_fields

                     curRowString = curRowString& "Field " &"==> " & myrs.fields(ii).Name &" : Value ==>" & myrs.fields(ii).Value & vbCrLf

              Next

              myrs.MoveNext

              reporter.ReportEvent 4,"Current row"& curRow , curRowString

       Loop

End Function