Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2007
    Posts
    19

    Unanswered: ' in a sql statement

    Hi,

    I have the following statement:
    insert into User.Ausschnitt values ('O'reilly','2','3','4','5','6');

    And I get the message that there is a problem with the '

    But how can I solve this problem? I looked for escape signs for the DB2, but I found nothing till now.

    Can you help?

    Thanks in advance,
    Chris

  2. #2
    Join Date
    Sep 2003
    Posts
    237
    Try 'O''Reily'
    mota

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    As dbamota suggested - double apostrophes are the escape character. Apostrophes in fields can cause all sorts of problems - so you must either remove them or make sure you replace all singles withing the field value with (EDIT: two single quotes in succession). It is your decision on whether to drop them completely or replace with (EDIT: two single quotes in succession) for every insert (perhaps this depends on your front end?).
    Anyway, ihth
    Last edited by gvee; 06-05-07 at 06:56.
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    george, don't say "replace with doubles"

    say "replace with two single quotes in succession"

    can you see why?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Mais oui - my apologies.
    There is a very big difference.
    Rudy is quite correct
    George
    Home | Blog

  6. #6
    Join Date
    Jun 2007
    Posts
    19
    Thanks a lot for all your support.
    I just implemented it the code. I replace a ' by ''!
    But now it's shown like this in the respective field in the database.

    Is there no possibility to import the string like it looks orginial, e.g. O'Reilly?
    Because now I have O''Reilly in the field.

    Chris

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What happens when you use a SELECT statement to pick out this record?
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    chris, did you see post #4 and the EDITs in post #3?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2007
    Posts
    19
    @georgev: I get O''Reilly

    @r937: I have seen the edits and I used two single quotes in succession

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    are you escaping your sql somehow, like the way the php programmers do?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2007
    Posts
    19
    NO! But I'm looking for something like that. E.g. I found an escape function for mysql but not for the DB2. Thats my problem!

    My code is written in PHP!

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, i don't do php, but i've helped an awful lot of php programmers who get into trouble with sql

    you (or your php code) must be doing something to escape each single quote

    perhaps you could start a new thread in the php forum?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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