in reply to Unable to run SQL query

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]

Replies are listed 'Best First'.
Re^2: Unable to run SQL query
by Rocko19 (Acolyte) on Sep 07, 2009 at 10:18 UTC
    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]

In Section Seekers of Perl Wisdom