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)
What am I missing?
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.
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.
To be more specific, the code it is throwing this exception on is:
The result from rs.getString("acctid") is an empty string.
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).
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.
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.
Log in to post a comment.