Re: Is this a DBD::Pg problem or Postgres problem?

--wcpi23gmvz7w2aoh
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2023-10-15 23:26:03 +0000, Johnson, Bruce E - (bjohnson) wrote:
> I=E2=80=99ve move an application to Postgres for the database and am havi=
ng a problem
> with using named parameters.
>=20
> My code:
>=20
> my $csr_newinv =3D$lda->prepare("insert into inventory (inv_id, instid, a=
v_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 :NE=
WID");
> 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");=20
> $csr_newinv->execute();

That doesn't look correct. PostgreSQL returns the result of "...
returning ..." as a result set.=20

So you would normally do something like

my $csr_newinv =3D $lda->prepare("insert ... returning inv_id")
$csr_newinv->execute()
$result =3D $csr_newinv->fetchrow_hashref();=20
$newid =3D $result->{inv_id};

> [Sun Oct 15 16:01:01.059801 2023] [cgi:error] [pid 814746:tid 814746] [cl=
ient
> 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] [cl=
ient
> 10.139.39.203:49782] AH01215: LINE 1: ...te('10-15-2023','MM-DD-YYYY'), n=
ow())
> returning inv_id as $5: /home/allwebfiles/perl/edit_inst_inv2.pl, referer:
> https://avi.pharmacy.arizona.edu/a/edit_inst_inv.pl
>=20
> 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 814=
746] [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=20

> 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

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [email protected]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--wcpi23gmvz7w2aoh
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmUtO/0ACgkQ8g5IURL+
KF1Tdg/+OwfJhW0tWUSTLlmsDecI+BlbvoS016g3SSJuLAiixlUDm+9RLh1Vrn6C
qLIv2zpRHOuyUkWaiB5SxvfIWwyhUlWmjGXrySggZt02Uu7BQkCr8S5YCSgJPUtb
If6Bhsc/XRLAkMxgpiipAeYJevyHjbw7a5b59BNnabaKyk7tRpxji8URiR3bER5u
hLkiWrgS+7nX1dgcqs198F7vL6DLEL13+ssUb/gsTRo1ODzbur9LC2iyFBY7tSGT
WlHf278WgYhC6VPfUsZeeBMlQzbsuKHMmJBzwSHwWntOOz9nl0QUmzqOrMaJBOUo
Nwc6ESkHSrwI7zZNq+1XmfI4WoyRRqu+W5zi3YA7PKgNc7INOgnZRqBIZF62w98L
wC9aEurCiYg6Fh3tn3N9YWrBCma+at7+oSawolNP3Hy+tAIX6Tbdhabq40FqEyUO
MX4qi55sKvRh/tgUeDPYbKGR9eGgXhRFUZ9Gk9pI+3q/1QFV7D2jsOsTDoQNMkbb
rXtqiYpjUEcABorbV9diwaQNmKOfcpvYlqST37KXik6Nth7/OuvXAPnxMjfTO1Wh
7M5fnoQnpE1tdYZe7bvL3aRKX2MIVoXocSaqmPvQRIsz2fdkfUtoboq7DR5oZ6J1
XVV8Vcdf2HESubNPU6mOJv+pVz0jEJBoJhjXHQLCIvZkNXilW/M=
=jak7
-----END PGP SIGNATURE-----

--wcpi23gmvz7w2aoh--