IT-Consultant-Forum (IT-Berater)

[erosolmi]

Hi Josè,

as you know, I'm using your ODBC lib under thinBasic with great success so far. While I'm trying to create some script examples following yours, I'm also using it for some scripts we use to automate our company IT departement. Today I had to use OdbcGetDataStringByColName function connect to MSSQL db and I had problems. At the moment I have few time to give you high details (I will give in next 2 days, sorry) but what I can say is that it seems I get back some bynary data instead of only text data.

I was on hurry to produce a script that extracts invoices lines to send to customers having special flags in their customer record so I switched using OdbcGetDataString and finish the job. But I will return on it and I will give you more details.

Just few data I do not know if it can help:
MSSQL 2000 sp3 db of 60Gb
SLQ Server on a Win2K Server on a GBit lan
Client: WinXP Sp2

Ciao
Eros

[José Roca]

 
Weird. It uses the same code that OdbcGetString:

Code:

   s = SPACE$(lMaxChars + 1)  ' Make room for the null character
   ODBC_ERROR_HRESULT = SQLGetData(hStmt, ColNumber, %SQL_C_CHAR, BYVAL STRPTR(s), LEN(s), BYVAL %NULL)
   FUNCTION = TRIM$(LEFT$(s, INSTR(s, CHR$(0)) - 1))

The only difference is that uses SQLNumResultCols and SQLDescribeCol to retrieve the number of the column. It is better to use OdbcGetString because retrieving metadata can be slow if there are many columns in the result set.

The fastest way to retrieve data is to bind the columns to variables using OdbcBindCol.

[erosolmi]

Thanks Josè.

Regarding speed, it is fine. It takes few seconds to get all invoices lines for each customer (it can vary from few hundred to many thousands per customer for about 4800 customers). And because it is a montly job, even if it takes 50/60 mins instead of 30/40 mins it doesn't matter.
I was trying to understand if OdbcGetDataStringByColName is a reliable function (something I can trust) or there can be problems.
I have the impression that when data is NULL there can be problems, but not sure, sorry. In any case I will return on it making specific tests and let you know.

Thanks a lot
Eros

[José Roca]

 
I have made modified OdbcGetDataString and OdbcGetDataStringByColName to make them more robust. Now they retrieve the length of the returned data instead of searching for a null terminator and check if the returned data is null.
 

Code:

FUNCTION OdbcGetDataString ALIAS "OdbcGetDataString" (BYVAL hStmt AS DWORD, BYVAL iCol AS INTEGER, OPTIONAL BYVAL lMaxChars AS LONG) EXPORT AS STRING
   LOCAL s AS STRING, cbLen AS LONG
   IF lMaxChars < 1 THEN lMaxChars = 256
   s = SPACE$(lMaxChars + 1)  ' Make room for the null character
   ODBC_ERROR_HRESULT = SQLGetData(hStmt, iCol, %SQL_C_CHAR, BYVAL STRPTR(s), LEN(s), cbLen)
   IF ODBC_ERROR_HRESULT = %SQL_SUCCESS OR ODBC_ERROR_HRESULT = %SQL_SUCCESS_WITH_INFO THEN
      IF cbLen <> %SQL_NULL_DATA THEN FUNCTION = LEFT$(s, cbLen)
   END IF
END FUNCTION

Code:

FUNCTION OdbcGetDataStringByColName ALIAS "OdbcGetDataStringByColName" (BYVAL hStmt AS DWORD, BYVAL ColumnName AS STRING, OPTIONAL BYVAL lMaxChars AS LONG) EXPORT AS STRING

   LOCAL cbLen AS LONG
   LOCAL NumCols AS INTEGER
   LOCAL i AS LONG
   LOCAL ColNumber AS INTEGER
   LOCAL ColName AS ASCIIZ * 255
   LOCAL s AS STRING

   IF lMaxChars < 1 THEN lMaxChars = 256
   ColName = UCASE$(TRIM$(ColumnName))
   ODBC_ERROR_HRESULT = SQLNumResultCols(hStmt, NumCols)
   IF NumCols < 1 THEN EXIT FUNCTION
   DIM ucol(1 TO Numcols) AS ODBC_COL_DESC_TYPE
   FOR i = 1 TO NumCols
      ODBC_ERROR_HRESULT = SQLDescribeCol(hStmt, i, ucol(i).clName, SIZEOF(ucol(i).clName), _
                      ucol(i).clNameLen, ucol(i).clDataType, ucol(i).clSize, _
                      ucol(i).clDecimals, ucol(i).clNullable)
      IF ODBC_ERROR_HRESULT <> %SQL_SUCCESS THEN EXIT FUNCTION
      IF UCASE$(ucol(i).clName) = ColName THEN
         ColNumber = i
         EXIT FOR
      END IF
   NEXT i
   IF ColNumber THEN
      s = SPACE$(lMaxChars + 1)  ' Make room for the null character
      ODBC_ERROR_HRESULT = SQLGetData(hStmt, ColNumber, %SQL_C_CHAR, BYVAL STRPTR(s), LEN(s), cbLen)
      IF ODBC_ERROR_HRESULT = %SQL_SUCCESS OR ODBC_ERROR_HRESULT = %SQL_SUCCESS_WITH_INFO THEN
         IF cbLen <> %SQL_NULL_DATA THEN FUNCTION = LEFT$(s, cbLen)
      END IF
   END IF

END FUNCTION

[erosolmi]

I've made some check and I think problem in on my side.