[Chicago-talk] perl DBI ?

[Richard Reina]

I'll try to explain as susinctly as possible.  The
table is a temp table that will be used temporarily
(on an off for a day or two ) by a user (worker in the
office) update the progress of the transaction he is
working on.  Hense, that's why the table is to take
the name of the transaction number.  By day two the
transaction is either completed or lost, so the data (
that was compilled for and is specific to that unique
transaction is useless and will be discarder with
"drop table".

I know, I know I could put the data in one table and
select in by the transaction ID number -- and I still
may end up doing it that way.  However, not only would
it mean row level locking as more than one user would
need to be updating the table eventhough they are all
working on diff. transactions., it would just seem
much cleaner to compile the data for the transaction
as it's own table and discard it when the transaction
is done.

Feed back ( but not flames :) ) is always welcome.

Thanks,

Richard


 
--- Pete Krawczyk <mongers at bsod.net> wrote:

> Subject: Re: [Chicago-talk] perl DBI ?
> From: Richard Reina <richard at rushlogistics.com>
> Date: Wed, 16 Mar 2005 09:15:58 -0800 (PST)
> 
> }However, do you happen to know why is it that it
> }works when it's a stand alone program?  Would you
> }happen to know of a work around for creating a
> table
> }with a unique name from w/in another program. 
> 
> Well, I'll be damned:
> 
> $ cat bind.pl
> #!/usr/bin/perl -Tw
> use strict;
> use DBI;
> my ($db,$un,$pw) = ('test');
> 
> my $dbh =
> DBI->connect("DBI:mysql:database=$db",$un,$pw);
> my $table_sth = $dbh->prepare('CREATE TABLE TMP_?
> (NAME CHAR(1))');
> $table_sth->execute($$); $table_sth->finish;
> my $insert_sth = $dbh->prepare("INSERT INTO TMP_$$
> VALUES (?)");
> $insert_sth->execute("A"); $insert_sth->finish;
> my $select_sth = $dbh->prepare("SELECT NAME FROM
> TMP_?");
> $select_sth->execute($$); my $vals =
> $select_sth->fetchall_arrayref;
> $select_sth->finish;
> print scalar(@$vals) . " rows in table TMP_$$\n";
> $dbh->do("DROP TABLE TMP_$$");
> $dbh->disconnect;
> exit(0);
> 
> $ ./bind.pl 
> 1 rows in table TMP_10493
> 
> Oh, here's why:  (perldoc DBI)
>     With most drivers, placeholders can't be used
> for any element of a
>     statement that would prevent the database server
> from validating the
>     statement and creating a query execution plan
> for it. For example:
> 
>       "SELECT name, age FROM ?"         # wrong
> (will probably fail)
>       "SELECT name, ?   FROM people"    # wrong (but
> may not 'fail')
> 
> So apparently the DBD::mysql driver isn't playing by
> the same rules as 
> most of the other DBD drivers, and is just inserting
> the binds by itself 
> at execute time.
> 
> So given that, I couldn't explain why your code
> works in one situation and 
> not the other.
> 
> I would also echo Andy's question about why you need
> to create a unique 
> table in your code.
> 
> -Pete K
> -- 
> Pete Krawczyk
>   perl at bsod dot net
> 
> 
> _______________________________________________
> Chicago-talk mailing list
> Chicago-talk at pm.org
> http://mail.pm.org/mailman/listinfo/chicago-talk
>