[GENERAL] DBD::Pg problem
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 "<[EMAIL PROTECTED]>": can't parse "<[EMAIL PROTECTED]>" 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
[GENERAL] DBD::Pg problem?
The DBD::Pg perl module for some reason has a limit of 8140 bytes when inserting, and I don't see why (yeah, I'll be contacting the authors there too), but I thought I'd ask here... I'm writing a Postgres-backed mail client, and storing emails in the database is obviously a little difficult when you can't insert more than 8k. Does anyone know why this limit exists, or if there's a way around it? John -- # John Madden [EMAIL PROTECTED] ICQ: 2EB9EA # FreeLists, Free mailing lists for all: http://www.freelists.org # UNIX Systems Engineer, Ivy Tech State College: http://www.ivy.tec.in.us # Linux, Apache, Perl and C: All the best things in life are free!
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 > "<[EMAIL PROTECTED]>": can't parse "<[EMAIL PROTECTED]>" > > 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 <[EMAIL PROTECTED]> 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 pgp0.pgp Description: PGP signature
Re: [GENERAL] DBD::Pg problem
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: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Martijn van
Oosterhout
Sent: Wednesday, December 03, 2003 3:52 PM
To: Ausrack Webmaster
Cc: [EMAIL PROTECTED]
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
> "<[EMAIL PROTECTED]>": can't parse "<[EMAIL PROTECTED]>"
>
> 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 <[EMAIL PROTECTED]> 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
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
Re: [GENERAL] DBD::Pg problem
Tried that ...it is definetely the to_email field, not any others that is causing the problem. Jason -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Alex Satrapa Sent: Wednesday, December 03, 2003 6:31 PM To: Ausrack Webmaster Cc: [EMAIL PROTECTED] 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
Re: [GENERAL] DBD::Pg problem
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('[EMAIL PROTECTED]','[EMAIL PROTECTED]','Blah2');
do_ins('<[EMAIL PROTECTED]>','[EMAIL PROTECTED]','Blah3');
do_ins('<[EMAIL PROTECTED]>','<[EMAIL PROTECTED]>','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
