JDBC Request errors when semicolon is included in SQL

[bojasna]

When you end your sql statement with a semicolon in JDBC Requests, soapUI fails to run the request and reports the following error:

"Error getting response; java.sql.SQLSyntaxErrorException: ORA-00911: invalid character"

I'd like to run several sql statements in one jdb step and this is making me create a lot of jdbc steps.

I see it has already been reported but archived: https://community.smartbear.com/t5/API-Functional-Security-Testing/JDBC-Request-errors-when-semicolo...

Do you know of a workaround?

Strangely enough I found that I get the error if I send a call like this:

GRANT SELECT ON TABLENAME TO USER;

But it works with the following statement:

CREATE OR REPLACE FUNCTION USER.NUMBER ( input VARCHAR2 ) RETURN VARCHAR2 IS
begin
if input is null then return null;

[richie]

Hey @bojasna,

The mandatory use of semi colons in queries seems to have changed in the last 10years or so, before then whether it was oracle, sqlserver, db2 semi colons to end a query was required.

Have you got the latest version of soapui? The defect seems to have been around for a long time. Its mot present in readyapi.

I can understand why the open source developers havent really focused on this issue as im guessing theyre assuming most people wouldnt include multiple queries within an individual jdbc test step cos your assertions would only be effective if they worked on all the queries in the step.

Only thing i can suggest is picking up thr latest version. If thr defect is still present then i think youre gonna have to go with adding multiple jdbc test steps into your testcases.....1 for each query.

Ta

Rich

[ChrisAdams · Accepted solution]

Hi,

In terms of a workaround, you could look at a Groovy script step.  At least then you could script call the db several times and interact with the results in between each.

The downside to the scripting is having to manage creating, opening and closing the connection by using Groovy script, but that isn't too bad to do.

[richie]

Ooh! @ChrisAdams, im a dunce sometimes, thats a good point!

Rich

[bojasna]

Hi @ChrisAdams ,

Thanks for the answer. Do you by any chance have some link to an explanation or maybe examples of how these scripts would look like, since I'm not sure what to search for?

Thanks,

Bojana

[bojasna]

Hey @richie 

Thanks for the answer. I'll check out the paid version to see if it has what I need. It's a bit of a shame it's not fixed since I saw other people asking the same thing, but what can you do 🙂

[ChrisAdams]

Hi,

I don't believe the paid version gives you this either.  From my own experience, I know that you cannot have semi-colon's in the command.

It actually makes sense to restrict each JDBC step to a single SQL statement, otherwise it would be hard to tease out the response for assertions.

In terms of connecting to a db from Groovy, here is a good start... Link.  Here is another example from the coding challenge last year... TechCorner-Challenge-13-Call-Database-Connection-Using-Groovy  

Once you have a connection in a Groovy script step, then you can run whatever query you like....

// Note conn is the db connection

def theSmiths = conn.rows("SELECT surname FROM user WHERE surname = 'Smith'")

log.info(theSmiths);

I suppose the choice you have is based on the number of queries to execute.  Lots of queries, then Groovy.  Few queries, stick to JDBC steps.

[richie]

Hey @ChrisAdams,

What version you using? I use semi colons in all my JDBC steps all the time!?!?!?!

Ta

Rich

[ChrisAdams]

Hi @richie , I'm using ReadyAPI 2.7.0.  I've had problems in the past with a semi-colon in a JDBC step, but now you've got me doubting myself!