Hello everyone,
Thank to the help of everyone on the list, more especially the links you
gave me, I found the solution. Having not done perl for a long time, I
was doing the right logic but with the wrong notation.
This is my final working code :
$dbh_as400 = DBI->connect... (source)
$dbh_pgsql = DBI->connect... (dest)
$sth_as400 = $dbh_as400->prepare...
$sth_as400->execute...
do {
my @row;
my @array_data;
my $sth_pgsql = $dbh_pgsql->prepare...
# fetch each row in array
while (@row = $sth_as400->fetchrow_array())
{
@array_data = @row;
$sth_pgsql->execute(@array_data)...
}
} while ($sth_as400->{odbc_more_results});
This code is working !!!
I had only to change 3 times the '@' character : @array_data = @row; and
$sth_pgsql->execute(@array_data)...
Denis
Denis BUCHER a écrit :
> Hello,
>
> If I understand correctlyy execute_array is to do many INSERT at once
> AND to contain all colums ? It seem it could could what I want, then...
>
> Example from http://search.cpan.org/~timb/DBI/DBI.pm#bind_param_array
>
> $dbh->{RaiseError} = 1; # save having to check each method call
> $sth = $dbh->prepare("INSERT INTO staff (first_name, last_name, dept)
> VALUES(?, ?, ?)");
> $sth->bind_param_array(1, [ 'John', 'Mary', 'Tim' ]);
> $sth->bind_param_array(2, [ 'Booth', 'Todd', 'Robinson' ]);
> $sth->bind_param_array(3, "SALES"); # scalar will be reused for each row
> $sth->execute_array( { ArrayTupleStatus => \my @tuple_status } );
>
> Looks interesting but I think I found a better solution using my code...
>
> See next email
>
> Denis
>
> John Scoles a écrit :
>> I think you want is the |execute_array| <#___top> command
>>
>> however you can not use it like this.
>>
>> It would be more like this
>>
>> SQL='Insert into aTable (Y,X) values (?,?)
>> my $tuples = $sth->execute_array( { ArrayTupleStatus => \my
>> @tuple_status }, \...@y_values, \...@x_values );
>>
>>
>> In your case below you want to bind a single parameter to an array.
>> You might have to use |bind_param_array| <#___top> for that
>>
>> cheers
>>
>> Denis BUCHER wrote:
>>> Tim Bunce a écrit :
>>>
>>>>> Does someone knows if it's possible to specify an array when
>>>>> executing a
>>>>> prepared statement ?
>>>>>
>>>> The question seems confused...
>>>>
>>> Yes was difficult to explain ;-)
>>>
>>>
>>>>> $sth->execute(%array_data)
>>>>>
>>>> %array_data isn't an array, it's a hash.
>>>> See http://perldoc.perl.org/perldata.html
>>>>
>>> Yes absolutely, "associative array" i.e. "hash".
>>> I didn't developped in perl for a while...
>>>
>>>
>>>>> instead of :
>>>>> $sth->execute($array_data["firstname"],array_data["lastname"])
>>>>>
>>>> Square brackets are used to access array elements, but in that case the
>>>> value in the square brackets must be an integer.
>>>>
>>> Oh yes sorry...
>>>
>>>
>>>> It looks like you're trying to use a hash. For that you'd use curly
>>>> braces, like this:
>>>>
>>>> $sth->execute( $hash_data{"firstname"}, $hash_data{"lastname"} );
>>>>
>>>> Using 'hash slice' syntax (http://perldoc.perl.org/perldata.html#Slices)
>>>> you can shorten to:
>>>>
>>>> $sth->execute( @hash_data{"firstname", "lastname"} );
>>>>
>>> Ok I didn't know "hash slice" syntax, that's very interesting...
>>>
>>> What I want is to get the whole hash, therefore maybe what I want to do
>>> is this ?
>>>
>>> $sth->execute( @hash_data );
>>>
>>> In fact I should explain what I am trying to do :
>>>
>>> #SELECT FROM database 1 and INSERT INTO database 2...
>>> while (@row = $sth_source->fetchrow_array())
>>> {
>>> @array_data = @row;
>>> # INSERT
>>> $sth_pgsql->execute(@array_data) or die ;
>>> }
>>>
>>> What do you think ?
