ODBC internal conversion produces garbage

[Wolfram Nücker]

Hello,

While trying to load seemingly innocent data via ODBC I came across a strange bug. We
store data in binary form (IEEE doubles, various integers, strings) and directly load the
without "digitizing" them to decimal values in between. We isolated the problem to a value
that looks like 10., but was off by the least significant bit (binary 0x4023ffffffffffff,
9.999..).

The problem is:

Feeding certain values for sql input parameter of type SQL_DOUBLE / SQL_C_DOUBLE result in
error message "[SAP AG][LIBSQLOD SO][SAP DB] General error;-3018 POS(1) Invalid numeric
parameter". 
These occurs for values that are very near to the next decimal wrap, for example
9.999999999999999 (10.-1.e-15)

I suspect that internal conversion in ODBC driver sometimes rounds up and sometimes down
and so gets the internal vdn representation wrong (which the server then rejects as
invalid).

Seen with the distributed Linux ODBC driver (7.3.0.18), but not with the corresponding NT
driver (same server under Linux). 
Server is 7.3.0.18 as well.

Example was compiled under gcc 2.95.3 with GLIBC 2.2.2 on x86 Linux System with Kernel
2.4.10.

Sorry, no Kernel Diag / Trace Files.
(where can I find the slow variants of libsqlod.so to get client traces?).


regards

Wolf Nuecker


attached is a rudimentary test program that tries to execute 
"SELECT 0. + ? INTO ? FROM DUAL" for problematic values.

Output from testprogram tst_inv_num:
 dbl_in = 9.999999999999998223643160599750e+00
 [SAP AG][LIBSQLOD SO][SAP DB] General error;-3018 POS(1) Invalid numeric  parameter.
 delta = 9.765624999999999803580543254449e-16
 dbl_in = 9.999999999999998223643160599750e+00
 [SAP AG][LIBSQLOD SO][SAP DB] General error;-3018 POS(1) Invalid numeric parameter.
 delta = 4.882812499999999901790271627225e-16
 dbl_in = 1.000000000000000000000000000000e+01
 dbl_out = 1.000000000000000000000000000000e+01
[...]
 dbl_in = 9.999999999999998578914528479800e+01
 [SAP AG][LIBSQLOD SO][SAP DB] General error;-3018 POS(1) Invalid numeric parameter.
 delta = 9.765625000000000986871901085967e-15
 dbl_in = 9.999999999999998578914528479800e+01
 [SAP AG][LIBSQLOD SO][SAP DB] General error;-3018 POS(1) Invalid numeric parameter
 delta = 4.882812500000000493435950542984e-15
 dbl_in = 1.000000000000000000000000000000e+02
 dbl_out = 1.000000000000000000000000000000e+02
[..]
 dbl_in = 9.999999999999998863131622783840e+02
 [SAP AG][LIBSQLOD SO][SAP DB] General error;-3018 POS(1) Invalid numeric parameter


______________________________________________________________________________
Die schönsten Ski-Regionen der Alpen - jetzt bei Ferienklick.de
http://ferienklick.de/ski/?PP=2-5-100-105-38

#include <WINDOWS.H>
#include <sql.h>
#include <sqlext.h>
#include <stdio.h>


void printSQLErr( SQLRETURN res, SQLHENV henv, SQLHDBC hdbc, SQLHSTMT hstmt )
{
	char szErrMsg[512];
	SQLCHAR sqlstat[20];
	SQLINTEGER native;
	SQLSMALLINT pcbErrMsg;

	SQLError( henv, hdbc, hstmt, sqlstat, &native, szErrMsg, sizeof(szErrMsg), &pcbErrMsg);
	printf("%s\n", szErrMsg );
}

int main(int argc, char *argv[])
{
	SQLRETURN res;
	SQLHENV henv;
	SQLHDBC hdbc;
	SQLHSTMT hstmt;

	SQLCHAR syskey[16+1];
	SQLINTEGER ind_syskey = SQL_NULL_DATA;

	SQLCHAR dummy[16+1];
	SQLINTEGER ind_dummy = SQL_NULL_DATA;
	
	if( argc < 4 )
	{
		printf("%s: DSN USR PWD\n", argv[0]);
		return 1;
	}
	
	res = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv );
	res = SQLSetEnvAttr( henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
	res = SQLAllocHandle( SQL_HANDLE_DBC, henv, &hdbc );
	res = SQLConnect( hdbc, argv[1], SQL_NTS, argv[2], SQL_NTS, argv[3], SQL_NTS );
	if( !SQL_SUCCEEDED( res ) )
	{
		printf("%s: could not connect to %s\n", argv[0], argv[1] );
		return 1;
	}


	res = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt );

	{ 
	double base, delta;
	
	double dbl_in;
	SQLINTEGER ind_dbl_in;

	double dbl_out = 0.;
	SQLINTEGER ind_dbl_out;

	char *sql_check_num = "select 0. + ? INTO ? FROM dual";

	ind_dbl_in = sizeof(dbl_in);

	res = SQLPrepare( hstmt, sql_check_num, SQL_NTS );
	res = SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, 
		SQL_C_DOUBLE, SQL_DOUBLE, 0, 0, &dbl_in, 
		sizeof(dbl_in), &ind_dbl_in ); 
	res = SQLBindParameter( hstmt, 2, SQL_PARAM_OUTPUT, 
		SQL_C_DOUBLE, SQL_DOUBLE, 0, 0, &dbl_out, 
		sizeof(dbl_out), &ind_dbl_out ); 
		
	for( base = 1.; base <= 1000.; base *= 10. )
	{
		for( delta = base * 1.e-13; base - delta < base ; delta/=2. )
		{
			dbl_in = base  - delta;
			dbl_out = 0.;
			printf( "delta = %20.30e\n", delta );
			printf( "dbl_in = %20.30e\n", dbl_in );
			res = SQLExecute( hstmt );

			if( !SQL_SUCCEEDED( res ) )
				printSQLErr( res, henv, hdbc, hstmt );
			else
				printf("dbl_out = %20.30e\n", dbl_out );
		}
	}

	}
	res = SQLFreeHandle( SQL_HANDLE_STMT, hstmt );
	res = SQLFreeHandle( SQL_HANDLE_ENV, hdbc );
	res = SQLFreeHandle( SQL_HANDLE_DBC, henv );
	
	return res;
}