Troubleshooting - How to convert Bit to Integer using QODBC

Problem Description:

I am trying to get our ODBC extension module interface working with QODBC. Everything seems to work fine except trying to read SQL_BIT type fields. It causes the Script BASIC interface to error out.

I searched on Google about the issue, and others (Oracle, ...) have a similar problem. Do you have any way to translate the SQL_BIT type to a numeric or a CHAR(1)?

Solutions:

You can convert the Bit field to an integer using the below query.

For Example:

In the Customer table, the IsActive field is of Bit datatype. We will convert it to numeric/integer using the below query:

SELECT ListID, {fn CONVERT("IsActive", SQL_Integer)} AS "IsActive" FROM Customer

[Script BASIC Code]

IMPORT odbc. by

dbh = odbc::RealConnect("QuickBooks Data","","")

odbc::query(dbh,"SELECT FullName, AccountNumber, {fn CONVERT(\"IsActive \", SQL_Integer)} AS \"IsActive\", Balance FROM Account")

FOR x = 1 to 5

odbc::FetchHash(dbh,dbcol)

PRINT Left(dbcol{"FullName"} & STRING(50," "),50),"\t",dbcol{"AccountNumber"},"\t",dbcol{"IsActive"},"\t",FORMAT("% ~-###,###.00~",dbcol{"Balance"}),"\n" NEXT

odbc::Close(dbh)