Windows API Headers News

[Haakon Birkeland]

If you are using XP, try using pOdbc = NewOdbc(%SQL_OV_ODBC3).

I have noticed the added(?) text in your source sample about this, so I'm up and running with creating the object as in the quote above.

Are you suggesting that access to the column data by it's name might not be supported prior to 3.8?

[José Roca]

No. This capability depends on the ODBC driver used. Some drivers support it and others don't. With some drivers, you can't even read by ordinal first column 4 and then column 2, for example. Once you read a column, you no longer can read columns with a lower ordinal number.

[Haakon Birkeland]

That's just insane. Seems some developers shouldn't have gotten paid for their job ...

I'm away from my file collection/documentation now, but are there functions for testing the drivers capabilities?

[José Roca]

What the class does is simple. Both the Execute and ExecDirect methods execute the statement and, if it succeeds, retrieve the column names of the result set and store them in a PowerCollection.

Code Select Expand


   ' =====================================================================================
   ' Executes a prepared statement, using the current values of the parameter marker
   ' variables if any parameter markers exist in the statement.
   ' =====================================================================================
   METHOD Execute () AS INTEGER
      LOCAL r AS INTEGER
      r = SQLExecute(m_hStmt)
      IF r = %SQL_ERROR OR r = %SQL_INVALID_HANDLE THEN
         METHOD OBJRESULT = r
         OleSetErrorInfo $IID_IOdbcStatement, "IODBCSTATEMENT." & FUNCNAME$, ME.ErrorInfo(r)
      END IF
      METHOD = r
      ' // Clear the collection of column names
      IF ISNOTHING(m_pColNames) THEN EXIT METHOD
      IF m_pColNames.Count THEN m_pColNames.Clear
      ' // Retrieve the column names
      LOCAL i AS LONG, NumCols, colNameLen AS INTEGER
      LOCAL szColName AS ASCIIZ * 255, bstrKey AS WSTRING
      IF SQL_SUCCEEDED(r) THEN
         r = SQLNumResultCols(m_hStmt, NumCols)
         IF NumCols < 1 THEN EXIT METHOD
         FOR i = 1 TO NumCols
            r = SQLColAttribute(m_hStmt, i, %SQL_DESC_NAME, szColName, SIZEOF(szColName), colNameLen, BYVAL %NULL)
            IF SQL_SUCCEEDED(r) = 0 THEN EXIT FOR
            bstrKey = UCASE$(LEFT$(szColName, colNameLen))
            m_pColNames.Add(bstrKey, i)
         NEXT
      END IF
   END METHOD
   ' =====================================================================================

   ' =====================================================================================
   ' Executes the specified statement.
   ' =====================================================================================
   METHOD ExecDirect (BYVAL SqlStr AS STRING) AS INTEGER
      LOCAL r AS INTEGER
      r = SQLExecDirect (m_hStmt, BYCOPY SqlStr, LEN(SqlStr))
      IF r = %SQL_ERROR OR r = %SQL_INVALID_HANDLE THEN
         METHOD OBJRESULT = r
         OleSetErrorInfo $IID_IOdbcStatement, "IODBCSTATEMENT." & FUNCNAME$, ME.ErrorInfo(r)
      END IF
      METHOD = r
      ' // Clear the collection of column names
      IF ISNOTHING(m_pColNames) THEN EXIT METHOD
      IF m_pColNames.Count THEN m_pColNames.Clear
      ' // Retrieve the column names
      LOCAL i AS LONG, NumCols, colNameLen AS INTEGER
      LOCAL szColName AS ASCIIZ * 255, bstrKey AS WSTRING
      IF SQL_SUCCEEDED(r) THEN
         r = SQLNumResultCols(m_hStmt, NumCols)
         IF NumCols < 1 THEN EXIT METHOD
         FOR i = 1 TO NumCols
            r = SQLColAttribute(m_hStmt, i, %SQL_DESC_NAME, szColName, SIZEOF(szColName), colNameLen, BYVAL %NULL)
            IF SQL_SUCCEEDED(r) = 0 THEN EXIT FOR
            bstrKey = UCASE$(LEFT$(szColName, colNameLen))
            m_pColNames.Add(bstrKey, i)
         NEXT
      END IF
   END METHOD
   ' =====================================================================================


GetDataString retrieves the column number from the collection and calls SQLGetData.

Code Select Expand


   ' =====================================================================================
   ' Returns the data in a specefied column as a string.
   ' =====================================================================================
   METHOD GetDataString (BYVAL vColumn AS VARIANT, OPTIONAL BYVAL lMaxChars AS LONG) AS STRING
      LOCAL r AS INTEGER, ColumnNumber AS WORD, s AS STRING, cbLen AS LONG
      IF VARIANTVT(vColumn) = %VT_BSTR THEN
         LOCAL bstrKey AS WSTRING
         bstrKey = UCASE$(VARIANT$(vColumn))
         LOCAL vCol AS VARIANT
         IF ISNOTHING(m_pColNames) THEN EXIT METHOD
         vCol = m_pColNames.Item(bstrKey)
         ColumnNumber = VARIANT#(vCol)
      ELSE
         ColumnNumber = VARIANT#(vColumn)
      END IF
      IF lMaxChars < 1 THEN lMaxChars = 256
      s = SPACE$(lMaxChars + 1)  ' Make room for the null character
      r = SQLGetData(m_hStmt, ColumnNumber, %SQL_C_CHAR, BYVAL STRPTR(s), LEN(s), cbLen)
      IF cbLen <> %SQL_NULL_DATA THEN METHOD = LEFT$(s, cbLen)
      IF r = %SQL_ERROR OR r = %SQL_INVALID_HANDLE THEN
         METHOD OBJRESULT = r
         OleSetErrorInfo $IID_IOdbcStatement, "IODBCSTATEMENT." & FUNCNAME$, ME.ErrorInfo(r)
      END IF
   END METHOD
   ' =====================================================================================


If you are using ExecDirect, check in this part of the code if the column names are being retrieved correctly.

Code Select Expand


      LOCAL szColName AS ASCIIZ * 255, bstrKey AS WSTRING
      IF SQL_SUCCEEDED(r) THEN
         r = SQLNumResultCols(m_hStmt, NumCols)
         IF NumCols < 1 THEN EXIT METHOD
         FOR i = 1 TO NumCols
            r = SQLColAttribute(m_hStmt, i, %SQL_DESC_NAME, szColName, SIZEOF(szColName), colNameLen, BYVAL %NULL)
            IF SQL_SUCCEEDED(r) = 0 THEN EXIT FOR
            bstrKey = UCASE$(LEFT$(szColName, colNameLen))
            m_pColNames.Add(bstrKey, i)
         NEXT
      END IF


and, in GetDataString, if this part of the code is retrieving the correct column number.

Code Select Expand


      IF VARIANTVT(vColumn) = %VT_BSTR THEN
         LOCAL bstrKey AS WSTRING
         bstrKey = UCASE$(VARIANT$(vColumn))
         LOCAL vCol AS VARIANT
         IF ISNOTHING(m_pColNames) THEN EXIT METHOD
         vCol = m_pColNames.Item(bstrKey)
         ColumnNumber = VARIANT#(vCol)
      ELSE
         ColumnNumber = VARIANT#(vColumn)
      END IF