Please suggest - Issue with comparing values

T

test

Greetings,

I have a perl file which is called from a ksh file. In this perl
script, I have to execute a procedure and get the output parameter.
this output paramtere(proces Date) needs to be compared with an input
parameter(run_date). If both dates are not same, I need to exit out of
the job. However, when I am trying to execute the procedure and
capture the value, it always returns 0. I am unable o capture this
date output and compare it with the input date. I am using DBI for
connectivity.

Below is the SAMPLE script. Can you please advise

my $sth;


$db_handle = &connectDB($server, $user, $password, $database);

my $extract_sql;
my $date_cmp_sql ;

$date_cmp_sql = "
DECLARE \@ReportDate DATETIME,
\@ProcessDate DATETIME

SELECT \@ReportDate ='$report_date'

EXEC $db_name..GetPriorDate 1, \@ReportDate, \@ProcessDate output
SELECT \@ProcessDate
";
my $st;
$st = $db_handle->prepare("$date_cmp_sql") or die("Could not prepare
for SQL statement");

$st->execute() or die localtime(time). " ---Can't excute SQL
statement: $DBI::errstr\n";
$err_str=$db_handle->errstr;
$err_no=$db_handle->err;

if ($err_str ne "")
{
print(" error \n");
exit 1;
}

my @RptFlag = ();
@RptFlag = $st->fetchrow_array() ;

my $RprtGenrFlag = $RptFlag[0];

print("RprtGenrFlag is $RprtGenrFlag \n\n");


##if ($Report_date eq "0")
$run_date = $RprtGenrFlag;
print("run_date is $run_date");
if($RprtGenrFlag eq $run_date)
{
print("two dates are equal \n");
}
else
{
print("dates are not equal \n");
}

$st->finish();
&disconnectDB ($db_handle, $server);
 
S

Steve C

test said:
Greetings,

I have a perl file which is called from a ksh file. In this perl
script, I have to execute a procedure and get the output parameter.
this output paramtere(proces Date) needs to be compared with an input
parameter(run_date). If both dates are not same, I need to exit out of
the job. However, when I am trying to execute the procedure and
capture the value, it always returns 0. I am unable o capture this
date output and compare it with the input date. I am using DBI for
connectivity.

Below is the SAMPLE script. Can you please advise

my $sth;


$db_handle = &connectDB($server, $user, $password, $database);

my $extract_sql;
my $date_cmp_sql ;

$date_cmp_sql = "
DECLARE \@ReportDate DATETIME,
\@ProcessDate DATETIME

SELECT \@ReportDate ='$report_date'

Is SQL picky about spaces after '='? I think it's good practice.
EXEC $db_name..GetPriorDate 1, \@ReportDate, \@ProcessDate output
SELECT \@ProcessDate
";
my $st;
$st = $db_handle->prepare("$date_cmp_sql") or die("Could not prepare
for SQL statement");

$st->execute() or die localtime(time). " ---Can't excute SQL
statement: $DBI::errstr\n";
$err_str=$db_handle->errstr;
$err_no=$db_handle->err;

if ($err_str ne "")
{
print(" error \n");
exit 1;
}

my @RptFlag = ();
@RptFlag = $st->fetchrow_array() ;

I wonder if $#RptFlag is -1 right here?
my $RprtGenrFlag = $RptFlag[0];

print("RprtGenrFlag is $RprtGenrFlag \n\n");


##if ($Report_date eq "0")
$run_date = $RprtGenrFlag;
print("run_date is $run_date");
if($RprtGenrFlag eq $run_date)
{
print("two dates are equal \n");
}
else
{
print("dates are not equal \n");
}

$st->finish();
&disconnectDB ($db_handle, $server);
 
D

Dave Weaver

Greetings,

I have a perl file which is called from a ksh file. In this perl
script, I have to execute a procedure and get the output parameter.
this output paramtere(proces Date) needs to be compared with an input
parameter(run_date). If both dates are not same, I need to exit out of
the job. However, when I am trying to execute the procedure and
capture the value, it always returns 0. I am unable o capture this

Which procedure always returns 0? I see no
procedures/subroutines/functions in your code.

Does the SQL you execute return a date?
date output and compare it with the input date. I am using DBI for
connectivity.

Below is the SAMPLE script. Can you please advise


use strict;
use warnings.

What is this? I dont see it used below.
$db_handle = &connectDB($server, $user, $password, $database);

You don't declare $db_handle. Also, you don't need the & on
sub calls, unless you need its effects, which you don't.

my $db_handle = connectDB( $server, $user, $password, $database );
my $extract_sql;

What is this? Doesn't seem to be used.
Please use more care when constructing sample code.
my $date_cmp_sql ;

Why declare variables then assign to them in a separate line?
You can make your code more compact and simultaneously more
readable by doing declaration and initialisation in one line:

my $date_cmp_sql = "... etc ...";
$date_cmp_sql = "
DECLARE \@ReportDate DATETIME,
\@ProcessDate DATETIME

SELECT \@ReportDate ='$report_date'

EXEC $db_name..GetPriorDate 1, \@ReportDate, \@ProcessDate output
SELECT \@ProcessDate
";

Did you print out $date_cmp_sql here to make sure it's what you expect?
Did you cut-and-paste that output into your database frontend to see if
you get the expected output?

Learn the art of debugging - it shouldn't be difficult, either with
print statements or with the perl debugger, to find the line of code
that's causing you problems.

my $st;
$st = $db_handle->prepare("$date_cmp_sql") or die("Could not prepare
for SQL statement");

again, do this in one line for clarity:

my $st = $db_handle->prepare( ... );
$st->execute() or die localtime(time). " ---Can't excute SQL
statement: $DBI::errstr\n";
$err_str=$db_handle->errstr;
$err_no=$db_handle->err;

if ($err_str ne "")
{
print(" error \n");
exit 1;
}

You've already checked the execute() for errors; I think this check of
errstr is unneccessary and possibly wrong. I am not a DBI expert
though. According to the DBI docs:

The errstr() method should not be used to test for errors, use err()
for that,

my @RptFlag = ();
@RptFlag = $st->fetchrow_array() ;

my @RptFlag = $st->fetchrow_array();

What are the contents of @RptFlag here?
use Data::Dumper;
print "\@RptFlag = " . Dumper \@RptFlag;

my $RprtGenrFlag = $RptFlag[0];

print("RprtGenrFlag is $RprtGenrFlag \n\n");


##if ($Report_date eq "0")
$run_date = $RprtGenrFlag;
print("run_date is $run_date");
if($RprtGenrFlag eq $run_date)

2 lines back You set $run_date = $RprtGenrFlag, now you compare
the two values - of course they will be equal!
{
print("two dates are equal \n");
}
else
{
print("dates are not equal \n");
}

$st->finish();
&disconnectDB ($db_handle, $server);

As above, no '&' required:
disconnectDB( $db_handle, $server );
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,900
Latest member
Nell636132

Latest Threads

Top