RE: Possible Problem with bind_param

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


David Wu wrote:
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@xxxxxxxxxxxxxx]
Sent: Friday, November 10, 2006 4:34 PM
To: dbi-users@xxxxxxxx
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@xxxxxxxxxxxxx". 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@xxxxxxxxx" or
"lkj92mf0v2lkjd0f2_39443s@xxxxxxxxxxxx", 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@xxxxxxxxxxxxxx

.

Relevant Pages

  • Re: Possible Problem with bind_param
    ... 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. ... I came across some funny behavior in Perl with the DBI package, ...
    (perl.dbi.users)
  • RE: Possible Problem with bind_param
    ... as I'm not familiar with your particular Database and DBD. ... 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". ... I came across some funny behavior in Perl with the DBI package, ... I've also tried reforming the query like "UPDATE m_user set email = ...
    (perl.dbi.users)
  • Re: How to write multiuser application?
    ... > How can i write application the concurrently access the database ... Postgres, like all modern database management systems, allows ... Please post general perl questions to comp.lang.perl.misc in the ...
    (comp.lang.perl)
  • Re: DBI/DBD Oracle Connections
    ... > The Oracle ENV variables defined outside of perl are: ... > however, all subsequent OS calls from perl return failure, even though ... > Now if we connect to an Oracle database and provide the database name. ... > The DBD has been compiled against an Oracle 10.1.0.4 64 Bit database. ...
    (perl.dbi.users)
  • PostgreSQL and Customer Database
    ... I've been working on both SQL and Perl for a while now, ... get them all onto an SQL database, and be able to keep track of sales, as ... the database available to customers and potential customers, ...
    (perl.beginners)