2 cursors? at the same time

From: Nina Markova (markova_at_seismo.nrcan.gc.ca)
Date: 01/19/05 

Date: Wed, 19 Jan 2005 15:11:50 -0500 (EST)
To: dbi-users@perl.org

Hi,

How to use select and insert at the same time?

I select information from one table, process and reorganize it and want to
insert it in another table. I receive error :

DBD::Ingres::st execute failed: E_US08B4 No MST is currently in progress, cannot
declare another cursor.
    (Wed Jan 19 14:55:58 2005) at ../make_action.pl line 69.
DBD::Ingres::st fetchrow_hashref failed: E_LQ0058 Cursor
's0_ticketdescriptionfro' not open for 'fetch' command. at ../make_action.pl
line 123.
Use of uninitialized value in concatenation (.) or string at ../make_action.pl
line 158.
DBI::db=HASH(0x14f070)->disconnect invalidates 1 active statement handle (either
destroy statement handles or call finish on them before disconnecting) at
../make_action.pl line 160.

Usually I use
 1) perl dbi script for processing which writes to a text file f.txt
 2) load data with 'copy table from f.txt' statement
 
 This time information I reorganize is a large text field, which includes new
lines. When I'm trying to load with 'copy from ...', new lines blow Ingres
eroors. I don't know how to preserve this formating and at the same time be
able to load data, so decided to insert directly from same perl script.
 
 Code attached.
 
 This is the exaple of formating I'd like to keep:
 --------------------------------------------------
Unused partition found, added in to directory tree at:

/web1/htdocs/nehp/hplots

This does two things, one keeps the file structure the same for applications
writing heliplot files and two gives the operations phone a break from /web1
diskspace messages for atleast a handful of months.

Thanks in advance,

Nina Markova
DBA

#!/usr/local/bin/perl -w

# --------------------------------------------------
# make_action.pl
# Reads data from opslog table in STA.
#
# input : opslog table
#
# output : tables OPSLOG2ACTION and ACTIONLOG
#
# opslog2actionlog actionlog
# ~~~~~~~~~~~~~~~~ ~~~~~~~~~~
# actionid P.K. actionid P.K.
# ticket Alt.K. page P.K.
# text
#
# 3 Nov 2004
# Nina Markova
# ---------------------------------------------------

use strict;
use POSIX;
use DBD::Ingres;
use lib '/usr/local/seis/lib/perl';
use Seis::Time;

if (`uname -n` eq "yoda\n")
{
  $ENV{'II_SYSTEM'} = '/dbsys';
  $ENV{'LD_LIBRARY_PATH'} = '/dbsys/ingres/lib';
}
else
{
  $ENV{'II_SYSTEM'} = '/dbyoda';
  $ENV{'LD_LIBRARY_PATH'} = '/dbyoda/ingres/lib';
}

################
#
# VARIABLES
#
################
my $row;
my $length;
my $pageLen = 300;
my $page2plusNum; # number of ticket with more than 2 pages

###############
# Globals
###############
# or $ROOTDIR = pwd
my $ROOTDIR = '/yoda/home/dba/STA/cnsn_ops_log/UPDATE_20041103/20050119';
my $sep ='@';
my $actionCount = 0; # actionid counter which is actually almost ticketid
                        # as for old data we are assuming that
                        # there is one action with 1 sequence
my $sequence = 1; # i.e. one sequence always
my $pageNumber = 0; # page number in ACTIONLOG table

#####################
# sub insertActionlog
#####################
sub insertActionlog
{
        my ($dbconnection, $actionid, $page, $text) = @_;
        print "id=$actionid\n";
        print "page=$page\n";
        print "text=$text\n";
        my $sqlStr = "insert into actionlog2 values($actionid, $page, '$text')";

        my $stmt = $dbconnection->prepare("$sqlStr");
        $stmt->execute();
        $stmt->finish();
}
################
#
# MAIN
#
################
open(ACTIONLOG, "> $ROOTDIR/txt/actionlog.txt") || die "Can't open file";
open(LINKER, "> $ROOTDIR/txt/linker.txt") || die "Can't open file";
open(PAGELOG, "> $ROOTDIR/txt/actionlog_page2plus.log") || die "Can't open file";
open(ERRLOG , "> $ROOTDIR/txt/actionlog.err") || die "Can't open file";

my $db1 = DBI->connect("DBI:Ingres:yoda::test") or
              die("Couldn't connect to database: $!\n");

my $sqlStr = 'select ticket, description from opslog ';

my $stmt = $db1->prepare("$sqlStr");
$stmt->execute();

while( $row = $stmt->fetchrow_hashref() ) {
        $length = 0;
        $pageNumber = 0;
        #
        # print all data
        #
        # print OUT "$$row{'ticket'} $sep $$row{'description'} $sep ";
        # print OUT "$$row{'uname_modified'} $$row{'date_modified'}\n";

        #
        # print LINKER (opslog2actionlog)
        #
        $actionCount++;
        print LINKER "$$row{'ticket'}$sep$actionCount\n";
        #print ACTION "$$row{'ticket'}$sep$sequence$sep$actionCount$sep";
        #print ACTION "$actionCount$sep$$row{'ticket'}$sep$sequence$sep";
        #print ACTION "$$row{'uname_modified'}$sep$$row{'date_modified'}\n";

        #
        # print ACTIONLOG
        #
        my $token; # each text field char(300)
        my $i;
        my $offset;
        
        # take the length of description and calculate number of pages
        $length = length $$row{'description'};

        #$token = join("\r", split(/\n/, $$row{'description'}));
        $token = $$row{'description'};

        # if multiple pages
        if ( $length > $pageLen )
        {
                 $pageNumber = 1 + int($length / $pageLen);
                $page2plusNum++;
                # for
                for ($i = 1; $i <= $pageNumber; $i++)
                {
                        $offset = ($i - 1)*$pageLen; # offset=0 for the 1st substr
                        my $pageToken = substr($token, $offset, $pageLen);
                        #
                        # print actions with multiple pages
                        #
                        if (!defined($pageToken))
                        {
                          print STDERR "$$row{'ticket'} \n";
                          print ERRLOG "substr outside of string ticket=$$row{'ticket'} ";
                          print ERRLOG "action=$actionCount len=$length offset=$offset\n";
                        }
                        print PAGELOG "ticket=$$row{'ticket'} i=$i pageNum=$pageNumber";
                        print PAGELOG " len=$length offset=$offset\n";
                        print ACTIONLOG "$actionCount$sep$i$sep$pageToken\n";
                        # enter directly into actionlog table
                        #
                        insertActionlog($db1, $actionCount, $i, $pageToken);
                }
                
        }
        else
        {
                # if single page
                $pageNumber = 1;
                print ACTIONLOG "$actionCount$sep$pageNumber$sep$token\n";
                insertActionlog($db1, $actionCount, $pageNumber, $token);
        }
}
print PAGELOG "\n $page2plusNum - number of ticket with more than 2 pages \n";

$db1->disconnect();

close LINKER;
close ACTIONLOG;

exit 0;
        #for (;;) # endless loop
        #{
         # # get 1st $pageLen symbols or the entire string, if smaller
         # my $pageToken = substr($token, 0, $pageLen);
        # print ACTIONLOG "$actionCount$sep$i++$sep$pageToken\n";
        # # drop 1st $pageLen symbols, get undef if smaller
        # $token = substr($token, $pageLen);
        # if (!defined($token))
        # {
        # print "len=$length \n";
        # last;
        # }
                
        #}