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,

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.