Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Re: perl code generators

by Generoso (Vicar)
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 drinking their drinks and smoking their pipes about the Monastery: (12)
As of 2014-09-17 14:08 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (81 votes), past polls