Possible Problem with bind_param

Hi all,

I came across some funny behavior in Perl with the DBI package, and I
don't know how to explain it or why it is happening. I've tried
googling it, but haven't found anything yet. Here is the scenario:

I have some code that automatically tries to update a user's email
address if it has changed when they come through using an
integration. However, it seems that although there was no error with
the database, the update does not go through. Here is the code for
reference:

my $sql = "UPDATE m_user SET email = ?, first_name = ?,
last_name = ? WHERE id = ?;";
my $sth = $dbh->prepare($sql);
$sth->bind_param(1, $get_vars->{new_email});
$sth->bind_param(2, $get_vars->{userfirstname});
$sth->bind_param(3, $get_vars->{userlastname});
$sth->bind_param(4, $api_user_id);
$sth->execute;
$sth->finish;

So, as an example, say somehow the value of the previous email
address for this one user was "341". I print out the value of
"$get_vars->{new_email}" before and after the SQL statement, and the
value is what I expect it to be, something like
"341_43392s@tiimoodle.com". There are no errors and I hit the "$dbh-
>commit" line. But if I run the query to retrieve the email of that
same user, the email hasn't changed. Also, Rich helped me to tail
the database logs so that I could see the query as it was being
executed, and I saw that it has truncated the value for the email,
looking something like this: "UPDATE m_user SET email = 341,
first_name = ...".

So this leads me to believe that there is something in the Perl that
is causing the value to be truncated, something to do with bind_param
or something like that. I think it doesn't like the underscore
character, or something. What I don't understand is that other
values have been working, like "_12312451_1_36644s@tiibb.com" or
"lkj92mf0v2lkjd0f2_39443s@tiiwebct.com", but the moodle ones doesn't
work. However, I haven't been able to find any explanation or fix.

I've also tried reforming the query like "UPDATE m_user set email =
'" . $get->vars->{new_email} ."', first_name = '" . $get->vars->
{userfirstname} . "', last_name = '" . $get_vars->{userlastname} . '"
WHERE id = " . $api_user_id . "';";, but it wouldn't execute correctly.

I can correctly update the email directly in the database and in a
perl command line script that uses the same format of the code as
what I included above, but it won't work within the application.

I was wondering if anyone else ever encountered anything like this or
new of a solution? I'm using Perl 5.8.7 and DBI 1.52 and PostgreSQL
8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian
1:3.3.5-13) as the database.

Thanks,

Dave
davidw@iparadigms.com

RE: Possible Problem with bind_param

David Wu wrote:
[snip]
>=20
> I have some code that automatically tries to update a user's email
> address if it has changed when they come through using an
> integration. However, it seems that although there was no error with
> the database, the update does not go through. Here is the code for
> reference:
>=20
> my $sql =3D "UPDATE m_user SET email =3D ?, first_name =
=3D ?,
> last_name =3D ? WHERE id =3D ?;";
> my $sth =3D $dbh->prepare($sql);
> $sth->bind_param(1, $get_vars->{new_email});
> $sth->bind_param(2, $get_vars->{userfirstname});
> $sth->bind_param(3, $get_vars->{userlastname});
> $sth->bind_param(4, $api_user_id);
> $sth->execute;
> $sth->finish;

I see you aren't checking for errors. Is $dbh->{RaiseError} turned on?

Regards,
Philip

RE: Possible Problem with bind_param

Here's a guess, as I'm not familiar with your particular Database and DBD.

It looks like the DBD is treating your value as in integer. Perhaps the DBD looks at the first part of the string, and thinks, "here's a number, so I'll pass an integer type to the database engine".

Suggestion: try adding a type attribute to the the bind_param call, forcing the value to treated as a CHAR type.

-----Original Message-----
From: David Wu [mailto:davidw@iparadigms.com]
Sent: Friday, November 10, 2006 4:34 PM
To: dbi-users@perl.org
Subject: Possible Problem with bind_param


Hi all,

I came across some funny behavior in Perl with the DBI package, and I
don't know how to explain it or why it is happening. I've tried
googling it, but haven't found anything yet. Here is the scenario:

I have some code that automatically tries to update a user's email
address if it has changed when they come through using an
integration. However, it seems that although there was no error with
the database, the update does not go through. Here is the code for
reference:

my $sql = "UPDATE m_user SET email = ?, first_name = ?,
last_name = ? WHERE id = ?;";
my $sth = $dbh->prepare($sql);
$sth->bind_param(1, $get_vars->{new_email});
$sth->bind_param(2, $get_vars->{userfirstname});
$sth->bind_param(3, $get_vars->{userlastname});
$sth->bind_param(4, $api_user_id);
$sth->execute;
$sth->finish;

So, as an example, say somehow the value of the previous email
address for this one user was "341". I print out the value of
"$get_vars->{new_email}" before and after the SQL statement, and the
value is what I expect it to be, something like
"341_43392s@tiimoodle.com". There are no errors and I hit the "$dbh-
>commit" line. But if I run the query to retrieve the email of that
same user, the email hasn't changed. Also, Rich helped me to tail
the database logs so that I could see the query as it was being
executed, and I saw that it has truncated the value for the email,
looking something like this: "UPDATE m_user SET email = 341,
first_name = ...".

So this leads me to believe that there is something in the Perl that
is causing the value to be truncated, something to do with bind_param
or something like that. I think it doesn't like the underscore
character, or something. What I don't understand is that other
values have been working, like "_12312451_1_36644s@tiibb.com" or
"lkj92mf0v2lkjd0f2_39443s@tiiwebct.com", but the moodle ones doesn't
work. However, I haven't been able to find any explanation or fix.

I've also tried reforming the query like "UPDATE m_user set email =
'" . $get->vars->{new_email} ."', first_name = '" . $get->vars->
{userfirstname} . "', last_name = '" . $get_vars->{userlastname} . '"
WHERE id = " . $api_user_id . "';";, but it wouldn't execute correctly.

I can correctly update the email directly in the database and in a
perl command line script that uses the same format of the code as
what I included above, but it won't work within the application.

I was wondering if anyone else ever encountered anything like this or
new of a solution? I'm using Perl 5.8.7 and DBI 1.52 and PostgreSQL
8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian
1:3.3.5-13) as the database.

Thanks,

Dave
davidw@iparadigms.com

Re: Possible Problem with bind_param

Hi Philip,

Yes, $dbh->{RaiseError} is set to 1. I have the code surrounded in
an eval that does a rollback if there is an error, or a commit if
there is no error. The code is committing and the query is being
executed, just not with the value I expect.

Thanks,
Dave

On Nov 10, 2006, at 4:21 PM, Garrett, Philip ((MAN-Corporate)) wrote:

> David Wu wrote:
> [snip]
>>
>> I have some code that automatically tries to update a user's email
>> address if it has changed when they come through using an
>> integration. However, it seems that although there was no error with
>> the database, the update does not go through. Here is the code for
>> reference:
>>
>> my $sql = "UPDATE m_user SET email = ?, first_name = ?,
>> last_name = ? WHERE id = ?;";
>> my $sth = $dbh->prepare($sql);
>> $sth->bind_param(1, $get_vars->{new_email});
>> $sth->bind_param(2, $get_vars->{userfirstname});
>> $sth->bind_param(3, $get_vars->{userlastname});
>> $sth->bind_param(4, $api_user_id);
>> $sth->execute;
>> $sth->finish;
>
> I see you aren't checking for errors. Is $dbh->{RaiseError} turned
> on?
>
> Regards,
> Philip

Re: Possible Problem with bind_param

Hi Brian,

Thanks for the suggestion. I thought that might be the case as well,
and I've already tried adding the type attribute as a varchar, but
there was no change.

Dave

On Nov 10, 2006, at 4:31 PM, CAMPBELL, BRIAN D (BRIAN) wrote:

> Here's a guess, as I'm not familiar with your particular Database
> and DBD.
>
> It looks like the DBD is treating your value as in integer.
> Perhaps the DBD looks at the first part of the string, and thinks,
> "here's a number, so I'll pass an integer type to the database
> engine".
>
> Suggestion: try adding a type attribute to the the bind_param call,
> forcing the value to treated as a CHAR type.
>
> -----Original Message-----
> From: David Wu [mailto:davidw@iparadigms.com]
> Sent: Friday, November 10, 2006 4:34 PM
> To: dbi-users@perl.org
> Subject: Possible Problem with bind_param
>
>
> Hi all,
>
> I came across some funny behavior in Perl with the DBI package, and I
> don't know how to explain it or why it is happening. I've tried
> googling it, but haven't found anything yet. Here is the scenario:
>
> I have some code that automatically tries to update a user's email
> address if it has changed when they come through using an
> integration. However, it seems that although there was no error with
> the database, the update does not go through. Here is the code for
> reference:
>
> my $sql = "UPDATE m_user SET email = ?, first_name = ?,
> last_name = ? WHERE id = ?;";
> my $sth = $dbh->prepare($sql);
> $sth->bind_param(1, $get_vars->{new_email});
> $sth->bind_param(2, $get_vars->{userfirstname});
> $sth->bind_param(3, $get_vars->{userlastname});
> $sth->bind_param(4, $api_user_id);
> $sth->execute;
> $sth->finish;
>
> So, as an example, say somehow the value of the previous email
> address for this one user was "341". I print out the value of
> "$get_vars->{new_email}" before and after the SQL statement, and the
> value is what I expect it to be, something like
> "341_43392s@tiimoodle.com". There are no errors and I hit the "$dbh-
>> commit" line. But if I run the query to retrieve the email of that
> same user, the email hasn't changed. Also, Rich helped me to tail
> the database logs so that I could see the query as it was being
> executed, and I saw that it has truncated the value for the email,
> looking something like this: "UPDATE m_user SET email = 341,
> first_name = ...".
>
> So this leads me to believe that there is something in the Perl that
> is causing the value to be truncated, something to do with bind_param
> or something like that. I think it doesn't like the underscore
> character, or something. What I don't understand is that other
> values have been working, like "_12312451_1_36644s@tiibb.com" or
> "lkj92mf0v2lkjd0f2_39443s@tiiwebct.com", but the moodle ones doesn't
> work. However, I haven't been able to find any explanation or fix.
>
> I've also tried reforming the query like "UPDATE m_user set email =
> '" . $get->vars->{new_email} ."', first_name = '" . $get->vars->
> {userfirstname} . "', last_name = '" . $get_vars->{userlastname} . '"
> WHERE id = " . $api_user_id . "';";, but it wouldn't execute
> correctly.
>
> I can correctly update the email directly in the database and in a
> perl command line script that uses the same format of the code as
> what I included above, but it won't work within the application.
>
> I was wondering if anyone else ever encountered anything like this or
> new of a solution? I'm using Perl 5.8.7 and DBI 1.52 and PostgreSQL
> 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian
> 1:3.3.5-13) as the database.
>
> Thanks,
>
> Dave
> davidw@iparadigms.com

RE: Possible Problem with bind_param

FWIW, I tested a simple "SELECT ?" with your '341_43392s@tiimoodle.com'
value against my Postgres 7.2 (DBD::Pg 1.21). It bound fine and
returned the correct value.


David Wu wrote:
> Hi Brian,
>=20
> Thanks for the suggestion. I thought that might be the case as well,
> and I've already tried adding the type attribute as a varchar, but
> there was no change.
>=20
> Dave
>=20
> On Nov 10, 2006, at 4:31 PM, CAMPBELL, BRIAN D (BRIAN) wrote:
>=20
>> Here's a guess, as I'm not familiar with your particular Database
>> and DBD.=20
>>=20
>> It looks like the DBD is treating your value as in integer.
>> Perhaps the DBD looks at the first part of the string, and thinks,
>> "here's a number, so I'll pass an integer type to the database
>> engine".=20
>>=20
>> Suggestion: try adding a type attribute to the the bind_param call,
>> forcing the value to treated as a CHAR type.
>>=20
>> -----Original Message-----
>> From: David Wu [mailto:davidw@iparadigms.com]
>> Sent: Friday, November 10, 2006 4:34 PM
>> To: dbi-users@perl.org
>> Subject: Possible Problem with bind_param
>>=20
>>=20
>> Hi all,
>>=20
>> I came across some funny behavior in Perl with the DBI package, and I
>> don't know how to explain it or why it is happening. I've tried
>> googling it, but haven't found anything yet. Here is the scenario:
>>=20
>> I have some code that automatically tries to update a user's email
>> address if it has changed when they come through using an
>> integration. However, it seems that although there was no error with
>> the database, the update does not go through. Here is the code for
>> reference:=20
>>=20
>> my $sql =3D "UPDATE m_user SET email =3D ?, first_name =
=3D ?,
>> last_name =3D ? WHERE id =3D ?;"; my $sth =3D
>> $dbh->prepare($sql); $sth->bind_param(1,
>> $get_vars->{new_email}); $sth->bind_param(2,
>> $get_vars->{userfirstname}); $sth->bind_param(3,
>> $get_vars->{userlastname}); $sth->bind_param(4,
>> $api_user_id); $sth->execute;
>> $sth->finish;
>>=20
>> So, as an example, say somehow the value of the previous email
>> address for this one user was "341". I print out the value of
>> "$get_vars->{new_email}" before and after the SQL statement, and the
>> value is what I expect it to be, something like
>> "341_43392s@tiimoodle.com". There are no errors and I hit the "$dbh-
>>> commit" line. But if I run the query to retrieve the email of that
>> same user, the email hasn't changed. Also, Rich helped me to tail
>> the database logs so that I could see the query as it was being
>> executed, and I saw that it has truncated the value for the email,
>> looking something like this: "UPDATE m_user SET email =3D 341,
>> first_name =3D ...".=20
>>=20
>> So this leads me to believe that there is something in the Perl that
>> is causing the value to be truncated, something to do with bind_param
>> or something like that. I think it doesn't like the underscore
>> character, or something. What I don't understand is that other
>> values have been working, like "_12312451_1_36644s@tiibb.com" or
>> "lkj92mf0v2lkjd0f2_39443s@tiiwebct.com", but the moodle ones doesn't
>> work. However, I haven't been able to find any explanation or fix.
>>=20
>> I've also tried reforming the query like "UPDATE m_user set email =3D
>> '" . $get->vars->{new_email} ."', first_name =3D '" . $get->vars->
>> {userfirstname} . "', last_name =3D '" . $get_vars->{userlastname} . =
'"
>> WHERE id =3D " . $api_user_id . "';";, but it wouldn't execute
>> correctly.=20
>>=20
>> I can correctly update the email directly in the database and in a
>> perl command line script that uses the same format of the code as
>> what I included above, but it won't work within the application.
>>=20
>> I was wondering if anyone else ever encountered anything like this or
>> new of a solution? I'm using Perl 5.8.7 and DBI 1.52 and PostgreSQL
>> 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian
>> 1:3.3.5-13) as the database.=20
>>=20
>> Thanks,
>>=20
>> Dave
>> davidw@iparadigms.com

RE: Possible Problem with bind_param

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


Philip Garrett:
> FWIW, I tested a simple "SELECT ?" with your '341_43392s@tiimoodle.com'
> value against my Postgres 7.2 (DBD::Pg 1.21). It bound fine and
> returned the correct value.

Yikes! Please update both of those right away - they are severely
old and unsupported. Might want to check DBI as well. :)

David Wu wrote:
> Thanks for the suggestion. I thought that might be the case as well,
> and I've already tried adding the type attribute as a varchar, but
> there was no change.

Can you show us the code you used to do so? You might also try this
variant and see if it helps:

$dbh->{pg_server_prepare} = 0;
my $sql = "UPDATE m_user SET email = ?, first_name = ?, last_name = ? WHERE id = ?";
my $sth = $dbh->prepare($sql);

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200611122008
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B90 6714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFFV8ePvJuQZxSWSsgRArF8AJ94D9ytn1HhGQgq1PgvJ3JkoBjmVQCg 6yJU
DJ9RMVFEKI8qw90z4YVwPkY=
=yXRj
-----END PGP SIGNATURE-----

Re: Possible Problem with bind_param

--Apple-Mail-44-450474907
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
delsp=yes;
format=flowed

Hi Greg,

Basically, it was the same code, but I just added the type parameter
to the bind_param call:

my $sql = "UPDATE m_user SET email = ?, first_name = ?,
last_name = ? WHERE id = ?;";
my $sth = $dbh->prepare($sql);
$sth->bind_param(1, $get_vars->{new_email}, SQL_VARCHAR);
$sth->bind_param(2, $get_vars->{userfirstname});
$sth->bind_param(3, $get_vars->{userlastname});
$sth->bind_param(4, $api_user_id);
$sth->execute;
$sth->finish;

Just out of curiosity, what would "$dbh->{pg_server_prepare} = 0" do?

Thanks,
Dave


On Nov 12, 2006, at 5:19 PM, Greg Sabino Mullane wrote:

>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> Philip Garrett:
>> FWIW, I tested a simple "SELECT ?" with your
>> '341_43392s@tiimoodle.com'
>> value against my Postgres 7.2 (DBD::Pg 1.21). It bound fine and
>> returned the correct value.
>
> Yikes! Please update both of those right away - they are severely
> old and unsupported. Might want to check DBI as well. :)
>
> David Wu wrote:
>> Thanks for the suggestion. I thought that might be the case as well,
>> and I've already tried adding the type attribute as a varchar, but
>> there was no change.
>
> Can you show us the code you used to do so? You might also try this
> variant and see if it helps:
>
> $dbh->{pg_server_prepare} = 0;
> my $sql = "UPDATE m_user SET email = ?, first_name = ?, last_name
> = ? WHERE id = ?";
> my $sth = $dbh->prepare($sql);
>
> - --
> Greg Sabino Mullane greg@turnstep.com
> PGP Key: 0x14964AC8 200611122008
> http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B90 6714964AC8
> -----BEGIN PGP SIGNATURE-----
>
> iD8DBQFFV8ePvJuQZxSWSsgRArF8AJ94D9ytn1HhGQgq1PgvJ3JkoBjmVQCg 6yJU
> DJ9RMVFEKI8qw90z4YVwPkY=
> =yXRj
> -----END PGP SIGNATURE-----
>
>


--Apple-Mail-44-450474907--

Re: Possible Problem with bind_param

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


> Basically, it was the same code, but I just added the type parameter
> to the bind_param call:
....
> Just out of curiosity, what would "$dbh->{pg_server_prepare} = 0" do?

It forces the placeholders to get prepared by DBD::Pg, and not by Postgres
itself. Of course, it depends on what version of everything you have.
(please tell us that too). Either way, I can't see how anything in the code
you gave can transform what is clearly a string to an int without giving any
errors. Are you sure that $get_vars is returning what you think it does?
Have you tried setting $dbh->trace(10); before the block? It will produce
enough output to choke a hippopotamus, but should help narrow down the
problem by showing the exact output that DBI is receiving.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200611151047
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B90 6714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFFWza+vJuQZxSWSsgRAmItAKCohBee+582js64Z31FdJewAEkVPgCf WU+Z
avhkU/7XqXykbtmTLZmuqAg=
=PdXh
-----END PGP SIGNATURE-----