I am using Spring jdbcTemplate.update(String sql, Object[] args)
to execute a prepared insert statement in an Oracle database. One of the objects is a Character object containing the value "Y", and the target column is of type CHAR (1), but I get
java.sql.SQLException: Invalid column type
an exception.
I debugged this back and forth, and there is no doubt that this particular object is causing the problem. The insert is performed as expected when this character object is omitted.
I can also output the sql and Object [] values, copy the sql to the sql developer, replace the placeholders (?'s)
value with the actual values โโof the objects, and the insert will work fine.
sql (confusing to protect the perpetrators):
INSERT INTO SCHEMA.TABLE(NUMBER_COLUMN,VARCHAR_COLUMN,DATE_COLUMN,CHAR_COLUMN) VALUES (?,?,?,?);
Object Values:
values[0] = [123] values[1] = [Some String] values[2] = [2012-04-19] values[3] = [Y]
The combination is started manually in the sql developer and works very well:
INSERT INTO SCHEMA.TABLE(NUMBER_COLUMN,VARCHAR_COLUMN,DATE_COLUMN,CHAR_COLUMN) VALUES (123,'Some String','19-Apr-2012','Y');
The prepared sql statement itself is generated dynamically based on non-zero instance variable objects contained in the data transfer object (we want the database to handle the generation of default values), so I can not accept any answers suggesting that I just rework the sql or insert procedure .
Has anyone ever come across this and can explain to me what is happening and how to fix it? Surprisingly, I cannot insert a Character object into the CHAR (1) field. Any help would be greatly appreciated.
Sincerely, the long-standing Lurker poster for the first time