Please suggest - Issue with comparing values

Discussion in 'Perl Misc' started by test, Jul 22, 2009.

  1. test

    test Guest

    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);
     
    test, Jul 22, 2009
    #1
    1. Advertising

  2. test

    Steve C Guest

    test wrote:
    > 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);
    >
     
    Steve C, Jul 22, 2009
    #2
    1. Advertising

  3. test

    Dave Weaver Guest

    On Wed, 22 Jul 2009 05:36:34 -0700 (PDT), test <> wrote:
    > 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.

    > my $sth;


    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 );
     
    Dave Weaver, Jul 23, 2009
    #3
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jorgen Gustafsson
    Replies:
    4
    Views:
    599
    Jorgen Gustafsson
    Dec 12, 2003
  2. HK
    Replies:
    3
    Views:
    371
    Kevin
    Apr 14, 2005
  3. KK
    Replies:
    2
    Views:
    664
    Big Brian
    Oct 14, 2003
  4. Alec Taylor
    Replies:
    6
    Views:
    124
    Alec Taylor
    Nov 23, 2013
  5. Jason Friedman
    Replies:
    2
    Views:
    101
    Alec Taylor
    Nov 17, 2013
Loading...

Share This Page