sybperl-l Archive

From: "Marcotte, Lawrence" <Lawrence dot Marcotte at LibertyMutual dot com>
Subject: RE:
Date: Dec 11 1998 12:58PM

jrisley@nex-web4.corp.fedex.com  writes original message below.  This might
help


========================================
Larry Marcotte 
Principal Software Engineer
Liberty Mutual
225 Borthwick Ave
Mail Drop - 01S
Portsmouth NH 03801               
Phone: (603) 431-8400 x53064 
Fax:   (603) 431-1204                 
mailto:Lawrence.Marcotte@LibertyMutual.com 




It looks to me like your not completing your result loop.  I've had good
luck with the following code:

You can also use ct_sql for inserts, updates etc. but I choose not to
because it's difficult to process any errors.


$query = qq/
update students
set  addr = "50 BLUE ST."
where stno = "1011"/;

$rc = execloop($dbh,$query);  

warn "Bad return code from $query\n\n" if ($rc == CS_CMD_FAIL);


sub execloop
{

  my (
    $dbh,
    $query,
    $restype,
    $key,$val,
    $rc,
    %dat,
    );

  $dbh = shift;  # get the db handle
  $query = shift; # get the sql;

  $dbh->ct_execute($query);

  $restype = 0;

  while(($rc = $dbh->ct_results($restype)) == CS_SUCCEED)
  {
    # something bogus happened
    if ($restype == CS_CMD_FAIL)
    {
      $rc = $restype; #need to do this becuase restype gets wiped by
ct_cancel
      $dbh->ct_cancel(CS_CANCEL_ALL);  #clean up handle
      return $rc;
    }

    next if($restype == CS_CMD_DONE || $restype == CS_CMD_SUCCEED);


    while(%dat = $dbh->ct_fetch(1))
    {
      while (($key,$val) = each %dat)
      {
        print "column => $key\tvalue => $val\n";
      }
    }
  }

  print "End of Results Sets\n" if($restype == CS_END_RESULTS);
  print "Error!\n" if($restype == CS_FAIL);

  return $restype;
}

> -----Original Message-----
> From:	jrisley@nex-web4.corp.fedex.com
> [SMTP:jrisley@nex-web4.corp.fedex.com]
> Sent:	Thursday, December 10, 1998 6:38 PM
> To:	SybPerl Discussion List
> Subject:	
> 
> OBJECTIVE: To read in a | (pipe) delimited file and insert the data into a
> Sybase Database.
> 
> Below is my DATAFILE, OUTPUT and CODE. When I run the script, the first
> two
> rows of data make it into Sybase, but the last two rows do not. Being New
> to
> SybPerl I'm not sure how to address the errors that I am getting. I've
> used
> the same insert code in processing single request from a Web Form, but
> this
> is my first time to use the code in a nightly batch job.
> 
> Any assistance is greatly appreciated!!!!!!
> Jason
> 
> 
> 
> DATAFILE (input):
> 256025283061788703678          |5|0000022654|703678|AMPLIFIER, POWER RF
> C-55
> |HFDA |256025283061788|981113|981113
> 256040283294563703678          |5|0000166045|703678|AMPLIFIER, POWER RF
> C-55
> |DTTA |256040283294563|981204|981204
> 256120283081547109726          |5|0000022654|109726|MODULE, STATION
> CONTROL
> |HTOA |256120283081547|981113|981113
> 256166283318066703678          |5|0000166045|703678|AMPLIFIER, POWER RF
> C-55
> |AOOA |256166283318066|981204|981204
> 
> 
> OUTPUT:
> 
> C:\Dev\DADS_Parts\src>dads_import.sybpl
> CNT: 1  Row: 1
> 
> Open Client Message:
> Message number: LAYER = (1) ORIGIN = (1) SEVERITY = (1) NUMBER = (16)
> Message String: ct_command(): user api layer: external error: This routine
> cannot be called while results are pending for a command that has been
> sent
> to the server.
> CNT: 2  Row: 1
> 
> Open Client Message:
> Message number: LAYER = (1) ORIGIN = (1) SEVERITY = (1) NUMBER = (16)
> Message String: ct_command(): user api layer: external error: This routine
> cannot be called while results are pending for a command that has been
> sent
> to the server.
> CNT: 4  Row: 1
> 
> Open Client Message:
> Message number: LAYER = (1) ORIGIN = (1) SEVERITY = (1) NUMBER = (159)
> Message String: ct_cmd_drop(): user api layer: external error: This
> routine
> can be called only if the command structure is idle.
> 
> C:\Dev\DADS_Parts\src>
> 
> 
> 
> CODE:
> 
> #!C:\sybperl\bin\perl.exe
> 
> use DBI;
> use File::Copy;
> use Date::Format;
> use CGI::Switch;
> use Sybase::CTlib;
> 
> $uid = 'parts';
> $pwd = 'd';
> $srv = 'g';
> 
> #########################################################################
> #	Variables                                                         #
> #########################################################################
>   $ftp_path = "C:/Dev/DADS_Parts/ftp";
>   $back_path = "C:/DEV/DADS_Parts/Nightly.Bak";
> 
>   $back_cust_path = "C:/DEV/DADS_Parts/Nightly.Bak/DADS_Cust";
>   $file_cust = "dads_cust.txt2";
> 
> #########################################################################
> #	Process Customer File                                             #
> #########################################################################
> 
>       open (DATABASE, "$ftp_path/$file_cust");
> 	$dbh=Sybase::CTlib->ct_connect($uid, $pwd, $srv);
>       $cnt = 0;
>       while ()
>         {
> 	      $cnt++;
> 		($cust_key, $order_status, $entry_id, $item_number,
> $item_description,
> $api_id, $order_number, $order_status_date, $date_recv) = split (/\|/,$_);
> 		$dbh->ct_execute("insert into outbound_parts (order_status,
> item_number,
> item_description, api_id, order_number, order_date, entry_id,
> last_updated)
> VALUES ('$order_status', '$item_number', '$item_description', '$api_id',
> '$order_number', '$order_date', '$entry_id', getdate())");
> 		if ( $dbh->ct_results($result) == CS_SUCCEED) {
> 			$row_count = $dbh->ct_res_info(CS_ROW_COUNT);
> 			print "CNT: $cnt  Row: $row_count\n";
> 			}
> 
>        } # End of while ()
>       close (DATABASE);
>       $date_str = time2str("%Y%m%d.%H%M%S.txt", time);
>       move( "$ftp_path/$file_cust", "$back_cust_path/$date_str");