sybperl-l Archive

From: "Robert Banniza" <hunt127588 at home dot com>
Subject: Updating records in table failing....
Date: Oct 26 1999 1:35AM

Guys,
	I need some help. I have a huge cluster f^@k on my hands because some
"person" at work chose not to instill data integrity in the database. Now,
I'm cleaning up this crap and what a mess it is. Here is some background
info. For every comment in this table, there should be a comment_type. But
there isn't. The table has a clustered index so I'm doing a table sweep of
all 1.9 million rows to find all subsequent null comment_type values (the
users would enter a comment_type for the first record but not for subsequent
records). If the null falls after a record with a comment_type of 'QA', I
want to update the following nulls (to 'QA') until I hit the next non-null
value (basically).

	Now here is my problem, since I'm updating only certain records depending
on the criteria above, how do I do this? I have tried using the following:

	$dbh->ct_execute("update
	providerdb..phycomment set comment_type = 'QA'
	where phycomment_sid = $phycomment_sid");

and this is what I get:
	ct_cancel() failed - dying at
/usr/local/lib/perl5/site_perl/5.005/sun4-solaris-thread/Sybase/CTlib.pm
line 899.


What am I doing wrong here??!! I'm so confused at this point that I'm
beginning to wonder if the DBA installed Sybperl correctly. I have also
tried various ct_send_data() and ct_send() functions and am getting the same
error. Basically, if I get into the 2nd elsif of count_it(), I am wanting to
update that particular row in the table. Can you guys tell me how to do
this? I have commented out all of the cs_send_data routine. Please help me
as I don't know who else to turn to. Here is the entire program for those
that want to look:

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

#!/usr/bin/perl

use Sybase::CTlib;
#use lib "/export/home/rst/PERL";

ct_callback(CS_CLIENTMSG_CB, \&COMMON::msg_cb);
ct_callback(CS_SERVERMSG_CB, "COMMON::srv_cb");

require 'login.pm';
require 'common.pm';
#LOGIN::get_login();
#LOGIN::open_logfile();

$dbh = Sybase::CTlib->ct_connect("myname", "mypassword",
        "myserver");

$counter = 0;
$counter2 = 0;
$total_count = 0;
$tot_rec_cntr = 0;
$other_count = 0;
$qa_check_flg = "";
count_qa();

#############################
#   count_qa                #
#############################
sub count_qa
        {
        $msg = "count_qa\n";
        #print LOGIN::LOGFILE $msg;



         $dbh->ct_sql("select phycmnt.physician_sid,
                phycmnt.phycomment_sid, phycmnt.entry_date,
                phycmnt.comment_type from
                providerdb..phycomment phycmnt", \&count_it );

        print "\n\nFinal Total Record Count: $tot_rec_cntr\n";
        print "Final Total QA comment types: $counter\n";
        print "Final subsequent NULL comment types: $counter2\n";
        print "Other: $other_count\n\n";
        $total_count = $counter + $counter2;
        print "Total QA and NULLS: $total_count\n\n";
        }


sub count_it {
        @line = @_;
        $physician_sid          = @line[0];
        $phycomment_sid         = @line[1];
        $entry_date             = @line[2];
        $comment_type           = @line[3];

        #print "Physician_sid: $physician_sid -- Comment_type:
$comment_type -- Entry_date: $entry_date\n";

        $tot_rec_cntr ++;
        chomp($phycomment_sid);
        chomp($physician_sid);
        chomp($comment_type);
        chomp($entry_date);

        if ($comment_type eq "QA")
                {
                $physician_sid_flag = $physician_sid;
                $qa_check_flg = "QA";
                $counter ++;
                }

        elsif ($comment_type ne "QA" && $comment_type ne "")
                {
                $physician_sid_flag = "reset";
                $qa_check_flg = "reset";
                }

        elsif ($qa_check_flg eq "QA" && $comment_type eq ""
                && $physician_sid eq $physician_sid_flag)


                $counter2 ++;
                print "phycomment_sid: $phycomment_sid\n";
                $dbh->ct_execute("update
                providerdb..phycomment set comment_type = 'QA'
                where phycomment_sid = $phycomment_sid");

                ##my $restype;
                ##while($dbh->ct_results($restype) == CS_SUCCEED) {
                        ##next unless($dbh->ct_fetchable($restype));
                        ##my @dat;
                        ##while(@dat = $dbh->ct_fetch) {
                                ##$dbh->ct_data_info(CS_GET, 2);
                                ##}
                        ##}

                #$dbh->ct_data_info(CS_GET, 2);
                #my $data = "QA";
                #my @dat;
                #$dbh->ct_command(CS_SEND_DATA_CMD, '', CS_UNUSED,
CS_COLUMN_DATA);
                #$dbh->ct_data_info(CS_SET, 2, {total_txtlen =>
length($data)});
                #$dbh->ct_send_data($data, length($data));
                #$dbh->ct_send;

                ##while($dbh->ct_results($restype) == CS_SUCCEED) {
                        ##next unless $dbh->ct_fetchable($restype);

                        ##while(@dat = $dbh->ct_fetch) {
                                ##print "@dat\n";
                                ##}
                        ##}


                }
        else


                $other_count ++;
                #exit;
                }

        if ($tot_rec_cntr % 100000 == 0)
                {
                print "QA Records processed: $counter\n";
                print "Subsequent NULL Records processed: $counter2\n";
                $total_count = $counter + $counter2;
                print "Total QA and NULLS: $total_count\n";
                print "Total Record Count: $tot_rec_cntr\n\n";
                }
}
----------------------------------------------------------------------------
--------------------------------------------------------

Thanks for any and all help,
Robert
robert_banniza@focusppo.com