barrymavin
2013-10-27, 22:03
There was an issue reported with the SQL being generated for Virtual Tables when the data being used in the CRUD statement contained embedded ' characters.
There is no real standard in the way this is handled. All SQL engines seem to handle it all differently.
To solve this I have added a new property to the CREATE VIRTUALTABLE ... PROPERTIES "..." called quotestring.
This is now correctly handled for "local".
e.g.
create virtualtable vtmssql_table properties "quotestring='';..." will replace single quotes by two single quotes for MSSQL
Other databases e.g. MySQL use
create virtual table vtmysql_table properties "quotestring=\';..." will replace single quotes by \' for MSSQL
Lianja specifies this for "local" tables. So you can use expressions if you need to.
create virtual table vtlianjasql_table properties "quotestring='+chr(39)+';..."
So basically embedded single quotes get substituted for whatever you specify.
The property is also accessible on the CA e.g.
ca.quotestring="'+chr(39)+'"
There is no real standard in the way this is handled. All SQL engines seem to handle it all differently.
To solve this I have added a new property to the CREATE VIRTUALTABLE ... PROPERTIES "..." called quotestring.
This is now correctly handled for "local".
e.g.
create virtualtable vtmssql_table properties "quotestring='';..." will replace single quotes by two single quotes for MSSQL
Other databases e.g. MySQL use
create virtual table vtmysql_table properties "quotestring=\';..." will replace single quotes by \' for MSSQL
Lianja specifies this for "local" tables. So you can use expressions if you need to.
create virtual table vtlianjasql_table properties "quotestring='+chr(39)+';..."
So basically embedded single quotes get substituted for whatever you specify.
The property is also accessible on the CA e.g.
ca.quotestring="'+chr(39)+'"