http://www.perlmonks.org?node_id=836862

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')";

[download]

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

how can i overcome this error

Replies are listed 'Best First'.
Re: Date Problem
by marto (Cardinal) 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;

    [download]

    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;

    [download]

    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

[reply]
[d/l]
[select]
      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();

      [download]

      This what i am doing

[reply]
[d/l]

        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.

[reply]
[d/l]
        I think the problem is because of not quoting the date value($adt_h_date) in the query preparation.
[reply]
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.
[reply]
      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();

      [download]

      This is what i am doing

[reply]
[d/l]
        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;

        [download]

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

[reply]
[d/l]
        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)

[reply]

Back to Seekers of Perl Wisdom