Bug 64651 - Issue with JDBC Post Processor while saving the parameter value which contains special characters which typically exists in JSON response
Summary: Issue with JDBC Post Processor while saving the parameter value which contain...
Status: NEW
Alias: None
Product: JMeter
Classification: Unclassified
Component: HTTP (show other bugs)
Version: 5.3
Hardware: All All
: P2 enhancement (vote)
Target Milestone: ---
Assignee: JMeter issues mailing list
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-08-06 16:12 UTC by Andy
Modified: 2020-08-09 08:56 UTC (History)
1 user (show)



Attachments
inserting json value in a db (7.27 KB, application/xml)
2020-08-08 14:06 UTC, Felix Schumacher
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Andy 2020-08-06 16:12:47 UTC
Dear Sir,

I have the JSON from the prev response and trying to save it in the sql server database after cleaning it a little bit but JDBC Post Processor is internally seems to be splitting the response varchar string into many chunks as it may contains commas, dots, angled brackets, double slashes etc. I know it because for every JSON JDBC post processor is complaining that number of supplied arguments are more than specified in the input to JDBC db saving request. For every save request, the number of arguments are varying in the logs as complained by the jdbc processor. However, if I take the same string and go to sql server developer studio , I am able to save the string perfectly fine. So sql server accepts it but problem is in the jdbc post processor how it translates it to the jdbc driver, it seems that it is chopping the string into multiple requests. 

so right now just to make it work, i had to use replace all on the json string to replace all characters to the simple string using below logic..

String responseJSON2BSaved = prev.getResponseDataAsString().replaceAll("[^a-zA-Z0-9=-></:.-]", "");
responseJSON2BSaved = responseJSON2BSaved.replaceAll(" ","");
responseJSON2BSaved = responseJSON2BSaved.replaceAll(":","");

//log.info("responseJSON2BSaved: " +responseJSON2BSaved);


vars.put("responseJSON2BSaved", responseJSON2BSaved);

This seems to be a bug in the jdbc processor. please advice, I would love to make it work where I can save the JSON into the database as whole it is without removing all its special characters.
Comment 1 Felix Schumacher 2020-08-06 16:50:27 UTC
I think this would have been better asked on the users mailing list.

Have you tried to surround your value with quotes?
Comment 2 Andy 2020-08-06 18:55:04 UTC
Hi Felix,

Yes tried appending single quote before and after the beanshell prep processor variable which contains the json response. That didn't help. Keeping everything same, when i removed all special characters,  it saved it.  How do i send it to the users mailing list,  please let me know.  Thanks you Andy
Comment 3 Felix Schumacher 2020-08-06 19:28:20 UTC
The instructions to join the mailing list (users would have been probably the right one) can be found at https://jmeter.apache.org/mail2.html

With quote I meant a double quote like "${jsonVar}". (Like encoding things in a comma separated values (CSV) list.)
Comment 4 Felix Schumacher 2020-08-08 13:05:06 UTC
With JSON data a simple double quote will not be enough, as there are probably double quotes in the data. Say, we have a JSON value of

{"something": "stupid", "answer": 42}

stored in the JMeter variable ${jsonValue}

Using this as masked by a double quote as proposed earlier would lead to an error, as the CSV parser used internally would expect a comma just before 'something": ...'.

A workaround is to use the quoteDelimiter function from CSVSaveService like

${__groovy(org.apache.jmeter.save.CSVSaveService.quoteDelimiters(vars.get("jsonValue")\,'\,"'.chars))}

I marked this issue as an enhancement, as it would change the current logic and would probably need a switch in the GUI to indicate whether the input data should be seen as comma separated, or as one big data element.
Comment 5 Andy 2020-08-08 13:40:07 UTC
Okay thank you Felix, That sounds good. Yesterday I was trying to add comments but bugzilla was down. HI will try groovy function you sent me and see if it works...



In beanshell , i am grabbing the JSON response like this.

vars.put("responseJSON2BSaved", prev.getResponseDataAsString().toString());

then, I am sending this parameter to the JDBC post processor like this 

${__V(responseJSON2BSaved)}


it complaints that input input arguments does not match with parameters. for each request the difference of argument is a different number while its processing each response. Indicating its splitting the JSON response which its thinking that there are many parameters. 2020-08-07 11:47:07,592 WARN o.a.j.p.j.p.AbstractJDBCProcessor: IO Problem in java.io.IOException: Cannot have quote-char in plain field:[{"]: {}JDBC PostProcessor
2

as per your suggestion : "${jsonVar}". 
i tried like this "${__V(responseJSON2BSaved)}" at the jdbc parameter value, keep on getting same error. 

2020-08-07 13:23:28,489 WARN o.a.j.p.j.p.AbstractJDBCProcessor: IO Problem in java.io.IOException: Cannot have quote-char in plain field:['{"]: {}JDBC PostProcessor
2020-08-07 13:23:28,960 INFO o.a.j.u.BeanShellTestElement: assertionValue: 0.97
2020-08-07 13:23:28,960 INFO o.a.j.u.BeanShellTestElement: assertionStatus: Pass
2020-08-07 13:23:28,961 WARN o.a.j.p.j.p.AbstractJDBCProcessor: IO Problem in java.io.IOException: Cannot have quote-char in plain field:['{"]: {}JDBC PostProcessor
2020-08-07 13:23:29,293 INFO o.a.j.u.BeanShellTestElement: assertionValue: Chairs
2020-08-07 13:23:29,293 INFO o.a.j.u.BeanShellTestElement: assertionStatus: Pass
2020-08-07 13:23:29,294 WARN o.a.j.p.j.p.AbstractJDBCProcessor: IO Problem in java.io.IOException: Cannot have quote-char in plain field:['{"]: {}JDBC PostProcessor
2

problem seems to be in the happening before it can hit the jdbc layer to save it.
Comment 6 Andy 2020-08-08 13:42:28 UTC
but when I use replace all function to get rid of all the special characters and pretty much convert it to the large text field , then it works and jdbc saves it.

String responseJSON2BSaved = prev.getResponseDataAsString().replaceAll("[^a-zA-Z0-9=-></:.-]", "");

but problem is I lose the jason format lol
Comment 7 Felix Schumacher 2020-08-08 14:06:16 UTC
Created attachment 37386 [details]
inserting json value in a db

It might be easier to start off with a simple example (and better to use Groovy than Beanshell :))

The attached test plan simulates a request, that returns our JSON value. The response is then extracted with a Groovy script into a JMeter variable (and quoted/escaped on the fly). The JDBC Post Processor can use the quoted JMeter variable without problems (have a look at the documentation for the JDBC Request about the used quoting).

To check, that we indeed inserted our JSON value in the db, we list all rows of our db at the end of the test.

To run this test copy the driver jar for h2 into lib/ext or change the plan to use your database.
Comment 8 Andy 2020-08-09 03:22:15 UTC
Hi Felix,

I tried the delimiters method of the csv api. It worked and was able to save in the database, I saw the command from your example. I have a lots of code that works well in beanshell processor but JSR gives errors and doesn't reference the variables correctly. Somehow beanshell works fine. so I have to use bean shell. 

I will give you an example, if you are storing thread number in a local variable under a thread and if you are trying to access it under BZ parallel processor whiich is one level below the thread, beanshell always returns correct value but JSR processor seems to over ride it and returns you BZ parallel processor' thread name. JSR seems to over write and seems to ref to one level up thread rather where the logic is written which is two levels up thread. 

Another issue with JSR, i have lots of vars (contains assertion values) that I setup before feeding to parallel thread, when i access then in a loop in JSR ,it always returns same assertion value and same counter but if I use beanshell, it works fine. 

thats why I am sticking to beanshell. 

but the issue of JSON not able to save is working for me, now you can close this bug. Thank you for your help.
Andy.
Comment 9 Felix Schumacher 2020-08-09 08:56:19 UTC
For the usage of Beanshell or JSR223, the mailing list is surely the better place. I will not discuss it here.

Read https://jmeter.apache.org/mail2.html, subscribe to the users mailing list and send your questions, experience or even answers to other users questions to the list.

I will change the state of this issue to new, meaning, that we could think about ways to enable your scenario with less setup.