Aurelius 2.3 - SQLite Nullable<integer> bug

I'm trying to access a Sq-lite table with some nullable string fields and one nullable integer field. The SQL script, database and datamodel classes are all configured accordingly and seem okay. However, when my application starts and reads the database i get the message that the database field could not be converted to the object. The integer field is kept null but some string values are filled.

If i debug Aurelius i notice that internally the variant is of type tkUString and not tkInteger. Not sure if that is the issue. If i fill 0 in the integer field in the database all is running fine. I fixed this temporary by setting the variant value to 0 if it is a integer column and ''.

Without the fix it means that with Sq-lite you can not have integer database fields that are nullable.


I found the problem, and the solution :-)

In the unit Aurelius.Drivers.SQLite.Classes the following function is used to determine the data type of the columns in the database:  

 sqlite3_column_type: function(stmt: pointer; col: integer): integer; cdecl;

This is done in the unit Aurelius.Drivers.SQLite.Classes:
function TSqliteStatement.ColumnType(i: integer): TSQLiteFieldType;

This is not the correct way to get the column type from an SQLite dataset because the type it returns is dynamically determined by the SQLite sql parser. The parser causes a integer null field to report as an text null field (because it is nullable).

The solution is to declare:
  sqlite3_column_decltype: function(stmt: pointer; col: integer): PAnsiChar; cdecl;

This function returns the actual column type as a string. If we convert that to the type that Aurelius uses internally the issue is fixed.

My fix is quick and dirty at the moment, if you wish i can post the code changes here.


Can you provide the code you used to raise the exception? Even better, if you are using SQLite, can you send me the project that reproduces the problem, as it would be easy to run and debug it here. We have a big set of unit tests and we didn't fall into such situations. Using sqlite3_column_decltype is not an option since it can be any arbitrary type.

My apologies for the late reply. Eventualy the problem was not related to Aurelius or SQLite. It was caused by a tool used to create the SQLite database that caused some corruption or invalid structure in the DB file itself.