sybperl-l Archive

From: Knut Behrends <knb at gfz-potsdam dot de>
Subject: Re: problems porting Sybase::BCP to Sybase::BLK
Date: Nov 17 2006 10:48AM

michael.peppler@bnpparibas.com wrote:
> 
> Various issues...
> 
> First,
> 
> You need to include the HAS_IDENTITY => 1 in the same $blk->config()
> call as the rest of the configuration to enable IDENTITY_INSERT for the
> table. So I'd do something like
> 
> my $identity_flag = ( $contbl1{$tg} ? 1 : 0 );
> 
> $bcp->config( INPUT => sub { $_ = shift @rows || last; return @{$_} },
> ERRORS => ('G:\_svncode\scripts\trunk\blk.err.' . $tg. "." .. time()) ,
> OUTPUT => "$mydb.dbo.$tg", TAB_INFO => \@fieldnames, NULL => qq{undef},
> HAS_IDENTITY => $identity_flag );
> 
> Second, I don't understand why you pass qq(undef) for the NULL pattern.

I have passed in many different expressions such as, for instance,
q(^$), but Sybase::BLK *always* complains about the $null_pattern
variable being undefined although it isn't undefined, $null_pattern
should be the string "undef" or '^$'etc

> Are NULL values in the input stream empty strings (i.e. undef) or
> something else?

In the Input stream there is the following
Dumped with DBI::trace(5)

fetch num_fields=8
fetch col#0 ORGANISATION_ID datalen=4 displ=12
fetch col#1 ORGANISATION_NAME datalen=45 displ=151
fetch col#2 ORGANISATION_LOCATION datalen=9 displ=51
fetch col#3 ORGANISATION_COUNTRY datalen=26 displ=51
fetch col#4 ORGANISATION_URL datalen=8 displ=151
fetch col#5 ORGANISATION_ACRONYM datalen=-1 displ=16
fetch col#6 ORGANISATION_CATEGORY datalen=8 displ=16
fetch col#7 ORGANISATION_DESCRIPTION datalen=-1 displ=51
2   <- fetch= [ '7193' 'University of Karlsruhe, Geological Institute'
'Karlsruhe' 'Baden-Wurttemberg, Germany' 'some_url' undef 'academic'
undef ] row21

This data comes from a MSSQL server fetched with DBD::ODBC on a windows
machine.


> I'm not sure why the SET CHAR_CONVERT option doesn't work - although it
> may be that you need to set this at the BLK API level, in which case you
> should probably set the client charset when you create the connection.

Meanwhile I have found out how to do this myself, I am using

my $DSN_target  =
"dbi:Sybase:server=$server;database=$mydb;bulkLogin=1;scriptName=$scriptName;charset=iso_1";


by the way, is it bulkLogin=1 or bcpLogin = 1 , or any of them? Both
seem to work.

and by the way again,

I think the documentation of this topic "Experimental Bulk-Load
Functionality"  in the perldoc for  DBD::Sybase can be improved :

my $dbh = DBI->connect(’dbi:Sybase:server=MY_SERVER;bulkLogin=1’, $user,
$pwd);

$dbh->begin_work;  # optional.
my $sth = $dbh->prepare("insert the_table values(?, ?, ?, ?, ?)",
  {syb_bcp_attribs => { identity_flag => 0,  identity_column => 0 }}});


There should be two closing parens,  }} instead of }}}



The connection string in the perldoc examples should include the
charset=iso_1 at least once .



The error handler example also can be improved.

You set the handler like this:
    DBD::Sybase::syb_set_cslib_cb(\&handler);

is wrong, it should be
    DBD::Sybase::set_cslib_cb(\&handler);

as can be seen in the xblk.t file

There is a $msg  variable in the cslib_handler which is not defined,
shouldn't  it be $errmsg instead?

          sub cslib_handler {
            my ($layer, $origin, $severity, $errno, $errmsg, $osmsg,
$blkmsg) =
@_;

            print "Layer: $layer, Origin: $origin, Severity: $severity,
Error: $errno\n";
            print $msg;
            print $osmsg if($osmsg);
            print $blkmsg if $blkmsg;

            return 1 if($errno == 36)

            return 0;
          }







I have also used the new "Experimental Bulk-Load Functionality" of
DBD::Sybase, with some success. It works fine with the first 10 rows of
each table, although with the full tables it creates error messages and
then it crashes perl.exe .

DBD::Sybase::st execute failed: cs_convert: cslib user api layer: common
library error: The conversion/operation was stopped due to a syntax
error in the source field. at scr.pl line 215.

Maybe I can find out myself what the data conversion issue is here. Date
values, blob lengths or others.


Knut

> Michael
> 
> 
>