Oracle's RPAD problematic via Perl's DBI module

Discussion in 'Perl Misc' started by dn_perl@hotmail.com, Aug 25, 2004.

  1. Guest

    The following code is returning an unexpected result.
    (Untested cut-n-paste; apologies)


    Say the table is : students(name CHAR(8))
    Entries in Students are : ' ' (8 blanks),
    'bob ' and 'dave ' .

    use strict ;
    use DBI ;

    my $st_name = " " ; # non-blank name
    my $dstmt = $dbh->prepare("select count(*) from students
    where name = RPAD(?,8) ") ; # STMT AA
    $dstmt->execute($st_name) or die "sql call failed";
    my $num_entries = $dstmt->fetchrow() ;
    $dstmt->finish ;

    $num_entries should be set to 1; instead it is set to 0.
    This problem occurs only for a blank string.
    If $st_name = "dave" , then the statements work properly.

    If I run the query via sqlplus :
    select count(*) from students where name = RPAD(' ',8) ,
    the result is 1, as expected.


    I am using the getaround :
    my $dstmt = $dbh->prepare("select count(*) from students
    where trim(name) = ? or (name = ' ' and trim(?) is null) ") ;
    $dstmt->execute($st_name, $st_name) or die "sql call failed";


    But still I am surprised why STMT AA (above) fails to return
    the expected result.

    -----------
     
    , Aug 25, 2004
    #1
    1. Advertising

  2. In comp.lang.perl.misc <> wrote:
    : Say the table is : students(name CHAR(8))
    : Entries in Students are : ' ' (8 blanks),
    : 'bob ' and 'dave ' .

    : my $st_name = " " ; # non-blank name
    : my $dstmt = $dbh->prepare("select count(*) from students
    : where name = RPAD(?,8) ") ; # STMT AA
    : $dstmt->execute($st_name) or die "sql call failed";
    : my $num_entries = $dstmt->fetchrow() ;
    : $dstmt->finish ;

    : $num_entries should be set to 1; instead it is set to 0.
    : This problem occurs only for a blank string.

    : But still I am surprised why STMT AA (above) fails to return
    : the expected result.

    What Oracle Version are you using? There are some subtle differences
    in the handling of trailing blanks between Oracle 8 and 9. I have a
    table "foo" containing two blank entries. When running the following code

    my $ss = "SELECT COUNT(*) FROM foo WHERE bar = RPAD(?,8)";
    my $blank = ' ';
    my $sth = $dbh->prepare($ss) or die;
    $sth->execute($blank) or die;
    print "Blank: ", $sth->fetch->[0], "\n";

    I get the following result:

    Blank: 2 # Oracle 9 database using local Oracle 9 client
    Blank: 2 # Oracle 9 client connecting to a remote Oracle 8 database
    Blank: 0 # Oracle 8 database using local Oracle 8 client
    Blank: 0 # Oracle 8 client connecting to a remote Oracle 9 database

    Peter

    --
    Peter Marksteiner
    Vienna University Computer Center
     
    Peter Marksteiner, Aug 25, 2004
    #2
    1. Advertising

  3. wrote:

    > The following code is returning an unexpected result.
    > (Untested cut-n-paste; apologies)
    >
    >
    > Say the table is : students(name CHAR(8))
    > Entries in Students are : ' ' (8 blanks),
    > 'bob ' and 'dave ' .
    >
    > use strict ;
    > use DBI ;
    >
    > my $st_name = " " ; # non-blank name
    > my $dstmt = $dbh->prepare("select count(*) from students
    > where name = RPAD(?,8) ") ; # STMT AA
    > $dstmt->execute($st_name) or die "sql call failed";
    > my $num_entries = $dstmt->fetchrow() ;
    > $dstmt->finish ;
    >
    > $num_entries should be set to 1; instead it is set to 0.
    > This problem occurs only for a blank string.
    > If $st_name = "dave" , then the statements work properly.
    >
    > If I run the query via sqlplus :
    > select count(*) from students where name = RPAD(' ',8) ,
    > the result is 1, as expected.
    >
    > I am using the getaround :
    > my $dstmt = $dbh->prepare("select count(*) from students
    > where trim(name) = ? or (name = ' ' and trim(?) is null) ") ;
    > $dstmt->execute($st_name, $st_name) or die "sql call failed";


    This is doing a full table scan.

    > But still I am surprised why STMT AA (above) fails to return
    > the expected result.
    >
    > -----------


    For a start its very unusual to rpad fields in a database.

    The underlying problem probably has to do with Oracle/dbi treatment of ''
    and NULL.

    gtoomey
     
    Gregory Toomey, Aug 25, 2004
    #3
  4. Mark Bole Guest

    Peter Marksteiner wrote:

    > In comp.lang.perl.misc <> wrote:
    > : Say the table is : students(name CHAR(8))
    > : Entries in Students are : ' ' (8 blanks),
    > : 'bob ' and 'dave ' .
    >
    > : my $st_name = " " ; # non-blank name
    > : my $dstmt = $dbh->prepare("select count(*) from students
    > : where name = RPAD(?,8) ") ; # STMT AA
    > : $dstmt->execute($st_name) or die "sql call failed";
    > : my $num_entries = $dstmt->fetchrow() ;
    > : $dstmt->finish ;
    >
    > : $num_entries should be set to 1; instead it is set to 0.
    > : This problem occurs only for a blank string.
    >
    > : But still I am surprised why STMT AA (above) fails to return
    > : the expected result.
    >
    > What Oracle Version are you using? There are some subtle differences
    > in the handling of trailing blanks between Oracle 8 and 9. I have a
    > table "foo" containing two blank entries. When running the following code
    >
    > my $ss = "SELECT COUNT(*) FROM foo WHERE bar = RPAD(?,8)";
    > my $blank = ' ';
    > my $sth = $dbh->prepare($ss) or die;
    > $sth->execute($blank) or die;
    > print "Blank: ", $sth->fetch->[0], "\n";
    >
    > I get the following result:
    >
    > Blank: 2 # Oracle 9 database using local Oracle 9 client
    > Blank: 2 # Oracle 9 client connecting to a remote Oracle 8 database
    > Blank: 0 # Oracle 8 database using local Oracle 8 client
    > Blank: 0 # Oracle 8 client connecting to a remote Oracle 9 database
    >
    > Peter
    >


    I too received the correct result using the OP's test case under Oracle
    9i client and server.

    Only Oracle 8.1.7.4 client (terminal release of 8i product) is certified
    for connecting to Oracle 9i server. Earlier versions (such as 8.1.7.3)
    are not, and you may also face other issues, such as mysterious errors
    related to the DATE datatype. Highly recommended to recompile your DBD
    module using the Oracle 9i libraries if you haven't done so.

    You might also find help in the Perl documentation under Database Handle
    Attributes, "ora_ph_type". Your use of the CHAR datatype in your table
    is unusual in my experience, one almost always uses VARCHAR2 instead.
    Try searching for "blank-padded comparison semantics" at
    http://tahiti.oracle.com

    ORA_VARCHAR2 - Strip trailing spaces and allow embedded \0 bytes.
    This is the normal default placeholder type.

    ORA_STRING - Don't strip trailing spaces and end the string at
    the first \0.

    ORA_CHAR - Don't strip trailing spaces and allow embedded \0.
    Force 'blank-padded comparison semantics'.

    --Mark Bole
     
    Mark Bole, Aug 25, 2004
    #4
  5. J Guest

    () wrote in message news:<>...
    > The following code is returning an unexpected result.
    > (Untested cut-n-paste; apologies)
    >
    >
    > Say the table is : students(name CHAR(8))
    > Entries in Students are : ' ' (8 blanks),
    > 'bob ' and 'dave ' .
    >
    > use strict ;
    > use DBI ;
    >
    > my $st_name = " " ; # non-blank name
    > my $dstmt = $dbh->prepare("select count(*) from students
    > where name = RPAD(?,8) ") ; # STMT AA
    > $dstmt->execute($st_name) or die "sql call failed";
    > my $num_entries = $dstmt->fetchrow() ;
    > $dstmt->finish ;
    >
    > $num_entries should be set to 1; instead it is set to 0.
    > This problem occurs only for a blank string.
    > If $st_name = "dave" , then the statements work properly.
    >
    > If I run the query via sqlplus :
    > select count(*) from students where name = RPAD(' ',8) ,
    > the result is 1, as expected.
    >
    >
    > I am using the getaround :
    > my $dstmt = $dbh->prepare("select count(*) from students
    > where trim(name) = ? or (name = ' ' and trim(?) is null) ") ;
    > $dstmt->execute($st_name, $st_name) or die "sql call failed";
    >
    >
    > But still I am surprised why STMT AA (above) fails to return
    > the expected result.
    >
    > -----------


    Check this out
    RPAD(?,8)

    Does this work?Not probably!
     
    J, Aug 25, 2004
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ulloa
    Replies:
    1
    Views:
    531
    Juha Laiho
    Jul 22, 2004
  2. Xif
    Replies:
    1
    Views:
    320
    Steven Bethard
    Mar 9, 2005
  3. Vincent Le-Texier
    Replies:
    1
    Views:
    156
    Paul Lalli
    Dec 3, 2004
  4. Feyruz
    Replies:
    4
    Views:
    2,199
    Sherm Pendley
    Oct 14, 2005
  5. PerlNovice

    Problematic Perl code

    PerlNovice, Jan 2, 2007, in forum: Perl Misc
    Replies:
    8
    Views:
    114
    Eric Schwartz
    Jan 5, 2007
Loading...

Share This Page