[Chicago-talk] perl DBI ?
Warren Lindsey
wlindsey at blackhatlounge.net
Wed Mar 16 17:08:53 PST 2005
Just add another column to a permanent temp table. This applies to any
database system. You have to keep track of the temp tables you're
creating somehow or else you end up with a system littered with temp tables.
There is a huge performance hit for creating new objects in a database
(DDL) vs inserting/updating/deleting a row (DML) because space must be
allocated and the data-dictionary must be updated. It fragments the
tablespaces in your database as you create and drop objects. It breaks
your transactional integrity because DDL requires a commit. It becomes
very difficult to estimate space usage when objects do not last long,
sometimes they are there, sometimes they aren't. It requires the
application user to have create and drop privileges that only your
application owner should have.
But hey, if you're going to do it, do it right :-) Create the table and
catch the exception/error if it exists. This let's the database do the
work and saves you a trip by not having to lookup whether it exists or
not. Same trick applies when inserting a row into a unique/primary key
column. Let the database do the work.
Richard Reina wrote:
>when I run these lines of code :
>
>my $T_NO = 12569;
>use DBI;
>my $dbh =
>DBI->connect("DBI:mysql:database=carr_search;192.168.0.1",user,password);
>
>my $q = "CREATE TABLE IF NOT EXISTS CS_? (
>ID_NO MEDIUMINT,
>NAME VARCHAR(30),
>TYPE CHAR(1)
>)";
>my $sth = $dbh->prepare($q);
>$sth->execute($T_NO);
>
>from w/in a program I get:
>
>DBD:mysql::st execute failed: You have an error in
>your SQL syntax near '12569 (
>ID_NO MEDIUMINT,
>NAME VARCHAR(30),
>TYP' at line 2 at ./carr_s.pl line 36.
>
>However if I cut and paste the exact same code and
>make it it's own program then execute it, it works
>perfectly. Can anyone tell me what's happening and how
>I can fix it?
>
>Thanks,
>
>Richard
>
>
>_______________________________________________
>Chicago-talk mailing list
>Chicago-talk at pm.org
>http://mail.pm.org/mailman/listinfo/chicago-talk
>
>
More information about the Chicago-talk
mailing list