CREATE TABLE DDL from DBI Statement

whereiskurt has asked for the wisdom of the Perl Monks concerning the following question:

Fellow Monks,

I have some really basic DB statement stuff:

    my $dbh = DBI->connect( 'DBI:ODBC:ALIAS', $username, $password);
    my $stmt = $dbh->prepare('SELECT * FROM SCHEMA.TABLE');
    $stmt->execute();
    ...
[download]

I want to be able to generate the DDL (CREATE TABLE ...) that could hold the returned rows.

I realise there are some deeper issues that could crop up, but I'm really hoping there is a solution based on the DBIs structs:

    my $type_ref =  $stmt->{TYPE};
    my $precision_ref =  $stmt->{PRECISION};
    my $scale_ref =  $stmt->{SCALE};
    my $colnames = $stmt->{NAME};
    my $nullable = $stmt->{NULLABLE};
[download]

I've started looking at SQL::Translator but it seems a bit heavy, and might not support DB2. I'm just a little overwhelmed by the amount of places to look ...

Does someone know of such a snippet or a good starting point? I'm sure I could hand code it, but, if I don't have to that'd be even better. :-)

Regards,
Kurt

PS: Long live the Monastery!!

Comment on CREATE TABLE DDL from DBI Statement Select or Download Code

Replies are listed 'Best First'.
Re: CREATE TABLE DDL from DBI Statement by jfroebe (Parson) on Jul 21, 2008 at 21:57 UTC
SQL::Translator is your best bet with Perl but you might be able to do this with a script from IBM. Jason L. Froebe Blog, Tech Blog	[reply]
Re: CREATE TABLE DDL from DBI Statement by jfroebe (Parson) on Jul 21, 2008 at 21:50 UTC
Generally, you will want to do it in SQL to keep the work on the server: `$dbh->do('select * into SOME_TABLE from SCHEMA.TABLE') or ....` Update: nevermind.. misread the op Jason L. Froebe Blog, Tech Blog	[reply] [d/l]
Re: CREATE TABLE DDL from DBI Statement by CountZero (Bishop) on Jul 21, 2008 at 22:40 UTC
Can you give us an example of what your `SELECT * FROM SCHEMA.TABLE` returns? CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James	[reply] [d/l]
Re^2: CREATE TABLE DDL from DBI Statement by whereiskurt (Friar) on Jul 21, 2008 at 23:36 UTC
Thanks for the follow-up! The output as CSV would look like this: `CONT_ID,DW_EFF_STRT_DT_KEY,DW_EFF_END_DT_KEY,ADMIN_CLIENT_ID,H_CREATE_ +DT,ADMIN_SYS_TP_CD,DW_ACTIVE_IND,H_ACTION_CODE 045234232344234874,42561,2234618,33ASDF948AKDH49234TI,"2005-10-03 11:0 +3:51.144836",2,1,I 002346234135653687,42556,2123123,TUIK84K9D90D0AF2L43I,"2005-11-09 06:4 +4:37.045530",2,1,I` [download] Essentially, I have some routines that query off DB tables and output CSV files (similar to DBD::CSV). Now I want to be able to output to an embedded SQL server, like SQLlite, but I first need to create a similarly structured table. I fully realise that I could use a simple template and fill in the blanks, but I figured maybe something like this might already exist elsewhere (?) Thanks for any help you can provide! Kurt	[reply] [d/l]
Re^3: CREATE TABLE DDL from DBI Statement by CountZero (Bishop) on Jul 22, 2008 at 05:24 UTC
I don't think that will work. The first line gives you just the field-names but as a CSV-file has no idea of the type, length, whether or not it is a key, ... you loose too much info to be able to rebuild a similar table. You definitely need something like SQL::Translator. It will read the table structure of your database and save it in an internal representation: that is wat the SQL::Translator::Parser::name_your_database_here does. Next you call SQL::Translator::Producer::name_your_other_database_here on this internal object and it will make you the SQL to build the structure to do so. Changing databases is as easy as replacing the type of the database in 'to'. Good thing is that there are parsers and producers for DB2! SQL::Translator is a big module but it pays to have a look at it. The full program could be as easy as: `use strict; use SQL::Translator; my $translator = SQL::Translator->new( add_drop_table => 1, no_comments => 0, show_warnings => 1, quote_table_names => 1, quote_field_names => 1, from => 'DBI', parser_args => { dsn => 'dbi:DB2:Your_Database;host=Your_Host', db_user => 'user', db_password => 'password', }, to => 'SQLite', ); my $output = $translator->translate or die $translator->error; print $output;` [download] CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James	[reply] [d/l]
Re^4: CREATE TABLE DDL from DBI Statement by whereiskurt (Friar) on Jul 22, 2008 at 13:08 UTC
Re^4: CREATE TABLE DDL from DBI Statement by whereiskurt (Friar) on Jul 22, 2008 at 21:09 UTC
Re: CREATE TABLE DDL from DBI Statement by dokkeldepper (Friar) on Jul 22, 2008 at 05:58 UTC
"I want to be able to generate the DDL (CREATE TABLE ...) that could hold the returned rows." Why dont you do a `SELECT * INTO <target> FROM <sourcetable>` [download] and a `TRUNCATE <target>` [download] afterwards?	[reply] [d/l] [select]
Re^2: CREATE TABLE DDL from DBI Statement by whereiskurt (Friar) on Jul 22, 2008 at 13:12 UTC
That would work fine if everything was in the same spot. I'm actually SELECTing records from a remote DB2, and then trying to loosely 'mirror' them locally to a SQLite file. Kurt	[reply]