invalid character value for cast exception

2010-06-18
2014-01-19
  • Bill Burdick

    Bill Burdick - 2010-06-18

    Greetings:

    I have an hsql database setup as a socket server.  I have built a table and tied a CSV file to it:

    hsqlDBCon.createStatement().execute("SET TABLE foo SOURCE \"" + config.getFilePath() + ";all_quoted=true;encoding=UTF-8\"");
    

    In another process, I try to read from this table and insert a value from the above table into a different table.   When I try to do the insert, I get this error:

    java.sql.SQLDataException: data exception: invalid character value for cast
            at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
            at org.hsqldb.jdbc.Util.throwError(Unknown Source)
            at org.hsqldb.jdbc.JDBCPreparedStatement.setParameter(Unknown Source)
            at org.hsqldb.jdbc.JDBCPreparedStatement.setString(Unknown Source)
            at odbproxy.MonitorChecker.run(MonitorChecker.java:105)
            at java.lang.Thread.run(Thread.java:619)
    

    What am I missing? 

     
  • Fred Toussi

    Fred Toussi - 2010-06-18

    First try connecting to the server with DatabaseManager and select the contents of the table. This will confirm the data is accessible.

    For the error, add code to your exception handler to show which value is assigned to which paramter. This will often give you the answer if an invalid string is assigned to a numeric or datetime column.

     
  • Bill Burdick

    Bill Burdick - 2010-06-18

    Fred,

      Thanks for the reply.  I verified I could access the data with the HSQL Database Manager.  It turns out the value it is choking on is an empty string.

     
  • Bill Burdick

    Bill Burdick - 2010-06-18

    To be more specific, the code it is throwing this exception on is:

    insertPS.setString(2, rs.getString("acctid"));
    

    The result from rs.getString("acctid") is an empty string.

     
  • Bill Burdick

    Bill Burdick - 2010-06-18

    Okay,

      So it seems that I can't set string with an empty string to a string field.  I must be doing something very wrong.  I wrote a method that just inserts a simple empty string in that field, and I get the same error.  I have verified the table create statement, and this particular field is set to a Varchar(20).

     
  • Fred Toussi

    Fred Toussi - 2010-06-18

    Well, this is strange and there is no good reason it should happen. The error message does not indicate a CHECK or FORIEGN KEY constraint is involved.

    I suggest you check further by getting the ParameterMetaData for the PreparedStatement parameters and verifying the types.

     
  • Bill Burdick

    Bill Burdick - 2010-06-18

    Fred,

      I found it!  Thank you for your valuable assistance!  Turns out that back when the database was created, it was created with a column type of INT (I was poking around in the files - I think it was the .script file).  I checked in the HSQL Database Manager, and sure enough, it was an INT.  I removed the tables, and my program re-created them correctly.  I'll be sure to remember this one.

      Thanks again.

     

Log in to post a comment.