Forum Discussion

bojasna's avatar
bojasna
Occasional Contributor
4 years ago
Solved

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-semicolon-is-included-in-SQL/td-p/14870

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;

Scripting
  • ChrisAdams's avatar
    ChrisAdams
    4 years ago

    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.

     

8 Replies

Sort By
  • ChrisAdams's avatar
    ChrisAdams
    Champion Level 3
    4 years ago

    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.

     

    • bojasna's avatar
      bojasna
      Occasional Contributor
      4 years ago

      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

  • richie's avatar
    richie
    Community Hero
    4 years ago
    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
    • bojasna's avatar
      bojasna
      Occasional Contributor
      4 years ago

      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's avatar
        ChrisAdams
        Champion Level 3
        4 years ago

        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.