Finder fails even though db row exists - Weblogic

This is a discussion on Finder fails even though db row exists - Weblogic ; Hello, We're using Weblogic Server 8.1 and Oracle 10g. I'm having trouble with a unique finder method on an EJB 2.0 CMP bean - it always fails even though the row is definitely in the database. If I do 'findAll' ...

+ Reply to Thread
Results 1 to 3 of 3

Thread: Finder fails even though db row exists

  1. Finder fails even though db row exists


    Hello,

    We're using Weblogic Server 8.1 and Oracle 10g.

    I'm having trouble with a unique finder method on an EJB 2.0 CMP bean - it always
    fails even though the row is definitely in the database. If I do 'findAll' and
    list out the beans that are returned, the bean I am looking for IS there, and
    the field is set as expected.

    Furthermore, if I use the debug driver and enable JDBC logging, I can see that
    the appropriate SELECT statement is executed (and if I execute it myself, I get
    the expected result).

    Any ideas?! Below I've attached all the relevant code.

    -------------------
    Creating the table
    -------------------

    create table T_ORGANIZATION (
    F_ORGANIZATION_PK NUMBER(16) not null,
    F_ORGANIZATION_ID INTEGER not null,
    F_NAME NCHAR(32) not null,
    F_NICENAME NVARCHAR2(64),
    F_CREATION_TIME TIMESTAMP not null,
    F_LASTMODIFY_TIME TIMESTAMP not null,
    constraint PK_T_ORGANIZATION primary key (F_ORGANIZATION_PK),
    constraint AK_AK__ORGANIZATION_N_T_ORGANI unique (F_NAME),
    constraint AK_AK__ORGANIZATION_I_T_ORGANI unique (F_ORGANIZATION_ID)
    );

    -------------------
    CMP Bean definition in weblogic-cmp-rdbms-jar.xml:
    -------------------


    Organization
    ....

    name
    F_NAME

    ....


    findByName

    java.lang.String


    o.name = ?1]]>

    ....


    -------------------
    Inserting the row:
    -------------------

    INSERT INTO T_ORGANIZATION VALUES ( 1, 1, 'ZKS', 'Zero Knowledge Systems, Inc.',
    SYSTIMESTAMP, SYSTIMESTAMP );

    -------------------
    And the method invocation... this always throws ObjectNotFoundException:
    -------------------

    OrganizationLocalHome olh = OrganizationUtil.getLocalHome();
    olh.findByName("ZKS");

    -------------------
    Output in JDBC Log:
    -------------------

    DRVR SQLS Input SQL: "SELECT DISTINCT WL0.F_ORGANIZATION_PK, WL0.F_CREATION_TIME,
    WL0.F_LASTMODIFY_TIME, WL0.F_NAME, WL0.F_NICENAME, WL0.F_ORGANIZATION_ID FROM
    T_ORGANIZATION WL0 WHERE ( WL0.F_NAME = ? )"
    DRVR OPER OraclePreparedStatement.setString(paramIndex=1, x=ZKS)



  2. Re: Finder fails even though db row exists


    OK,

    So I just noticed that the field in question (F_NAME) is a fixed length field
    (NCHAR) instead of variable (NVARCHAR2).

    What's wierd is that I'm still able to do the query successfully in sqlplus, using
    "... WHERE F_NAME = 'ZKS'".

    I guess WLS must do a check after executing the query, to be sure that the field
    is actually as expected.

    Is there any way to indicate to WLS that it should trim the value of a CMP field
    after retrieving it?

    Thanks,

    Erik

    "Erik Wright" wrote:
    >
    >Hello,
    >
    >We're using Weblogic Server 8.1 and Oracle 10g.
    >
    >I'm having trouble with a unique finder method on an EJB 2.0 CMP bean
    >- it always
    >fails even though the row is definitely in the database. If I do 'findAll'
    >and
    >list out the beans that are returned, the bean I am looking for IS there,
    >and
    >the field is set as expected.
    >
    >Furthermore, if I use the debug driver and enable JDBC logging, I can
    >see that
    >the appropriate SELECT statement is executed (and if I execute it myself,
    >I get
    >the expected result).
    >
    >Any ideas?! Below I've attached all the relevant code.
    >
    >-------------------
    >Creating the table
    >-------------------
    >
    >create table T_ORGANIZATION (
    > F_ORGANIZATION_PK NUMBER(16) not null,
    > F_ORGANIZATION_ID INTEGER not null,
    > F_NAME NCHAR(32) not null,
    > F_NICENAME NVARCHAR2(64),
    > F_CREATION_TIME TIMESTAMP not null,
    > F_LASTMODIFY_TIME TIMESTAMP not null,
    > constraint PK_T_ORGANIZATION primary key (F_ORGANIZATION_PK),
    > constraint AK_AK__ORGANIZATION_N_T_ORGANI unique (F_NAME),
    > constraint AK_AK__ORGANIZATION_I_T_ORGANI unique (F_ORGANIZATION_ID)
    >);
    >
    >-------------------
    >CMP Bean definition in weblogic-cmp-rdbms-jar.xml:
    >-------------------
    >
    >
    > Organization
    >...
    >
    > name
    > F_NAME
    >

    >...
    >
    >
    > findByName
    >
    > java.lang.String
    >

    >

    > [CDATA[SELECT DISTINCT OBJECT(o) FROM Organization
    >o WHERE
    >o.name = ?1]]

    >

    >...
    >

    >
    >-------------------
    >Inserting the row:
    >-------------------
    >
    >INSERT INTO T_ORGANIZATION VALUES ( 1, 1, 'ZKS', 'Zero Knowledge Systems,
    >Inc.',
    >SYSTIMESTAMP, SYSTIMESTAMP );
    >
    >-------------------
    >And the method invocation... this always throws ObjectNotFoundException:
    >-------------------
    >
    >OrganizationLocalHome olh = OrganizationUtil.getLocalHome();
    >olh.findByName("ZKS");
    >
    >-------------------
    >Output in JDBC Log:
    >-------------------
    >
    >DRVR SQLS Input SQL: "SELECT DISTINCT WL0.F_ORGANIZATION_PK, WL0.F_CREATION_TIME,
    >WL0.F_LASTMODIFY_TIME, WL0.F_NAME, WL0.F_NICENAME, WL0.F_ORGANIZATION_ID
    > FROM
    >T_ORGANIZATION WL0 WHERE ( WL0.F_NAME = ? )"
    >DRVR OPER OraclePreparedStatement.setString(paramIndex=1, x=ZKS)
    >
    >



  3. Re: Finder fails even though db row exists


    Hey Erik,

    There currently is no way to tell WLS to trim the cmp field after retrieving it.
    This feature is slated for a future release. Also, I don't believe our generated
    code does any checks after retrieving the field to ensure it's the value we expected.
    We just depend on the equals check in the query to ensure this. My guess is
    the descrepency between the behavior you see with SQL Plus and the CMP container
    is due to the JDBC driver being used. If you're unable to change the table so
    that the column is a variable length string, I think your only choice is to explicitly
    pad the parameter value to the appropriate length before passing it to the finder
    method.

    - Matt

    "Erik Wright" wrote:
    >
    >OK,
    >
    >So I just noticed that the field in question (F_NAME) is a fixed length
    >field
    >(NCHAR) instead of variable (NVARCHAR2).
    >
    >What's wierd is that I'm still able to do the query successfully in sqlplus,
    >using
    >"... WHERE F_NAME = 'ZKS'".
    >
    >I guess WLS must do a check after executing the query, to be sure that
    >the field
    >is actually as expected.
    >
    >Is there any way to indicate to WLS that it should trim the value of
    >a CMP field
    >after retrieving it?
    >
    >Thanks,
    >
    >Erik
    >
    >"Erik Wright" wrote:
    >>
    >>Hello,
    >>
    >>We're using Weblogic Server 8.1 and Oracle 10g.
    >>
    >>I'm having trouble with a unique finder method on an EJB 2.0 CMP bean
    >>- it always
    >>fails even though the row is definitely in the database. If I do 'findAll'
    >>and
    >>list out the beans that are returned, the bean I am looking for IS there,
    >>and
    >>the field is set as expected.
    >>
    >>Furthermore, if I use the debug driver and enable JDBC logging, I can
    >>see that
    >>the appropriate SELECT statement is executed (and if I execute it myself,
    >>I get
    >>the expected result).
    >>
    >>Any ideas?! Below I've attached all the relevant code.
    >>
    >>-------------------
    >>Creating the table
    >>-------------------
    >>
    >>create table T_ORGANIZATION (
    >> F_ORGANIZATION_PK NUMBER(16) not null,
    >> F_ORGANIZATION_ID INTEGER not null,
    >> F_NAME NCHAR(32) not null,
    >> F_NICENAME NVARCHAR2(64),
    >> F_CREATION_TIME TIMESTAMP not null,
    >> F_LASTMODIFY_TIME TIMESTAMP not null,
    >> constraint PK_T_ORGANIZATION primary key (F_ORGANIZATION_PK),
    >> constraint AK_AK__ORGANIZATION_N_T_ORGANI unique (F_NAME),
    >> constraint AK_AK__ORGANIZATION_I_T_ORGANI unique (F_ORGANIZATION_ID)
    >>);
    >>
    >>-------------------
    >>CMP Bean definition in weblogic-cmp-rdbms-jar.xml:
    >>-------------------
    >>
    >>
    >> Organization
    >>...
    >>
    >> name
    >> F_NAME
    >>

    >>...
    >>
    >>
    >> findByName
    >>
    >> java.lang.String
    >>

    >>

    >> [CDATA[SELECT DISTINCT OBJECT(o) FROM Organization
    >>o WHERE
    >>o.name = ?1]]

    >>

    >>...
    >>

    >>
    >>-------------------
    >>Inserting the row:
    >>-------------------
    >>
    >>INSERT INTO T_ORGANIZATION VALUES ( 1, 1, 'ZKS', 'Zero Knowledge Systems,
    >>Inc.',
    >>SYSTIMESTAMP, SYSTIMESTAMP );
    >>
    >>-------------------
    >>And the method invocation... this always throws ObjectNotFoundException:
    >>-------------------
    >>
    >>OrganizationLocalHome olh = OrganizationUtil.getLocalHome();
    >>olh.findByName("ZKS");
    >>
    >>-------------------
    >>Output in JDBC Log:
    >>-------------------
    >>
    >>DRVR SQLS Input SQL: "SELECT DISTINCT WL0.F_ORGANIZATION_PK, WL0.F_CREATION_TIME,
    >>WL0.F_LASTMODIFY_TIME, WL0.F_NAME, WL0.F_NICENAME, WL0.F_ORGANIZATION_ID
    >> FROM
    >>T_ORGANIZATION WL0 WHERE ( WL0.F_NAME = ? )"
    >>DRVR OPER OraclePreparedStatement.setString(paramIndex=1, x=ZKS)
    >>
    >>

    >



+ Reply to Thread