I’ve move an application to Postgres for the database and am having a problem
with using named parameters.
My code:
my $csr_newinv =$lda->prepare("insert into inventory (inv_id, instid, av_id,
vials, volume, expiration_date, update_date) values (nextval(invid_seq),:INST,
:AV,:VI,:VO, to_date('$exp','MM-DD-YYYY'), now()) returning inv_id as :NEWID");
my $newid;
$csr_newinv->bind_param(':INST',$instid);
$csr_newinv->bind_param(':AV',$av_id);
$csr_newinv->bind_param(':VI',$vials);
$csr_newinv->bind_param(':VO',$volume);
$csr_newinv->bind_param_inout(':NEWID',\$newid,"SQL_NUMERIC");
$csr_newinv->execute();
The database is trhowing an cryptic error on the $csr_newinv->execute();
statement:
[Sun Oct 15 16:01:01.059801 2023] [cgi:error] [pid 814746:tid 814746] [client
10.139.39.203:49782] AH01215: DBD::Pg::st execute failed: ERROR: syntax error
at or near "$5": /home/allwebfiles/perl/edit_inst_inv2.pl, referer:
https://avi.pharmacy.arizona.edu/a/edit_inst_inv.pl
[Sun Oct 15 16:01:01.059943 2023] [cgi:error] [pid 814746:tid 814746] [client
10.139.39.203:49782] AH01215: LINE 1: ...te('10-15-2023','MM-DD-YYYY'), now())
returning inv_id as $5: /home/allwebfiles/perl/edit_inst_inv2.pl, referer:
https://avi.pharmacy.arizona.edu/a/edit_inst_inv.pl
Is $5 referring to :NEWID ?
based on my reading of the Postgres Insert syntax I think it should be correct..
(also, I do not know if the error logging is a DBD::Pg thing or Postgres thing,
but it very hard to follow these kinds of errors compared to DBD::Oracle )
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
