On Thu, 26 Dec 2002 12:39:53 -0500 "Rozengurtel, Daniel"
<[EMAIL PROTECTED]> wrote:
> I have a little problem when I bound three variables from bind_param.
Be sure to make the distinction between placeholders/bind variables and
result columns. You have two placeholders in your SELECT and it returns
three columns.
> I am using union in my sql. I am not sure where the problem comes arises.
> The three fields from each table have different names although defined and
> contain the same data (CHAR(10),VARCHAR2(40),CHAR(8)) I have
> TYPE=>SQL_CHAR.
> Do I need to define separately for each variable I bind the value to its
> type? If so how do I do that? If this is not the case, why not all of the
> vars are getting bound?
By default, each placeholder is separate from the other. You will note in
the trace() output that you have two bind variables (:p1 and :p2).
> I include a trace from the run and a piece of code.
> Thanx a lot in advance for your help.
>
> CODE:
>
> #************************************************************************
> ***
> ****
> # prepared sqls
> #************************************************************************
> ***
> ****
> my $sth_isid_frip =
> $dbh->prepare(q{SELECT INST_MNEM,ID_CTXT_TYP,ISS_ID
> FROM FT_T_ISID WHERE INSTR_ID=? AND
> END_TMS IS NULL
> UNION
> SELECT
> INST_MNEM,PREF_ID_CTXT_TYP,ISID_ISS_ID FROM FT_T_FRIP WHERE INSTR_ID=?
> AND END_TMS IS NULL AND INST_MNEM
> IS NOT NULL
> ORDER BY INST_MNEM,ID_CTXT_TYP}) ||
> die $dbh->errstr;
An alternative to anonymous placeholders here would be to use bind
variables. DBD::Oracle allows either ? or :name formed
placeholders/bind variables. In this case it would probably be easiest to
replace the '?'s with ':p1' in both places without the quotes. That way
you can provide the value either with $sth->bind_param() or as a single
argument to $sth->execute(). If you retain the ?s, either call
$sth->bind_param() twice with 1 and 2 as the first argument or call
'$sth->execute( $val1, $val2 ) or die "Execute failed: $DBI::errstr"'.
> #************************************************************************
> ***
> ***
>
> my ($sql, %ids_hash, $instr_id);
>
> $instr_id='0001x5MWFg';
>
> #****************************************************************
> # Creating a hash of ISID/FRIP ids
> #****************************************************************
>
> $sth_isid_frip->bind_param(1, $instr_id, {TYPE=>SQL_CHAR}); #
> placeholders are numbered from 1
The placeholders are numbered 1 and 2, so if you are going to use
bind_param(), you need to provide both.
> $sth_isid_frip->execute;
I don't see any error checking on the execute(). Either set
$dbh->{RaiseError} to 1 before the prepare(), or include 'or die
"Something: $DBI::errstr"' with each call.
> ## the problem is somewhere here.
> my( $prim_exch, $ctxt_typ, $id_value );
> $sth_isid_frip->bind_columns( undef, \$prim_exch, \$ctxt_typ,
> \$id_value
> );
The first, hash reference argument is optional and probably best left out.
The \ operator is also distributive.
$sth_isid_frip->bind_columns( \( $prim_exch, $ctxt_typ,
$id_value ) );
I don't normally explicitly check for errors on bind_columns() because I
should have caught statement parsing errors either in prepare() or
execute(). I also usually have $dbh->{RaiseError} set.
> #process further
> while( $sth_isid_frip->fetch() ) {
> if (defined $prim_exch and defined $id_value and defined
> $ctxt_typ)
> { $ids_hash{$instr_id}{$prim_exch}{$ctxt_typ}=$id_value; }
> } #while
>
> $sth_isid_frip->finish();
Since you are fetching all rows in the while loop, finish() is not
required and is discouraged.
> TRACE:
>
> DBI::db=HASH(0x116be8) trace level set to 2 in DBI 1.15-nothread
> -> prepare for DBD::Oracle::db (DBI::db=HASH(0x27ef08)~0x116be8
> 'SELECT
> INST_MNEM,ID_CTXT_TYP,ISS_ID
> FROM FT_T_ISID WHERE INSTR_ID=?
> AND
> END_TMS IS NULL
> UNION
> SELECT
> INST_MNEM,PREF_ID_CTXT_TYP,ISID_ISS_ID FROM FT_T_FRIP WHERE INST
> R_ID=?
> AND END_TMS IS NULL AND
> INST_MNEM
> IS NOT NULL
> ORDER BY INST_MNEM,ID_CTXT_TYP')
> dbd_preparse scanned 2 distinct placeholders
Note the number of distinct placeholders.
> fbh 1: 'INST_MNEM' NULLable, otype 96-> 5, dbsize 8/9, p8.s0
> fbh 2: 'ID_CTXT_TYP' NULLable, otype 96-> 5, dbsize 8/9,
> p8.s0
> fbh 3: 'ISS_ID' NULLable, otype 1-> 5, dbsize 40/41, p40.s0
And the three separate returned fields.
> <- prepare= DBI::st=HASH(0x26fb94) at get_isid_frip_ids.pl line 88.
> -> bind_param for DBD::Oracle::st (DBI::st=HASH(0x26fb94)~0x1079cc 1
> '0001x5MWFg' HASH(0x117a8c))
> bind :p1 <== '0001x5MWFg' (type 1, attribs: HASH(0x117a8c))
> bind :p1 <== '0001x5MWFg' (size 10/11/0, ptype 4, otype 96)
> <- bind_param= 1 at get_isid_frip_ids.pl line 106.
Note that DBD::Oracle renamed the placeholder as bind variable :p1. There
is also a :p2 that you did not given a value for ...
> -> execute for DBD::Oracle::st (DBI::st=HASH(0x26fb94)~0x1079cc)
> dbd_st_execute SELECT (out0, lob0)...
> !! ERROR: 1008 'ORA-01008: not all variables bound (DBD ERROR:
> OCIStmtExecute)'
as this tells you. That error message is available in $DBI::errstr or
$sth_isid_frp->errstr, so trace()ing wasn't necessary to see it.
> <- execute= undef at get_isid_frip_ids.pl line 107.
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
