Using Sybase::BCP ~ getting NULL values

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

Problem: I'm using Sybase::BCP to import data and seeing nothing but NULL in the target table. Here's the code:

$bcp = new Sybase::BCP $user, $passwd, $server;
$bcp->config(INPUT => 'testStud2.bcp',
             OUTPUT => 'tempdb..agStudDB',
             FIELDS => 6,
             SEPARATOR => '|',
             ERRORS => 'BCP.err');
$bcp->run;
[download]

Sample data:

"AARDVARK, GLOIN"|04|1770 FAKE ADDRESS|(509) 666-6666|0888888|114
"AARDVARK, CHOMPERS"|06|1770 FAKE ADDRESS|(509) 665-6666|0999999|212
[download]

Using standard BCP puts my friends the Aardvarks in the table, but using Sybase::BCP inside Perl gives me the following:

1> select * from tempdb..agStudDB
2> go
 name                                                                 
+            grade 
        address                                                       
+                   prime_phone         
        altid                ent   
 ---------------------------------------------------------------------
+----------- ------
        --------------------------------------------------------------
+------------------ --------------------
        -------------------- ------
 NULL                                                                 
+            NULL  
        NULL                                                          
+                   NULL                
        NULL                 NULL  
 NULL                                                                 
+            NULL  
        NULL                                                          
+                   NULL                
        NULL                 NULL  

(2 rows affected)
[download]

So it looks like it's getting there, just not as expected. Any ideas ( preferably not involving standard bcp )?

Long live the Monks!

Bubnoff

Just tried this with Sybase::BLK and got:

This version of Sybase::CTlib doesn't support the CS_SERVERADDR property at /usr/local/lib/perl/5.8.8/Sybase/BLK.pm line 363

Comment on Using Sybase::BCP ~ getting NULL values Select or Download Code

Replies are listed 'Best First'.
Re: Using Sybase::BCP ~ getting NULL values by roboticus (Chancellor) on Nov 13, 2009 at 15:15 UTC
bubnoff: I've never used Sybase::BCP, but I've used bcp from the command line before, and I'd suspect that the configuration needs a bit more information. Somehow, it has to map the columns in the input file to the columns in the table. So try using the REORDER parameter to map them, something like: `$bcp->config( ... REORDER => { 1=>'name', 2=>'grade', 3=>'address', 4=>'prime_phone +', 4=>'prime_phone', 5=>'altid', 6=>'ent' }, ...);` [download] ...roboticus	[reply] [d/l]
Re^2: Using Sybase::BCP ~ getting NULL values by bubnoff (Novice) on Dec 18, 2009 at 18:33 UTC
Thanks for the reply Roboticus! I haven't had time to test your suggestion yet but hope to get a chance soon. I have a feeling though, that the original BCP is incompatible with newer versions of Sybase. Also, the BLK package doesn't seem to work on my machines due to a library conflict or similar. I may just stick with regular BCP or use Python's implementation which sits on top of the ASE libraries. I believe Peppler has switched to MS SQL Server and has stopped all the great work he was doing for the Sybase/PERL community. Such is life. Thanks again, Bub	[reply]
Re: Using Sybase::BCP ~ getting NULL values by runrig (Abbot) on May 06, 2016 at 20:53 UTC
Plugging relatively new library DBIx::BulkUtil, which is among other things a wrapper around Sybase bcp, Oracle sqlldr, and SybaseIQ "LOAD TABLE": `my ($dbh, $dbu) = DBIx::BulkUtil->syb_connect( Server => $server, Database => $database, User => $user, Password => $pw, ); # Default is "\|" column, "\n" row delimiter $dbu->bcp_in($table, $file);` [download] Columns in the file are assumed to be in the same order as the table, but that can be overridden.	[reply] [d/l]


laziness, impatience, and hubris
	PerlMonks