[Bug 64651] New: Issue with JDBC Post Processor while saving the parameter value which contains special characters which typically exists in JSON response

[bu...@apache.org]

https://bz.apache.org/bugzilla/show_bug.cgi?id=64651

            Bug ID: 64651
           Summary: Issue with JDBC Post Processor while saving the
                    parameter value which contains special characters
                    which typically exists in JSON response
           Product: JMeter
           Version: 5.3
          Hardware: PC
            Status: NEW
          Severity: major
          Priority: P2
         Component: HTTP
          Assignee: issues@jmeter.apache.org
          Reporter: atulsharma75@yahoo.com
  Target Milestone: JMETER_5.3.1

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.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 64651] Issue with JDBC Post Processor while saving the parameter value which contains special characters which typically exists in JSON response

[bu...@apache.org]

https://bz.apache.org/bugzilla/show_bug.cgi?id=64651

--- Comment #7 from Felix Schumacher <fe...@internetallee.de> ---
Created attachment 37386
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=37386&action=edit
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.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 64651] Issue with JDBC Post Processor while saving the parameter value which contains special characters which typically exists in JSON response

[bu...@apache.org]

https://bz.apache.org/bugzilla/show_bug.cgi?id=64651

Felix Schumacher <fe...@internetallee.de> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEEDINFO                    |NEW

--- Comment #9 from Felix Schumacher <fe...@internetallee.de> ---
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.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 64651] Issue with JDBC Post Processor while saving the parameter value which contains special characters which typically exists in JSON response

[bu...@apache.org]

https://bz.apache.org/bugzilla/show_bug.cgi?id=64651

--- Comment #5 from Andy <at...@yahoo.com> ---
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.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 64651] Issue with JDBC Post Processor while saving the parameter value which contains special characters which typically exists in JSON response

[bu...@apache.org]

https://bz.apache.org/bugzilla/show_bug.cgi?id=64651

Felix Schumacher <fe...@internetallee.de> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 OS|                            |All
             Status|NEW                         |NEEDINFO
           Severity|major                       |normal

--- Comment #1 from Felix Schumacher <fe...@internetallee.de> ---
I think this would have been better asked on the users mailing list.

Have you tried to surround your value with quotes?

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 64651] Issue with JDBC Post Processor while saving the parameter value which contains special characters which typically exists in JSON response

[bu...@apache.org]

https://bz.apache.org/bugzilla/show_bug.cgi?id=64651

--- Comment #3 from Felix Schumacher <fe...@internetallee.de> ---
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.)

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 64651] Issue with JDBC Post Processor while saving the parameter value which contains special characters which typically exists in JSON response

[bu...@apache.org]

https://bz.apache.org/bugzilla/show_bug.cgi?id=64651

--- Comment #8 from Andy <at...@yahoo.com> ---
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.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 64651] Issue with JDBC Post Processor while saving the parameter value which contains special characters which typically exists in JSON response

[bu...@apache.org]

https://bz.apache.org/bugzilla/show_bug.cgi?id=64651

--- Comment #2 from Andy <at...@yahoo.com> ---
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

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 64651] Issue with JDBC Post Processor while saving the parameter value which contains special characters which typically exists in JSON response

[bu...@apache.org]

https://bz.apache.org/bugzilla/show_bug.cgi?id=64651

--- Comment #6 from Andy <at...@yahoo.com> ---
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

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 64651] Issue with JDBC Post Processor while saving the parameter value which contains special characters which typically exists in JSON response

[bu...@apache.org]

https://bz.apache.org/bugzilla/show_bug.cgi?id=64651

Philippe Mouawad <p....@ubik-ingenierie.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |p.mouawad@ubik-ingenierie.c
                   |                            |om
   Target Milestone|JMETER_5.3.1                |---

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 64651] Issue with JDBC Post Processor while saving the parameter value which contains special characters which typically exists in JSON response

[bu...@apache.org]

https://bz.apache.org/bugzilla/show_bug.cgi?id=64651

Felix Schumacher <fe...@internetallee.de> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           Severity|normal                      |enhancement
           Hardware|PC                          |All

--- Comment #4 from Felix Schumacher <fe...@internetallee.de> ---
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.

-- 
You are receiving this mail because:
You are the assignee for the bug.