cancel
Showing results for 
Search instead for 
Did you mean: 

JDBC Request errors when semicolon is included in SQL

SOLVED
bojasna
Occasional Contributor

JDBC Request errors when semicolon is included in SQL

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;

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisAdams
Community Leader

Re: JDBC Request errors when semicolon is included in SQL

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.

 

View solution in original post

8 REPLIES 8
richie
Community Hero

Re: JDBC Request errors when semicolon is included in SQL

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
if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta
ChrisAdams
Community Leader

Re: JDBC Request errors when semicolon is included in SQL

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.

 

View solution in original post

richie
Community Hero

Re: JDBC Request errors when semicolon is included in SQL

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

Rich
if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta
bojasna
Occasional Contributor

Re: JDBC Request errors when semicolon is included in SQL

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
Occasional Contributor

Re: JDBC Request errors when semicolon is included in SQL

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
Community Leader

Re: JDBC Request errors when semicolon is included in SQL

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
Community Hero

Re: JDBC Request errors when semicolon is included in SQL

Hey @ChrisAdams,

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

Ta

Rich
if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta
ChrisAdams
Community Leader

Re: JDBC Request errors when semicolon is included in SQL

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!

New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors