[rochester-pm-list] DBI-Oracle Update..
Fred Edmister
fedm at pkcommunications.com
Wed Dec 1 14:37:01 CST 1999
UGH!! Well, it seems this whole time I've been going by a printout given
to me a couple days ago by our DBA with ALPHA entries as the store id, I
find that it's been changed, and is now all NUMERIC (and forced to be
numbers only) entries... Now I see the problem... Thanks you all for the
help!! I might go check that oraperl and see how that works... :) Thanks
again for everything everyone!!
Fred
At 03:02 PM 12/1/99 -0500, you wrote:
>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
>>
>>
>>
>
>
>
>
More information about the Rochester-pm
mailing list