By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,660 Members | 1,267 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,660 IT Pros & Developers. It's quick & easy.

DBD::Pg problem

P: n/a
Hi

I am trying to insert a simple email address into a text field,
and I get the below error:

DBD::Pg::st execute failed: ERROR: pg_atoi: error in
"<su*****@somedomain.com>": can't parse "<su*****@somedomain.com>"

I figure it is because of the < and @ in the value, but why does it take
these as operators even
when the value has single quotes around it?
I have even tried binding the values and PG_TEXT beforehand and still
not luck.

Any help would be greatly appreciated.

Jason Frisch

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
pg_atoi is the string to int converter. You're trying to insert it into an
integer field.

On Wed, Dec 03, 2003 at 03:45:53PM +0900, Ausrack Webmaster wrote:
Hi

I am trying to insert a simple email address into a text field,
and I get the below error:

DBD::Pg::st execute failed: ERROR: pg_atoi: error in
"<su*****@somedomain.com>": can't parse "<su*****@somedomain.com>"

I figure it is because of the < and @ in the value, but why does it take
these as operators even
when the value has single quotes around it?
I have even tried binding the values and PG_TEXT beforehand and still
not luck.

Any help would be greatly appreciated.

Jason Frisch



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ "All that is needed for the forces of evil to triumph is for enough good
men to do nothing." - Edmond Burke
"The penalty good people pay for not being interested in politics is to be
governed by people worse than themselves." - Plato


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/zYgKY5Twig3Ge+YRArVDAJ0YqO4Ua0jeiLz0xyYPLU74yqcoiQ CdHq2G
pTH9QrMtdFGDdpYj3DpixAI=
=KGnB
-----END PGP SIGNATURE-----

Nov 12 '05 #2

P: n/a
The thing is...I am not. I am inserting it into a varchar field.

$sql="insert into it_contact (email, to_email, subject,
details,modify,parent) values(?,?,?,'$body',now(),'$parent')";
$sth = $dbh->prepare($sql);
$sth->bind_param(1, $from, {pg_type => DBD::Pg::PG_TEXT});
$sth->bind_param(2, $to, {pg_type => DBD::Pg::PG_TEXT});
$sth->bind_param(3, $subject, {pg_type => DBD::Pg::PG_TEXT});
$sth->execute;

\d it_contact;
Table "public.it_contact"
Column | Type |
Modifiers
------------+-----------------------------+-----------------------------
---------------------------------------
contact_id | integer | not null default
nextval('public.it_contact_contact_id_seq'::text)
email | character varying(100) |
to_email | character varying(100) |
subject | text |
fname | character varying(30) |
lname | character varying(30) |
kafname | character varying(30) |
kalname | character varying(30) |
details | text |
modify | timestamp without time zone |
status | smallint |
parent | integer |
Jason

-----Original Message-----
From: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org] On Behalf Of Martijn van
Oosterhout
Sent: Wednesday, December 03, 2003 3:52 PM
To: Ausrack Webmaster
Cc: pg***********@postgresql.org
Subject: Re: [GENERAL] DBD::Pg problem
pg_atoi is the string to int converter. You're trying to insert it into
an integer field.

On Wed, Dec 03, 2003 at 03:45:53PM +0900, Ausrack Webmaster wrote:
Hi

I am trying to insert a simple email address into a text field,
and I get the below error:

DBD::Pg::st execute failed: ERROR: pg_atoi: error in
"<su*****@somedomain.com>": can't parse "<su*****@somedomain.com>"

I figure it is because of the < and @ in the value, but why does it
take these as operators even when the value has single quotes around
it? I have even tried binding the values and PG_TEXT beforehand and
still not luck.

Any help would be greatly appreciated.

Jason Frisch

---------------------------(end of
broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ "All that is needed for the forces of evil to triumph is for enough
good men to do nothing." - Edmond Burke "The penalty good people pay
for not being interested in politics is to be governed by people worse than themselves." - Plato


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #3

P: n/a
Ausrack Webmaster wrote:
The thing is...I am not. I am inserting it into a varchar field.


Are there any single quotes in the message body? They will wreak havoc
with the rest of the query. And why are you putting single quotes around
'$parent'?

What happens if you move the '$body' to the end:

$sql="insert into it_contact (email, to_email,
subject,modify,parent,details) values(?,?,?,now(),$parent,'$body')";

Alex
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #4

P: n/a

Tried that ...it is definetely the to_email field, not any others that
is causing
the problem.

Jason
-----Original Message-----
From: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org] On Behalf Of Alex Satrapa
Sent: Wednesday, December 03, 2003 6:31 PM
To: Ausrack Webmaster
Cc: pg***********@postgresql.org
Subject: Re: [GENERAL] DBD::Pg problem
Ausrack Webmaster wrote:
The thing is...I am not. I am inserting it into a varchar field.


Are there any single quotes in the message body? They will wreak havoc
with the rest of the query. And why are you putting single quotes around

'$parent'?

What happens if you move the '$body' to the end:

$sql="insert into it_contact (email, to_email,
subject,modify,parent,details) values(?,?,?,now(),$parent,'$body')";

Alex
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #5

P: n/a
On Wednesday 03 December 2003 09:43, Ausrack Webmaster wrote:
Tried that ...it is definetely the to_email field, not any others that
is causing
the problem.


With the table schema you gave, the following seems to work fine for me. Only
changes from your example are to remove quoting on $parent and let
bind_param() sort out types by itself.
#!/usr/bin/perl -w

use DBI;

$dbh = DBI->connect("dbi:Pg:dbname=DBNAMEHERE", "", "");

do_ins('alpha','beta','Blah1');
do_ins('d**@archonet.com','d**@archonet.com','Blah 2');
do_ins('<de*@archonet.com>','d**@archonet.com','Bl ah3');
do_ins('<de*@archonet.com>','<de*@archonet.com>',' Blah4');

$dbh->disconnect;
exit;

sub do_ins {
my ($from,$to,$subject) = @_;
my $body = 'BBB';
my $parent = 0;

print STDERR "Trying f/t = $from / $to\n";
$sql="insert into it_contact (email, to_email, subject,
details,modify,parent) values(?,?,?,'$body',now(),$parent)";
$sth = $dbh->prepare($sql);
$sth->bind_param(1, $from);
$sth->bind_param(2, $to);
$sth->bind_param(3, $subject);
$sth->execute;
print STDERR "Ending f/t = $from / $to\n\n";
}

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #6

P: n/a

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I am trying to insert a simple email address into a text field,
and I get the below error:

DBD::Pg::st execute failed: ERROR: pg_atoi: error in
"<su*****@somedomain.com>": can't parse "<su*****@somedomain.com>"


Try a $dbh->trace(1) and see if that gives you any additional
information. Perhaps $parent is being inadvertantly set to a
string inside of the application?

- --
Greg Sabino Mullane gr**@turnstep.com
PGP Key: 0x14964AC8 200312030733

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

iD8DBQE/zdiRvJuQZxSWSsgRAu0RAJ4/b9QmzZs7/w9n/Ta58Ba6ZqffHQCg/mSm
l/qPB/RNfyhtXeRKEQksQwg=
=CXfi
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #7

P: n/a
When I have problems like this, I do something like this:
$sql="insert into it_contact (email, to_email,
subject,details,modify,parent) values(
'".$from."','".$to,"','".$subject."','".$body." ', now(),'".$parent."')";

Then you dont have to bind params. You can just prepare and execute.

Besides that, you can print the $sql string with the expanded variables
and copy and paste it into psql to see if that gets you a different
error to help debug if it is not already obvious when you see the whole SQL.

Hope this helps.
Barb

usrack Webmaster wrote:
The thing is...I am not. I am inserting it into a varchar field.

$sql="insert into it_contact (email, to_email, subject,
details,modify,parent) values(?,?,?,'$body',now(),'$parent')";
$sth = $dbh->prepare($sql);
$sth->bind_param(1, $from, {pg_type => DBD::Pg::PG_TEXT});
$sth->bind_param(2, $to, {pg_type => DBD::Pg::PG_TEXT});
$sth->bind_param(3, $subject, {pg_type => DBD::Pg::PG_TEXT});
$sth->execute;

\d it_contact;
Table "public.it_contact"
Column | Type |
Modifiers
------------+-----------------------------+-----------------------------
---------------------------------------
contact_id | integer | not null default
nextval('public.it_contact_contact_id_seq'::text)
email | character varying(100) |
to_email | character varying(100) |
subject | text |
fname | character varying(30) |
lname | character varying(30) |
kafname | character varying(30) |
kalname | character varying(30) |
details | text |
modify | timestamp without time zone |
status | smallint |
parent | integer |
Jason

-----Original Message-----
From: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org] On Behalf Of Martijn van
Oosterhout
Sent: Wednesday, December 03, 2003 3:52 PM
To: Ausrack Webmaster
Cc: pg***********@postgresql.org
Subject: Re: [GENERAL] DBD::Pg problem
pg_atoi is the string to int converter. You're trying to insert it into
an integer field.

On Wed, Dec 03, 2003 at 03:45:53PM +0900, Ausrack Webmaster wrote:
Hi

I am trying to insert a simple email address into a text field,
and I get the below error:

DBD::Pg::st execute failed: ERROR: pg_atoi: error in
"<su*****@somedomain.com>": can't parse "<su*****@somedomain.com>"

I figure it is because of the < and @ in the value, but why does it
take these as operators even when the value has single quotes around
it? I have even tried binding the values and PG_TEXT beforehand and
still not luck.

Any help would be greatly appreciated.

Jason Frisch

---------------------------(end of
broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



--
Barbara E. Lindsey,
COG RDC
Phone: (352) 392-5198 ext. 314 Fax: (352) 392-8162

----
CONFIDENTIALITY NOTICE: The information contained in this electronic
message is legally privileged and confidential and intended only for the
use of the individual(s) or entity(ies) named above. If the reader of
this message is not the intended recipient, you are hereby notified that
any dissemination, distribution, or copying of this email or any of it's
components is strictly prohibited. If you have received this email in
error, please contact the sender.
----
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #8

P: n/a
Barbara Lindsey <bl******@cog.ufl.edu> writes:
When I have problems like this, I do something like this:
$sql="insert into it_contact (email, to_email,
subject,details,modify,parent) values(
'".$from."','".$to,"','".$subject."','".$body." ',
now(),'".$parent."')";

Then you dont have to bind params. You can just prepare and execute.
If you do this in production (rather than just for debugging) you may
be vulnerable to an SQL injection attack...
Besides that, you can print the $sql string with the expanded
variables and copy and paste it into psql to see if that gets you a
different error to help debug if it is not already obvious when you
see the whole SQL.


....but it is useful for debugging.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #9

P: n/a
In article <3F**************@cog.ufl.edu>,
Barbara Lindsey <bl******@cog.ufl.edu> writes:
When I have problems like this, I do something like this:
$sql="insert into it_contact (email, to_email,
subject,details,modify,parent) values(
'".$from."','".$to,"','".$subject."','".$body." ', now(),'".$parent."')"; Then you dont have to bind params. You can just prepare and execute.


.... and get interesting results if one of the variables contains
quotes or backslashes.

I often use something like that:

$dbh->do (q{
INSERT INTO it_contact (email, to_email, subject, details, modify, parent)
VALUES (?, ?, ?, ?, ?, ?)
}, undef, $from, $to, $subject, $body, now(), $parent);

This lets DBI do the proper quoting for you.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #10

P: n/a
Hello,

I don't have your code but I think you are using "do" instead of
"prepare".
Sincerely,

Joshua D. Drake

Ausrack Webmaster wrote:
Hi

I am trying to insert a simple email address into a text field,
and I get the below error:

DBD::Pg::st execute failed: ERROR: pg_atoi: error in
"<su*****@somedomain.com>": can't parse "<su*****@somedomain.com>"

I figure it is because of the < and @ in the value, but why does it take
these as operators even
when the value has single quotes around it?
I have even tried binding the values and PG_TEXT beforehand and still
not luck.

Any help would be greatly appreciated.

Jason Frisch

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC
Postgresql support, programming, shared hosting and dedicated hosting.
+1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.