Weird "Invalid column type" error

[Noam Y. Tenne]

Hey guys
I was wandering if you could help me with this one.
I use jdk 1.4 and Oracle 9i and currently building a simple application
Which saves data to a DataBase. All was going well until one day I
Suddenly got this exception while trying to save information which was
Working fine just the other day:
SQLException : java.sql.SQLException: Invalid column type
I have searched my code and DB for days trying to find my mistake, and
Couldn't find it, even my most experienced colleagues tried and failed.
The types and nulls allowed are all correct.
Since then this problem popped up in another few places which the save
Was working fine just a few days ago. I have searched and googled high
And low and couldn't find an answer, could anyone help me?

[Jamie Wool]

Hey,
Is there any chance you could post some of the offending code and possibly details of the db. As the error says it suggest that it is receiving something unexpected/illegal in the data being passed to the db.

I've always found displaying the created sql command useful, sometimes helps debugging the sql statements.

[Noam Y. Tenne]

Yes, sorry for the lack of info, i was just in a hurry

here ais the code were i insert the data:
UPDATE pdrecord#Army SET needsMedicalInquiry = ?, needsCommity = ?,
assignedProfessionComment = ? WHERE personId=636

as you can see I insert to the prepared statement the variables needsMedicalInquiry a boolean value and then this exception pops up:

java.sql.SQLException: Invalid column type
at oracle.jdbc.dbaccess.DBError,throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError,throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError,throwSqlException(DBError.java:269)
at oracle.jdbc.driver.oraclePerparedStatement.setObject(OraclePerparedStatement.java:3088)
at oracle.jdbc.driver.oraclePerparedStatement.setObject(OraclePerparedStatement.java:3107)
at erp.services.pdrecord.server.PDRecordCollection.doUDATEQuery(PDRecordCollection.java:285)

of course there are more lines of the error message but it is not relevant

[Jamie Wool]

I had a similar problem with copying data using the prepare statement, which turned out to be the way that the prepare statement adds single quotation marks to code. Also note the it uses '1' instead of true. See my code example below.

I haven't got Oracle installed at the moment so couldn't test exactly what oracle expects, so you will need to have a play. I would personally create a working statement manually in oracle, then experiment until the prepare staement to generate the exact same code.


The code

    
int rows[]; 
boolean tester = true;
PreparedStatement sm = con.prepareStatement("UPDATE weights SET p? = p");
sm.setBoolean(1, tester);
rows = sm.executeBatch();
// This just prints my code to screen
System.out.println(sm); 

UPDATE weights SET p'1' = p

I hope this helps a bit?

[Noam Y. Tenne]

I'll try it out, thanks