sybperl-l Archive

From: Michael Peppler <mpeppler at peppler dot org>
Subject: Re: ct_sql woes
Date: Oct 25 2002 4:33PM

On Fri, 2002-10-25 at 09:01, Sabherwal, Balvinder (MBS) wrote:
> Guru's
> 
> I have a script as below which is failing to print the results after the
> query execution. I have tried to run the query using isql and it returns the
> result back fine. 
> 
> Is there anything wrong I'm doing in here?? the scalar report 0 elements in
> the array and ct_callback reports no error after the query executions. All
> I'm trying to do is, select the data from one database and insert it into
> the other database.

Let's see:


#!c:\perl/bin/perl.exe
############################################################################
#
# SCRIPT:    mt_busctr.pl
# DESCRIPTION:
#            This program gets the business counters from middletier
database
#            and puts them in the matrics database for Sybase.
#
# AUTHOR:    Balvinder Sabherwal
# CREATED:   Oct 25, 2002
#
# 
#
# MODIFICATION HISTORY:
#
#
#
############################################################################

# import all modules
use Sybase::CTlib;

use strict;   ###### ALWAYS USE THIS!!!! Avoids countless typos!!!

# We only need to set the callback once
ct_callback(CS_SERVERMSG_CB, \&srv_cb);
ct_callback(CS_CLIENTMSG_CB, \&msg_cb);

my $dbh = Sybase::CTlib->ct_connect(user, "password", "SYB_DBSRVR"); 
if ($dbh){
        print "Connected to mt server\n";
}

$dbh->ct_sql("use middletierdb");

$sql = "
select
    MiddleTierServiceDesc,     
    convert(varchar,convert(datetime,convert(varchar(12),RequestDate))),
    convert(varchar,datepart(hour,RequestDate)),
    convert(varchar,count(*)) 
from ServiceReqStatus a (index NClServiceReqStatus),
     MiddleTierServiceType b
where a.RequestDate between
           convert(datetime,substring(getdate(),1,12)) and getdate()
  and a.MiddleTierServiceTypeCd = b.MiddleTierServiceTypeCd
group by MiddleTierServiceDesc,
         convert(datetime,convert(varchar(12),RequestDate)),
         datepart(hour,RequestDate)
";

print "$sql \n";

my $DBRow = $dbh->ct_sql($sql);

#print "Executed the sql \n $DBRow \n printing the rows \n";

# NOTE: ct_sql() returns a *reference* to an array, so we need to 
# de-reference it to get the items in the array.
my $a = scalar @$DBRow;
print "There are $a elements in array\n";

# Each row in the array is a reference to the row
# so @$Row represents the array of column for that row
foreach my $Row ( @$DBRow ) {
        print "values ==>> @$Row \n";
}

my $dbh = Sybase::CTlib->ct_connect(user, "password", SYB_DBSRVR); ##
Connect to the server.

if ($dbh){
        print "Connected to the matrics server \n";
        $dbh->ct_sql("use perfmondb");
}

foreach my $Row ( @$DBRow ) {
        print "@$Row \n";
        my ($BusCtr, $SDt, $hr, $Value) = @$Row;
        print "$BusCtr, $SDt, $hr, $Value \n";
        $dbh->ct_sql("exec proc_busctr_data_save '$BusCtr' , '$SDT' ,
'$hr'
,  $Value");
} 



sub msg_cb
{
    my($layer, $origin, $severity, $number, $msg, $osmsg, $dbh) = @_;

    printf "\nOpen Client Message: (In msg_cb)\n";
    printf "Message number: LAYER = (%ld) ORIGIN = (%ld) ",
               $layer, $origin;
    printf "SEVERITY = (%ld) NUMBER = (%ld)\n",
               $severity, $number;
    printf "Message String: %s\n", $msg;
    if (defined($osmsg))
    {
        printf "Operating System Error: %s\n", $osmsg;
    }
    CS_SUCCEED;
}


sub srv_cb
{
    my($dbh, $number, $severity, $state, $line, $server,
       $proc, $msg) = @_;
    # If $dbh is defined, then you can set or check attributes
    # in the callback, which can be tested in the main body
    # of the code.

    printf "\nServer message: (In srv_cb)\n";
    printf "Message number: %ld, Severity %ld, ",
               $number, $severity;
    printf "State %ld, Line %ld\n", $state, $line;
    if (defined($server))
    {
        printf "Server '%s'\n", $server;
    }
    if (defined($proc))
    {
        printf " Procedure '%s'\n", $proc;
    }
    printf "Message String: %s\n", $msg;  CS_SUCCEED;
}

__END__


OK - some comments.

Always, always, ALWAYS "use strict". This forces you to "declare" the
variables you want to use (via "my"), and to quote all strings. This is
a *good* thing. In your original script you had the following:

$DBRow = $dbh->ct_sql(...);
$a = scalar @DBRow;

$DBRow and @DBRow are NOT the same variable - of course $a would be 0 in
this case, because @DBRow has never been populated!

ct_sql() returns a reference to an array, where each element is a
reference to a row. So it isn't necessary to join the data in the query
(as you do with a "|" character).

Instead, you can do this:

my $data = $dbh->ct_sql(...);
foreach my $row (@$data) {
    my ($col1, $col2, $col3) = @$row;
    ... now you have columns 1, 2 and 3 for this particular row, and 
        you can process them ...
}

Hope this clears things up a little...

Michael
-- 
Michael Peppler / mpeppler@peppler.org / http://www.mbay.net/~mpeppler
mpeppler@zetatools.com / ZetaTools, Inc / http://www.zetatools.com
ZetaTools: Call perl functions as Sybase stored procedures!