CODEWITHSUNDEEP
sqlpostgresqlperldbi
Jarmund
Jarmund

Reputation: 3205

perl + postgresql code results in problems with Pg.pm

I'm getting an odd error when trying to insert data into a table while working with data being fetched from a different table:

DBI::st=HASH(0x56261b345948)->_prepare(...): attribute parameter '0' is not a hash ref at /usr/lib/x86_64-linux-gnu/perl5/5.24/DBD/Pg.pm line 277.

The line that seems to be causing it is the $dbh->do() in the above code snippet:

 my $sth = $dbh->prepare(
     "SELECT (name, model), serial, firmware, timestamp FROM devicelog 
         WHERE id = $id AND fleet = $fleet AND timestamp >  
             (SELECT timestamp FROM devicelog WHERE fleet = $fleet AND id = $id ORDER BY timestamp DESC LIMIT 1)
             - INTERVAL '50 seconds'"
 );
 $sth->execute();
 while (my @row = $sth->fetchrow_array())
 {

     if (param('savetemplate'))
     {
         $dbh->do('INSERT INTO template (fleet, id, name, model, serial) VALUES (?, ?, ?, ?, ?)',
             $fleet,
             $id,
             $row[0],
             $row[1],
             $row[2]
         ) or die $!;
     }
     # Do some printing to screen and other mundane stuff
}

I went over the data that is returned by the first SQL query, but I cannot find anything wrong with it, as the results yelds the expected result:

                  row                   |  serial   |      firmware       |         timestamp          
----------------------------------------+-----------+---------------------+----------------------------
 ("HS60     GPS COMPASS",000-12308-001) | 19030590# |         01000023    | 2017-11-12 00:08:01.435483
 ("IS42     Instrument","")             | 007564#   | 01000_E 1.0.54.3.21 | 2017-11-12 00:08:01.476376

I'm starting to think that something that should be escaped isn't? I also suspected that maybe DBI didn't like running a do() while still iterating through a fetchrow_array() series, but rewriting to avoid doing it simultaneously yielded the same result.

If more info is required, leave a comment, and I'll supply it.

Upvotes: 0

Views: 132

Answers (1)

melpomene
melpomene

Reputation: 85757

According to the documentation, do has the following forms:

do

$rows = $dbh->do($statement)           or die $dbh->errstr;
$rows = $dbh->do($statement, \%attr)   or die $dbh->errstr;
$rows = $dbh->do($statement, \%attr, @bind_values) or die ...

Your code:

     $dbh->do('INSERT INTO template (fleet, id, name, model, serial) VALUES (?, ?, ?, ?, ?)',
         $fleet,
         $id,
         $row[0],
         $row[1],
         $row[2]
     )

This treats $fleet (the second argument) as a reference to an attribute hash (the \%attr parameter), which it isn't (it's a number). You don't want to pass any attributes, so you should specify undef:

     $dbh->do('INSERT INTO template (fleet, id, name, model, serial) VALUES (?, ?, ?, ?, ?)',
         undef,
         $fleet,
         $id,
         $row[0],
         $row[1],
         $row[2],
     )

Also note that DBI doesn't set $!, so using it in error message makes little sense. You should use $dbh->errstr instead (or simply pass RaiseError => 1 in your connect call to make all methods throw exceptions on error).

Upvotes: 6

Related Questions