AW: ? embed scalars in the sql

[Sage, Christian]

Francesco,

here is an amended and simplified version of your script that worked on my computer 
30secs ago:

---< snip

#!/usr/bin/perl -w
#
# test script to query an mysql database
#
use strict;
use DBI;

my ($sql, $dbh, $sth, $field1, $value1, $field2, $value2, @rows, $counter);

print "Enter the first fieldname (field1)       : "; chomp ($field1 = <>);
print "enter the value for field one (value1)   : "; chomp ($value1 = <>);
print "Enter the second fieldname (field2)      : "; chomp ($field2 = <>);
print "enter the value for field two (value2)   : "; chomp ($value2 = <>);

$dbh = DBI -> connect ("DBI:Oracle:SID", "sys", "***************") || die $DBI::errstr;
$sql = "SELECT * FROM sapr3.t000 WHERE $field1 = '$value1' and $field2 = '$value2'";
$sth = $dbh -> prepare($sql);
$sth -> execute();
$counter = "0";
while ( @rows = $sth  -> fetchrow_array())  {
    $counter++;
    print "@rows\n";
}
print "Number of records  =  $counter\n\n";
$dbh -> disconnect;

---< snip

I've tried to change as little possible, but for (obvious) security reasons I've 
changed the database name and password. The query returned exactly one row. I guess in 
your version the problem is that the substitution values for the field names end up 
being quoted, which gives you a condition that is always false. I've not verified 
that, though (and may get beaten over the head for suggesting it ;-). Hope this helps.

Cheers,
Christian Sage

-----Ursprüngliche Nachricht-----
Von: Francesco Scaglioni [mailto:[EMAIL PROTECTED]]
Gesendet am: Dienstag, 26. Juni 2001 13:43
An: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Betreff: RE: ? embed scalars in the sql

The following still reports nil records (I know they are there -
honest!!).

TIA - Francesco

#!/usr/bin/perl -w
#
# test script to query an mysql database
#
use strict;
use DBI;

my ($sql, $dbh, $sth, $field1, $value1, $field2, $value2, @rows, $counter);

print "Enter the first fieldname (field1)       : "; chomp ($field1 = <>);
print "enter the value for field one (value1)   : "; chomp ($value1 = <>);
print "Enter the second fieldname (field2)      : "; chomp ($field2 = <>);
print "enter the value for field two (value2)   : "; chomp ($value2 = <>);

$dbh = DBI -> connect ("DBI:mysql:ami","fgs") || die $DBI::errstr;
$sql = qq{SELECT * FROM testami WHERE ? = ? and ? = ?};
$sth = $dbh -> prepare($sql);
$sth -> execute($field1, $value1, $field2, $value2);
$sth -> execute();
$counter = "0";
while ( @rows = $sth  -> fetchrow_array())  {
    $counter++;
    print "@rows\n";
}
    print "Number of records  =  $counter\n\n";
$dbh -> disconnect;