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 imageOn 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]
<?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,"{""name"": ""whatever""}"</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
Description: OpenPGP public key
OpenPGP_signature.asc
Description: OpenPGP digital signature
