Unable to execute SQL queries

Question

I have a bunch of DOIs separated by '\n' in a *.txt file. The perl script I have written should read each line of the file and execute a select query. However, the program is unable to execute the SQL query. Could you please help me fix the problem?

After execution I get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'http://dx.doi.org/10.1002/1521-3757(20001103)112:21<3947::aid-ange3947>3.0'

The first line of the dois.txt file contains the following DOI:

`http://dx.doi.org/10.1002/1521-3757(20001103)112:21<3947::aid-ange3947>3.0.co;2-k`

Here's my code:

my $file = 'dois.txt';

open(my $fh, '<:encoding(UTF-8)', $file)
   or die " Could not open file $file";
while (my $doi = <$fh>) {

chomp($doi);
my $sth = $dbh->prepare("select * from mytable where doi = ''$doi'';");
$sth->execute || die "failed to execute:\n ", $DBI::Errstr;
print $sth->fetchrow_array, "\n";

}
close FH;
$dbh->disconnect;

create a string and publish it from "select * from mytable where doi = ''$doi'';" — Drew
– Drew, Commented Aug 27, 2015 at 23:38

friedo · Accepted Answer · 2015-08-28 06:31:13Z

2

You want to call prepare outside the loop so that the execution plan only has to be calculated once (i.e., faster). Also, in addition to only preparing the query once, you don't have to figure out how to quote values correctly if you use placeholders/bind parameters.

my $file = 'dois.txt';

open(my $fh, '<:encoding(UTF-8)', $file) or die "Could not open file $file: $!\n";
my $sth = $dbh->prepare(q{select * from mytable where doi = ?});

while (my $doi = <$fh>) {
    chomp($doi);    
    $sth->execute($doi) || die "failed to execute:\n ", $DBI::Errstr;
    print $sth->fetchrow_array, "\n";
}

close $fh;
$dbh->disconnect;

edited Aug 28, 2015 at 6:31

friedo

67.3k17 gold badges119 silver badges186 bronze badges

answered Aug 27, 2015 at 23:44

Matt Jacob

6,5732 gold badges27 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unable to execute SQL queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related