FAQ
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
Daniel.Rozengurtel@bofasecurities.com



_____________________________________________________________________
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.

Search Discussions

  • Dan Muey at Feb 21, 2003 at 3:46 pm
    If it won't be exactly 8 characters every time you need to change it to VARCHAR(8)
    That will allow up to 8 characters but doesn't have to be exactly 8.


    Dan
    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
    Daniel.Rozengurtel@bofasecurities.com



    _____________________________________________________________________
    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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedFeb 20, '03 at 4:18p
activeFeb 21, '03 at 3:46p
posts2
users2
websitedbi.perl.org

2 users in discussion

Rozengurtel, Daniel: 1 post Dan Muey: 1 post

People

Translate

site design / logo © 2022 Grokbase