Announcement Announcement Module
Collapse
No announcement yet.
Why won't this work? Inserting null value Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Why won't this work? Inserting null value

    I created a single table in oracle database:

    Code:
    create table test_table (
       test_value   varchar(50)
    )
    I tried inserting a null value into the table using both of these methods.
    Code:
    String testValue = null;
    my=JDBCTemplate.update("INSERT INTO TABLE test_table VALUES (?)",
    new Object[]{testValue});
    
    my=JDBCTemplate.update("INSERT INTO TABLE test_table VALUES (?)",
    new Object[]{ testValue }, new int[]{ Types.NULL });
    Each time I get an exception with a message indicating Invalid Column Type.

    The only way I got around it was to check the value and if it was null, change it to an empty String. This means I would have to do this for every value, regardless of the type (so if a date was null, pass empty string object). In the real application, I have many columns to insert, so checking each column is tedious.

    I am wondering why doesn't the Java null translate to Oracle null?

  • #2
    Your SQL is wrong, try - "INSERT INTO test_table VALUES (?)". It should work.

    Comment


    • #3
      Originally posted by trisberg View Post
      Your SQL is wrong, try - "INSERT INTO test_table VALUES (?)". It should work.
      My SQL is correct in my code, it was only incorrectly typed in this post (I do not actually cut and paste, not have the exact code in front of me when I type a post)

      My point is when I use a non-null value it works just fine as opposed to a null value. I am wondering if anyone else has had the same issue.

      Comment


      • #4
        Try

        Code:
        my=JDBCTemplate.update("INSERT INTO test_table VALUES (?)",
        new Object[]{ testValue }, new int[]{ Types.VARCHAR });

        And, by the way, how test_table is defined? And which DB are you using?Sorry, not noticed that this was explained in the original post.

        Regards,
        Oleksandr

        Originally posted by smiles78 View Post
        My SQL is correct in my code, it was only incorrectly typed in this post (I do not actually cut and paste, not have the exact code in front of me when I type a post)

        My point is when I use a non-null value it works just fine as opposed to a null value. I am wondering if anyone else has had the same issue.
        Last edited by al0; Apr 21st, 2008, 08:38 AM.

        Comment


        • #5
          Inserting a null value into a varchar column should work with an Oracle db. Some JDBC drivers have issues with null value handling in preparedStatement.setObject so that's why declaring the type has a better chance of working.

          If you can answer these questions I can try to solve this issue:
          - what database and version are you using?
          - what JDBC driver and version are you using?
          - what version of Spring are you using?

          Comment


          • #6
            I believe al0 is right, it should be Types.VARCHAR. You're specifying the SQL type of the column not the type of the value...this equates to the PreparedStatement JDBC call ps.setNull(index, Types.VARCHAR). If the value is non-null the template class will use ps.setString(index, testValue)

            Comment


            • #7
              Just small remark - both methods used in an original post effectively results in the same call
              Code:
                     ps.setNull(paramIndex, Types.NULL);
              as it is clear from StatementCreatorUtils.java code (setParameterValueInternal() method).

              Regards,
              Oleksandr


              Originally posted by kev374 View Post
              I believe al0 is right, it should be Types.VARCHAR. You're specifying the SQL type of the column not the type of the value...this equates to the PreparedStatement JDBC call ps.setNull(index, Types.VARCHAR). If the value is non-null the template class will use ps.setString(index, testValue)

              Comment


              • #8
                Very small remark

                At the end I have defeated my laziness and instead of giving advices has written a small piece of code that test claim of original poster - result is quite predictable all 3 variants work flawlessly:
                Code:
                                    String testValue = null;
                                    getJdbcTemplate().update(
                                        "INSERT INTO test_table VALUES (?)",
                                         new Object[]{testValue});
                
                                    getJdbcTemplate().update(
                                      "INSERT INTO test_table VALUES (?)",
                                       new Object[]{ testValue }, 
                                       new int[]{ NULL });
                
                                    getJdbcTemplate().update(
                                      "INSERT INTO test_table VALUES (?)",
                                       new Object[]{ testValue }, 
                                       new int[]{ VARCHAR });
                After run of this code TEST_TABLE contains 3 null records

                Code:
                SQL> set null null
                SQL> select * from test_table;
                
                TEST_VALUE
                --------------------------------------------------
                null
                null
                null
                
                SQL>
                Oracle server 9.2.0.8 JDBC driver 10.2.0.1.

                So problem is somewhere else. Please, provide more information - full stack trace and test program code along with DB and JDBC driver versions would be very appreciated.

                Regards,
                Oleksandr

                Comment

                Working...
                X