PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: jmanio19 at eos dot eos dot net (John Manion)
Subject: BCP and index drop Questions
Date: Feb 21 1996 6:06PM

Hi There,

First off, I'm completly new to sybperl and I apologize if any of this has
been hashed out before.  Also, this is a long note, and I apologize for that as
well, I'm trying to be thorough.  :-)

Version Stuff:

**** sybperl *****

	This is sybperl, version 2.03

	Sybase::DBlib version 1.26 1/31/96

	Copyright (c) 1991-1995 Michael Peppler

	This is sybperl, version 2.03

	Sybase::CTlib version 1.18 1/30/96      Beta

	Copyright (c) 1995 Michael Peppler
	Portions Copyright (c) 1995 Sybase, Inc.

*** Sybase ***

	Sybase DB-LIBRARY Version 10.0.1

*** Perl ***

	This is perl, version 5.001

			Unofficial patchlevel 1m.



The problems:


  I have a sybase table which consist of 14 fields.  7 of the fields are
  indexes.  My basic goal is to bcp a bunch of data into this table, but first
  I want to drop the indexes on the table, then do the bcp, then re-build the
  indexes so the bcp work without logging everything.

  I have a loop that builds an SQL string to drop the indexes, which works
  except for the first one in the loop.  I've added the same index name to 
  my list as the first and second and the second time it works fine.  I've
  also used different Indexes as the first one and it still fails.  This
  is the error I get:

  Server 'sybase_mcapdev', Line 1
		  Premature end-of-message while reading current row from host. 
		  Host program may have died.
  DB-Library error:
				  General SQL Server error: Check messages from the SQL Server.


  Once I get all the indexes dropped I start my bcp loop.  I pretty much stole 
  this from the bcp.pl script included in the eg dir of the sybperl 
  distribution.  I read the first line of my input file, split it into an array
  and call bcp_sendrow.  I get these error messages.

  DB-Library error:
        Column number out of range.
  DB-Library error:
        Column number out of range.
  DB-Library error:
        Attempt to convert data stopped by syntax error in source field.
  bcp_sendrow failed at row  at bin/tst-bcp.pl line 38,  line 1.


Below is my code and some sample data.  Any help is appreciated.


------------------------------------ CODE -------------------------------------

#! /usr/local/bin/perl

use Sybase::DBlib;

$ext = "jjj";

@mbrIndices = ("SSN", "NOAid", "ObscDate", "Name_Last", "DOB", "Name_First", "Address_Zip");

&data_bcp(*mbrIndices, "JMMember", "mbr.$ext.csv");


sub data_bcp {

   local (*Indices, $Table, $FileName) = @_;
   local ($Cnt, @dat, $Index, $SybSess);

   &BCP_SETL(&Sybase::DBlib::TRUE);

   $SybSess = Sybase::DBlib->dblogin('mcapdev', 'mcapdev', 'sybase_mcapdev');
   $SybSess->dbuse('member_db');

   $SybSess->bcp_init($Table, undef, "bcp.err", DB_IN);
   $SybSess->bcp_meminit(14);

   open(BCP_DATA, "$FileName") || die "Unable to open $FileName for reading";

   foreach $Index (@Indices) {
       $sqlStr = "drop index $Table.$Index";
       $SybSess->sql($sqlStr);
   }

   $Cnt = 0;
   while () {
     chop;
     @dat = split(/\t/);
     die "bcp_sendrow failed at row $Cnt" if ($SybSess->bcp_sendrow($SybSess, @dat) == FAIL);
     ++$Cnt;

     if (($Cnt % 100) == 0) {
        $Rows = $SybSess->bcp_batch;
        print "$Rows Rows Sent to Sybase Server\n";
     }

   }
   $Rows = $SybSess->bcp_done;
   print "$Rows rows Total Sent to Sybase Server\n";

   close BCP_DATA;

   foreach $Index (@Indices) {
       $sqlStr = "create Index $Index on $Table($Index)";
       $SybSess->sql($sqlStr);
   }

}

-------------------------------- DATA ------------------------------------------

0	"MGMember.1"	1	"mcapdev"	"2/21/1996 12:01"	"mcapdev"	"2/21/1996 12:01"	"2/21/1996 12:01"	"000000000"	"BALONIER"	"19950312"	"CAMERON"	"45305"	'Member ( MIDUOID MGMember.1 Name ( First CAMERON Middle A Last BALONIER  ) SSN 000000000 DOB 19950312 Address ( Street1 "4420 BERRY AVE" City BELLBROOK State OH Zip 45305  ) Gender Male NOAid MGMember.1  ) '
0	"MGMember.2"	1	"mcapdev"	"2/21/1996 12:01"	"mcapdev"	"2/21/1996 12:01"	"2/21/1996 12:01"	"000000000"	"MURPHY"	"19460608"	"JAMES"	"45405"	'Member ( MIDUOID MGMember.2 Name ( First JAMES Middle E Last MURPHY  ) SSN 000000000 DOB 19460608 Address ( Street1 "367 NILWOR AVENUE" City DAYTON State OH Zip 45405  ) Gender Male NOAid MGMember.2  ) '


-------------------------------- Sybase Table info -----------------------------

 Name                           Owner                         
	 Type                   
 ------------------------------ ------------------------------ 
	---------------------- 
 JMMember                       dbo                           
	 user table             

 Data_located_on_segment        When_created               
 ------------------------------ -------------------------- 
 default                               Feb 21 1996 12:10PM 

 Column_name     Type            Length Prec Scale Nulls Default_name   
	 Rule_name       Identity 
 --------------- --------------- ------ ---- ----- ----- --------------- 
	--------------- -------- 
 Status          smallint             2 NULL  NULL     1 NULL           
	 NULL                   0 
 NOAid           varchar             42 NULL  NULL     1 NULL           
	 NULL                   0 
 Lock            smallint             2 NULL  NULL     1 NULL           
	 NULL                   0 
 UpdtUser        varchar             32 NULL  NULL     1 NULL           
	 NULL                   0 
 UpdtDate        datetime             8 NULL  NULL     1 NULL           
	 NULL                   0 
 UserID          varchar             32 NULL  NULL     1 NULL           
	 NULL                   0 
 CreateDate      datetime             8 NULL  NULL     1 NULL           
	 NULL                   0 
 ObscDate        datetime             8 NULL  NULL     1 NULL           
	 NULL                   0 
 SSN             varchar              9 NULL  NULL     1 NULL           
	 NULL                   0 
 Name_Last       varchar             32 NULL  NULL     1 NULL           
	 NULL                   0 
 DOB             varchar              6 NULL  NULL     1 NULL           
	 NULL                   0 
 Name_First      varchar             32 NULL  NULL     1 NULL           
	 NULL                   0 
 Address_Zip     varchar             10 NULL  NULL     1 NULL           
	 NULL                   0 
 NOA             text                16 NULL  NULL     1 NULL           
	 NULL                   0 

 index_name           index_description                                       
	 index_keys                                                                                                                                                                                                                                                      
 -------------------- -------------------------------------------------------- 
	--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
 NOAid                nonclustered, unique located on default                 
	  NOAid                                                                                                                                                                                                                                                          
 ObscDate             nonclustered located on default                         
	  ObscDate                                                                                                                                                                                                                                                       
 SSN                  nonclustered located on default                         
	  SSN                                                                                                                                                                                                                                                            
 Name_Last            nonclustered located on default                         
	  Name_Last                                                                                                                                                                                                                                                      
 DOB                  nonclustered located on default                         
	  DOB                                                                                                                                                                                                                                                            
 Name_First           nonclustered located on default                         
	  Name_First                                                                                                                                                                                                                                                     
 Address_Zip          nonclustered located on default                         
	  Address_Zip                                                                                                                                                                                                                                                    
No defined keys for this object.

(7 rows affected, return status = 0)