Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
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
Re: Date Problem
by marto (Chancellor) 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 think the problem is because of not quoting the date value($adt_h_date) in the query preparation.

        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.

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 2014-07-28 23:01 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (210 votes), past polls