Hello all,
I am having a problem with prepared sth that uses fetchrow_array with two
parameters passed in.
The problem is as follows:
in one of my tables I have a column STAT_DEF_ID ( CHAR(8) ) which is also
one of the four unique constraints for that table. I am able to select my
rows into an array only if value passed to STAT_DEF_ID is exactly 8 char. if
its less then 8 char I don't get any results back. Now I tried to leave
trailing spaces and pass a value with blanks at the end of a string to
constitute for 8 chars. No effect. However if I do execute that sql from
SQLPLUS I get the desired results. Also I created another statement that
selects everything from that table with no reference to STAT_DEF_ID but the
other unique key. In that case I get all the data I want. Here are some perl
code.
# the following code works just fine since I don't have STAT_DEF_ID=? in a
WHERE close
my $sth_test = $dbh->prepare(q{ SELECT STAT_ID, STAT_DEF_ID,
nvl(INST_MNEM,'N.A.'),
nvl(FINSRL_TYP,'N.A.'), DENOM_CURR_CDE, ORG_ID, SUBDIV_ID,
STAT_VAL_DTE, STAT_VAL_TMS, STAT_VAL_AMT, STAT_DEC_VAL_AMT,
STAT_INT_VAL_NUM, STAT_CHAR_VAL_TXT
FROM FT_T_ISST WHERE INSTR_ID=?
ORDER BY STAT_DEF_ID,LAST_CHG_TMS DESC}) or die $dbh->errstr;
$sth_test->execute($instr_id);
while (my @row=$sth_test->fetchrow_array) {
print join(',',@row)."\n";
}
###############################
# However this code don't return those records that have STAT_DEF_ID < than
8 chars long
my $sth_test = $dbh->prepare(q{ SELECT STAT_ID, STAT_DEF_ID,
nvl(INST_MNEM,'N.A.'),
nvl(FINSRL_TYP,'N.A.'), DENOM_CURR_CDE, ORG_ID, SUBDIV_ID,
STAT_VAL_DTE, STAT_VAL_TMS, STAT_VAL_AMT, STAT_DEC_VAL_AMT,
STAT_INT_VAL_NUM, STAT_CHAR_VAL_TXT
FROM FT_T_ISST WHERE INSTR_ID=? AND STAT_DEF_ID=?
ORDER BY STAT_DEF_ID,LAST_CHG_TMS DESC}) or die $dbh->errstr;
# $instr_id and $stat_def_id are obtained from a file
$sth_test->execute($instr_id, $stat_def_id);
while (my @row=$sth_test->fetchrow_array) {
print join(',',@row)."\n";
}
NOTE: there are two prepared statements in a trace.txt I am executing the
second one. As you can see the fetchrow_array works for 'FD12BFEE' and
doesnt for 'FDPHONE'
Thanx much for your help
<<trace.txt>>
Daniel Rozengurtel
Analyst II - Data Mining/WHSE
IT Clearing-Settlements
tell: (646) 733-4242
[EMAIL PROTECTED]
_____________________________________________________________________
IMPORTANT NOTICES:
This message is intended only for the addressee. Please notify the
sender by e-mail if you are not the intended recipient. If you are not the
intended recipient, you may not copy, disclose, or distribute this message
or its contents to any other person and any such actions may be unlawful.
Banc of America Securities LLC("BAS") does not accept time
sensitive, action-oriented messages or transaction orders, including orders
to purchase or sell securities, via e-mail.
BAS reserves the right to monitor and review the content of all
messages sent to or from this e-mail address. Messages sent to or from this
e-mail address may be stored on the BAS e-mail system.
DBI::db=HASH(0x25fdc4) trace level set to 3 in DBI 1.15-nothread
-> prepare for DBD::Oracle::db (DBI::db=HASH(0x2823e8)~0x25fdc4 ' SELECT
STAT_ID,
STAT_DEF_ID,
nvl(INST_MNEM,'N.A.'),
nvl(FINSRL_TYP,'N.A.'),
DENOM_CURR_CDE,
ORG_ID,
SUBDIV_ID,
STAT_VAL_DTE,
STAT_VAL_TMS,
STAT_VAL_AMT,
STAT_DEC_VAL_AMT,
STAT_INT_VAL_NUM,
STAT_CHAR_VAL_TXT FROM FT_T_ISST WHERE INSTR_ID=? AND STAT_DEF_ID=? AND END_TMS IS
NULL
order by stat_Def_id,last_chg_tms desc')
dbih_setup_handle(DBI::st=HASH(0x267480)=>DBI::st=HASH(0x2244c8), DBD::Oracle::st,
26748c, Null!)
dbih_make_com(DBI::db=HASH(0x25fdc4), DBD::Oracle::st, 204)
dbd_preparse scanned 2 distinct placeholders
dbd_st_prepare'd sql SELECT
dbd_describe SELECT (EXPLICIT, lb 80)...
fbh 1: 'STAT_ID' NO null , otype 96-> 5, dbsize 10/11, p10.s0
fbh 2: 'STAT_DEF_ID' NO null , otype 96-> 5, dbsize 8/9, p8.s0
fbh 3: 'NVL(INST_MNEM,'N.A.')' NULLable, otype 1-> 5, dbsize 8/9, p8.s0
fbh 4: 'NVL(FINSRL_TYP,'N.A.')' NULLable, otype 1-> 5, dbsize 8/9, p8.s0
fbh 5: 'DENOM_CURR_CDE' NO null , otype 96-> 5, dbsize 3/4, p3.s0
fbh 6: 'ORG_ID' NULLable, otype 96-> 5, dbsize 4/5, p4.s0
fbh 7: 'SUBDIV_ID' NULLable, otype 96-> 5, dbsize 4/5, p4.s0
fbh 8: 'STAT_VAL_DTE' NULLable, otype 12-> 5, dbsize 7/76, p75.s0
fbh 9: 'STAT_VAL_TMS' NULLable, otype 12-> 5, dbsize 7/76, p75.s0
fbh 10: 'STAT_VAL_AMT' NULLable, otype 2-> 5, dbsize 22/134, p15.s0
fbh 11: 'STAT_DEC_VAL_AMT' NULLable, otype 2-> 5, dbsize 22/134, p11.s11
fbh 12: 'STAT_INT_VAL_NUM' NULLable, otype 2-> 5, dbsize 22/134, p38.s0
fbh 13: 'STAT_CHAR_VAL_TXT' NULLable, otype 1-> 5, dbsize 254/255, p254.s0
dbd_describe'd 13 columns (row bytes: 379 max, 325 est avg, cache: 36)
<- prepare= DBI::st=HASH(0x267480) at end_tms_stats_20030219.pl line 78.
-> prepare for DBD::Oracle::db (DBI::db=HASH(0x2823e8)~0x25fdc4 ' SELECT
STAT_ID,
STAT_DEF_ID,
nvl(INST_MNEM,'N.A.'),
nvl(FINSRL_TYP,'N.A.'),
DENOM_CURR_CDE,
ORG_ID,
SUBDIV_ID,
STAT_VAL_DTE,
STAT_VAL_TMS,
STAT_VAL_AMT,
STAT_DEC_VAL_AMT,
STAT_INT_VAL_NUM,
STAT_CHAR_VAL_TXT FROM FT_T_ISST WHERE INSTR_ID=?
order by stat_Def_id,last_chg_tms desc')
dbih_setup_handle(DBI::st=HASH(0x2674bc)=>DBI::st=HASH(0x23c6d8), DBD::Oracle::st,
2674c8, Null!)
dbih_make_com(DBI::db=HASH(0x25fdc4), DBD::Oracle::st, 204)
dbd_preparse scanned 1 distinct placeholders
dbd_st_prepare'd sql SELECT
dbd_describe SELECT (EXPLICIT, lb 80)...
fbh 1: 'STAT_ID' NO null , otype 96-> 5, dbsize 10/11, p10.s0
fbh 2: 'STAT_DEF_ID' NO null , otype 96-> 5, dbsize 8/9, p8.s0
fbh 3: 'NVL(INST_MNEM,'N.A.')' NULLable, otype 1-> 5, dbsize 8/9, p8.s0
fbh 4: 'NVL(FINSRL_TYP,'N.A.')' NULLable, otype 1-> 5, dbsize 8/9, p8.s0
fbh 5: 'DENOM_CURR_CDE' NO null , otype 96-> 5, dbsize 3/4, p3.s0
fbh 6: 'ORG_ID' NULLable, otype 96-> 5, dbsize 4/5, p4.s0
fbh 7: 'SUBDIV_ID' NULLable, otype 96-> 5, dbsize 4/5, p4.s0
fbh 8: 'STAT_VAL_DTE' NULLable, otype 12-> 5, dbsize 7/76, p75.s0
fbh 9: 'STAT_VAL_TMS' NULLable, otype 12-> 5, dbsize 7/76, p75.s0
fbh 10: 'STAT_VAL_AMT' NULLable, otype 2-> 5, dbsize 22/134, p15.s0
fbh 11: 'STAT_DEC_VAL_AMT' NULLable, otype 2-> 5, dbsize 22/134, p11.s11
fbh 12: 'STAT_INT_VAL_NUM' NULLable, otype 2-> 5, dbsize 22/134, p38.s0
fbh 13: 'STAT_CHAR_VAL_TXT' NULLable, otype 1-> 5, dbsize 254/255, p254.s0
dbd_describe'd 13 columns (row bytes: 379 max, 325 est avg, cache: 36)
<- prepare= DBI::st=HASH(0x2674bc) at end_tms_stats_20030219.pl line 94.
-> execute for DBD::Oracle::st (DBI::st=HASH(0x267480)~0x2244c8 '0001x6Fuzw'
'FDPHONE')
bind :p1 <== '0001x6Fuzw' (type 0)
bind :p1 <== '0001x6Fuzw' (size 10/11/0, ptype 4, otype 1)
bind :p1 <== '0001x6Fuzw' (size 10/10, otype 1, indp 0)
bind :p1 done
bind :p2 <== 'FDPHONE' (type 0)
bind :p2 <== 'FDPHONE' (size 7/8/0, ptype 4, otype 1)
bind :p2 <== 'FDPHONE' (size 7/7, otype 1, indp 0)
bind :p2 done
dbd_st_execute SELECT (out0, lob0)...
dbd_phs_in ':p1' (0,0): len 10, ind 0
dbd_phs_in ':p2' (0,0): len 7, ind 0
dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0)
<- execute= '0E0' at end_tms_stats_20030219.pl line 121.
-> fetchrow_array for DBD::Oracle::st (DBI::st=HASH(0x267480)~0x2244c8)
dbd_st_fetch 13 fields...
dbd_st_fetch no-more-data
<- fetchrow_array= ( ) [0 items] at end_tms_stats_20030219.pl line 122.
-> execute for DBD::Oracle::st (DBI::st=HASH(0x267480)~0x2244c8 '0001x6Fuzw'
'FD12BFEE')
bind :p1 <== '0001x6Fuzw' (type 0)
bind :p1 <== '0001x6Fuzw' (size 10/11/0, ptype 4, otype 1)
bind :p1 <== '0001x6Fuzw' (size 10/10, otype 1, indp 0)
bind :p1 done
bind :p2 <== 'FD12BFEE' (type 0)
bind :p2 <== 'FD12BFEE' (size 8/9/0, ptype 4, otype 1)
bind :p2 <== 'FD12BFEE' (size 8/8, otype 1, indp 0)
bind :p2 done
dbd_st_execute SELECT (out0, lob0)...
dbd_phs_in ':p1' (0,0): len 10, ind 0
dbd_phs_in ':p2' (0,0): len 8, ind 0
dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0)
<- execute= '0E0' at end_tms_stats_20030219.pl line 121.
-> fetchrow_array for DBD::Oracle::st (DBI::st=HASH(0x267480)~0x2244c8)
dbd_st_fetch 13 fields...
dbih_setup_fbav for 13 fields => 0x26772c
dbd_st_fetch 13 fields SUCCESS
<- fetchrow_array= ( '0001CW$t1g' 'FD12BFEE' 'UQ ' 'EXCHANGE' ' ' undef
undef undef undef '0' '0' undef undef ) [13 items] at end_tms_stats_20030219.pl line
122.
-> fetchrow_array for DBD::Oracle::st (DBI::st=HASH(0x267480)~0x2244c8)
dbd_st_fetch 13 fields...
dbd_st_fetch no-more-data
<- fetchrow_array= ( ) [0 items] at end_tms_stats_20030219.pl line 122.
-> disconnect for DBD::Oracle::db (DBI::db=HASH(0x2823e8)~0x25fdc4)
<- disconnect= 1 at end_tms_stats_20030219.pl line 160.
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x2244c8)~INNER)
<- DESTROY= undef.
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x23c6d8)~INNER)
<- DESTROY= undef.
-> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x25fdc4)~INNER)
<- DESTROY= undef.
