[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