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

I am currently writing code to transfer data
from a .dbf file/database to an Oracle database
using DBI. I've used this: 
    # Create new Xbase object using the file name passed in
    my $table = new XBase "$dbfFileName" or die XBase->errstr;
    
    # Get field names in the table
    my @fieldNames = $table->field_names;

[download]
To get the field names from the .dbf table. I then massage
it to try to prepare to create a list which I will use
to fetch data from the table: 
    # Create a select statement to get this data from the table
    my $selectStatement = "SELECT ";
    $selectStatement .= join(', ',@fieldNames) ; # Add fields seperate
+d by commas
    $selectStatement .= " FROM ./POIS.dbf";   # Add the dbf file
    print "$selectStatement\n\n\n" if $debugFlag;

    # Get handle by opening the .dbf file which has POI info in it
    my $dbh = DBI->connect("DBI:XBase:$directory") or die $DBI::errstr
+;

    my $sth = $dbh->prepare(" $selectStatement ") or die $DBI::errstr;
    $sth->execute() or die $sth->errstr(0);

[download]
This works great mainly because I can pass the string to
the prepare method.
I now want to get the data from the results
set which I will then use to insert into the Oracle database.
I am looking to avoid something like this: 
    # Walk through results set one row at a time
    while ( my (@row) = $sth->fetchrow_array())
    {
        my $LINK_ID = $row[0];
        my $POI_ID = $row[1];
        my $FAC_TYPE = $row[2];
        my $POI_NAME = $row[3];
        my $POI_LANGCD = $row[4];
        my $POI_NMTYPE = $row[5];
        my $POI_ST_NUM = $row[6];
 .....
        my $ACTION_COD = $row[26];
        my $COMMENTS = $row[27];
        my $POSITION_ = $row[28];
        my $TEST_ID = $row[29];

[download]
Our hope is to be able to read the column names from
the .dbf file and not have to update the code each
time we add a column.
I wanted/started to use this code: 
     my $localValues = "my \$";
    $localValues .= join(', $',@fieldNames) ; # 
    print "$localValues\n" if $debugFlag;

[download]
To create something resembling this where I
walk through the results set : 
     while ( my ($localValues) = $sth->fetchrow_array())

[download]
but the string I have created which looks like
this: 
  my $LINK_ID, $POI_ID, $FAC_TYPE, ....  $TEST_ID

[download]
is not cutting it because I can't get it to
expand $localValues to a line of code which looks like
this: 
while ( my (my $LINK_ID, $POI_ID, $FAC_TYPE, ....  $TEST_ID) = $sth->f
+etchrow_array())

[download]
Any suggestions on how I can create this list of scalars
so that I can read the data in this table by only using
the list of columns ?
I then plan to use these scalars to do something like
this: 
my $prepStatement = <<TO_END_SQL_STATEMENT;
insert into ORACLE_TABLE 
( LINK_ID, POI_ID, ......  TEST_ID ) 
values 
( '$LINK_ID', $POI_ID, ..... , $TEST_ID' )  
TO_END_SQL_STATEMENT

[download]
Hope I've explained this well enough,

Replies are listed 'Best First'.
Re: Syntax for list of scalars to be populated by fetchrow_array() ?
by imp (Priest) on Aug 22, 2006 at 19:12 UTC
    I would strong recommend against doing this: 
    '$LINK_ID'

    [download]
    Unless you are absolutely certain that no values containing single quotes appear in the source data. Instead you should use the quote() method provided by DBI, or use placeholders.

    I'm not sure if this will solve your problem, but if you would like to simplify the list of values being provided you could do something like this: 

    $dbh->{FetchHashKeyName} = 'NAME_lc';
my $val_loh = $dbh->selectall_arrayref($sql,{Slice => {}});

    [download]
    This will provide you with a list of hashrefs, with the keys of the hash being the lowercase version of the columns that were returned. In your example it could work like this: 
    use strict;
use warnings;
use DBI;

my $dbh = DBI->connect($dsn,$username,$password) or die $DBI::errstr;

# Request lowercase column names
$dbh->{FetchHashKeyName} = 'NAME_lc';

my $sql_read = ''; # Whatever SQL you are using to select the records


# Fetch a list of hashes.
# The {Slice => {}} is an option provided to DBI that requests the lis
+t of hashes

my $val_loh = $dbh->selectall_arrayref($sql_read,{Slice => {}});

for my $row (@$val_loh) {
    print "link_id = $row->{link_id}\n";
    my @columns      = keys %$row;
    my @placeholders = map {'?'} @columns;
    my $sql_write = sprintf "INSERT INTO ORACLE_TABLE (%s) VALUES (%s)
+",
              join(',',@columns),
              join(',',@placeholders);
    print $sql_write,"\n";
# Enable the following line after you confirm this code is correct
# $dbh->do($sql,{},@{$row}{@columns});
}

    [download]
[reply]
[d/l]
[select]
Re: Syntax for list of scalars to be populated by fetchrow_array() ?
by dirving (Friar) on Aug 22, 2006 at 20:44 UTC
    You might be able to do something like this (omitting the parts that you already have working): 
    # Use the list of field names you pulled from the dbf file...
# Make the column list for the query...
my $fields = join(",", @fieldnames);

# Make a string consisting of ?'s joined by commas to be used as DBI
# placeholders in the query, one ? for each column...
my $placeholders = join(",", ('?') x scalar @fieldnames);

# Build and prepare the query... 
my $insert_query = <<"__END_OF_SQL__";
    INSERT INTO oracle_table
    ($fields)
    VALUES ( $placeholders )
__END_OF_SQL__

my $oracle_sth = $dbh->prepare($insert_query);

# Loop over the rows from the .dbf and use the prepared statement to 
# insert them into the oracle DB.
while (my $row = $dbf_sth->fetchrow_arrayref) {
    $oracle_sth->execute(@$row);
}

    [download]
    Anytime you have a huge list of scalars like that it's a sure sign you should be using an array, hash, or other data structure to your advantage. In this case instead of using the big list of scalars you can just use a flat array for each row combined with DBI's prepared statements to do what you need.
[reply]
[d/l]

Back to Seekers of Perl Wisdom