（转）QTP_Database Functions library

'Example of How to use functions.

''************************************************************************************
' Example use DSN created for database of Flight sample 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.Descrīption
        err.clear
        Exit Function
    End If
 
    connection.Open connection_string
    If Err.Number <> 0 then
        db_connect= "Error # " & CStr(Err.Number) & " " & Err.Descrīption
        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 , contain record set , that contain all values that retrieved from DB 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 denote current active connection
' tableName - name of the table , where 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 , contain record set , that contain all values that retrieved from DB 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 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 more than exists 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 , 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
'*************************************************************************************************
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 Function

'*************************************************************************************************
' db_add_row
' -----------------
' The function adds a new row to the desired table

' 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
'*************************************************************************************************
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 Function

'*************************************************************************************************
' represent_values_of_RecordSet
' ---------------------------------------------
' the function reports all the values on fields in a record set
' curRS - variable , contains the recordset , that contains all the values that were retrieved from the DB 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