From: | Curt Huffman <curt(dot)huffman(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Inserting JSON via Java PreparedStatment |
Date: | 2016-03-07 13:25:44 |
Message-ID: | CACaG-nFB=dgHJ6=1zgAMBb00=LKXCbr-FFfiHUeCWkTn1HBbnw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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:
JsonObject mbrLogRec = 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);
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jan de Visser | 2016-03-07 13:33:13 | Re: Does a call to a language handler provide a context/session, and somewhere to keep session data? |
Previous Message | Victor Yegorov | 2016-03-07 13:23:42 | Re: index problems (again) |