[vtk-developers] SQL text queries

David C Thompson dcthomp at sandia.gov
Thu Mar 13 21:08:34 EDT 2008


Hi all,

While using the vtkSQLQuery class, I've run into a use case where it
might be good to add a function or two to the query class; I'm inserting
rows into a table with TEXT columns and some of the values contain
characters (e.g., quotes) that need to be escaped. Exactly what needs to
be quoted varies across backends (and even on the same backend,
depending on the character set chosen for a DB or table). MySQL and
PostgreSQL provide routines to escape strings given a server connection.

I'd like to add a function EscapeString to vtkSQLQuery. There are a few
alternatives for the signature and the behavior. For the signature:

class vtkSQLQuery {
public:
  // Signature A: Easy to use, not wrappable.
  virtual vtkStdString EscapeString( vtkStdString& src );
  // Signature B: Painful to use, wrappable.
  virtual char* EscapeString( const char* src );
  // ...
};

Version A is nice for C++ but can't be wrapped. Version B could be
wrapped but would either (1) require the user to free the returned value
or (2) prohibit users from making multiple calls to EscapeString while
writing a single query (since an internal string holding the escaped
value would be overwritten with each call).

For behavior, there is also the question of whether to include the
quotes around the text value being escaped (MySQL uses double quotes,
PostgreSQL uses single quotes). The routines provided by the databases
do not provide surrounding quotes. Perhaps adding a "bool
surroundingQuotes" argument to the signatures would work? Any opinions?

I haven't looked at the Qt or ODBC APIs to see whether string escapes
are provided but I did look at SQLite and it doesn't seem provide a way
to escape strings. So either a trivial or default implementation would
need to be provided.

	David





More information about the vtk-developers mailing list