GDA Missing records in SQLite

[Mark Johnson]

Michael Vrable wrote:
> On Wed, Feb 20, 2008 at 03:09:50PM -0700, Mark Johnson wrote:
>   
>> 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.
>>     
>
> Double the single quote.  So
>      TD Int'l Equity
> is written as a string as
>      'TD Int''l Equity'
>
> I believe (but haven't double-checked) that this is the standard means 
> of escaping a single quote in SQL.  Some databases also happen to 
> support \', but I think '' should work for all.
>   
PostgreSQL gives a warning for '', but accepts it; it prefers \'.  MySql 
takes the ''; I'm not sure about \'.  Obviously SQLite won't take the \'.

However, the real problem is that it is not gnucash-gda code which is 
doing the escaping of the single quote.  It is libgda, probably the 
SQLite provider.  Therefore not under this project's control.
> --Michael Vrable
>
>   
Mark