Database Functions

' Example use DSN created for database of Flight sample application.1. DB Sample
  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 If2. DB_Connect
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 Function3. The function disconnects from the database and deletes the session. ' curSession - the session name (string)
Public Function db_disconnect( byRef curSession )
    curSession.close
    set curSession = Nothing
End Function4. db_execute_query
Public Function db_execute_query ( byRef curSession , SQL)
    set rs = curSession.Execute( SQL )
    set db_execute_query = rs
End Function5. db_get_rows_count
Public 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 Function6. db_get_rows_count_SQL
'ARG(s):
  'sFilePath - full path to xls file
  'sFileName - file name complete with extention
  'sSheetName - name to give the new worksheet

Public 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 Function7. db_get_field_value_SQL
Public 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 Function8. db_get_field_value
Public 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 more than exists in recordset
    Else
        curRecordSet.Move rowIndex
        db_get_field_value = curRecordSet.fields(colIndex).Value
    End If
End Function9. db_set_field_value
'ARG(s):
  '' curConnection - the session name (string)
  ' tableName - name of the table , where value should be retrieved
  ' colFind - the column we search the criteria in
  ' colFindValue - the value we search in the column
  ' colChange - the column were we want to change the value
  ' colChangeValue - the new value
  ' returned values
  ' -1 - requested field index that doesn't exists in the recordset

Public 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 doesn't 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 Function10. db_add_row
'ARG(s):
  ' curConnection - variable , contains a recordset , that contains all the values to be retrieved from DB by query execution
  ' tableName - name of the table , where value should be retrieved from
  ' 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 values
  ' -1 - the number of values to be entered to the table doesn't fit the number of columns
  ' 1 - execution of the query succeed and the data was entered to the table

Public 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 if numbers of values fit the numbers 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 Function11. represent_values_of_RecordSet
Public Function represent_values_of_RecordSet( myrs)
    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 if numbers of values fit the numbers 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 Function12. DSN - Less Connection
Public sconn
uid = "UID"
pwd = "PWD"
ServerName = "Server Name"
Call Connection()

Function Connection()
Set sconn = CreateObject("ADODB.connection")
sconn.Open "Driver=Microsoft ODBC for Oracle;Server="& ServerName &";Uid="& uid &";Pwd="& pwd &""
End Function' Oracle Data Base Functions1. DB Connection
Public sconn
uid = "UID"
pwd = "PWD"
ServerName = "Server Name"

Function Connection()
Set sconn = CreateObject("ADODB.connection")
sconn.Open "Driver=Microsoft ODBC for Oracle;Server="& ServerName &";Uid="& uid &";Pwd="& pwd &""
End Function2. DB Execute Query

Function ExecuteSingleValueQuery(sSQL)
Set sResultSet = sconn.execute(sSQL)
ExecuteSingleValueQuery = trim(sResultSet(0))
End Function3. Get Multiple Data

Function ExecuteMultiValueQuery(sSQL)
Set sResultSet = sconn.execute(sSQL)
ivalue = 0
while not sResultSet.EOF
Environment("sResultSet"&ivalue)= trim(sResultSet(0))
ivalue = ivalue +1
sResultSet.movenext
wend
ExecuteMultiValueQuery = ivalue
End Function4. Close Connection

Function dbConnectionClose()
sconn.Close
End Function' Common Data Base Steps'DECLARE AND INITIALZE
dim oRst, oConn, strSql

'CREATE THE CONNECTION AND RECORDSET OBJECT
set oConn = createobject("ADODB.Connection")
set oRst = createobject("ADODB.Recordset")

'DEFINE THE SQL STRING
strSql = "select * from every_project.bug where bg_bug_id = 100"
'An example sql string: project_name -> every_project
' table_name -> bug
' criteria -> bg_bug_id equals 100

'OPEN DSN-LESS CONNECTION
'FOR NEW ORACLE DRIVERS USE THIS
oConn.Open "Driver={Microsoft ODBC for Oracle};"
"server=;" & _
"uid=;" & _
"pwd=;"

'FOR OLD ORACLE DRIVERS USE THIS
oConn.Open "Driver={Microsoft ODBC Drivers for Oracle};" & _ "server=;" & _
"uid=;" & _
"pwd=;"

'OR TO OPEN A DSN BASED CONNECTION
oConn.Open "dsn=;" & _
"uid=;" & _
"pwd=;"

'PREPARE, EXECUTE QUERY AND FETCH DATA
oRst.Open strSql, oConn, 3

'WORK ON THE FETCHED DATA
msgbox oRst.GetString
'Or One can loop through all the records in recordset
'do while not oRst.EOF
' Reporter.ReportEvent 1, "Custom Step", oRst("bg_bug_id")
' oRst.MoveNext
'loop

'CLOSE AND CLEAN OBJECTS
oRst.Close
oConn.Close' Use Xls sheet as database and query the values and use it in QTP1. Sample Code
DataFilePath = "C:\Automation\Data\TestData.xls"
sSQL ="SELECT AWBNumber FROM [sheet Name$] where EBID = 1"
Set Con = CreateObject("ADODB.Connection")
StrCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source= " & DataFilePath & ";" & _ "Extended Properties=Excel 8.0;"
Con.Connectionstring= StrCon
Con.open
Set sResultSet = Con.execute(sSQL)
msgbox trim(sResultSet(0))