Home   |   Register   |   Rules   |   Calendar   |   Get Daily   |     |  
 


If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Help in sql query to include special characters

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 08-17-11, 15:09
Aparajita Aparajita is offline
Registered User
  
Join Date: Aug 2011
Posts: 5
Unhappy Help in sql query to include special characters
Hi i need help in including special characters in select query.
I am getting following error.As i have used following query:

queryString = "select name from tablename where (NAME like '"+ tempchild +"') with ur";

tempchild=wwwee_rss's&aa

these special characters like &,_,'s giving error.please help me
Error Creating ResultSet Object
SQLCODE = -10
SQLSTATE = 42603
SQLErrMsg = [IBM][CLI Driver][DB2/AIX64] SQL0010N The string constant beginning with "') with ur" does not have an ending string delimiter. SQLSTATE=42603

at com.ibm.crm.rtuu.adapter.lcia.util.DBConnector.exe cuteDBQuery(DBConnector.java:120)
at com.ibm.crm.rtuu.adapter.refdata.RTUUOpportunityCa tegory.processOpportunityCategory(RTUUOpportunityC ategory.java:505)
at com.ibm.crm.rtuu.adapter.refdata.RTUUStartOpportun ityCategory.startOpportunityCategory(RTUUStartOppo rtunityCategory.java:57)
at com.ibm.crm.rtuu.adapter.utilities.twoMain.callOpp tyCategory(twoMain.java:117)
at com.ibm.crm.rtuu.adapter.utilities.twoMain.main(tw oMain.java:65)
com.ibm.crm.rtuu.adapter.lcia.exception.LciaDBExce ption: Error Creating ResultSet Object
Reply With Quote
  #2 (permalink)  
Old 08-17-11, 15:36
ARWinner ARWinner is offline
Registered User
  
Join Date: Jan 2003
Posts: 3,575
Only one character is causing you problems in that string: the single quote. You will need to escape it with another single quote.

Andy
Reply With Quote
  #3 (permalink)  
Old 08-17-11, 15:44
Aparajita Aparajita is offline
Registered User
  
Join Date: Aug 2011
Posts: 5
Red face
but how can i insert a escape character as i am getting this tempchild variable value from the xml sheet when i raun this code there is different values for each run as i dont know how to do this...
Reply With Quote
  #4 (permalink)  
Old 08-17-11, 15:47
ARWinner ARWinner is offline
Registered User
  
Join Date: Jan 2003
Posts: 3,575
What language are you using?

Andy
Reply With Quote
  #5 (permalink)  
Old 08-17-11, 15:51
Aparajita Aparajita is offline
Registered User
  
Join Date: Aug 2011
Posts: 5
code is written in java
Reply With Quote
  #6 (permalink)  
Old 08-17-11, 15:52
ARWinner ARWinner is offline
Registered User
  
Join Date: Jan 2003
Posts: 3,575
Then why not use a PerparedStatement and parameterize the variable?

Andy
Reply With Quote
  #7 (permalink)  
Old 08-17-11, 16:00
Aparajita Aparajita is offline
Registered User
  
Join Date: Aug 2011
Posts: 5
Unhappy
This is an existing very complex code written in java as i am very new to java...without using that prepared statement i cannot solve this problem...???
this cannot be done by a simple statement.
Reply With Quote
  #8 (permalink)  
Old 08-17-11, 16:38
n_i n_i is offline
:-)
  
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
How about String.replace()?
Reply With Quote
  #9 (permalink)  
Old 08-17-11, 16:41
Aparajita Aparajita is offline
Registered User
  
Join Date: Aug 2011
Posts: 5
Unhappy
how can i use this plz elaborate by giving example.Do i need to use this in the variable which i defined as tempchild??
Reply With Quote
  #10 (permalink)  
Old 08-18-11, 02:16
przytula_guy przytula_guy is offline
Registered User
  
Join Date: Apr 2006
Location: Belgium
Posts: 1,159
how about reading some doc : or do you like spoonfeeding ??
Learn Java from the ground up - JavaWorld
__________________
Best Regards, Guy Przytula
Database Software Consultant
DB2 UDB LUW Certified V7-V8-V9-V9.7 DB Admin - Dprop..
Information Server Datastage Certified
http://www.infocura.be
Reply With Quote
  #11 (permalink)  
Old 08-18-11, 08:26
marc_ marc_ is offline
Registered User
  
Join Date: Aug 2011
Location: Glasgow, UK
Posts: 36
Change queryString to:

Code:
queryString = "select name from tablename where (NAME like '"+ 
     tempchild.replaceAll("'", "''") +"') with ur";

Which will then give you:
Code:
select name from tablename where (NAME like 'wwwee_rss''s&aa') with ur
Effectively escaping the single quote.

It's that simple
Last edited by marc_; 08-18-11 at 08:28. Reason: Format
Reply With Quote
  #12 (permalink)  
Old 08-18-11, 09:56
marc_ marc_ is offline
Registered User
  
Join Date: Aug 2011
Location: Glasgow, UK
Posts: 36
Forgot to mention. Your like predicate doesn't used any wildcards therefore it effectively becomes an equal to.

select name from tablename where (NAME like 'wwwee_rss''s&aa%') ...
or
select name from tablename where (NAME like '%wwwee_rss''s&aa%') ...

where '%' is the wildcard.
Last edited by marc_; 08-18-11 at 09:58. Reason: typo
Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


 
 
 
Home  |   Register  |   Get New  |   FAQ's  |   Sitemap

Powered by vBulletin
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.