Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Re: perl code generators

by Generoso (Parson)
on May 20, 2011 at 03:44 UTC ( #905847=note: print w/ replies, xml ) Need Help??


in reply to perl code generators

Maybe this example will help you,

in this example I am doing a copy of a table from Oracle to Mysql taking as a parameter just the name of the table:

#!/usr/bin/perl -w use strict; use DBI; use Date::Calc qw(:all); use Switch; use Time::HiRes qw/ time gettimeofday tv_interval /; $| = 1; my @Month_name = ( "JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SET", "OCT", "NOV", "DEC" ); sub tstampms { my $u_sec = time; my @date = localtime($u_sec); my $format = sprintf( "%4d-%s-%02d %02d:%02d:%02d.%06d", $date[5] + 1900, $Month_name[$date[4]], $date[3], $date[2], $date[1], $date[0], substr($u_sec, index($u_sec, '.') + 1) ); print $format, "\n";} #defalt name for the table my $hbn = 'trades'; $hbn = $ARGV[0] if $#ARGV > -1; # mysql database information my $db="db"; my $host="localhost"; my $userid="user"; my $passwd="password"; my $connectionInfo="dbi:mysql:$db;$host"; # make connection to database my $dbh = DBI->connect($connectionInfo,$userid,$passwd) || die( $DBI::errstr . "\n" ); # database information to connect to ORACLE my $port1='1521'; my $host1="192.168.133.127"; my $sid1="oradb1"; my $userid1="user"; my $passwd1="password"; #my $connectionInfo1="dbi:Oracle:HOST=$host1;SERVICE_NAME=$service1;PO +RT=$port1"; my $connectionInfo1="dbi:Oracle:HOST=$host1;SID=$sid1;PORT=$port1"; # connect to the Oracle server my $db1 = DBI->connect( $connectionInfo1, $userid1, $passwd1 ) || die( $DBI::errstr . "\n" ); $db1->{AutoCommit} = 0; $db1->{RaiseError} = 1; $db1->{ora_check_sql} = 0; $db1->{RowCacheSize} = 16; # primary key my $SEL2 = qq[SELECT cols.column_name FROM all_constraints cons, all_cons_columns cols WHERE cols.table_name = ? AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner]; my $sth1 = $db1->prepare($SEL2); $sth1->execute(uc($hbn)); my @row = $sth1->fetchrow_array(); my $PKeyn = $row[0]; print "Key field $PKeyn\n"; # get fields info trasformed to mysql format my $SEL3 = qq[select column_name, ( CASE data_type When 'VARCHAR2' THEN 'VARCHAR'||'('||data_length||')' When 'DATE' THEN 'DATE' When 'NUMBER' THEN 'DOUBLE'|| ( CASE nvl(data_precision,-1) when -1 then null else '('||data_precision||','||data_scale||')' end ) End) data_typ, decode(NULLABLE,'N','NOT NULL') nl from ALL_TAB_COLUMNS where table_name = ? order by table_name,column_id]; # excute SQL my $sth2 = $db1->prepare($SEL3); $sth2->execute(uc($hbn)); #drop table in mysql db schema my $query = "DROP TABLE IF EXISTS $hbn"; print "$query\n"; my $sth = $dbh->prepare($query); $sth->execute(); #construct create sql statement for Mysql $query = "CREATE TABLE $hbn (\n"; while ( my @row = $sth2->fetchrow_array() ) { foreach (@row) { $_ = ' ' if !defined($_); $query .= "$_ "; } $query .= ",\n"; } $query .= " PRIMARY KEY ($PKeyn)\n"; $query .= ") ENGINE=InnoDB DEFAULT CHARSET=latin1\n\n"; print "$query\n"; $sth2->finish(); #execute creat table $sth = $dbh->prepare($query); $sth->execute(); # creat select random row fom oracle table $SEL3 = qq[SELECT * FROM ( SELECT * FROM ].uc($hbn).qq[ ORDER BY dbms_random.value ) WHERE rownum < 11]; # excute SQL $sth2 = $db1->prepare($SEL3); $sth2->execute(); # assemble insert sql form results of Oracle query while ( my @row = $sth2->fetchrow_array() ) { $query = "INSERT INTO $hbn VALUES ("; my $j = 0; foreach my $fl (@row) { my $type2 = $sth2->{TYPE}[$j]; $j++; switch ($type2) { case 12 {$fl = '\''.$fl.'\'';} case 93 {$fl = '\''.$fl.'\''; $fl =~ s/JAN/01/; $fl =~ s/FEB/02/; $fl =~ s/MAR/03/; $fl =~ s/APR/04/; $fl =~ s/MAY/05/; $fl =~ s/JUN/06/; $fl =~ s/JUL/07/; $fl =~ s/AUG/08/; $fl =~ s/SEP/09/; $fl =~ s/OCT/10/; $fl =~ s/NOV/11/; $fl =~ s/DEC/12/; } # else { print "previous case $type2 not true" } } $query .= "$fl"; $query .= "," if $j < $sth2->{NUM_OF_FIELDS}; } $query .= ")"; print "$query\n"; # do the insert in mysql table $sth = $dbh->prepare($query); $sth->execute(); } print "\n"; # an other way of getting the oracle table fields information my $SEL1 = qq[select * from ].$hbn.qq[ where 0 = 1]; # excute SQL $sth1 = $db1->prepare($SEL1); $sth1->execute(); my $nf = $sth1->{NUM_OF_FIELDS}; for ( my $l = 0; $l < $nf; $l++ ) { my $name1 = $sth1->{NAME}[$l]; my $type1 = $sth1->{TYPE}[$l]; my $prec1 = $sth1->{PRECISION}[$l]; my $scle1 = $sth1->{SCALE}[$l]; my $tn1=$db1->type_info($type1)->{TYPE_NAME}; $tn1 =~ s/ PRECISION//; $tn1 =~ s/VARCHAR2/VARCHAR/; $tn1 =~ s/DECIMAL/DOUBLE/; print "Field number $l: name $name1 of type $type1 $tn1 with precisi +on $prec1,$scle1\n"; #print "$name1 $tn1($prec1"; #print ",$scle1" if $scle1 > 0; #print ") ,\n"; } tstampms(); # Close all connections END { $dbh->disconnect if defined($dbh); $db1->disconnect if defined($db1); }


Comment on Re: perl code generators
Download Code

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://905847]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (19)
As of 2015-07-02 16:37 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (44 votes), past polls