Error Inserting Java Symbol Object Value in Oracle CHAR (1) Column

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

+4
source share
1 answer

There is no PreparedStatement.setXxx () that takes a character value, and Oracle docs indicate that all JDBC character types are mapped to Java Strings. Also, see http://docs.oracle.com/javase/1.3/docs/guide/jdbc/getstart/mapping.html#1039196 , which does not include mapping from Java char or Character to JDBC type.

You will need to convert the value to a string.

+2
source

Source: https://habr.com/ru/post/1414272/


All Articles