[Chicago-talk] perl DBI ?

[Warren Lindsey]

DBAs around the world smile upon your decision :-)

Richard Reina wrote:

>Warren,
>
>Thanks very much the reply.  In the end I thought it
>best to make one permanent table.
>
>Thanks again,
>
>Richard
>--- Warren Lindsey <wlindsey at blackhatlounge.net>
>wrote:
>
>  
>
>>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
>>> 
>>>
>>>      
>>>
>>_______________________________________________
>>Chicago-talk mailing list
>>Chicago-talk at pm.org
>>http://mail.pm.org/mailman/listinfo/chicago-talk
>>
>>    
>>
>
>_______________________________________________
>Chicago-talk mailing list
>Chicago-talk at pm.org
>http://mail.pm.org/mailman/listinfo/chicago-talk
>  
>