How to insert / update string containing wildcards into a field in a DB2 table

[Marilyn de Queiroz]

Hi,

I'm trying to update a string in a field in DB2. The string contains special characters, but I don't want to modify the string. I've tried updating the string inside a tool (DbVisualizer) and also using a plain SQL update.

The string looks something like this
'<a href="javascript:launchInWindow('', '', 'ui.gateway', '', 'ui.tool', 'Viewer', 'ui.action', 'run', 'run.prompt', 'false', 'ui.object', '/content/folder[@name=\'Analytics\']/folder[@name=\'Release 0.1\'] /folder[@name=\'Objects\']/folder [@name=\'Custom Accounts\']/folder[@name=\'Account\']/report[@name=\'ReportName\']', 'run.outputFormat','{{FORMAT}}', 'p_P_AccountID','{{COMPANYID}}', 'p_P_IsOnlySummaryNeeded', 'Detail', 'p_P_TypeValue', '{{TYPEVALUE}}',  'p_Datasource', 'DEV', 'p_Schema', 'TRGRX', 'p_P_Metric', {{METRICID}}, 'p_P_StartDate', '{{STARTDATE}}', 'p_P_EndDate', '{{ENDDATE}}');">'
where I'm trying to add 4 chars to the ReportName to become ReportName_Dev. (This is all one long string. I just broke it up so it would fit on the screen better)

The tool says "There were problems saving to the database table. Review the list, do the necessary changes and try again.
Note: DbVisualizer use bind variables when executing these statements. The effect of this is that the SQL listed below may not be 100% compliant with data formats for the target database i.e. they may fail to execute in for example the SQL Commander."

A straight SQL gives me this message: [Code: -4463, SQL State: 42601]  [jcc][t4][1061][10303][4.22.29] Escape syntax error.  See attached Throwable for details. ERRORCODE=-4463, SQLSTATE=42601

Any ideas on how this can be updated in the table without modifying the string (the href / url) would be appreciated.

[Tim Holloway]

I can't actually see the offending string. It's crammed into that little scrollable box. But the message certainly sounds like what you'd get when you attempted to do something like this:
UPDATE mytable SET last_name = 'O'Rourke'

That is, the single-quote in the literal string has annoyed the SQL parser.

To fix that, you'd have to do one of two things:

1) Escape the single-quote: "'O''Rourke'. Note that that's 2 single-quotes together, not 1 double-quote.

OR

2) Use a preparedstatement.

Another possibility is if you're trying to put 2 tons of fertilizer in a 1-ton truck (the database column definition isn't big enough), but I'm pretty sure it's a quotes problem.

SQL doesn't care about wild cards except when they appear in the LIKE clause of a SELECT statement. Otherwise they're simply seen as text.

[Marilyn de Queiroz]

Hi Tim,

Thanks for the response. I considered excaping the single quotes, but I'm worried. When I insert it into the field, will it also show up as an excaped quote or just as a quote mark? If the escaped quote is stored in the field, I think that the javascript won't work correctly.

[Swastik Dey]

PreparedStatement seems like a better option.

[Tim Holloway]

The SQL command parser needs to know what single quotes are literal string delimiters and what single quotes are actually part of the literal's value. Doubled up adjacent single quotes are seen as part of the value and collapse down to a single single quote. Non-doubled single quotes are seen as string literal delimiters by the SQL parser. So storing 'O''Brian' will set the database column value to O'Brian and that's what you'll see when you fetch it.

[Marilyn de Queiroz]

Thank you.