DBI:Problem in binding VARCHAR values to statement

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

Hi all, I'm trying to supply two varchar values to a SQL statements by using bind_param(). The sql statement is not giving results. I even tried to pass values using execute(value1,value2) to SQL statement but it didn't work. can anyone please help me in this. Your advice is greatly appreciated. Thanks!

use DBI;
my $dbh;
use DBI qw(:sql_types);
$dbh = DBI->connec('Dbname','usrname','passwd','Oracle', \%attr);
my $sth_LOG = $dbh->prepare(
      qq{select num_org_mfg,cd_typ_evnt_dntm,num_rsn_dntm
             from mfg_evnt.dntm_rsn_ulog
                              where num_org_mfg = 7664 
                              and cd_typ_evnt_dntm ='DNTM'
                              and num_rsn_dntm = 2});

 my  $sth_DNTM = $dbh->prepare(qq{select num_org_mfg,nam_rsn_dntm 
                             from mfg_evnt.dntm_rsn 
                             and cd_typ_evnt_dntm= ?
                             and num_org_mfg = ?
                            });


if($DBI::errstr)
{    print "error in preparation ........\n"; }


$sth_LOG->execute();
if ($DBI::errstr) { print "error in sth_log execute\n"; }

($num_org, $cd_typ, $num_rsn) = $sth_LOG->fetchrow_array();

   # binding parameters to sth_DNTM statememt
 
    $sth_DNTM->bind_param(1,$cd_typ);
    $sth_DNTM->bind_param(2,$num_org);

    $sth_DNTM->execute();
    @row = $sth->fetchrow_array();
    print "@row\n";
 
$sth_LOG->finish;
$sth_DNTM->finish;
$dbh->disconnect;
[download]

Comment on DBI:Problem in binding VARCHAR values to statement Download Code

Replies are listed 'Best First'.
Re: DBI:Problem in binding VARCHAR values to statement by chromatic (Archbishop) on May 07, 2001 at 02:40 UTC
bind_param() is used to retrieve values. Placeholders are used to supply values. They are completely different. I suspect you will have better results with: `$sth_DNTM->execute($cd_typ, $num_org); @row = $sth->fetchrow_array();` [download] If you used bind_param(), you wouldn't need to assign anything to @row, and you would use fetch() instead. See perldoc DBI for more details. Update: chipmunk is right, I'm thinking of bind_columns(). Sorry about that! My guess is on the missing 'WHERE' SQL keyword.	[reply] [d/l]
Re: Re: DBI:Problem in binding VARCHAR values to statement by chipmunk (Parson) on May 07, 2001 at 06:52 UTC
That's really not correct. bind_param() is used to bind values to placeholders ahead of time, instead of in the execute() call. bind_param_inout() is like bind_param(), but also allows values to be set by the call to fetch(). While your example code is fine, the code that the original poster wrote using bind_param() should work just as well. See the DBI documentation for more details.	[reply]
Re: DBI:Problem in binding VARCHAR values to statement by dws (Chancellor) on May 07, 2001 at 01:27 UTC
This might not get you all the way there, but it'll help. Do more error checking. Your first `prepare()` is unchecked. And when you find that `$DBI::errstr` holds a value, print it!. Dropping clues on the ground isn't going to do you any good.	[reply] [d/l] [select]
Re: DBI:Problem in binding VARCHAR values to statement by runrig (Abbot) on May 07, 2001 at 05:35 UTC
It may or may not help in this case, but it is a good idea in general to 'use strict', and also check the return value of all DBI statements (connect, prepare, and execute at the very least), or better yet, use 'RaiseError=>1' in the connect %attr hash. I also notice 'connect' is not spelled correctly, is this a typo here, or something 'use strict' would have caught? (Update:Nevermind, it ought to be obvious if this was the problem even without 'use strict'). You also have no 'where' clause on your second SQL statement. This would have been caught with RaiseError or checking the return value of the prepare.	[reply]