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

I'm currently trying to populate a database table with data from an array (which contains data from another database). However, BCP doesn't seem to accept an array, array reference or list as a valid INPUT source, saying it can't find the file or that the INPUT is a "ref but not a CODE ref".

I've tried using a subroutine to return the array, but get the same errors.

How can I get round this?

My code atm is as follows:

$bcp->config(INPUT = \@data,
             OUTPUT = 'db.dbo.info_tbl');
$bcp->run;

[download]

Replies are listed 'Best First'.
Re: Sybase::BCP and arrays
by demerphq (Chancellor) on Aug 15, 2002 at 12:19 UTC
    Er, minor point, but im fairly certain that ive read something by mpeppler the author of the Sybase DBI modules (DBI::Sybase) that you are actually beter off (speed primarily) shelling out to the normal sybase BCP executable. My own experience seems to indicate that this is better as well, and it isnt too hard to handle executing it from perl.

    HTH

    Yves / DeMerphq
    ---
    Software Engineering is Programming when you can't. -- E. W. Dijkstra (RIP)

[reply]
Re: Sybase::BCP and arrays
by rinceWind (Monsignor) on Aug 15, 2002 at 12:13 UTC
    Seems like you need a filename, not an array. But the routine can also take a callback. From the POD:

    Paramaters for config()

    DIRECTION

    The direction in which the bulkcopy operation is done. Can be 'IN' or 'OUT'. Default: 'IN' (Note: 'OUT' is not implemented yet.)

    INPUT

    Where BCP should take it's input from. It's a filename for bcp IN, it's a table name for bcp OUT. For bcp IN INPUT can also be a reference to a perl subroutine that returns the array to be inserted via bcp_sendrow().

    OUTPUT

    Where BCP should place it's output. It's a table name for bcp IN, a filename for bcp OUT.

    ...

    It seems like you want to make a callback routine such as: (Warning: untested code)
    
my $current_elem = 0;
sub getnext {
   $array[$current_elem++];
}

...

$bcp->config(INPUT = \&getnext,
             OUTPUT = 'db.dbo.info_tbl');

    [download]
    hth

    --rW

[reply]
[d/l]
Re: Sybase::BCP and arrays
by mpeppler (Vicar) on Aug 16, 2002 at 10:12 UTC
    I'll add my own comment here - you should try to use Sybase::BLK instead of Sybase::BCP if you can. It uses the same syntax, but is built with the Client Library API, which will avoid problems when loading to a DOL (row or data locked) table, or a partitioned table.

    This said - the previous poster is correct - if you have a large array you should probably shell out and run the bcp binary - it'll be a lot faster.

    Michael

[reply]

Back to Seekers of Perl Wisdom