Why won't this work? Inserting null value

[smiles78]

I created a single table in oracle database:

create table test_table (
   test_value   varchar(50)
)

I tried inserting a null value into the table using both of these methods.

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?

[trisberg]

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

[smiles78]

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.

[al0]

Try

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

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.

[trisberg]

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?

[kev374]

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)

[al0]

Just small remark - both methods used in an original post effectively results in the same call

       ps.setNull(paramIndex, Types.NULL);

as it is clear from StatementCreatorUtils.java code (setParameterValueInternal() method).

Regards,
Oleksandr

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)

[al0]

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:

                    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

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