sybperl-l Archive

From: Mark Sutfin <MSutfin at affinitygroup dot com>
Subject: RE: How to use BCP and Perl
Date: Sep 19 2002 9:46PM

: On Thurs 9/19/2002, Steve Wechsler pointed me in this direction
:
:Mark, the best (or at least most straightforward, if you're already using
:Sybperl) way would probably be to use MSSQL Perl by Erland Sommarskog,
which
:can be found here:
:http://www.algonet.se/~sommar/mssql/
:It's based on Sybperl (although it doesn't a support community like
Sybperl;
:I've tried to convinced Erland to set up something, but he seems to prefer
:supporting on a case-by-case basis) so should be fairly easy for you to use
:(some of the calls are slightly different, so it's not just a simple
:changing of Use statements)...and yes, it supports BCP.

Steve,
Thanks up front for the help.. Here's the error message I get, with the code
that follows... There are only 9 rows in the $datafile, so rows copied must
be affected by another error in my code. The return value of $init_status is
0. I had expected 1 to be successful.

It errors out on the db_close statement. Values up to that point are:
$dbh = 'MSSQL::DBlib=HASH(0x1aaf084)'
$use_status = 1
$init_status = 0

I think I need to verify the init() prior to moving on. Any suggestions ?

TIA, Mark
-------------------------------------------

status: 0
rows_copied: 29258736
DB-Library error: 10071
	Bad bulk-copy direction.  Must be either IN or OUT.
DB-Library error: 10055
	bcp_init() must be called before any other bcp routines.

-------------------------------------------

use strict;
use MSSQL::DBlib;
use MSSQL::DBlib::Const::BCP;

my $user 	= 'msutfin';
my $pass 	= 'XXXXXXXX';
my $server 	= 'online-staging';
my $database 	= 'ccr_dev';
my $table	= 'personofferhistoryload';
my $datafile 	= 'g:\agisdev\docs\currdev\11425\bcp_infile.txt';
my $errfile 	= 'g:\agisdev\docs\currdev\11425\bcp_errfile.txt';
my $direction	= "DB_IN";
my $rows_copied	= ();

my $dbh 	= MSSQL::DBlib->dblogin($user, $pass, $server);
my $use_status 	= $dbh->dbuse($database);
my $init_status 	= $dbh->bcp_init($table, $datafile, $errfile,
$direction);
($status, $rows_copied) = $dbh->bcp_exec;
print "status: $status\n";
print "rows_copied: $rows_copied\n";

$dbh->dbclose;




-----Original Message-----
From: Wechsler, Steven [mailto:steven.wechsler@wsaccess.com]
Sent: Thursday, September 19, 2002 6:41 AM
To: SybPerl Discussion List
Subject: RE: How to use BCP and Perl


> On Tue, 2002-09-17 at 12:53, Mark Sutfin wrote:
> > To BCP data into an SQL Server db table, I have been using this code
> > snippet:
> > system 'bcp ccr_dev..personofferhistoryload in
> > g:\agisdev\docs\currdev\11425\bcp_infile.txt -c -e 
> bcpload_fail.txt -h
> > "ORDER (personid ASC)" -Uusername -Ppasswd -Sservername';
> > I would like to capture some of BCP's messages, (e.g. 
> starting to copy, rows
> > sent to server, packet size).
> > I realize this is not a list for SQL Server, but any 
> direction would be much
> > appreciated.
> > SQL Server 2000

Mark, the best (or at least most straightforward, if you're already using
Sybperl) way would probably be to use MSSQL Perl by Erland Sommarskog, which
can be found here:

http://www.algonet.se/~sommar/mssql/

It's based on Sybperl (although it doesn't a support community like Sybperl;
I've tried to convinced Erland to set up something, but he seems to prefer
supporting on a case-by-case basis) so should be fairly easy for you to use
(some of the calls are slightly different, so it's not just a simple
changing of Use statements)...and yes, it supports BCP.

Steve
--
Steve Wechsler/Application Manager/Wall St. Access
Steven.Wechsler@wsaccess.com
"There is no reason anyone would want a computer in their home." 
-- Ken Olson, president, chairman and founder of DEC, 1977