On 2023-10-15 23:26:03 +0000, Johnson, Bruce E - (bjohnson) wrote:
> 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();
That doesn't look correct. PostgreSQL returns the result of "...
returning ..." as a result set.
So you would normally do something like
my $csr_newinv = $lda->prepare("insert ... returning inv_id")
$csr_newinv->execute()
$result = $csr_newinv->fetchrow_hashref();
$newid = $result->{inv_id};
> [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 ?
Yes. And a parameter isn't allowed in this position, only an identifier
(column alias).
> based on my reading of the Postgres Insert syntax I think it should be
> correct..
I think something like that works in PL/PgSQL. But in SQL you need to
fetch the result.
> (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 )
Having nested output from three different systems in the same line is a
bit confusing, yes. It becomes clearer if you know where the boundaries
are:
Apache: [Sun Oct 15 16:01:01.059801 2023] [cgi:error] [pid 814746:tid 814746]
[client 10.139.39.203:49782] AH01215:
Perl/DBD: DBD::Pg::st execute failed:
Postgres: ERROR: syntax error at or near "$5"
Perl/DBD: /home/allwebfiles/perl/edit_inst_inv2.pl,
Apache: referer: https:// avi.pharmacy.arizona.edu/a/edit_inst_inv.pl
Also, PostgreSQL error messsages contain context, so you know that the
> LINE 1: ...te('10-15-2023','MM-DD-YYYY'), now()) returning inv_id as $5
tells you wich line the error occured in (which is very handy on long
multi-line SQL queries) and the next line (which you didn't quote) shows
you where in the line the error was (with an ^ character pointing at
$5).
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | h...@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
signature.asc
Description: PGP signature
