[rochester-pm-list] DBI-DBD and Oracle

[Pat]

On Wed, Dec 01, 1999 at 12:39:28PM -0500, Fred Edmister wrote:
> At 12:25 PM 12/1/99 -0500, you wrote:
> 	Ok... Here's the code... :)  
> 
> {begin code}
>      my $sth = $dbh->prepare('SELECT * FROM stores_master WHERE store_id = ?')
>         or die "Couldn't prepare statement: " . $dbh->errstr;
>               print "Enter name> ";
>               while ($lastname = <>) {      # Read input from the user
>                   chomp $lastname;
>                   $sth->execute($lastname)             # Execute the query
>                   or die "Couldn't execute statement: " . $sth->errstr;

OK, I stripped the code down to the prepare and the execute for clarity.
It seems to me that there is some confusion here.  The prepare statement is looking
for a variable to match with the field 'store_id', you are prompting for a 'last name'.  The
oracle error you were getting 'ORA01722' supports that you are trying to match a character
string to a numeric field.

The prepare and execute statements should be related, as:
$sth=$dhp->prepare(blah blah WHERE somefield = '?');
$sth->execute($some_possible_value_of_somefield);

In your code, 'last name' bears no relation to 'store_id'

I believe the above code should look like:
 {begin code}
# the change is store_id => last_name
      my $sth = $dbh->prepare('SELECT * FROM stores_master WHERE last_name = ?')
# changed above line store_id => last_name
         or die "Couldn't prepare statement: " . $dbh->errstr;
               print "Enter name> ";
               while ($lastname = <>) {      # Read input from the user
                   chomp $lastname;
                   $sth->execute($lastname)             # Execute the query
                   or die "Couldn't execute statement: " . $sth->errstr;

Of course this presuposes that a 'last_name' field exists, your field may be named something
different, just change to suit.

One other option is to ask for the 'store_id' as input, but that seems to be not as useful to me, YMMV.

Let us know how it goes, if you have any problems just ask. :-)

--Pat Ludwig