ahewitt96 has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to add a string that contains a single quote, but I am getting an error. I assumed that I would put a back slash before the single quote, but that does not seem to work. The statement is shown below where the single quote follows the word Attempt. Does anyone know how to fix this?

Here is the exact SQL statement:
INSERT INTO TRANS_TABLE
(Id, TransString, PoundDef, Filename)
VALUES
(0, 'Attempt\' to connect to %U?', 'qtn_ics_ap_conne ct_text', 'agentdialog_00.loc')


Here is the error string:

DBD::ODBC::st execute failed: MicrosoftODBC Microsoft Access DriverCOUNT field incorrect (SQL-07002)(DBD: s t_execute/SQLExecute err=-1) at D:\perlScripts\AddLocToDatabase\AddLocToDatabase.pl line 203, <> line 4. MicrosoftODBC Microsoft Access DriverCOUNT field incorrect (SQL-07002)(DBD: st_execute/SQLExecute err=-1) a

Replies are listed 'Best First'.
Re: DBI question
by sh1tn (Priest) on Mar 10, 2005 at 23:04 UTC 
    $quoted_string = $dbh->quote($string);

    [download]
    You may want to see perldoc DBI.


[reply]
[d/l]
Re: DBI question
by jZed (Prior) on Mar 11, 2005 at 00:45 UTC
    Let DBD::ODBC do the quote escaping, it knows what kind of escapes to use. This can be done with $dbh->quote() as others have said, but it can also be done with placeholders and placeholders have many other benefits. Read the DBI docs about placeholders, here's an example: 
      my $sth = $dbh->prepare(q{
      INSERT INTO TRANS_TABLE 
                  (Id, TransString, PoundDef, Filename)
           VALUES( ?, ? , ?, ?)
  });
  $sth->execute(
      0, 
      q{Attempt' to connect to %U?},
      q{qtn_ics_ap_conne ct_text}, 
      q{agentdialog_00.loc}
  );

    [download]
[reply]
[d/l]
Re: DBI question
by Whitehawke (Pilgrim) on Mar 10, 2005 at 23:27 UTC

    The suggestion above about the DBI quote method is your best bet in this specific case. In general, you are better off using the q{} and qq{} generic quote operators, as opposed to backwhacking internal quotes. (Note that you can use almost any character for the delimiters, not just {}; perldoc perlop for full details.)

    For example: q{"Sue said 'Go away'", Sarah told me}.

[reply]
[d/l]
[select]
      I think the OPs problem was not escaping text within a Perl string, but escaping text within a SQL statement. In general, you double up your quotes for that, i.e. to insert the string "Sue's Sister" into a database, you would write... 
      INSERT INTO sometable VALUES ('Sue''s Sister')

      [download]
      ... although the $dbh->quote method is cleaner if you're working with Perl.
[reply]
[d/l]
[select]

        Yep. That's why I said "...is better in this specific case. In general...."

        :>
[reply]
        It was a problem with SQL and not a perl problem. Doubling up the single quotes solved the problem.
        Thanks for you help
[reply]

Back to Seekers of Perl Wisdom