This discussion is archived
10 Replies Latest reply: Jan 23, 2013 1:19 PM by rp0428 RSS

OraclePreparedStatement.setNull Not Working

user10737970 Newbie
Currently Being Moderated
I have a table that has an abstract data type:

desc wf_std_transition
Name Null? Type
----------------------------------------- -------- ----------------------------
WORKFLOW_NAME_PRED NOT NULL VARCHAR2(200 CHAR)
STATE_NAME_PRED NOT NULL VARCHAR2(40 CHAR)
WORKFLOW_NAME_SUCC NOT NULL VARCHAR2(200 CHAR)
STATE_NAME_SUCC NOT NULL VARCHAR2(40 CHAR)
TRAN_NAME NOT NULL VARCHAR2(100 CHAR)
TRAN_DESCR VARCHAR2(4000 CHAR)
DISPLAY_LABEL VARCHAR2(40 CHAR)
DFLT_MSG VARCHAR2(4000 CHAR)
DFLT_MSG_SEC_DQI KPS_ADM.SEC_DQI_T
DFLT_SUBJECT VARCHAR2(200 CHAR)
DFLT_SUBJECT_SEC_DQI KPS_ADM.SEC_DQI_T
REQUIRE_ALL_INDCTR NOT NULL CHAR(1 CHAR)
DATE_CREATED NOT NULL DATE
DATE_MODIFIED DATE
CREATED_BY NOT NULL VARCHAR2(30 CHAR)
MODIFIED_BY VARCHAR2(30 CHAR)

I am using Java/JDBC to insert into the above table and want to detect if the input for the DFLT_MSG column is null. If the input is null I want to set the DFLT_MSG column to null.

if (msg == null) {
stmt.setNull(9, Types.OTHER);
} else {
... create an ORAData type here
stmt.setORAData(9, msgDQIType);
}

The setNull does not work, I am getting 932 oracle error: inconsistent datatypes.

How can I set an ADT column to null through JDBC?

thanks,
Beth
  • 1. Re: OraclePreparedStatement.setNull Not Working
    rp0428 Guru
    Currently Being Moderated
    >
    The setNull does not work, I am getting 932 oracle error: inconsistent datatypes.

    How can I set an ADT column to null through JDBC?
    >
    Aren't you defining 'msgDQIType' somewhere?

    Did you try setting that to null and using it just like you use it now when 'msgDQIType' isn't null?
    stmt.setORAData(9, msgDQIType);
  • 2. Re: OraclePreparedStatement.setNull Not Working
    user10737970 Newbie
    Currently Being Moderated
    yes, I have tried:

    stmt.setORAData(9, null),

    but this throws a SQLException.

    Beth
  • 3. Re: OraclePreparedStatement.setNull Not Working
    rp0428 Guru
    Currently Being Moderated
    >
    yes, I have tried:

    stmt.setORAData(9, null),

    but this throws a SQLException.
    >
    That is NOT what I asked. This is what I asked
    >
    Aren't you defining 'msgDQIType' somewhere?

    Did you try setting that to null and using it just like you use it now when 'msgDQIType' isn't null?
    stmt.setORAData(9, msgDQIType);
    I didn't ask if you used the word 'null' in the setORAData statement. I asked how you defined 'msgDQIType' and if you then initialized it to null and used it in the 'set' statement.

    And don't say what error you get. Post the exact error message and text that you get.
  • 4. Re: OraclePreparedStatement.setNull Not Working
    dsurber Explorer
    Currently Being Moderated
    The DFLT_MSG column is defined as VARCHAR2(4000) not as an ADT. setNull(9, Types.VARCHAR) should work. If the column were defined as an ADT then you should use the appropriate ADT type, eg setNull(9, Types.STRUCT) or setNull(9, Types.ARRAY).
  • 5. Re: OraclePreparedStatement.setNull Not Working
    rp0428 Guru
    Currently Being Moderated
    The 9th field is 'DFLT_MSG_SEC_DQI KPS_ADM.SEC_DQI_T', which is an ADT.

    I read it that OP wants to set DFLT_MSG_SEC_DQI KPS_ADM.SEC_DQI_T to null if the DFLT_MSG is null. But who knows for sure.
  • 6. Re: OraclePreparedStatement.setNull Not Working
    user10737970 Newbie
    Currently Being Moderated
    Sorry for all the confusion, my first post was in error. Basically I want to set the DFLT_MSG_SEC_DQI column to null when needed in my Java app. The DFLT_MSG_SEC_DQI column is defined as KPS_ADM.SEC_DQI_T, which is an Oracle ADT.

    First I tried to use setNull method on the OraclePreparedStatement with the java.sql.Types value of NULL:

    setNull(9, Types.NULL)

    This produces a SQLException: ORA-00932: inconsistent datatypes: expected KPS_ADM.SEC_DQI_T got CHAR

    Next, I tried to use the Types.STRUCT value:

    setNull(9, Types.STRUCT)

    This produces a SQLException on the setNull method: Invalid column type: sqlType=2002

    I then tried to set a variable of the ADT type to null and use the variable in the setORAData method:

    SecurityDqiType msgDqiType = null;
    setORAData(9, msgDqiType);

    The SecurityDqiType is mapped to the database ADT.

    This approach results in a NullPointerException on the setORAData method.
  • 7. Re: OraclePreparedStatement.setNull Not Working
    Tolls Journeyer
    Currently Being Moderated
    One option I see you haven't tried is setObject.
    This is referenced in here:
    http://docs.oracle.com/cd/B28359_01/java.111/b31224/oraoot.htm#i1040531

    Another is using the setNull with three parameters.
    I've seen it used with OPAQUE, and supplying the type name, in your case "KPS_ADM.SEC_DQI_T".
    In this:
    http://docs.oracle.com/cd/B19306_01/java.102/b14188/codeex.htm#CCJBDCHJ
    search for setORAData and there's a setNull on the line before. It's generated, so take it with a pinch of salt.
    (OPAQUE is 2007).

    I would go with setObject if it works. Pulling stuff from generated code is not always wise.
    It's quite possible one of the other Types should be used instead.
  • 8. Re: OraclePreparedStatement.setNull Not Working
    rp0428 Guru
    Currently Being Moderated
    You never posted any info about the DB, Java or JDBC jar versions you are using but since 10i Release 1 you can use this version of 'setNull' where you specify the SQL type. The variant of 'setNull' you are using send a Java NULL, not a SQL type NULL.
    >
    setNull
    void setNull(java.lang.String parameterName,
    int sqlType,
    java.lang.String typeName)
    throws java.sql.SQLException

    Sets the designated parameter to SQL NULL. This version of the method setNull should be used for user-defined types and REF type parameters. Examples of user-defined types include: STRUCT, DISTINCT, JAVA_OBJECT, and named array types.

    Note: To be portable, applications must give the SQL type code and the fully-qualified SQL type name when specifying a NULL user-defined or REF parameter. In the case of a user-defined type the name is the type name of the parameter itself. For a REF parameter, the name is the type name of the referenced type. If a JDBC driver does not need the type code or type name information, it may ignore it. Although it is intended for user-defined and Ref parameters, this method may be used to set a null parameter of any JDBC type. If the parameter does not have a user-defined or REF type, the given typeName is ignored.

    Specified by:
    setNull in interface java.sql.CallableStatement

    Parameters:
    parameterName - the name of the parameter
    sqlType - a value from java.sql.Types
    typeName - the fully-qualified name of an SQL user-defined type; ignored if the parameter is not a user-defined type or SQL REF value

    Throws:
    java.sql.SQLException - if a database access error occurs

    Since:
    10i Release 1
  • 9. Re: OraclePreparedStatement.setNull Not Working
    user10737970 Newbie
    Currently Being Moderated
    Thanks to everyone who posted. The setNull with 3 parameters (index, sqlType, typeName) worked for me.

    Beth
  • 10. Re: OraclePreparedStatement.setNull Not Working
    rp0428 Guru
    Currently Being Moderated
    Then you should give Tolls credit for answering your question.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points