The information in this article applies to:
Product: Metastorm BPM (e-Work)
Version: 6.x 7.x
Issue
Using %ExecSQL to execute a SQL statement that inserts data into a table fails if the data contains a single quote (apostrophe).
In the following example, the value of the variable LastName is being inserted into the column SingleQuote in the table TestTable. This will fail to execute and will generate error messages in the Designer log and Windows application event log if the value of %LastName contains an apostrophe (e.g. O'Sullivan).
%ExecSQL("INSERT INTO TestTable(SingleQuote) VALUES ('%LastName')")
Resolution
This is expected behavior as SQL uses a single quote as a text delimiter. To insert a single quote, you need to replace it with two single quotes (not a double quote). The following example shows the correct SQL statement:
%ExecSQL("INSERT INTO TestTable(SingleQuote) values ('%Replace(%LastName,','')')")
