CRecordset fails on adding a new row using the free ODBC driver for SQLite

Question

I have an C++ application using MFC CRecordset to add rows to a table of an SQLite database, using the free ODBC driver by Ch. Werner. For this, I use the usual sequence of rs.Open(), rs.AddNew(), set values, and finally rs.Update().

This works on a small example, but with my actual database rs.Update() fails with error -1 and the following error message: unrecognized token: ""RedFaktorFly" (1). The 'token' is a truncated name of column 14 of the table, whose full name is "RedFaktorFlyt". In some runs, it appends seemingly random characters, so the message becomes for ex. unrecognized token: ""RedFaktorFlyH" (1).

Interestingly, when I add "LongNames=true" to the ODBC connection string, which prepends table names to the column names and therefore makes the SQL query longer, the error becomes (for ex.) unrecognized token: ""K_Noder.MaxKompresjox" (1) - where "MaxKompresjonsFaktor" is the name of column 10 of the table.

This seems to suggest that there is a limit on the length of a SQL query accepted by the driver - but it seems strange that such a limit would be so small that it would fail already with 14 columns.

I do not think that the limit is in the C++ part, since the same code works fine both with the (commercial) SQLite driver from Devart and with Microsoft's ODBC driver for Access.

I tried adding a TraceFile option to the ODBC connection string, but it does not seem to do anything, so I do not know what exactly gets sent to the ODBC driver.

I see the same behaviour both with 32- and 64-bit builds, using Visual Studio 2015 on Windows 10.

Any suggestions what to try next?

Answer 1

I'll give you a solution to solve your issue completely. I don't know if it's suitable for you, but definitely it works. I successfully use it on my own.

For any operation except listing records, use CDatabase, not CRecordset. So, to insert rows to any table, to update records, to delete records, use CDatabase. To retrieve records from SQLLite database, use CRecordset. I can give you examples if you need.