2

Created a table with blob

CREATE TABLE SUGGEST_DATA (
LOCALE VARCHAR2(20) NOT NULL,
DATA_COLLECTION blob,
UPDATED_ON TIMESTAMP,
PRIMARY KEY (LOCALE)
)

using below code to insert a hashtable (key=string, value=ArrayList of String) to the above table.

Java code :-

conn = getDBConnection();
stmt = conn.prepareStatement("INSERT INTO SUGGEST_DATA (LOCALE,DATA_COLLECTION,UPDATED_ON) VALUES (?, ?, ?)");
stmt.setString(1, locale);
stmt.setObject(2, data);//this is a java hashtable
stmt.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
stmt.executeUpdate();

it fails on "stmt.setObject"

java.sql.SQLException: Invalid column type
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:11256)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:10605)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:11665)
    at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:11631)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:253)
Ashish Verma
  • 23
  • 5
  • 1
    What would you expect that do with the Hashtable? A byte array or a Blob, I would understand, but a Hashtable? – JB Nizet Oct 26 '18 at 19:07
  • I thought that we can store anything serializable in a blob, so hashtable should work as it is serialized. Am I missing something here? – Ashish Verma Oct 26 '18 at 19:18
  • 1
    You can, if you serialize the object by yourself first and pass it as a Blob to the prepared statement. JDBC won't do that for you (probably because it's a terrible idea, in general) – JB Nizet Oct 26 '18 at 19:23
  • yes, agree, it's not the best to keep a hashtable in DB like this but I have such a scenario where I need to do something like this. – Ashish Verma Oct 26 '18 at 19:33
  • At least choose a data format that can be read by something other than your specific Java program, like JSON or XML. – JB Nizet Oct 26 '18 at 19:40
  • Yes, I do have control over the approach, I can write to a file or just put this object to coherence cache with expiry set as a year or so, I will solve this problem but I am really curious that why it will not work this way. – Ashish Verma Oct 26 '18 at 19:53
  • Because JDBC doesn't serialize objects. You think it does that, but it simply doesn't. – JB Nizet Oct 26 '18 at 19:55
  • 1
    @JBNizet A JDBC driver may actually serialize, but if it does, it will probably only do that for columns of `Types.JAVA_OBJECT` (although after JDBC 2.1, the specification is silent on the exact behavior for this type). – Mark Rotteveel Oct 26 '18 at 20:20
  • Convert the hastable to a byte array (anyway you like, though serialization is the simplest I guess) and store it in the BLOB. Problem solved. – The Impaler Oct 26 '18 at 20:22
  • Well, Hashtable is serializable? how can I serialize it again? – Ashish Verma Oct 26 '18 at 20:22
  • Thanks for the info @Mark – JB Nizet Oct 26 '18 at 20:23
  • https://stackoverflow.com/questions/31781496/convert-hash-table-to-byte-array – Ashish Verma Oct 26 '18 at 20:25
  • Thanks, @TheImpaler for the idea. awesome :) – Ashish Verma Oct 26 '18 at 20:26

0 Answers0