Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2011
    Posts
    5

    Unhappy Unanswered: 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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Only one character is causing you problems in that string: the single quote. You will need to escape it with another single quote.

    Andy

  3. #3
    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...

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What language are you using?

    Andy

  5. #5
    Join Date
    Aug 2011
    Posts
    5
    code is written in java

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Then why not use a PerparedStatement and parameterize the variable?

    Andy

  7. #7
    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.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    How about String.replace()?

  9. #9
    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??

  10. #10
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    how about reading some doc : or do you like spoonfeeding ??
    Learn Java from the ground up - JavaWorld
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  11. #11
    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 09:28. Reason: Format

  12. #12
    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 10:58. Reason: typo

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •