Intellipro - Automating QTP Test Automation - Downloads - SQL - Get Rows

SQL Function - Get Rows

This function retrieves rows from a database and loads then into an array.

For simplicity it requires a named odbc connection and only retrieves a single field.

Download it Here (right-click and then save target as).

(NOTE - you can just cut & paste from below, but the formatting will have multiple spaces instead of tabs).

'=========================================================================

Function zSql_GetRows(sDsn,sSql,sField)

'Usage:
'    Dim sDsn, sSql, sField, aRows, iRows, iRowIndex
'
'    sDsn = ""
'    sSql = ""
'    sField = ""
'
'    aRows = zSql_GetRows(sDsn,sSql,sField)
'
'    iRows = uBound(aRows)
'
'    If iRows = 0 Then
'        Msgbox "Nothing Found"
'    Else
'        For iRowIndex = 1 to iRows
'            sField = aRows(iRowIndex)
'            Msgbox sField
'            Wait 1
'        Next
'    End If

    Dim oCon, oRec, iCount, aRows()

    iCount = 0

    Set oCon=CreateObject("ADODB.Connection")
    Set oRec=CreateObject("ADODB.Recordset")

    oCon.Open("DSN=" & sDsn)

    oRec.Open sSQL,oCon

    If oRec.Eof Then
        'Nothing found
        ReDim aRows(0)

    Else
        'Read thru rows

        oRec.MoveFirst

        While Not oRec.EOF

            iCount = iCount + 1

            ReDim Preserve aRows(iCount)

            aRows(iCount) = oRec.Fields(sField)

            oRec.MoveNext

        Wend

    End If

    oRec.Close
    oCon.Close

    Set oRec=Nothing
    Set oCon=Nothing

    zSql_GetRows = aRows

End Function

'=========================================================================