Bugs item #2421761, was opened at 2008-12-12 16:27 Message generated for change (Comment added) made by nat__ You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=512945&aid=2421761&group_id=65979
Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: None Group: None Status: Open Resolution: None Priority: 5 Private: No Submitted By: Nathan Catlow (nat__) Assigned to: Nobody/Anonymous (nobody) Summary: sqlite3 - primary index type confusion Initial Comment: libdbi thinks primary index is an INTEGER and returns a long. Documentation states "Do not forget to use dbi_result_get_longlong() or dbi_result_get_ulonglong to retrieve values from columns declared as INTEGER PRIMARY KEY." I think sqlite has changed/doesn't work, looking briefly at dbd_sqlite3.c it seems to rely on PRAGMA table_info() and look for "INTEGER PRIMARY KEY". sqlite seems to only return INTEGER. $ sqlite3 testdb SQLite version 3.6.2 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE test(id INTEGER PRIMARY KEY, int INTEGER, bigint BIGINT); sqlite> PRAGMA table_info(test); 0|id|INTEGER|0||1 1|int|INTEGER|0||0 2|bigint|BIGINT|0||0 So dbd_sqlite3.c (_get_row_data) ends up setting data->d_long (line dbd_sqlite3.c:1549). But the call to dbi_get_ulonglong untimately returns from dbi_result_get_longlong_idx (from libdbi) which returns data->d_longlong (dbi_result.c:1004). This is a union (dbi_data_u). On little endian this fudges though, but on big endian this is incorrect. Getting a primary key of decimal 1 from a database; Little Endian: <--d_longlong---------> <--d_long-> 01 00 00 00 00 00 00 00 = decimal 1 Big Endian: <--d_longlong---------> <--d_long-> 00 00 00 01 00 00 00 00 = decimal 4294967296 I'm not sure of the fix if you can't get sqlite to tell you what the primary key field is properly. maybe an api added to libdbi, dbi_result_get_primarykey() which ensures a long long. (this would only be needed for databases like sqlite) but would be still compatible for code moving to mysql for example. regards, nat ---------------------------------------------------------------------- >Comment By: Nathan Catlow (nat__) Date: 2008-12-14 16:23 Message: Ok, I've raised a ticket with sqlite. sqlite doesn't support an autoincrement BIGINT primary key. Which is ridiculous as it really is ROWID which is 64bit. So i am back to square one :( http://www.sqlite.org/cvstrac/tktview?tn=3540 ---------------------------------------------------------------------- Comment By: Nathan Catlow (nat__) Date: 2008-12-14 10:47 Message: Argh, error. By defining an INTEGER PRIMARY KEY, I have in fact made it an INTEGER. Not really sure what sqlite does here, probably ignores the type and continues to use a 64bit number. So after two days chasing down a bug that in theory doesn't exist, annoying! Perhaps to use libdbi in the way it was intended (to seamlessly move from one db engine to another) the documentation should reflect that primary keys in sqlite should be explicitly defined as bigint; CREATE TABLE test(id BIGINT PRIMARY KEY) to allow get_ulonglong to work properly. I learned; dbi_result_get_ulonglong doesn't ensure you get a long long, but will only fail on big endian processors. Most of find_result_field_types can be replaced by calling; http://www.sqlite.org/c3ref/column_decltype.html which returns the column types from an sql statement. If sqlite is compiled with -DSQLITE_ENABLE_COLUMN_METADATA, you can use the API to return the types (but this is possibly not what libdbi needs) but interesting; http://www.sqlite.org/c3ref/column_database_name.html Please update your documentation so nobody else experiences this pain. Thanks for libdbi it's working well. regards, nat ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=512945&aid=2421761&group_id=65979 ------------------------------------------------------------------------------ SF.Net email is Sponsored by MIX09, March 18-20, 2009 in Las Vegas, Nevada. The future of the web can't happen without you. Join us at MIX09 to help pave the way to the Next Web now. Learn more and register at http://ad.doubleclick.net/clk;208669438;13503038;i?http://2009.visitmix.com/ _______________________________________________ Libdbi-drivers-devel mailing list Libdbi-drivers-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-drivers-devel
