Re: Perl Beginners problem with DBI

The first error i see is "WHERE CODE==$CODE" should be "WHERE CODE = $CODE". Your queries could be written a bit more ... professionally:

$dbh->do(
   'UPDATE INV SET SALESMAN = ? WHERE CODE = ?',
   undef, $SALESMAN, $CODE,
);
[download]

And be sure that you connect with RaiseError set to true while you are at it:

my $dbh = DBI->connect(
   $data_source, $username, $pass,
   {RaiseError => 1},
);
[download]

UPDATE:
Now i see the real culript:

my $updy="Y";
$dbh->do("UPDATE INV SET SOLD=$updy  ... ");
[download]

You need to quote that first!

$dbh->do("UPDATE INV SET SOLD='$updy'  ... ");
[download]

or just use placeholders instead and let DBI do the quoting:

$dbh->do("UPDATE INV SET SOLD = ? WHERE CODE = ?", undef,$updy,$CODE);
[download]

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

Comment on Re: Perl Beginners problem with DBI Select or Download Code

Replies are listed 'Best First'.
Re: Re: Perl Beginners problem with DBI by Anonymous Monk on Mar 17, 2004 at 14:49 UTC
Thanks that fixed my problem. I'm only wondering why you need the undef. I guess I'll have to look it up.	[reply]
3Re: Perl Beginners problem with DBI by jeffa (Bishop) on Mar 17, 2004 at 15:10 UTC
Very good question, and very good answer ... but it's hard to grok straight out of the docs. In the case of $dbh->do() ... i never use the attribute hash, which is why it is set to undef. It kinda gets in the way, but it is a small price to pay for the gain. I use the attribute hash when i am fetching rows from the DB. Here is a quick example, just replace the query with one of your own: `use DBI; use Data::Dumper; ... my $query = 'select * from foo'; my %attr = ( Slice => {} ); print Dumper $dbh->selectall_arrayref($query); print Dumper $dbh->selectall_arrayref($query, \%attr);` [download] The first DBI call returns a two dimensional array, or as we like to call it in Perl, a List of Lists (LoL). This is a nice "snapshot" of the table that is returned, one of the coolest features of Database programming in Perl. The second DBI call returns something even cooler. A List of Hashes (LoH). Each hash contains key/value pairs of the column name and its value for the current row. Just run the code and you should see what i mean. :) What's great about the second call is that you can then pass the results straight to your Templating Engine of choice, such as HTML::Template or Template. I gave another example over at Re: DBI fetchrow_hashref issue. Fun stuff. UPDATE: you are welcome zigdon, but credit where credit is due, i learned this from gmax. jeffa L-LL-L--L-LL-L--L-LL-L-- -R--R-RR-R--R-RR-R--R-RR B--B--B--B--B--B--B--B-- H---H---H---H---H---H--- (the triplet paradiddle with high-hat)	[reply] [d/l]
Re: 3Re: Perl Beginners problem with DBI by dragonchild (Archbishop) on Mar 17, 2004 at 17:52 UTC
In the spirit of TMTOWDI, one can also write `my $query = q(select * from foo); my $sth = $dbh->prepare_cached($query); print Dumper $sth->fetchall_arrayref({});` [download] The benefit here is that the parsed form of the query has been cached for future use. ------ We are the carpenters and bricklayers of the Information Age. Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.	[reply] [d/l]
Re^5: Perl Beginners problem with DBI (z) by zigdon (Deacon) on Mar 17, 2004 at 16:15 UTC
Wow! Thank you! I've been doing this with wrappers around *_arrayref for a while, and all this time I could have let the module do it for me! -- zigdon	[reply]
Re: Re: Re: Perl Beginners problem with DBI by rdfield (Priest) on Mar 17, 2004 at 15:06 UTC
That's where the statement specific attributes go. rdfield	[reply]