Forum Discussion

Stalsy's avatar
Stalsy
Contributor
2 years ago

How can I interrupt a JDBC query

I am using JDBC as datasource queries and usually return a limited number of rows (say customer IDs) for testing.  In a couple of cases, after editing a query and re-running the test, I found that I accidentally commented out the limiting clause (say in DB2:  Order by rand() fetch first 400 rows only)  of my first CTE so the query is selecting every single  customerId in the database and it takes forever to return any results!  Is there a way in ReadyAPI (I'm using Ver 3.45.0) to somehow close the connection and interrupt the query, or stop/cancel the test?  I've been closing ReadyAPI but this seems to be a bit extreme...

Data-Driven Testing
Editors
  • nmrao's avatar
    nmrao
    Champion Level 3
    2 years ago

    In that case, it is possible to have different jdbc tests steps with different filters, so that run appropriate step instead of commenting part of sql.

     

    Another possibility is to reduce the timeout.

    • Stalsy's avatar
      Stalsy
      Contributor
      2 years ago

      Thanks for taking the time to reply Rao.  Greatly Appreciated.

      Different steps for different filters is out of the question, because then I'll need to maintain more scripts.  Some of my scripts are very complicated with multiple CTEs, accessing data from 30-40 tables, with lots of case statements (even in where clauses) and window functions like ROW_NUMBER() OVER (PARTITION BY..), listagg() within group etc. The last thing I need is to have multiple versions of them 🙂

      The commenting/uncommenting of course happens only during development or modifications. Otherwise I'm running tests for 1000s or records so the timeout reduction will not work. 

      So, the short answer is: No, there is no practical way offered by the ReadyAPI query editor to interrupt a query (other tools like SSMS and SQL Workbench/J have a menu button and/or menu option to cancel the execution)

  • nmrao's avatar
    nmrao
    Champion Level 3
    2 years ago

    I was referring to "accidentally commented the filtering part" in the question.

    • Stalsy's avatar
      Stalsy
      Contributor
      2 years ago

      The "accident" happens more often than what I'd like to. As part of my WHERE clause I have a general: 

      ORDER BY RAND() FETCH FIRST n ROWS ONLY -- Vary n to the number of customer IDs you want to test

      or ORDER BY customerId LIMIT x OFFSET y

      I also have above: 

      AND custimerId IN ('xxxxxx','yyyyyy','zzzzzz')

      and I comment out one or the other to allow me to test multiple random, multiple sequential chunks or individual  customerIds.  

      So, if I'm testing an individual case so 

      ORDER BY RAND() FETCH FIRST n ROWS ONLY

      is commented out, and then  I want to test random customerIds, I comment the 

      AND custimerId IN ('xxxxxx','yyyyyy','zzzzzz')

      and before I know it, I hit execute so I'm selecting ALL customerIds since both clauses are now commented out! 😞