DBI data types

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

Hey all, I want to update some tables in a Postgres dbase and I want to use DBI to do that. I'm using the DBD::Pg 1.01 driver. I am running into a problem of having to do a bind_param -

$select00->bind_param(1,$id,SQL_INTEGER)|| die $select00->errstr;
$select00->bind_param(3,$item,SQL_INTEGER)|| die $select00->errstr;
[download]

But when I run the script I get the following -

DBI::st=HASH(0x80f86e0)->bind_param(...): attribute parameter 'SQL_INT
+EGER' is n
ot a hash ref at ./dbtest.insert line 19.
[download]

Here is the entire code -

#!/usr/bin/perl -w
#
#
use strict;
use DBI;


my $dsn="dbi:Pg:dbname=tabletest";
my $user="postgres";
my $passwd="";

#
#
#
my ($dbh,$id,$descr,$item,$latin);
$dbh = DBI->connect($dsn, $user, $passwd, { RaiseError => 1, AutoCommi
+t => 0 });

my $select00 = $dbh->prepare('INSERT INTO hobby (id,descr,item,latin) 
+VALUES (?,
?,?,?)')|| die $dbh->errstr;
        $select00->bind_param(1,$id,SQL_INTEGER)|| die $select00->errs
+tr;
        $select00->bind_param(3,$item,SQL_INTEGER)|| die $select00->er
+rstr;

my $csvfile="/home/bradley/dbtest/item_instk.csv";
open (CSV,"$csvfile");
while (<CSV>) {
        chop;
        $id = 1;
        ($item,$descr,$latin) = split /\//;
        $select00->execute( $id, $descr, $item, $latin )||die $dbh->er
+rstr;
        ++$id;
}
close CSV;

$dbh->disconnect;
[download]

Also, do I need to quote the SQL_INTEGER parts of the bind_params? I get

Bareword "SQL_INTEGER" not allowed while "strict subs" in use at ./dbt
+est.insert
 line 18.
[download]

If I don't quote them. Any help anyone can provide would be greatly appreciated. Thanks Bradley Where ever there is confusion to be had... I'll be there.

Comment on DBI data types Select or Download Code

Replies are listed 'Best First'.
Re: DBI data types by maverick (Curate) on Aug 21, 2001 at 23:12 UTC
Here's some suggestions to try: `my $ins_sth = $dbh->prepare('INSERT INTO hobby (id,descr,item,latin) V +ALUES (?,?,?,?)')\|\| die $dbh->errstr; my $csvfile="/home/bradley/dbtest/item_instk.csv"; my $id = 1; open (CSV,"$csvfile"); while (<CSV>) { chomp; ($item,$descr,$latin) = split('/',$_); $ins_sth->execute( $id, $descr, $item, $latin )\|\|die $dbh->err +str; ++$id; } close CSV;` [download] I've never used bind_param, and as far as I can tell, it's completely unnecessary for what you are trying to do. setting $id to 1 inside the while loop will make all of your id's be set to 1 when the data is inserted. Further I suggest that you not use $id at all. Databases have built in functions for generating unique id's and it's much safer to use them than to make them yourself. I wouldn't call my statement handle 'select' if it's actually doing an insert. 'sth' is what the perldocs use in the examples...perhaps 'ins_sth' is a more mnemonic name. ~~you need to call 'finish' on a statement handle after you are done using it.~~ change 'chop' to 'chomp', 'chomp' only removes newlines, and if the last line of your data file doesn't have one, 'chop' would take off the last character of $latin. I realize that this isn't a direct answer to your question, but I hope that it helps you make things go more smoothly. Update:Thanks for the corrections runrig /\/\averick perl -l -e "eval pack('h*','072796e6470272f2c5f2c5166756279636b672');"	[reply] [d/l]
Re: Re: DBI data types by runrig (Abbot) on Aug 21, 2001 at 23:18 UTC
you need to call 'finish' on a statement handle after you are done using it. This is a common mistake. You rarely need to call finish on a statement handle, and you should never call it for an insert statement. You should only call it on a select handle where you do not fetch the entire result set (the select after the last record). It exists to tell the database that you are done with any temporary resources required by the statement (e.g. temp tables for an order by, etc.). I've never used bind_param, and as far as I can tell, it's completely unnecessary for what you are trying to do. Bind_param is occasionally necessary to tell DBI what type of argument you have when DBI guesses wrong (though I admit I've never used it either and it probably IS unnecessary in this instance). Update: I've also seen a case recently on the DBI list where a field is character, but the first value bound to it is numeric, so DBI (or the DBD?) assumes that the field is numeric and doesn't need quoting, which of course breaks when the value bound is non-numeric. Don't quote me on this, my memory is vague :)	[reply]
Re: bind_param by htoug (Deacon) on Aug 22, 2001 at 15:02 UTC
I agree that bind_param is unneccessary in this case. bind_param is neccessary when the DBI (or rather the underlying DBD) cannot determine the type of the argument and when the type matters. eg when a value is NULL - (then the DBD has no clue as to what type it is), when the SV behind the value has both a string and a numeric value (it does matter for some drivers) etc. The DBI caches the type of the column, so you only have to give the type once, in subsequent calls the type will be remmebered. These situations are not common, but they do exist. They are not as uncommon as the cases where finish is needed though :-)	[reply]
Re: Re: Re: DBI data types by Dalin (Sexton) on Aug 21, 2001 at 23:54 UTC
That seems to have corrected those problems, but I seem to be having a parsing problem. I made the change to the "Use DBI" line, and I get the following when I run the script - `DBD::Pg::st execute failed: ERROR: parser: parse error at or near "al +bino" at . /dbtest.insert line 27, <CSV> line 1. DBD::Pg::st execute failed: ERROR: parser: parse error at or near "al +bino" at . /dbtest.insert line 27, <CSV> line 1.` [download] "albino" is part of a name from the file being read for the values. The file is "\|" delimited, but two of the three fields have spaces between words within them. The other field is numeric. i.e. 12345\|SWORDTAIL ALBINO REG\|XIPHOPHORUS HELLERI (This is a fish in case you are wondering) Any ideas?????? Thanks for the help. Bradley Where ever there is confusion to be had... I'll be there.	[reply] [d/l]
Re: DBI data types by blakem (Monsignor) on Aug 21, 2001 at 23:08 UTC
To use the constants such as 'SQL_INTEGER' you need to define them by replacing 'use DBI' with: `use DBI qw(:sql_types);` [download] That should fix the bareword error, and might affect the other errors you are getting. -Blake	[reply] [d/l]
Re: Re: DBI data types by Dalin (Sexton) on Aug 21, 2001 at 23:18 UTC
That seems to have corrected those problems, but I seem to be having a parsing problem. I made the change to the "Use DBI" line, and I get the following when I run the script - `DBD::Pg::st execute failed: ERROR: parser: parse error at or near "al +bino" at . /dbtest.insert line 27, <CSV> line 1.` [download] "albino" is part of a name from the file being read for the values. The file is "\|" delimited, but two of the three fields have spaces between words within them. The other field is numeric. i.e. 12345\|SWORDTAIL ALBINO REG\|XIPHOPHORUS HELLERI (This is a fish in case you are wondering) Any ideas?????? Thanks for the help. Bradley Where ever there is confusion to be had... I'll be there.	[reply] [d/l]