Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hello all,

I tired to use place holders and i am getting following error : 

ORA-01008: not all variables bound (DBD ERROR: error possibly near <*>
+ indicator

[download]
Here is my code: 
use DBI;
use DBD::Oracle qw(:ora_types);
use DBD::ODBC;
use Getopt::Long;

main();

###########################################################
sub main
{

 $dbh = DBI->connect("dbi:ODBC:$DSN");

 $sqlstatement="SELECT * from test_table";

 $dbh->{LongReadLen} = 140000;
 $sth = $dbh->prepare($sqlstatement);
 $sth->execute || 
       DIE("Could not execute SQL statement ... maybe invalid?");


 $row_access=0;
 while (@row=$sth->fetchrow_array)
 { 
   $c1 = func("COL1",0,1);
   $c2 = func("COL2",1,2);
 

   $INSERT_SQL="INSERT INTO trg_table(".
"column1,".               
"column2".                  
") VALUES ".
"(?,".
"?)";
     
   $INSERT_CURSOR=$LDA_DB->prepare($INSERT_SQL);

    $rv = $INSERT_CURSOR->execute();

    $INSERT_CURSOR->finish;
    $row_access++;
 }
 $INSERT_CURSOR->finish;
 $LDA_DB->disconnect;
 close(W);
}
###########################################################

sub func
{
  # if there is no value it replaces with " " otherwise it replaces wi
+th value.
}
###########################################################

[download]
can any one help me with this? column1, column2 being target table columns and COL1, COL2 being source table columns.

while using place holders, what all things i need to take care of?

thanks.....

edit (broquaint): added formatting

Replies are listed 'Best First'.
Re: how to use place holders?
by gellyfish (Monsignor) on Aug 03, 2005 at 19:39 UTC

    If I can guess what it is what you are trying to do I think you want: 

    $rv = $INSERT_CURSOR->execute($c1, $c2);

    [download]
    - you pass the values for the placeholders to the execute

    /J\

[reply]
[d/l]
[select]
Re: how to use place holders?
by Enlil (Parson) on Aug 03, 2005 at 19:42 UTC
    You need to pass some arguments to execute: That is: 
    $INSERT_CURSOR->execute();

    [download]
    Should be: 
    $INSERT_CURSOR->execute($some_var,$some_other_var);

    [download]
    As you have two place holders but in your $INSERT_CURSOR but no variables bound to them when you call execute it. Note that in the above the variables $some_var, and $some_other_var relate to the two ? in your statement. Have a look at: The DBI Documention on Placeholders

    -enlil

[reply]
[d/l]
[select]
      Thank you! It worked! thanks......
[reply]
off-topic: using q() and qq() to improve readability
by Codon (Friar) on Aug 04, 2005 at 00:01 UTC

    Something you can do to make your code easier to read (beyond the formating in this forum) is to use q() and qq(). For example, where you do this: 

    $INSERT_SQL="INSERT INTO trg_table(".
"column1,".              
"column2".                  
") VALUES ".
"(?,".
"?)";

    [download]
    Try this instead: 
    $INSERT_SQL= q{
INSERT INTO trg_table( column1, column2 )
 VALUES (?,?)
};

    [download]

    Because you do not need variable interpolation here, you can use single quotes. If you need to do variable interpolation, use qq() instead.

    Ivan Heffner
    Sr. Software Engineer, DAS Lead
    WhitePages.com, Inc.
[reply]
[d/l]
[select]
Re: how to use place holders?
by Joost (Canon) on Aug 03, 2005 at 19:50 UTC
[reply]

Back to Seekers of Perl Wisdom