I have used the basic code from @Scott Forstie's gist to produce beautiful spreadsheets.  I need to confirm if any of you have tried to use this technique for a file in QTEMP.  Mine fail with file not found. Yes, I would prefer not using QTEMP, but much of the code I could retrofit uses QTEMP or the more up-to-date DECLARE GLOBAL TEMPORARY TABLE version. When I looked at a recent program, there was too much going in the SQLRPGLE to simply replace it with a single SQL.  I can live with this for some processes, but those where multiple users could be running the process at the same time benefit from QTEMP. 

https://gist.github.com/forstie/cec5b0bf5c0e6983125ae078b0d04b48

------------------------------
David Taylor
Sr Application Developer
Range Resources
Fort Worth TX
------------------------------

I have used the basic code from @Scott Forstie's gist to produce beautiful spreadsheets.  I need to confirm if any of you have tried to use this technique for a file in QTEMP.  Mine fail with file not found. Yes, I would prefer not using QTEMP, but much of the code I could retrofit uses QTEMP or the more up-to-date DECLARE GLOBAL TEMPORARY TABLE version. When I looked at a recent program, there was too much going in the SQLRPGLE to simply replace it with a single SQL.  I can live with this for some processes, but those where multiple users could be running the process at the same time benefit from QTEMP. 

https://gist.github.com/forstie/cec5b0bf5c0e6983125ae078b0d04b48

I have used the basic code from @Scott Forstie's gist to produce beautiful spreadsheets.  I need to confirm if any of you have tried to use this technique for a file in QTEMP.  Mine fail with file not found. Yes, I would prefer not using QTEMP, but much of the code I could retrofit uses QTEMP or the more up-to-date DECLARE GLOBAL TEMPORARY TABLE version. When I looked at a recent program, there was too much going in the SQLRPGLE to simply replace it with a single SQL.  I can live with this for some processes, but those where multiple users could be running the process at the same time benefit from QTEMP. 

https://gist.github.com/forstie/cec5b0bf5c0e6983125ae078b0d04b48

Yes, I needed to have supplied more detail.  I am running from the STRSQL command line from a terminal session with the correct library list, including QTEMP. When I run the stored procedure with the query containing a standard library, the process completes and loads 17 rows to the spreadsheet. When I call the SP with the query for the QTEMP library, I get the error below. I can plan around this, but the model for the SP I am using stores the query in file.  I guess I could plan to load a unique record into the query reference file for each user and then ...... You can see why I would prefer to know that this is a limitation to the tool rather than an error in logic. 

Transfer request is complete.                                                
Transfer statistics: 00:00:07                                                
Rows transferred: 17                                                         
Press ENTER to end terminal session.                              

          
MSGDB0036 - Server returned SQL error ([SQL0204] RVSX924PF in QTEMP type *FILE not found.)                                                                
Press ENTER to end terminal session.                                         

------------------------------
David Taylor
Sr Application Developer
Range Resources
Fort Worth TX

Original Message:
Sent: Mon January 15, 2024 02:36 PM
From: David Taylor
Subject: Using the Spreadsheet Functions in ACS for QTEMP files.

I have used the basic code from @Scott Forstie's gist to produce beautiful spreadsheets.  I need to confirm if any of you have tried to use this technique for a file in QTEMP.  Mine fail with file not found. Yes, I would prefer not using QTEMP, but much of the code I could retrofit uses QTEMP or the more up-to-date DECLARE GLOBAL TEMPORARY TABLE version. When I looked at a recent program, there was too much going in the SQLRPGLE to simply replace it with a single SQL.  I can live with this for some processes, but those where multiple users could be running the process at the same time benefit from QTEMP. 

https://gist.github.com/forstie/cec5b0bf5c0e6983125ae078b0d04b48

------------------------------
David Taylor
Sr Application Developer
Range Resources
Fort Worth TX
------------------------------

Dear David

I'm not familiar with a terminal session with the message "Press ENTER to end terminal session."   Is it a 5250 emulation session? I hope it is.  But then I'm not sure I understand what you meant by "the model for the SP I am using stores the query in file."

Anyway, after encountering the error, would you run DSPLIB QTEMP from 5250 command line to confirm that the object RVSX924PF is in QTEMP?  If so, the best I can guess is that the stored procedure you run has its own job to run in other than the same 5250 session job that you run STRSQL. The message text " Server returned SQL error (...." makes me suspect this.  You need to look into the code of the SP to confirm this.  Or if possible, post the SP source code here so I can try looking into to confirm my suspicion.

If the SP really runs in a new job, then a solution is to copy the data from QTEMP to a user library. 

One last point, STRSQL has many limitation compared to Run SQL Script tool.  You should try running your program that produces the result into QTEMP and calling your SP from Run SQL Script tool to see if it works or not.  When you run CL in Run SQL Script session, you begin the line with CL:  and end each command line with semicolon (;) just like each SQL statement. 

------------------------------
Chance favors only the prepared mind.
-- Louis Pasteur
------------------------------
Satid S.

Original Message:
Sent: Tue January 16, 2024 09:25 AM
From: David Taylor
Subject: Using the Spreadsheet Functions in ACS for QTEMP files.

Yes, I needed to have supplied more detail.  I am running from the STRSQL command line from a terminal session with the correct library list, including QTEMP. When I run the stored procedure with the query containing a standard library, the process completes and loads 17 rows to the spreadsheet. When I call the SP with the query for the QTEMP library, I get the error below. I can plan around this, but the model for the SP I am using stores the query in file.  I guess I could plan to load a unique record into the query reference file for each user and then ...... You can see why I would prefer to know that this is a limitation to the tool rather than an error in logic. 

Transfer request is complete.                                                
Transfer statistics: 00:00:07                                                
Rows transferred: 17                                                         
Press ENTER to end terminal session.                              

          
MSGDB0036 - Server returned SQL error ([SQL0204] RVSX924PF in QTEMP type *FILE not found.)                                                                
Press ENTER to end terminal session.                                         

------------------------------
David Taylor
Sr Application Developer
Range Resources
Fort Worth TX

Original Message:
Sent: Mon January 15, 2024 02:36 PM
From: David Taylor
Subject: Using the Spreadsheet Functions in ACS for QTEMP files.

I have used the basic code from @Scott Forstie's gist to produce beautiful spreadsheets.  I need to confirm if any of you have tried to use this technique for a file in QTEMP.  Mine fail with file not found. Yes, I would prefer not using QTEMP, but much of the code I could retrofit uses QTEMP or the more up-to-date DECLARE GLOBAL TEMPORARY TABLE version. When I looked at a recent program, there was too much going in the SQLRPGLE to simply replace it with a single SQL.  I can live with this for some processes, but those where multiple users could be running the process at the same time benefit from QTEMP. 

https://gist.github.com/forstie/cec5b0bf5c0e6983125ae078b0d04b48

------------------------------
David Taylor
Sr Application Developer
Range Resources
Fort Worth TX
------------------------------

Satid, 

That is a thought I had not considered, somehow the process has its own QTEMP.  I am going to run with that and stop worrying. Here is the very simple source modified from Scott's start. Since the SP requires the system name in the code, I will have two versions, one for the TEXTBOX and one for PROD.

On a side note--and yes I should make this a separate thread--when I run the process, it leaves extra files on the system. Since this XLSX was written to /TMP, I need to test to see if the left-overs are always in /TMP or not.  The poifiles subfolder has data from previous runs as well. Do I need a clean-up as part of the process?  

------------------------------
David Taylor
Sr Application Developer
Range Resources
Fort Worth TX

Original Message:
Sent: Wed January 17, 2024 02:12 AM
From: Satid Singkorapoom
Subject: Using the Spreadsheet Functions in ACS for QTEMP files.

Dear David

I'm not familiar with a terminal session with the message "Press ENTER to end terminal session."   Is it a 5250 emulation session? I hope it is.  But then I'm not sure I understand what you meant by "the model for the SP I am using stores the query in file."

Anyway, after encountering the error, would you run DSPLIB QTEMP from 5250 command line to confirm that the object RVSX924PF is in QTEMP?  If so, the best I can guess is that the stored procedure you run has its own job to run in other than the same 5250 session job that you run STRSQL. The message text " Server returned SQL error (...." makes me suspect this.  You need to look into the code of the SP to confirm this.  Or if possible, post the SP source code here so I can try looking into to confirm my suspicion.

If the SP really runs in a new job, then a solution is to copy the data from QTEMP to a user library. 

One last point, STRSQL has many limitation compared to Run SQL Script tool.  You should try running your program that produces the result into QTEMP and calling your SP from Run SQL Script tool to see if it works or not.  When you run CL in Run SQL Script session, you begin the line with CL:  and end each command line with semicolon (;) just like each SQL statement. 

------------------------------
Chance favors only the prepared mind.
-- Louis Pasteur
------------------------------
Satid S.

Original Message:
Sent: Tue January 16, 2024 09:25 AM
From: David Taylor
Subject: Using the Spreadsheet Functions in ACS for QTEMP files.

Yes, I needed to have supplied more detail.  I am running from the STRSQL command line from a terminal session with the correct library list, including QTEMP. When I run the stored procedure with the query containing a standard library, the process completes and loads 17 rows to the spreadsheet. When I call the SP with the query for the QTEMP library, I get the error below. I can plan around this, but the model for the SP I am using stores the query in file.  I guess I could plan to load a unique record into the query reference file for each user and then ...... You can see why I would prefer to know that this is a limitation to the tool rather than an error in logic. 

Transfer request is complete.                                                
Transfer statistics: 00:00:07                                                
Rows transferred: 17                                                         
Press ENTER to end terminal session.                              

          
MSGDB0036 - Server returned SQL error ([SQL0204] RVSX924PF in QTEMP type *FILE not found.)                                                                
Press ENTER to end terminal session.                                         

------------------------------
David Taylor
Sr Application Developer
Range Resources
Fort Worth TX

Original Message:
Sent: Mon January 15, 2024 02:36 PM
From: David Taylor
Subject: Using the Spreadsheet Functions in ACS for QTEMP files.

I have used the basic code from @Scott Forstie's gist to produce beautiful spreadsheets.  I need to confirm if any of you have tried to use this technique for a file in QTEMP.  Mine fail with file not found. Yes, I would prefer not using QTEMP, but much of the code I could retrofit uses QTEMP or the more up-to-date DECLARE GLOBAL TEMPORARY TABLE version. When I looked at a recent program, there was too much going in the SQLRPGLE to simply replace it with a single SQL.  I can live with this for some processes, but those where multiple users could be running the process at the same time benefit from QTEMP. 

https://gist.github.com/forstie/cec5b0bf5c0e6983125ae078b0d04b48

------------------------------
David Taylor
Sr Application Developer
Range Resources
Fort Worth TX
------------------------------

Satid, 

That is a thought I had not considered, somehow the process has its own QTEMP.  I am going to run with that and stop worrying. Here is the very simple source modified from Scott's start. Since the SP requires the system name in the code, I will have two versions, one for the TEXTBOX and one for PROD.

On a side note--and yes I should make this a separate thread--when I run the process, it leaves extra files on the system. Since this XLSX was written to /TMP, I need to test to see if the left-overs are always in /TMP or not.  The poifiles subfolder has data from previous runs as well. Do I need a clean-up as part of the process?  

Dear David

I'm not familiar with a terminal session with the message "Press ENTER to end terminal session."   Is it a 5250 emulation session? I hope it is.  But then I'm not sure I understand what you meant by "the model for the SP I am using stores the query in file."

Anyway, after encountering the error, would you run DSPLIB QTEMP from 5250 command line to confirm that the object RVSX924PF is in QTEMP?  If so, the best I can guess is that the stored procedure you run has its own job to run in other than the same 5250 session job that you run STRSQL. The message text " Server returned SQL error (...." makes me suspect this.  You need to look into the code of the SP to confirm this.  Or if possible, post the SP source code here so I can try looking into to confirm my suspicion.

If the SP really runs in a new job, then a solution is to copy the data from QTEMP to a user library. 

One last point, STRSQL has many limitation compared to Run SQL Script tool.  You should try running your program that produces the result into QTEMP and calling your SP from Run SQL Script tool to see if it works or not.  When you run CL in Run SQL Script session, you begin the line with CL:  and end each command line with semicolon (;) just like each SQL statement. 

------------------------------
Chance favors only the prepared mind.
-- Louis Pasteur
------------------------------
Satid S.

Original Message:
Sent: Tue January 16, 2024 09:25 AM
From: David Taylor
Subject: Using the Spreadsheet Functions in ACS for QTEMP files.

Yes, I needed to have supplied more detail.  I am running from the STRSQL command line from a terminal session with the correct library list, including QTEMP. When I run the stored procedure with the query containing a standard library, the process completes and loads 17 rows to the spreadsheet. When I call the SP with the query for the QTEMP library, I get the error below. I can plan around this, but the model for the SP I am using stores the query in file.  I guess I could plan to load a unique record into the query reference file for each user and then ...... You can see why I would prefer to know that this is a limitation to the tool rather than an error in logic. 

Transfer request is complete.                                                
Transfer statistics: 00:00:07                                                
Rows transferred: 17                                                         
Press ENTER to end terminal session.                              

          
MSGDB0036 - Server returned SQL error ([SQL0204] RVSX924PF in QTEMP type *FILE not found.)                                                                
Press ENTER to end terminal session.                                         

------------------------------
David Taylor
Sr Application Developer
Range Resources
Fort Worth TX

Original Message:
Sent: Mon January 15, 2024 02:36 PM
From: David Taylor
Subject: Using the Spreadsheet Functions in ACS for QTEMP files.

I have used the basic code from @Scott Forstie's gist to produce beautiful spreadsheets.  I need to confirm if any of you have tried to use this technique for a file in QTEMP.  Mine fail with file not found. Yes, I would prefer not using QTEMP, but much of the code I could retrofit uses QTEMP or the more up-to-date DECLARE GLOBAL TEMPORARY TABLE version. When I looked at a recent program, there was too much going in the SQLRPGLE to simply replace it with a single SQL.  I can live with this for some processes, but those where multiple users could be running the process at the same time benefit from QTEMP. 

https://gist.github.com/forstie/cec5b0bf5c0e6983125ae078b0d04b48

------------------------------
David Taylor
Sr Application Developer
Range Resources
Fort Worth TX
------------------------------

I have used the basic code from @Scott Forstie's gist to produce beautiful spreadsheets.  I need to confirm if any of you have tried to use this technique for a file in QTEMP.  Mine fail with file not found. Yes, I would prefer not using QTEMP, but much of the code I could retrofit uses QTEMP or the more up-to-date DECLARE GLOBAL TEMPORARY TABLE version. When I looked at a recent program, there was too much going in the SQLRPGLE to simply replace it with a single SQL.  I can live with this for some processes, but those where multiple users could be running the process at the same time benefit from QTEMP. 

https://gist.github.com/forstie/cec5b0bf5c0e6983125ae078b0d04b48