Solved

Iterate through database result table

  • 21 July 2023
  • 2 replies
  • 45 views

Userlevel 4
Badge +2
  • Head of Community
  • 38 replies

With the help of the database standard modules I do a select on a database table.
I want to iterate through all rows returned by the select and do stuff.

The only thing I found is this: https://support-hub.tricentis.com/open?id=kb_article_view&sys_kb_id=e059c7fddb24df4cb3e1a103ca9619fa

From a performance perspective this solution doesn't seem good. The SQL statement gets executed over and over again for each iteration. When it's a time intensive SQL statement you shouldn't do that. Actually you shouldn't do this even when it's not :)

Any idea on how to execute the SQL statement just once and iterate through all lines?

I saw in the documentation that you can export the result table content to a csv file. But I didn't find how to then read the csv file line by line and do stuff...

icon

Best answer by Community Administrator 21 July 2023, 11:42

View original

2 replies

Userlevel 4
Badge +2

For some users the following solution worked:

I created my own "Special Execution Task" with C#:

590ed23687a0d5508bdc326e3fbb3512.iix

 

The following call of this module will create buffers with the result values in the form of:

sql_Count: Number of result lines, so that I can iterate through all of them

sql_Column1_[Row Number]

sql_Column2_[Row Number]

Where [Row Number] is a number from 1 to the maximum number of result lines 

682ed63687a0d5508bdc326e3fbb359e.iix

 

Solves all of my issues: The SQL statement is execute just once. I automatically have all the values in buffers so that I can use them later...

Userlevel 4
Badge +2

Another possible solution would be to first download the result in a csv and then load it into caching database, then verify the data as per your needs would be faster. Of course the SET is a smarter solution. 

Reply