Got it working. Thanks again for your help Felix!
3 things I had to do:
- Use TWO double quotes for every double quote, so {"name": "whatever"}
becomes {""name"": ""whatever""}
- Put the final JSON string in double quotes when making the insertion
request. Single quotes don't work!
- In the JDBC request for insert, specify the data type as varchar instead
of text even though text is the data type for the column I am inserting to.
Otherwise, the inert will throw "invalid data type: text" error.
So as in your example, the following should be supplied for the insert:
"{""name"": ""whatever""}"
Jun
On Saturday, December 2, 2023 at 08:49:39 AM EST, Felix Schumacher
<[email protected]> wrote:
Hi Jun,
I have attached a working minimal example, that uses a JSON string and inserts
that with a prepared statement.
It assumes a locally running postgres which can be changed of course.
The interesting part is:
* You have to escape comma and quotes in the JSON string.
Given a JSON object like {"name": "whatever"} you will have to add quotes to
mask the comma and quotes to mask the quotes.
Your string will have to look like "{""name"": ""whatever""}"
* You have to cast the parameter to json in your insert statement.
Add a ::json to the placeholder, as in "insert into something values
(?::json)"
Regards
Felix
Am 02.12.23 um 14:19 schrieb Jun Zhuang:
Hi Filex,
Thanks for the response. I tried with the single quotes and singles quotes +
::json, still got the same error.
Just to be sure, are you using a Postgres DB? Also one thing I forgot to
mentioned in my original email was the field I am trying to insert into is of
data type text instead of varchar, I wonder if that makes any difference?
Following are some screenshots of what I am trying to do:
On Saturday, December 2, 2023 at 05:58:52 AM EST, Felix Schumacher
<[email protected]> wrote:
Hi Jun,
I am not sure, what you tried already. When I place the following text into a
JDBC Sampler's query field, I get no errors:
create table something (id int, data json);
insert into something values (1, '{"name": "whatever"}'::json);
Same result, when I remove the casting and use
create table something (id int, data json);
insert into something values (1, '{"name": "whatever"}');
Can you show us a minimal example that fails for you?
Regards
Felix
Am 01.12.23 um 23:24 schrieb Jun Zhuang:
> Hi,
> I am getting Cannot have quote-char in plain field:['{"] error when trying
> to insert a JSON string into a Postgres DB table using JDBC request.
> I tried the following but nothing worked:
> - place the JSON string in single quotes,
> - escaping the double quotes with \",
> - placing the string in $$<JSON string here>$$ but nothing worked.
> There is no issue with the connection and other select/insert queries and I
> was able to insert from pgAdmin with the same string in single quotes though.
> The JSON string looks like this: {"A":"A", "B":[{"C":"C"}]}
> I am really out of ideas at this time. Can someone help?
> Thanks,Jun
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
