Issue with reading INT fields in SQLite 3

[Natalia]

Hello,

I ran into an issue while using OTL with SQLite3 database. When I select values from integer fields, I only get correct value for the even rows in the return set. Odd rows return value 0. I suspected it is some kind of data size mismatch between the driver and OTL and added explicit bind type ("long" for Linux 64 bit build or "bigint" for Windows 64 bit build) and it resolved the problem. According to the documentation, SQLite creates 64 bit integer fields by default. OTL seems to process the data as if it was 32 bit type. Is there any way to resolve this wihtout adding explicit bind instruction for all integer fields in all queries? For the record, we have no issues with same code running on SQL Server, so the issue is specific to SQLite3.

Here is the example demonstrating the issue:
Table:

CREATE TABLE data_type(i1 int);
INSERT INTO "data_type" VALUES (1);INSERT INTO "data_type" VALUES (2);
INSERT INTO "data_type" VALUES (3);INSERT INTO "data_type" VALUES (4);

Query in the code looks like :
otl_stream stream(100, "select i1 from data_type", connection, otl_direct_exec_select);
otl_stream_read_iterator<otl_stream, otl_exception,="" otl_lob_stream=""> rs(stream);
while(rs.next_row())
{
long val;
rs.get(1, val);
std::cout << val << " ";
}

Output: 1, 0, 3, 0

When I modify the query to be "select i1 from data_type:#1<long>" this starts working correctly and produces output "1,2,3,4"

Thanks,
Natalia

[Sergei Kuchin]

Natalia,

Can you add the following to your code to see how the SQLite ODBC driver describes the data type:

otl_column_desc* desc;
int desc_len;

desc=myotl_stream.describe_select(desc_len);

for(int n=0;n<desc_len;++n){
cout<<"========== COLUMN #"<<n+1<<" ==========="<<endl;
cout<<"name="<<desc[n].name<<endl;
cout<<"dbtype="<<desc[n].dbtype<<endl;
cout<<"otl_var_dbtype="<<desc[n].otl_var_dbtype<<endl;
cout<<"dbsize="<<desc[n].dbsize<<endl;
cout<<"scale="<<desc[n].scale<<endl;
cout<<"prec="<<desc[n].prec<<endl;
cout<<"nullok="<<desc[n].nullok<<endl;
}

You can see the whole OTL example here: http://otl.sourceforge.net/otl4_ex282.htm

Sergei

[Natalia]

I got interesting results. When I run the plain "select <intfiled> from , I get otl_var_dbtype=4. When I add explicit "long" bind type, I get otl_var_dbtype=7, which is the right value for this type I believe.

SELECT data_type_ukey from tbl
========== COLUMN #1 ===========
name=data_type_ukey
dbtype=4
otl_var_dbtype=4
dbsize=10
scale=0
prec=10
nullok=0

SELECT data_type_ukey:#1<long> from tbl
========== COLUMN #1 ===========
name=data_type_ukey
dbtype=4
otl_var_dbtype=7
dbsize=10
scale=0
prec=10
nullok=0

[Sergei Kuchin]

Something isn't right in the ODBC driver. dbtype of 4 is an int the way the driver reports the column data type and it's a signed 4-byte integer. Yet, when OTL binds an array of ints on the C++ side, the driver returns bad integer values. When the "long" override is used, everything works correctly. "long" is a signed 8-byte whole number in 64-bit Linux. "bigint" is also a signed 8-byte whole number, that's perhaps why it works in 64-bit Windows. I wonder if "int" means an signed 8-byte integer in SQLite3?

[Natalia]

According to the SQLite documentation, "... as soon as INTEGER values are read off of disk and into memory for processing, they are converted to the most general datatype (8-byte signed integer)" http://www.sqlite.org/datatype3.html
Just to clarify - you are able to reproduce the issue on your end, correct? I just want to understand if something is wrong with my specific build of odbc driver or sqlite odbc driver in general.

[Sergei Kuchin]

The ODBC driver is doing it wrong. It needs to report an 8-byte int as an "ODBC big int", not as an "ODBC int", which is 4-bytes long. It may be similar to the old debate "is ODBC long int 4-bytes or 8-bytes". I can add a conditionally compiled patch (enabled with a #define) to OTL to map "ODBC int" to "ODBC big int", if that helps.

[Natalia]

Thank you for offering to provide the patch. I'd like to understand the scope first- will setting this conditional "ODBC int" macro affect the size of integers returned by other DB drivers as well or can it be limited specifically to the SQLite? I use SQL Server, Vertica and SQLite databases in the same code and I don't want to change the way my SQL Server and Vertica integers are processed. If this macro can be limited to SQLite integers specifically, that patch would really solve my problem.

[Sergei Kuchin]

It would be a "compile time patch" enabled by a #define. Not sure if you're talking about dynamically identifying what type of database you're connecting to. If you have "database drivers" that are built with different OTL #defines, in that case you'll have to add the new #define to enable the patch. The patch would literally map "database ints" to C++ "bigints" (8-bytes). Question: do you use #define OTL_ODBC_MSSQL_2008 for SQL Server, or just plain vanilla #define OTL_ODBC for all database types?

[Natalia]

Thanks a lot, Sergei. Armed with your explanation, I checked SQLite ODBC documentation and found that there is a flag "BigInt" that can be set on the connection level. Adding this flag resolved the issue. I am adding the reference here in case anyone else has similar issues http://ch-werner.de/sqliteodbc/html/index.html
Thanks again for the help!

[Sergei Kuchin]

Isn't that something? I'll add the reference to the OTL F.A.Q.

[Natalia]

Good idea. Hopefully it will save hours of debugging to the next person who stumbles upon this issue :)