[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