RE: CGI/DBI - Can anyone work out why this fails

2005-02-14 Thread mike 
On Fri, 2005-02-11 at 08:02 -0500, Bob Showalter wrote:
 mike wrote:
  I have the following code
  
  $cat1;
  if ($cat1){
  my $cat1=%$category%;
 
 The my here is scoped to this if { } block, so you aren't changing the
 outer $cat1
 
  }
  else {
  $cat1=$category;
  }
  
  $row=$dbh-prepare(SELECT
  contact_id,first_name,last_name,organisation FROM tb_contacts  WHERE
  (organisation ILIKE ? OR last_name ILIKE ?) OR contact_id = ?);
  $cat1;
  
  print $cat1;
  $row-bind_param(1,$cat1);
  $row-bind_param(2,$cat1);
  $row-bind_param(3,$cat1);
 
 Why are you binding $cat1 to param 3 (contact_id)?
 
  $row-execute();
  
  this fails with
   DBD::Pg::st execute failed: ERROR:  invalid input syntax for integer:
  ford at /home/www/cgi-bin/con_role_add_sel2_org.pl line 33.,
  referer: http://localhost/cgi-bin/con_role_add_sel1.pl
 
 I'm guessing that contact_id is a numeric column?
 
 Set DBI-trace(2) before the call to execute and you can see exactly what's
 being passed to the backend.
 
  

apologies this is the current state (which still fails the same way

$cat1=%$category%;

$catid1=$category;





$row=$dbh-prepare(SELECT contact_id,first_name,last_name,organisation
FROM tb_contacts  WHERE organisation ILIKE ? OR last_name ILIKE ? OR
contact_id IN (SELECT contact_id FROM tb_contacts WHERE
contact_id= ?));
#$cat1;

print $cat1;
$row-bind_param(1,$cat1);
$row-bind_param(2,$cat1);
$row-bind_param(3,$catid1);
$row-execute();

output is
SELECT contact_id,first_name,last_name,organisation FROM tb_contacts
WHERE organisation ILIKE  '%ford%' OR last_name ILIKE  '%ford%' OR
contact_id IN (SELECT contact_id FROM tb_contacts WHERE contact_id=
'ford')
ERROR:  invalid input syntax for integer: ford



  It only fails if I have both the name and int WHERE Criteria - in psql
  it works fine
 
 

-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/ http://learn.perl.org/first-response

CGI/DBI - Can anyone work out why this fails

2005-02-11 Thread mike 
I have the following code

$cat1;
if ($cat1){
my $cat1=%$category%;
}
else {
$cat1=$category;
}

$row=$dbh-prepare(SELECT contact_id,first_name,last_name,organisation
FROM tb_contacts  WHERE (organisation ILIKE ? OR last_name ILIKE ?) OR
contact_id = ?);
$cat1;

print $cat1;
$row-bind_param(1,$cat1);
$row-bind_param(2,$cat1);
$row-bind_param(3,$cat1);
$row-execute();

this fails with 
 DBD::Pg::st execute failed: ERROR:  invalid input syntax for integer:
ford at /home/www/cgi-bin/con_role_add_sel2_org.pl line 33., referer:
http://localhost/cgi-bin/con_role_add_sel1.pl

It only fails if I have both the name and int WHERE Criteria - in psql
it works fine



-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/ http://learn.perl.org/first-response

RE: CGI/DBI - Can anyone work out why this fails

2005-02-11 Thread Bob Showalter 
mike wrote:
 I have the following code
 
 $cat1;
 if ($cat1){
 my $cat1=%$category%;

The my here is scoped to this if { } block, so you aren't changing the
outer $cat1

 }
 else {
 $cat1=$category;
 }
 
 $row=$dbh-prepare(SELECT
 contact_id,first_name,last_name,organisation FROM tb_contacts  WHERE
 (organisation ILIKE ? OR last_name ILIKE ?) OR contact_id = ?);
 $cat1;
 
 print $cat1;
 $row-bind_param(1,$cat1);
 $row-bind_param(2,$cat1);
 $row-bind_param(3,$cat1);

Why are you binding $cat1 to param 3 (contact_id)?

 $row-execute();
 
 this fails with
  DBD::Pg::st execute failed: ERROR:  invalid input syntax for integer:
 ford at /home/www/cgi-bin/con_role_add_sel2_org.pl line 33.,
 referer: http://localhost/cgi-bin/con_role_add_sel1.pl

I'm guessing that contact_id is a numeric column?

Set DBI-trace(2) before the call to execute and you can see exactly what's
being passed to the backend.

 
 It only fails if I have both the name and int WHERE Criteria - in psql
 it works fine


-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/ http://learn.perl.org/first-response