Re: Inserting JSON via Java PreparedStatment

On 03/07/2016 05:25 AM, Curt Huffman wrote:
> Hello!
>
> I’m struggling to insert a JSON object into my postgres v9.4 DB. I have
> defined the column called "evtjson" as type json. (not jsonb)
> I am trying to use a prepared statement in Java (jdk1.8) to insert a
> Json object (built using JEE javax.json libraries) into the column, but
> I keep running into SQLException errors.
>
> I'm using JDBC 9.4.1208
>
> I create the JSON object using:
>
> |JsonObjectmbrLogRec =Json.createObjectBuilder().build();…mbrLogRec
> =Json.createObjectBuilder().add("New MbrID",newId).build();|
>
> Then I pass this object as a parameter to another method to write it to
> the DB using a prepared statement. (along with several other fields) As:
>
> |pStmt.setObject(11,dtlRec);|

You lost me here, I thought the object you are building is mbrLogRec?

>
> Using this method, I receive the following error:
>
> at org.postgresql.util.PSQLException: No hstore extension installed.
>
> at
> org.postgresql.jdbc.PgPreparedStatement.setMap(PgPreparedStatement.java:553)
>
>
> at
> org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:1036)
>
> I did try installing the hstore extension, but it then told me that it
> was not an hstore object.
>
> I have also tried:
>
> |pStmt.setString(11,dtlRec.toString());pStmt.setObject(11,dtlRec.toString());|
>
> Which produce a different error:
>
> Event JSON: {"New MbrID":29}
>
> SQLException: ERROR: column "evtjson" is of type json but expression
> is of type character varying
>
> Hint: You will need to rewrite or cast the expression.
>
> But, at least this tells me that the DB is recognizing the column as
> type JSON.
>
> OracleDocs shows a number of various methods to set the parameter value
> in the preparedStatement, but I'd rather not try them all if someone
> knows the answer.
> (http://docs.oracle.com/javase/8/docs/api/java/sql/PreparedStatement.html)
> These also reference an additional parameter, SQLType, but I can't find
> any refernce to these.
> Should I try setAsciiStream? CharacterStream? CLOB? ???
>
> I couldn't find any help or tutes on postgres or the web.
>
> Thanks for any help.
>
> -Curt
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com