View Full Version : String quoting with SQL Virtual Tables

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".


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.


2013-10-28, 03:43
thanks Barry.