Ora2Pg Migration – Data Type Mismatch Error

The code like this in your program can throw “Data Type Mismatch Error” at run time.

String query = " SELECT * from table where column=?";
Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/pgdatabase", "username", "password");
PreparedStatement preparedStatement = null;
try {
    preparedStatement =  connection.prepareStatement(
                                 query);            
    preparedStatement.setObject(1, '1');
}.....

Following is the actual runtime error will come in the application,

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: smallint <> character varying

 Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

This setObject(…) throws the aforesaid exception when the column type in database is “NUMBER(11)” and the setObject using it like “character”

Following are the solution to this problem,

  1. Fix the code and use correct data type
  2. Use :: or cast operator.

I would recommend avoiding option – 2 because typecasting may give performance issue as it might let the DB not to use the index if casting is not correct.

Leave a Reply

Your email address will not be published.

WP2Social Auto Publish Powered By : XYZScripts.com