Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

Date Problem

by Anonymous Monk
on Apr 26, 2010 at 09:11 UTC ( #836862=perlquestion: print w/ replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks

i am getting the following error

Placeholder :0 invalid, placeholders must be >= 1 at C:/Perl/lib/DBD/Oracle.pm l ine 273, <$data_fh> line 7.

While executing the following query

my $SQL ="Select tran_seq_nbr, sale_amount From Header Where org_number = 8 And store_number = 3 And tran_number = 2127 And terminal_number = 8 And tran_Date = to_date('$date','YYYY-MM-DD HH24:MI:SS')";

for date i have value like this:2007-08-10 00:00:00

how can i overcome this error

Comment on Date Problem
Download Code
Replies are listed 'Best First'.
Re: Date Problem
by marto (Bishop) on Apr 26, 2010 at 09:15 UTC
    my $SQL ="Select tran_seq_nbr, sale_amount From Header Where org_numbe +r = 8 And store_number = 3 And tran_number = 2127 And terminal_number + = 8 And tran_Date = to_date(?,'YYYY-MM-DD HH24:MI:SS')"; $sth->execute($date) or die $sth->errstr;

    Let me try that again, obviously I can't concentrate on sending a sms at the same time as answering your post :P

    my $SQL ="Select tran_seq_nbr, sale_amount From Header Where org_numbe +r = 8 And store_number = 3 And tran_number = 2127 And terminal_number + = 8 And tran_Date = to_date(?,'YYYY-MM-DD HH24:MI:SS')"; $sth->prepare($SQL); $sth->execute($date) or die $sth->errstr;

    See the section on placeholders from the DBI documentation.

    Update: Fixed my reply, not enough caffeine on a Monday morning while trying to do two things at once :P

      my $SQL = "Select tran_seq_nbr, sale_amount From KCPOS_Tran_Header Where org_number = $ai_div And store_number = $ai_store And tran_number = $al_trans And terminal_number = $ai_reg And tran_Date = $adt_h_date"; my $sth = $dbh->prepare( $SQL ) or die "Couldn't prepare statement: " . $dbh->errstr; $sth->execute() or die "Couldn't execute statement: " . $sth->errstr; ( $ac_seq_nbr, $ac_sale_amount ) = $sth->fetchrow();

      This what i am doing

        I don't know where you're getting the contents of $adt_h_date, but did you read the section on placeholders from the DBI docs? You want to use placeholders to protect against SQL injection. I'd suggest looking this up in the documentation.

        I think the problem is because of not quoting the date value($adt_h_date) in the query preparation.
Re: Date Problem
by moritz (Cardinal) on Apr 26, 2010 at 09:26 UTC
    Please show us the code where you actually execute the query.
      my $SQL = "Select tran_seq_nbr, sale_amount From KCPOS_Tran_Header Where org_number = $ai_div And store_number = $ai_store And tran_number = $al_trans And terminal_number = $ai_reg And tran_Date = $adt_h_date"; my $sth = $dbh->prepare( $SQL ) or die "Couldn't prepare statement: " . $dbh->errstr; $sth->execute() or die "Couldn't execute statement: " . $sth->errstr; ( $ac_seq_nbr, $ac_sale_amount ) = $sth->fetchrow();

      This is what i am doing

        The correct way of passing arguments to DB queries is to use placeholders:
        my $SQL = Select tran_seq_nbr, sale_amount From KCPOS_Tran_Header Where org_number = ? And store_number = ? And tran_number = ? And terminal_number =? And tran_Date = ?"; my $sth = $dbh->prepare( $SQL ) or die "Couldn't prepare statement: " . $dbh->errstr; $sth->execute($ai_div, $ai_store, $al_trans, $ai_reg, $adt_h_date ) or die "Couldn't execute statement: " . $sth->errstr;

        That way you can avoid quoting errors which might cause your problem.

        I think the problem is because of not quoting the date value($adt_h_date) in the query preparation.

        (Posting this, because the last post was reaped)

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://836862]
Approved by ahmad
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (10)
As of 2015-08-02 22:09 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The oldest computer book still on my shelves (or on my digital media) is ...













    Results (15 votes), past polls