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

[Patrick Ludwig]

Hmm, I'm not sure we're talking the same thing still, consider this simple
table.

first_name	last_name	store_id
Pat	Ludwig	3
Fred	Edmister	2

Now if the prepare reads
my $sth = $dbh->prepare('SELECT * FROM stores_master WHERE last_name='?');

and you use
$sth->execute($x);

The only values of $x that will return non null values are 'Ludwig' and
'Edmister'

for documentation, nothing beats perldoc,
try perldoc DBI and perldoc DBD::Oracle.
I don't think there is a book for DBI, but id'd be cool if there was,
especially if it was put out by O'Reilly. :-)

--Pat
From: owner-rochester-pm-list at pm.org
[mailto:owner-rochester-pm-list at pm.org]On Behalf Of Fred Edmister
Sent: Wednesday, December 01, 1999 1:37 PM
To: rochester-pm-list at happyfunball.pm.org
Subject: Re: [rochester-pm-list] DBI-DBD and Oracle


	Ok, so let me make sure I understand this... The variable $lastname should
be the name of the column in the database itself, not the feild being
entered at the prompt?  Sorry I sound so behind on this... Perl, I'm not
bad with, but this DBI thing is really gettin' my brain fried!  :)  Guess
I'll be gettin' more books too!!  (as if reading the code isn't enough for
us right, we have to read reference books too, and docs, and manuals,
and... (chuckle)  Sorry, kinda went off on a tangent.. Anyway, so is that
the way it works?  THat would explain a TON!  Also, is there a list of
commands and thier "options" available for this?  (lemme guess the DBI Perl
book...)  I think I'm gonna get that this afternoon... Thanks again for all
the help!!

	Fred


At 01:18 PM 12/1/99 -0500, you wrote:
>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
>
>
>