FWIW, I tested a simple "SELECT ?" with your '[EMAIL PROTECTED]' 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:[EMAIL PROTECTED] >> 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 >> "[EMAIL PROTECTED]". 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 "[EMAIL PROTECTED]" or >> "[EMAIL PROTECTED]", 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 >> [EMAIL PROTECTED]