SQLBindCol for long integer value on 64Bit platform?

Question

I want to query data from an oracle db with unixodbc which has an NUMBER(19) column. So this must fit in an long under 64 bit. But i dont know the right type for the TargetType parameter. With type SQL_C_LONG im getting only positive values right, that are smaller then INT_MAX (2147483647). Negative values causing an overflow.

With type SQL_C_SBIGINT i'm getting error

HY004:1:0:[Oracle][ODBC]Invalid SQL data type <-25>

So how can i query values into an long the right way?

Operating system: debian Debian 5.10 64bit

unixODBC: 2.3.6

oracle odbc driver version: 19.1

example code:

#include <stdio.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h>

void extract_error( SQLHANDLE handle, SQLSMALLINT type )
{

    SQLCHAR  odbc_error_text[SQL_MAX_MESSAGE_LENGTH  + 1];

    SQLINTEGER      i = 0;
    SQLINTEGER      native = 0;
    SQLCHAR         state[ SQL_SQLSTATE_SIZE + 1 ];
    SQLSMALLINT     len = 0;
    SQLRETURN       ret;


    memset( &odbc_error_text, 0, sizeof(odbc_error_text) );

    while( (ret = SQLGetDiagRec( type, handle, ++i, state,
                    &native, odbc_error_text,
                    sizeof(odbc_error_text), &len )) == SQL_SUCCESS  )
    {
        fprintf( stderr, "%s:%d:%d:%s\n", state, i, native, odbc_error_text );
        
    }

    if( ret == SQL_INVALID_HANDLE )
    {
        fprintf( stderr, "Invalid Handle!\n" );
    }

    return;

}

int main(int argc, char **argv) 
{
    SQLHENV env;
    SQLHDBC dbc;
    SQLHSTMT stmt;
    
    SQLSMALLINT columns;

    SQLLEN indicator;
    
    SQLCHAR        ColumnName[512];
    SQLSMALLINT    ColumnNameLen;
    SQLSMALLINT    ColumnDataType;
    SQLULEN        ColumnDataSize;
    SQLSMALLINT    ColumnDataDigits;
    SQLSMALLINT    ColumnDataNullable;
    SQLCHAR *      ColumnData;
    SQLLEN         ColumnDataLen;
    
    int result_int;
    long result_long;
    

    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
    
    SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

    if( !SQL_SUCCEEDED(SQLDriverConnect(dbc,
                    NULL,
                    "DSN=oracle;uid=TEST;pwd=TEST;", SQL_NTS,
                    NULL, 0, 
                    NULL, SQL_DRIVER_COMPLETE)) )
    {
        fprintf(stderr, "Connection error!\n");
        extract_error( dbc, SQL_HANDLE_DBC );
        goto exit;
    }

    SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
    SQLPrepare( stmt, "SELECT mylong FROM testtab WHERE id = 3", SQL_NTS);
    
    SQLDescribeCol( stmt,                    // Select Statement (Prepared)
                    1,                     // Columnn Number
                    ColumnName,            // Column Name (returned)
                    512,                // size of Column Name buffer
                    &ColumnNameLen,        // Actual size of column name
                    &ColumnDataType,       // SQL Data type of column
                    &ColumnDataSize,       // Data size of column in table
                    &ColumnDataDigits,     // Number of decimal digits
                    &ColumnDataNullable);  // Whether column nullable

    printf("Column Name : %s\n  Column Name Len : %i\n  SQL Data Type : %i\n  Data Size : %i\n  DecimalDigits : %i\n  Nullable %i\n",
                 ColumnName, 
                 (int)ColumnNameLen, 
                 (int)ColumnDataType,
                 (int)ColumnDataSize,
                 (int)ColumnDataDigits,
                 (int)ColumnDataNullable);


    
    if(! SQL_SUCCEEDED(SQLBindCol(stmt, 1,
            SQL_C_LONG,
            &result_long,
            (SQLLEN)NULL, 
            &indicator)) )
    {
        extract_error( stmt, SQL_HANDLE_STMT );
        goto exit;
    }
            
    if(! SQL_SUCCEEDED(SQLExecute(stmt)))
    {
        extract_error( stmt, SQL_HANDLE_STMT );
        goto exit;
    }


    while(SQL_SUCCEEDED(SQLFetch(stmt)))
    {
        printf("SizeIndicator: %d, Result: %ld\n", (int)indicator, result_long);
    }
    else
    {
        fprintf(stderr, "Error fetching data\n" );
        extract_error( stmt, SQL_HANDLE_STMT );
    }
    
exit:
    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
    
    SQLDisconnect(dbc);
    SQLFreeHandle(SQL_HANDLE_DBC, dbc);
    
    SQLFreeHandle(SQL_HANDLE_ENV, env);
    
    return 0;
}

Content of testtab:

0   -58
1   -9.223.372.036.854.775.807
2   42
3   9.223.372.036.854.775.807

Output:


SizeIndicator: 4, Result: 4294967238
SizeIndicator: 0, Result: 2147483648
SizeIndicator: 4, Result: 42
SizeIndicator: 0, Result: 2147483647

Jay Makhiija · Accepted Answer · 2025-02-17 11:18:33Z

2

+50

For UnixODBC and Oracle ODBC, to get long 64 bit value the proper approch will be to fetch the value as a string.

Use SQL_C_CHAR Then you can convert the value in long long (64 bit) using strtoll.

This will also work for negative numbers.

answered Feb 17 at 11:18

Jay Makhiija

1366 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marco Feb 17 at 15:59

I have seen this solution in an older post but i hoped there would be a better solution now. How big the char buffer must be for the char result to fit in? 20 characters (19 + sign) +1 for terminator should be right?

Jay Makhiija Feb 17 at 21:43

Yes, 21 should be fine. Also you can try using SQL_C_NUMERIC, but you will need to correctly define the SQL_NUMERIC_STRUCT to store and interpret the values.

marc_s · Accepted Answer · 2025-02-26 08:48:02Z

Oracle's ODBC driver doesn't support binding a NUMBER(19) column directly to a 64-bit C integer using SQL_C_SBIGINT. You end up with that HY004:Invalid SQL data type <-25> error because the Oracle driver simply doesn't implement that TargetType.

You should fetch as a string (SQL_C_CHAR), parse the string in your C code into a 64-bit integer (e.g. long long or int64_t) using something like strtoll().

Even though ODBC 3.0+ defines SQL_C_SBIGINT, many drivers (including certain versions of Oracle's) still do not support it.

The fallback is to treat the column as a decimal string (since NUMBER(19) can exceed 32-bit range), then manually convert it.

Use SQL_C_CHAR instead of SQL_C_LONG. You'll need a buffer big enough to hold up to 19-20 digits, plus the sign and the null terminator, so think at least 22-23 bytes.

#define BIGINT_STR_LEN 32sign/terminator

// ...
char result_str[BIGINT_STR_LEN];
SQLLEN indicator = 0;

SQLRETURN rc = SQLBindCol(stmt, 1, SQL_C_CHAR, result_str, BIGINT_STR_LEN, &indicator);
if (!SQL_SUCCEEDED(rc)) {
    extract_error(stmt, SQL_HANDLE_STMT);
    goto exit;
}

rc = SQLExecute(stmt);
if (!SQL_SUCCEEDED(rc)) {
    extract_error(stmt, SQL_HANDLE_STMT);
    goto exit;
}

while (SQL_SUCCEEDED(rc = SQLFetch(stmt))) {
    if (indicator == SQL_NULL_DATA) {
        printf("Column is NULL.\n");
        continue;
    }
    long long val = strtoll(result_str, NULL, 10);
    printf("Raw string: %s -> 64-bit: %lld\n", result_str, val);
}

In theory, you could use SQL_C_NUMERIC, but you still have to convert the numeric structure manually. Correctly unpacking the SQL_NUMERIC_STRUCT is more cumberstone than it is to parse a plain string.

Some people attempt casting the data in the query itself, for example:

SELECT CAST(mylong AS NUMBER(10)) FROM testtab

But that only works if you know for sure the column fits into 32-bit. If you truly need 64-bit range, that doesn't help.

So, in conclusion, the most portable, driver friendly solution is to just use a character buffer large enough for all possible digits, bind with SQL_C_CHAR, and do the string to long long conversion on the client side.

Collectives™ on Stack Overflow

SQLBindCol for long integer value on 64Bit platform?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related