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


Hi,
I am runnig a SQL script in my perl script but I am getting this error <code> String found where operator expected at datacsv.pl line 46, near Bad name after ACTIVE' at data.pl line 47. Please suggest how to run this SQL query
Thanks
Rocko

Replies are listed 'Best First'.
Re: Unable to run SQL query
by moritz (Cardinal) on Sep 07, 2009 at 08:22 UTC
    Your problem is that you are trying to use double quotes within a double quoted string. Try here-documents, which are explained in perlop, section "Quote and Quote-like Operators".
    Perl 6 - links to (nearly) everything that is Perl 6.
[reply]
Re: Unable to run SQL query
by Corion (Patriarch) on Sep 07, 2009 at 08:23 UTC

    You're using double quotes to quote your string, but your string also contains double quotes:

    my $sth = $dbh->prepare("SELECT SUBSTR(row_data, 2, INSTR (row_data
...
REPLACE (SUBSTR (row_data, INSTR (row_data, '~', 1, 22) + 2),' "',' ')
...

    [download]

    Either escape the double quotes or better use heredocs or a different quoting mechanism:

    my $sql = <<SQL;

    SELECT SUBSTR(row_data, 2, INSTR (row_data
    ...
    REPLACE (SUBSTR (row_data, INSTR (row_data, '~', 1, 22) + 2),' "',
+' ')
    ...
SQL

    [download]
    or 
    my $sql = qq{

    SELECT SUBSTR(row_data, 2, INSTR (row_data
    ...
    REPLACE (SUBSTR (row_data, INSTR (row_data, '~', 1, 22) + 2),' "',
+' ')
    ...

    };

    [download]
[reply]
[d/l]
[select]
      Hi, I understand that using heredoc will solve the problem of detecting the quotes but how do I run the SQL query?
[reply]

        Try putting the prepare back in with $heredoc as its parameter.

[reply]
        What Mr. Muskrat means is something like this: 
        use strict;
use DBI;

my $dbh = DBI::connect( ... ) or die "DBI::connect failed";

my $sql = <<ENDSQL;
SELECT ...
  FROM ...
WHERE ...
 ...
ENDSQL

my $sth = $dbh->prepare( $sql ) or die "DBI::prepare() failed";
$sth->execute( );

...

        [download]
        (and remember to add parameter variables or values in the execute() call, if your sql statement has any "?" placeholders).
[reply]
[d/l]

Back to Seekers of Perl Wisdom