GDA Missing records in SQLite

[Mark Johnson]

Mark Johnson wrote:
> Graham Leggett wrote:
>   
>> Mark Johnson wrote:
>>
>>     
>>> By examining which transactions, accounts, and splits were missing 
>>> from SQLite (as compared to MySQL), I was able to determine that 
>>> anything which had a single quote in a string (description, name, & 
>>> memo fields) failed to be inserted into SQLite.  This is a one-to-one 
>>> correspondence.  i.e. anything that had a single quote failed.  These 
>>> were the only records missing as compared to MySql.
>>>
>>> At the moment, I am guessing that the SQLite provider does not 
>>> properly escape such strings passed to it.  If so, the SQL INSERT 
>>> statements would be illegal and fail.  (I've built just such a bug 
>>> myself once using MySql.)
>>>
>>> More study of the SQLite provider is required.  I did not find any 
>>> existing bug reports regarding single quotes and the SQLite 
>>> provider.  If  I can confirm my guess, I'll file the bug report.
>>>       
>> This sounds more like a symptom of not using prepared statements in 
>> sqlite. This could be a GDA problem, or it could be sqlite not 
>> supporting prepared statements.
>>     
> Looking in SQLite's documentation on the SQL it supports, I do not see 
> anything on prepared statements.
>   
Correction: Here it is in the C API:
http://www.sqlite.org/c3ref/stmt.html
I was previously only looking in the SQL reference.  The SQLite provider 
is using prepared statements.
>> What is more worrying is that rows are being dropped and no error is 
>> being thrown. That looks to me like a more serious bug at this point, 
>> it means that strange data corruption errors are likely and the end 
>> user will never know.
>>     
> I agree completely.
>   
Found the error message.  It is buried in gnucash.trace.  This is really 
not sufficient notification that data has been dropped.

Here is a sample entry from my save:
* 13:47:49  WARN <> SQlite SQL: INSERT INTO accounts (guid, name, 
account_type, commodity_guid, parent_guid, code, description) VALUES 
('499fc8a75a6f9723cc42cf04448e9808', 'TD Int\'l Equity', 'MUTUAL', 
'67d52ac9d9c722972de705ea22507ff2', 'fc945d5e7719c32b986b2b30ddf957f8', 
'', '') (REMAIN:INSERT INTO accounts (guid, name, account_type, 
commodity_guid, parent_guid, code, description) VALUES 
('499fc8a75a6f9723cc42cf04448e9808', 'TD Int\'l Equity', 'MUTUAL', 
'67d52ac9d9c722972de705ea22507ff2', 'fc945d5e7719c32b986b2b30ddf957f8', 
'', ''))
* 13:47:49  CRIT <gnc.backend.gda> [gnc_gda_execute_query()] SQL error: 
near "l": syntax error

This is, indeed, one of the accounts that gnucash-gda failed to create.  
There are many similar errors in the gnucash.trace file.

NOTE the syntax error near "l".  This indicates to me that the escaping 
of the single quote has been done incorrectly for SQLite.  I haven't yet 
found what the correct way to do it is in their documentation.  
Unfortunately, the SQLite doc I am looking at directs me to look at the 
parse.y file for details on the language.
>> Regards,
>> Graham
>> -- 
>>     
> Mark
>   
Mark