I'm working on an SQL stored procedure that uses JSON_TABLE and HTTP_GET to call an API and return some data.  The API returns one result set and the JSON_TABLE/HTTP_GET works fine for that in the SQL stored procedure.  I'm trying to code it now for if the API in the HTTP_GET could return multiple result sets by using a cursor and/or a common table expression.  Can someone point me to an example for coding that or give me any suggestions on the best way to handle multiple result sets returned from HTTP_GET in an SQL stored procedure?

------------------------------
Amy Vozza
------------------------------

#SQL

HTTP_GET  is a Table Function and have to be specified within the FROM Clause in a SELECT Statement.
A SELECT Statement can only return a single table/result set.
So if you need to return multiple Result Sets, you have to code multiple Cursors, one for each HTTP_GET API you want to access.
How ever I'd suggest to ask yourself if you really need multiple result sets or if it would not possible to join the (decomposed) results in a single statement (may be with multiple CTEs) and then return this single statement.
On the other side I'd also check, also for the solution you already have, if it would not be better to create a UDTF instead of a stored procedure (returning a result set)

Birgitta

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964
------------------------------

Sorry, I misspoke on saying multiple result sets.  I meant multiple rows.  Can you use a cursor in an SQL stored procedure for the JSON from an HTTP_GET to process the rows in the data set returned from the API?  How would you code that?

------------------------------
Amy Vozza
------------------------------

Original Message:
Sent: Mon September 19, 2022 11:27 PM
From: Birgitta Hauser
Subject: HTTP_GET in SQL Stored Procedure - Multiple Result Sets

HTTP_GET  is a Table Function and have to be specified within the FROM Clause in a SELECT Statement.
A SELECT Statement can only return a single table/result set.
So if you need to return multiple Result Sets, you have to code multiple Cursors, one for each HTTP_GET API you want to access.
How ever I'd suggest to ask yourself if you really need multiple result sets or if it would not possible to join the (decomposed) results in a single statement (may be with multiple CTEs) and then return this single statement.
On the other side I'd also check, also for the solution you already have, if it would not be better to create a UDTF instead of a stored procedure (returning a result set)

Birgitta

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964
------------------------------

Dear Amy

If you only need one result set (no matter how many rows there are) and just read the result set without any need to update each row in the result set, then you have no need to use cursor. 

Are you in a situation that you need to retrieve the rows and do some calculation based on each individual row's data and then update each row (as opposed to using a calculation that uses row-independent value to update all rows in the result set) before finally use the result set? If so, then you need to use cursor and you can Google how to code for the use of SQL cursor with "db2 sql procedure cursor sample".

https://www.ibm.com/docs/en/i/7.4?topic=cursor-examples-using

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom
------------------------------

Original Message:
Sent: Tue September 20, 2022 12:33 AM
From: Amy Vozza
Subject: HTTP_GET in SQL Stored Procedure - Multiple Result Sets

Sorry, I misspoke on saying multiple result sets.  I meant multiple rows.  Can you use a cursor in an SQL stored procedure for the JSON from an HTTP_GET to process the rows in the data set returned from the API?  How would you code that?

------------------------------
Amy Vozza
------------------------------


Original Message:
Sent: Mon September 19, 2022 11:27 PM
From: Birgitta Hauser
Subject: HTTP_GET in SQL Stored Procedure - Multiple Result Sets

HTTP_GET  is a Table Function and have to be specified within the FROM Clause in a SELECT Statement.
A SELECT Statement can only return a single table/result set.
So if you need to return multiple Result Sets, you have to code multiple Cursors, one for each HTTP_GET API you want to access.
How ever I'd suggest to ask yourself if you really need multiple result sets or if it would not possible to join the (decomposed) results in a single statement (may be with multiple CTEs) and then return this single statement.
On the other side I'd also check, also for the solution you already have, if it would not be better to create a UDTF instead of a stored procedure (returning a result set)

Birgitta

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Thanks.  Yes, I did need to process each row returned.  I did get the cursor to work there this afternoon.

------------------------------
Amy Vozza
------------------------------

Original Message:
Sent: Tue September 20, 2022 07:58 PM
From: Satid Singkorapoom
Subject: HTTP_GET in SQL Stored Procedure - Multiple Result Sets

Dear Amy

If you only need one result set (no matter how many rows there are) and just read the result set without any need to update each row in the result set, then you have no need to use cursor. 

Are you in a situation that you need to retrieve the rows and do some calculation based on each individual row's data and then update each row (as opposed to using a calculation that uses row-independent value to update all rows in the result set) before finally use the result set? If so, then you need to use cursor and you can Google how to code for the use of SQL cursor with "db2 sql procedure cursor sample".

https://www.ibm.com/docs/en/i/7.4?topic=cursor-examples-using

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom
------------------------------


Original Message:
Sent: Tue September 20, 2022 12:33 AM
From: Amy Vozza
Subject: HTTP_GET in SQL Stored Procedure - Multiple Result Sets

Sorry, I misspoke on saying multiple result sets.  I meant multiple rows.  Can you use a cursor in an SQL stored procedure for the JSON from an HTTP_GET to process the rows in the data set returned from the API?  How would you code that?

------------------------------
Amy Vozza


Original Message:
Sent: Mon September 19, 2022 11:27 PM
From: Birgitta Hauser
Subject: HTTP_GET in SQL Stored Procedure - Multiple Result Sets

HTTP_GET  is a Table Function and have to be specified within the FROM Clause in a SELECT Statement.
A SELECT Statement can only return a single table/result set.
So if you need to return multiple Result Sets, you have to code multiple Cursors, one for each HTTP_GET API you want to access.
How ever I'd suggest to ask yourself if you really need multiple result sets or if it would not possible to join the (decomposed) results in a single statement (may be with multiple CTEs) and then return this single statement.
On the other side I'd also check, also for the solution you already have, if it would not be better to create a UDTF instead of a stored procedure (returning a result set)

Birgitta

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964