• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Paul Clapham
  • Jeanne Boyarsky
Sheriffs:
  • Devaka Cooray
  • Junilu Lacar
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Ron McLeod
  • Tim Holloway
  • Claude Moore
  • Stephan van Hulst
Bartenders:
  • Winston Gutkowski
  • Carey Brown
  • Frits Walraven

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

 
Sheriff
Posts: 9094
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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

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.

 
Saloon Keeper
Posts: 20509
115
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:


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
Sheriff
Posts: 9094
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Rancher
Posts: 1930
15
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
PreparedStatement seems like a better option.
 
Tim Holloway
Saloon Keeper
Posts: 20509
115
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 9094
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!