Win a copy of Get Programming with Java (MEAP only) this week in the Beginning Java forum!
  • 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
  • Jeanne Boyarsky
  • Bear Bibeault
  • Knute Snortum
  • Liutauras Vilda
Sheriffs:
  • Tim Cooke
  • Devaka Cooray
  • Paul Clapham
Saloon Keepers:
  • Tim Moores
  • Frits Walraven
  • Ron McLeod
  • Ganesh Patekar
  • salvin francis
Bartenders:
  • Tim Holloway
  • Carey Brown
  • Stephan van Hulst

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.

 
Bartender
Posts: 20146
103
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: 1920
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
Bartender
Posts: 20146
103
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.
 
I have always wanted to have a neighbor just like you - Fred Rogers. Tiny ad:
RavenDB is an Open Source NoSQL Database that’s fully transactional (ACID) across your database
https://coderanch.com/t/704633/RavenDB-Open-Source-NoSQL-Database
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!