Announcement Announcement Module
No announcement yet.
ibatis problem inserting null value for Date type Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • ibatis problem inserting null value for Date type

    Encountered a strange problem with iBATIS 1.3.
    When a java.util.Date bean field is null, the binding to the mapped statement fails. I receive a SQLException thrown on the iBATIS SQLMapTemplate class, claiming the field has an invalid column type.

    I looked at iBATIS Spring JPetStore, found that the Order bean class also has a Date field (orderDate) and found - comparing to my code - no meaningfull differences in the implementation further on.
    Only in the case of JPetStore, the orderDate is never null because it's being instantiated with the current time, so .. I'm wondering if a unit test on the insertOrder method would fail in case the order date is null.

    Anyway, does anyone know something about this ? If detailed info is need, just ask.


  • #2
    problem with spring1.1.1

    If I remember correctly, there was bug report that if insert value is null, in some cases iBatis1.x could throw exception, this has been fixed in iBatis2.0.

    However, the class SqlMapDaoSupport in spring-1.1.1.jar only support iBatis 1.x, even iBatis 2.0 has a adapt class which is back compatible with spring1.1.1. there is still a problem to deal with null value from resultset and there is no exception!

    In this case, if returned resultset contains one null value, then the rest value will become null..... no good
    Here is the message from recent test:

    [myApp] DEBUG [http-8080-Processor24] PreparedStatementLogProxy.invoke(45) | {pstm-100028} PreparedStatement: SELECT userid, username, screenname, statusid, typeid FROM user_table where username = ?
    [myApp] DEBUG [http-8080-Processor24] PreparedStatementLogProxy.invoke(46) | {pstm-100028} Parameters: [test1]
    [myApp] DEBUG [http-8080-Processor24] PreparedStatementLogProxy.invoke(47) | {pstm-100028} Types: [java.lang.String]
    [myApp] DEBUG [http-8080-Processor24] ResultSetLogProxy.<init>(38) | {rset-100029} ResultSet
    [myApp] DEBUG [http-8080-Processor24] ResultSetLogProxy.invoke(58) | {rset-100029} Header: [USERID, USERNAME, SCREENNAME, STATUSID, TYPEID]
    [myApp] DEBUG [http-8080-Processor24] ResultSetLogProxy.invoke(62) |{rset-10002
    9} Result: [2, test1, null, 1, 0]
    [myApp] DEBUG [http-8080-Processor24] ActionFilter.doFilter(156) | [email protected][

    wish to get some feedbacks on this issue.



    • #3
      If you are using Oracle, you have to explicitly define the SQL
      data-type in the sqlMap for columns that are nullable. This is
      discussed in the iBATIS Developer Guide.

      So, your SQL would look something like this (if myDate is an
      SQL DATE type and is nullable):

      into myTable (myName, myDate)
      values (#name# , #dateATE#)

      This is if for inline notation. It would look different if you are
      defining this via a resultMap.




      • #4
        I have a similar problem, but I am just using the JdbcTemplate stuff. Oracle database, inserting a Java Long into a db field defined as Number and is nullable. If I try to insert a null value I get an exception complaining about "invalid column type". How do I insert a null?


        • #5
          Could you post some more info - like the sql you are using, a code snippet and the actual exception.


          • #6
            OK. The SQL and the actual method call in the DAO is
            public void audit&#40;String reportCode, String templateFileName,
                        String userId, String applicationCode, String parameters,
                        String status, Long duration, String hostinfo, Long reportLength&#41; &#123;
                    String sqlString = "insert into report_service_audit "
                            + "&#40;audit_time, report_code, template_name, userid, "
                            + "application_code, parameters, status, duration, server, "
                            + "report_length&#41; "
                            + "values &#40;?, ?, ?, ?, ?, ?, ?, ?, ?, ?&#41;";
                            new Object&#91;&#93; &#123; new Date&#40;&#41;, reportCode, templateFileName,
                                    userId, applicationCode, parameters, status, duration,
                                    hostinfo, reportLength &#125;&#41;;
            The DDL is

            create table REPORT_SERVICE_AUDIT
              AUDIT_TIME       DATE not null,
              REPORT_CODE      VARCHAR2&#40;200&#41;,
              TEMPLATE_NAME    VARCHAR2&#40;200&#41;,
              USERID           VARCHAR2&#40;100&#41;,
              APPLICATION_CODE VARCHAR2&#40;100&#41;,
              PARAMETERS       VARCHAR2&#40;4000&#41;,
              STATUS           VARCHAR2&#40;200&#41; not null,
              DURATION         NUMBER not null,
              SERVER           VARCHAR2&#40;200&#41;,
              REPORT_LENGTH    NUMBER
            If reportCode or reportLength are passed into the method as null then the exception is
            org.springframework.jdbc.UncategorizedSQLException&#58; &#40;executing PreparedStatementCallback &#91;org.spring[email protected]12bed82&#93;&#41;&#58; encountered SQLException &#91;Invalid column type&#93;; nested exception is java.sql.SQLException&#58; Invalid column type
            java.sql.SQLException&#58; Invalid column type
                at oracle.jdbc.dbaccess.DBError.throwSqlException&#40;;134&#41;
                at oracle.jdbc.dbaccess.DBError.throwSqlException&#40;;179&#41;


            • #7
              When you supply a null here, then the code can't determine the column type. You will have to provide the data types as an array of ints. Try the following:

                              new Object&#91;&#93; &#123; new Date&#40;&#41;, reportCode, templateFileName,
                                      userId, applicationCode, parameters, status, duration,
                                      hostinfo, reportLength &#125;,
                                  new int&#91;&#93; &#123;Types.DATE, Types.VARCHAR, Types.VARCHAR,
                                          Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.NUMERIC,
                                          Types.VARCHAR, Types.NUMERIC&#125;&#41;;


              • #8
                java.sql.SQLException: Invalid column type

                I received this error as well while trying to enter a null value into an Oracle DB table using the to_date(?, MM-DD-YYYY) function (any format would apply not just MM-DD-YYYY). The simple solution was to change the null to a "" (empty string) instead and Spring handled this much better (without having to pass in the column types).