fetchall_arrayref into bind_param_array

[Oliver G]

Hey Folk,
i have to do a Job to extract Data from one Database into another.
So i thing this is a Job for Perl. I make a fetchall_arrayref then i
insert this block into the other Database with bind_param_array. Now i
have the Array of Arrays but i can't extract an Array of Column to use
bind_param_array.

Have any one a Idea?

Any Hint welcome!

Olli

 

[Ben Morrow]

Hey Folk,
i have to do a Job to extract Data from one Database into another.
So i thing this is a Job for Perl. I make a fetchall_arrayref then i
insert this block into the other Database with bind_param_array. Now i
have the Array of Arrays but i can't extract an Array of Column to use
bind_param_array.

Have any one a Idea?

Any Hint welcome!

Hint: try posting some code, and telling us what it doesn't do that
you want it to.

Ben

 

[James Willmore]

On 12 Nov 2003 07:19:19 -0800

Hey Folk,
i have to do a Job to extract Data from one Database into another.
So i thing this is a Job for Perl. I make a fetchall_arrayref then
i insert this block into the other Database with bind_param_array.
Now i have the Array of Arrays but i can't extract an Array of
Column to use bind_param_array.

Have any one a Idea?

Any Hint welcome!

Hint: post some code.
Hint: *read* the DBI documentation (perldoc DBI)
Hint: as some of my previous posts have shown, improper use of terms
can be hazardious to your code

HTH

--
Jim

Copyright notice: all code written by the author in this post is
released under the GPL. http://www.gnu.org/licenses/gpl.txt
for more information.

a fortune quote ...
There's a fine line between courage and foolishness. Too bad
it's not a fence.

 

[Oliver G]

On 12 Nov 2003 07:19:19 -0800


Hint: post some code.
Hint: *read* the DBI documentation (perldoc DBI)
Hint: as some of my previous posts have shown, improper use of terms
can be hazardious to your code

HTH

--
Jim

Copyright notice: all code written by the author in this post is
released under the GPL. http://www.gnu.org/licenses/gpl.txt
for more information.

a fortune quote ...
There's a fine line between courage and foolishness. Too bad
it's not a fence.

Thank you her the Code:

$DW_dbh = DBI->connect("dbi:ODBC:TestIHLE", "TEST2", "TEST2")
or die "$DBI::errstr\n";

$hSel = $DW_dbh-> prepare ("select * from temptab");
$hSel->execute();
$rows=$hSel->fetchall_arrayref();
$hHosttab = $DW_dbh-> prepare ("insert into temptab values (?,?,?)");
$hHosttab->bind_param_array (1,$rows[here i have the Problem
undef?][0]);
$hHosttab->bind_param_array (2,$rows[here i have the Problem
undef?][1]);
$hHosttab->bind_param_array (3,$rows[here i have the Problem
undef?][2]);
$hHosttab->execute_array({ArrayTupleStatus => \my @tuple_status });

What i wanna do is to fetch a Array and Insert into a Table. But i
can't extract the column of all rows!

 

[James Willmore]

On 12 Nov 2003 23:07:17 -0800
<snip>

*PLEASE* read the posting guidelines.
http://mail.augustmail.com/~tadmc/clpmisc/clpmisc_guidelines.html

Thank you her the Code:

<snip>

I guess this is actually what you have, right?

---------------------------------------------------------------------
--
#this is a comment
$DW_dbh = DBI->connect("dbi:ODBC:TestIHLE", "TEST2", "TEST2")
or die "$DBI::errstr\n";

$hSel = $DW_dbh-> prepare ("select * from temptab");

$hSel->execute();

$rows=$hSel->fetchall_arrayref();

$hHosttab = $DW_dbh-> prepare ("insert into temptab values (?,?,?)");

#[here i have the Problem undef?]
$hHosttab->bind_param_array (1,$rows [0]);

#[here i have the Problem undef?]
$hHosttab->bind_param_array (2,$rows[1]);

#[here i have the Problem undef?]
$hHosttab->bind_param_array (3,$rows[2]);

$hHosttab->execute_array({ArrayTupleStatus => \my @tuple_status });
---------------------------------------------------------------------
------

Some may not think this is an efficent way to do this, but I do it
because it's more readable. Plus, the results of the query will not
return properly if the schema of the data source changes - which will
signal a change to the script.

Try (untested):
------------------------------------------------------------
$DW_dbh =
DBI->connect(
"dbi:ODBC:TestIHLE", "TEST2", "TEST2",
{RaiseError=>1}
)
or die "$DBI::errstr\n";

$hSel = $DW_dbh-> prepare ("select * from temptab");

#are you *sure* you want to select from temptab and then,
#insert the results into temptab - the *same* place the
#select results came from?
$hHosttab =
$DW_dbh-> prepare ("insert into temptab values (?,?,?)");

$hSel->execute();

#change the *_column_name" to the name of
#each column - for example:
#if the first column is "dog", then
#"$ref->{first_colum_name}" should read
#"$ref->{dog}"
while(my $ref = $fetchrow_hashref){
$hHosttab->execute(
"$ref->{first_colum_name}",
"$ref->{second_column_name}",
"$ref->{third_column_name",
{ArrayTupleStatus => \my @tuple_status }
);
}
------------------------------------------------------------

Notice that I coded for hash references versus array references. For
me, I prefer to use hash references, because I should *know* the
database schema - especially if I'm going to do inserts with the data
latter on. I'm not saying it's more efficent or that you can't use
array refererences - I do it because I can read easily what's going on
with the code.

You should consider changing the table name for the insert statement
you posted - unless you *like* infinite loops

HTH and does what you want it to do.

--
Jim

Copyright notice: all code written by the author in this post is
released under the GPL. http://www.gnu.org/licenses/gpl.txt
for more information.

a fortune quote ...
"Dying is a very dull, dreary affair. And my advice to you is to
have nothing whatever to do with it." -- W. Somerset Maugham

 

[Oliver G]

On 12 Nov 2003 23:07:17 -0800
Some may not think this is an efficent way to do this, but I do it
because it's more readable. Plus, the results of the query will not
return properly if the schema of the data source changes - which will
signal a change to the script.

Try (untested):
------------------------------------------------------------
$DW_dbh =
DBI->connect(
"dbi:ODBC:TestIHLE", "TEST2", "TEST2",
{RaiseError=>1}
)
or die "$DBI::errstr\n";

$hSel = $DW_dbh-> prepare ("select * from temptab");

#are you *sure* you want to select from temptab and then,
#insert the results into temptab - the *same* place the
#select results came from?
$hHosttab =
$DW_dbh-> prepare ("insert into temptab values (?,?,?)");

$hSel->execute();

#change the *_column_name" to the name of
#each column - for example:
#if the first column is "dog", then
#"$ref->{first_colum_name}" should read
#"$ref->{dog}"
while(my $ref = $fetchrow_hashref){
$hHosttab->execute(
"$ref->{first_colum_name}",
"$ref->{second_column_name}",
"$ref->{third_column_name",
{ArrayTupleStatus => \my @tuple_status }
);
}
------------------------------------------------------------

Notice that I coded for hash references versus array references. For
me, I prefer to use hash references, because I should *know* the
database schema - especially if I'm going to do inserts with the data
latter on. I'm not saying it's more efficent or that you can't use
array refererences - I do it because I can read easily what's going on
with the code.

You should consider changing the table name for the insert statement
you posted - unless you *like* infinite loops

HTH and does what you want it to do.

--
Jim

Copyright notice: all code written by the author in this post is
released under the GPL. http://www.gnu.org/licenses/gpl.txt
for more information.

a fortune quote ...
"Dying is a very dull, dreary affair. And my advice to you is to
have nothing whatever to do with it." -- W. Somerset Maugham

Thank you Jim but it doesn't what i wanna do.
I am searching for the fastest way to transfer data between 2
Databases. So i wanna do a Batchinsert with 100 or more Rows per call!

Any other Idea how to Transfer Data fastest via Perl between DB2 and
SqlServer?

Greetings
Olli