Date: Thu, 27 Sep 2001 01:29:52 -0400
From: Benjamin Goldberg <goldbb2@earthlink.net>
Subject: Re: Problems with $ character
Message-Id: <3BB2B950.A9B49380@earthlink.net>

JJ wrote:
> 
> "Benjamin Goldberg" <goldbb2@earthlink.net> wrote in message
> news:3BAE53E8.EFE10411@earthlink.net...
> > JJ wrote:
> > >
> > > Hi,
> > >
> > > Problem 1.
> > > Step 1.1: I have a perl script that collects table names from a
> > > Oracle database.
> > > Step 1.2: In that script it then execute another perl script with
> > > the table as argument, that extracts the table (data and
> > > structure).
> > >
> > > The table name from step 1.1 can contain the character '$' such as
> > > 'MLOG$_WI_PISECTIONUS'. When executing the second script it says:
> > > "Table MLOG does not exist"
> > >
> > > Earlier when I executed those scripts on Linux it worked with the
> > > fix in the first script:
> > > $replace='\$';
> > > while(@row = $sth->fetchrow_array())
> > > {
> > >   $tmp = $row[0];
> > >   # fix the '$' in the table name if it exist
> > >   $tmp =~ s/\$/$replace/g; ## <<<== The fix <<<<
> > >   push @tables, $tmp;
> > > }
> > >
> > > # For each table, dump the data
> > > foreach $table ( @tables )
> > > {
> > >   $outfile = "$dataDir$table$dumpFileExtension";
> > >   print "Dumping table $table for schema $dbSchema\n";
> > >   $command = "./oracledump_to_mySQL.pl --add-drop-table -u $dbSchema
> > > -p $password $database $table >> $outfile";
> > >   system($command);
> > > }
> >
> > Eww.  Why do it like this?  Surely dropping the table from within
> > your script would be better than calling an external program to do
> > it...
[snip my code, which just drops tables]

> NO. The snipplet:
> foreach $table ( @tables )
> {
>   $outfile = "$dataDir$table$dumpFileExtension";
>   print "Dumping table $table for schema $dbSchema\n";
> $command = "./oracledump_to_mySQL.pl --add-drop-table -u $dbSchema -p
> $password $database $table >> $outfile";
>   system($command);
> }
> 
> Exports the Oracle database to mySQL files (one file/table and each
> file containing DROP CREATE and N * INSERT).

Ok then.  You can still do all this in perl without needing to run an
external program.

my $dbi1 = DBI->connect( "DBI:Oracle", ..., {RaiseError=>1} );
my $dbi2 = DBI->connect( "DBI:mySQL", ...., {RaiseError=>1} );
my $query  = $dbi1->prepare( "SELECT * FROM ?" );
my $drop   = $dbi2->prepare( "DROP TABLE ?" );
foreach my $table ($dbi1->tables) {
        $query->execute($table);
        create_table( $table, $query );
        my $store = $dbi2->prepare(do { local $" = ", "; qq[
                INSERT INTO $table (@{$query->{NAME}})
                VALUES (@{[("?") x $query->{NUM_OF_FIELDS}]})
        ] } );
        while( my @row = $query->fetchrow_array ) {
                $store->execute( @row );
        }
        # $drop->execute( $table );
}

my %types;
sub create_table {
        my ($tablename) = @_;
        foreach( @{$query->{TYPE}} ) {
                $types{$_} ||= ($dbi1->type_info($_))[0]{TYPE_NAME};
        }
        my @names = @{ $query->{NAME} };
        my @types = map $types{$_}, @{$query->{TYPE}};
        my @create = map "$names[$_] $types[$_]", 0..$#names;
        
        my $create = $dbi2->prepare( do { local $" = ", ";
                "CREATE TABLE $tablename ( @create )" } );
        $create->execute;
}

> I'm surley _NOT_ want to drop my data source since this a way to move
> data and structure from Oracle to mySQL.

Ok.  It's not as if you had said that anywhere before now.

Maybe you thought you implied it, but it wasn't clear.

Anyway, regardless of whether you're just dropping tables, or moving
tables, there's no need to call any external program to do this, you
can, as I have shown above, do this entirely within one perl process.

NB: this code is untested.  I would suggest you leave $drop->execute
commented out until you are sure that the copying works right.

-- 
"I think not," said Descartes, and promptly disappeared.