Re: Error inserting JSON string to Postgres DB

Felix Schumacher <[email protected]> Sat, 2 Dec 2023 14:48:40 +0100

Newsgroups	gmane.comp.jakarta.jmeter.user
Message-ID	<[email protected]>

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:
>
> Inline image
>
>
> Inline image
>
>
> Inline image
>
>
>
> 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]

json_with_postgres.jmx (text/xml, 6.9 KB)

<?xml version="1.0" encoding="UTF-8"?>
<jmeterTestPlan version="1.2" properties="5.0" jmeter="5.6.3-SNAPSHOT 91d8aea">
  <hashTree>
    <TestPlan guiclass="TestPlanGui" testclass="TestPlan" testname="Test Plan" enabled="true">
      <boolProp name="TestPlan.functional_mode">false</boolProp>
      <boolProp name="TestPlan.tearDown_on_shutdown">false</boolProp>
      <boolProp name="TestPlan.serialize_threadgroups">false</boolProp>
      <elementProp name="TestPlan.user_defined_variables" elementType="Arguments" guiclass="ArgumentsPanel" testclass="Arguments" testname="User Defined Variables" enabled="true">
        <collectionProp name="Arguments.arguments"/>
      </elementProp>
    </TestPlan>
    <hashTree>
      <ThreadGroup guiclass="ThreadGroupGui" testclass="ThreadGroup" testname="Thread Group" enabled="true">
        <stringProp name="ThreadGroup.on_sample_error">continue</stringProp>
        <elementProp name="ThreadGroup.main_controller" elementType="LoopController" guiclass="LoopControlPanel" testclass="LoopController" testname="Loop Controller" enabled="true">
          <stringProp name="LoopController.loops">1</stringProp>
          <boolProp name="LoopController.continue_forever">false</boolProp>
        </elementProp>
        <stringProp name="ThreadGroup.num_threads">1</stringProp>
        <stringProp name="ThreadGroup.ramp_time">1</stringProp>
        <boolProp name="ThreadGroup.delayedStart">false</boolProp>
        <boolProp name="ThreadGroup.scheduler">false</boolProp>
        <stringProp name="ThreadGroup.duration"></stringProp>
        <stringProp name="ThreadGroup.delay"></stringProp>
        <boolProp name="ThreadGroup.same_user_on_next_iteration">true</boolProp>
      </ThreadGroup>
      <hashTree>
        <ResultCollector guiclass="ViewResultsFullVisualizer" testclass="ResultCollector" testname="View Results Tree" enabled="true">
          <boolProp name="ResultCollector.error_logging">false</boolProp>
          <objProp>
            <name>saveConfig</name>
            <value class="SampleSaveConfiguration">
              <time>true</time>
              <latency>true</latency>
              <timestamp>true</timestamp>
              <success>true</success>
              <label>true</label>
              <code>true</code>
              <message>true</message>
              <threadName>true</threadName>
              <dataType>true</dataType>
              <encoding>false</encoding>
              <assertions>true</assertions>
              <subresults>true</subresults>
              <responseData>false</responseData>
              <samplerData>false</samplerData>
              <xml>false</xml>
              <fieldNames>true</fieldNames>
              <responseHeaders>false</responseHeaders>
              <requestHeaders>false</requestHeaders>
              <responseDataOnError>false</responseDataOnError>
              <saveAssertionResultsFailureMessage>true</saveAssertionResultsFailureMessage>
              <assertionsResultsToSave>0</assertionsResultsToSave>
              <bytes>true</bytes>
              <sentBytes>true</sentBytes>
              <url>true</url>
              <threadCounts>true</threadCounts>
              <idleTime>true</idleTime>
              <connectTime>true</connectTime>
            </value>
          </objProp>
          <stringProp name="filename"></stringProp>
        </ResultCollector>
        <hashTree/>
        <JDBCDataSource guiclass="TestBeanGUI" testclass="JDBCDataSource" testname="JDBC Connection Configuration" enabled="true">
          <boolProp name="autocommit">false</boolProp>
          <stringProp name="checkQuery"></stringProp>
          <stringProp name="connectionAge">5000</stringProp>
          <stringProp name="connectionProperties"></stringProp>
          <stringProp name="dataSource">db</stringProp>
          <stringProp name="dbUrl">jdbc:postgresql://localhost:5432/</stringProp>
          <stringProp name="driver">org.postgresql.Driver</stringProp>
          <stringProp name="initQuery"></stringProp>
          <boolProp name="keepAlive">true</boolProp>
          <stringProp name="password">secret</stringProp>
          <stringProp name="poolMax">0</stringProp>
          <boolProp name="preinit">false</boolProp>
          <stringProp name="timeout">10000</stringProp>
          <stringProp name="transactionIsolation">DEFAULT</stringProp>
          <stringProp name="trimInterval">60000</stringProp>
          <stringProp name="username">postgres</stringProp>
        </JDBCDataSource>
        <hashTree/>
        <JDBCSampler guiclass="TestBeanGUI" testclass="JDBCSampler" testname="Create table in DB" enabled="true">
          <stringProp name="dataSource">db</stringProp>
          <stringProp name="query">create table something (id int, data json);</stringProp>
          <stringProp name="queryArguments"></stringProp>
          <stringProp name="queryArgumentsTypes"></stringProp>
          <stringProp name="queryTimeout"></stringProp>
          <stringProp name="queryType">Update Statement</stringProp>
          <stringProp name="resultSetHandler">Store as String</stringProp>
          <stringProp name="resultSetMaxRows"></stringProp>
          <stringProp name="resultVariable"></stringProp>
          <stringProp name="variableNames"></stringProp>
        </JDBCSampler>
        <hashTree/>
        <JDBCSampler guiclass="TestBeanGUI" testclass="JDBCSampler" testname="Insert JSON into DB" enabled="true">
          <stringProp name="dataSource">db</stringProp>
          <stringProp name="queryType">Prepared Update Statement</stringProp>
          <stringProp name="query">insert into something values (?, ?)</stringProp>
          <stringProp name="queryArguments">1,&quot;{&quot;&quot;name&quot;&quot;: &quot;&quot;whatever&quot;&quot;}&quot;</stringProp>
          <stringProp name="queryArgumentsTypes">integer, varchar</stringProp>
          <stringProp name="variableNames"></stringProp>
          <stringProp name="resultVariable"></stringProp>
          <stringProp name="queryTimeout"></stringProp>
          <stringProp name="resultSetMaxRows"></stringProp>
          <stringProp name="resultSetHandler">Store as String</stringProp>
        </JDBCSampler>
        <hashTree/>
        <JDBCSampler guiclass="TestBeanGUI" testclass="JDBCSampler" testname="Read all from DB" enabled="true">
          <stringProp name="dataSource">db</stringProp>
          <stringProp name="queryType">Select Statement</stringProp>
          <stringProp name="query">select * from something</stringProp>
          <stringProp name="queryArguments"></stringProp>
          <stringProp name="queryArgumentsTypes"></stringProp>
          <stringProp name="variableNames"></stringProp>
          <stringProp name="resultVariable"></stringProp>
          <stringProp name="queryTimeout"></stringProp>
          <stringProp name="resultSetMaxRows"></stringProp>
          <stringProp name="resultSetHandler">Store as String</stringProp>
        </JDBCSampler>
        <hashTree/>
      </hashTree>
    </hashTree>
  </hashTree>
</jmeterTestPlan>

OpenPGP_0xEA6C3728EA91C4AF.asc (application/pgp-keys, 6.1 KB)

OpenPGP_signature.asc (application/pgp-signature, 840 B)