IBM i Global

No - you can't use SELECT or VALUES in RUNSQLSTM. 

Both statements SELECT and VALUES return a result set - and you can't handle a result set in RUNSQLSTM. 

But what you can do is, you can retrieve the source of SYSTOOLS.GENERATE_SPREADSHEET and create your own stored procedure from it, that does not return a result set - and then use a CALL.

HTH

Daniel

hhhmm, I am not returning anything, just creating a spreadhsheet from an existing db file...   sort of like cpytoimpf...  but you are saying runsqlstm is expecting a result set?  i have used it before with an insert into and it doesn't seem to be expecting a result set...  

Well - no, RUNSQLSTM cannot execute SELECT statements, right? This is, because every SELECT statement "returns" a result set - AKA a result "table" with data.

And now the thing is - the VALUES statement also returns a result set. 

VALUES and SELECT do both return a result set - this is easy to see, if you think about the INSERT statement:

INSERT INTO XXX (A, B, C) VALUES (1, 2, 3);

Or you can write:

INSERT INTO XXX (A, B, C) SELECT A, B, C FROM YYY;

In both cases, the INSERT statement uses the result set to insert the values into the table. 

But RUNSQLSTM can't handle a result set - it simply doesn't know what it should do with it. 

Regards,

Daniel

There are restrictions as to what kind of SQL Statements you can run in CL

Using the SQL statement processor - IBM Documentation

What you can do though is use CL to call a stored procedure using RUNSQL

you can look at this post from 2024 as it relates directly to your question

IBM i Service: Generate_Spreadsheet and Send_Email in batch | IBM i Global

Hope this helps - Rich

Re: using RUNSQLSTM for executing a VALUES sql statement

David,

As others have mentioned, there are restrictions on SQL statements in CL. However, I wanted to share an alternative approach we've successfully implemented that creates Excel spreadsheets directly from CL without needing GENERATE_SPREADSHEET or stored procedures.

Our Solution: Using ACS Bundle JAR with QSHONI/QSHEXEC (open source library which makes using QSH within CL much easier)

https://github.com/richardschoen/QshOni

Interestingly, this is actually what the `SYSTOOLS.GENERATE_SPREADSHEET` SQL function calls under the covers. By calling the `acsbundle.jar` file directly using the `cldownload` plugin, you can bypass the SQL statement restrictions in CL and have more control over the process.

Basic Implementation:

(Note: I've adapted this example to match your specific file names and paths from your original post)

/* Add QSHONI to library list */
ADDLIBLE LIB(QSHONI) POSITION(*LAST)
MONMSG MSGID(CPF2103)

/* Define variables */
DCL VAR(&CLIENTFILE) TYPE(*CHAR) LEN(256)
DCL VAR(&HOSTFILE) TYPE(*CHAR) LEN(256)
DCL VAR(&QSHCMD) TYPE(*CHAR) LEN(5000)

/* Set output file path and source DB2 file */
CHGVAR VAR(&CLIENTFILE) VALUE('/clientfile=/QOpenSys/ExportedFiles/CitySuiteGL/glupload/GLTOHARF.xlsx')
CHGVAR VAR(&HOSTFILE) VALUE('/hostfile=COALIBF/GLTOHARF')

/* Build the Java command */
CHGVAR VAR(&QSHCMD) VALUE('/QIBM/ProdData/Java400/bin/java -jar +
  /QIBM/proddata/Access/ACS/Base/acsbundle.jar +
  /plugin=cldownload /system=localhost +
  /adjustcolumns /colheadings=1 /usecollabels ' +
  *CAT &CLIENTFILE *BCAT &HOSTFILE)

/* Execute the command */
QSHONI/QSHEXEC CMDLINE(&QSHCMD) LOGSTDOUT(*YES) +
  PRTSTDOUT(*YES) PRTUSRDTA(GLTOHARF) +
  PRTTXT('Create Excel Spreadsheet')

/* Error handling */
MONMSG MSGID(CPF0000 MCH0000 QSH0006 QSH0007) EXEC(DO)
  SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) +
    MSGDTA('Spreadsheet creation failed') +
    TOPGMQ(*PRV (* *NONE *NONE)) MSGTYPE(*ESCAPE)
ENDDO

Key Parameters:
- `/plugin=cldownload` - Uses the client download plugin
- `/system=localhost` - Connects to local system
- `/adjustcolumns` - Auto-adjusts column widths
- `/colheadings=1` - Uses first row as headers
- `/usecollabels` - Uses DDS column labels

For Multi-Sheet Workbooks:

To add additional sheets, use `/sheet=2` and `/update` parameters:

CHGVAR VAR(&HOSTFILE) VALUE('/hostfile=COALIBF/SECONDFILE')
CHGVAR VAR(&QSHCMD) VALUE('/QIBM/ProdData/Java400/bin/java -jar +
  /QIBM/proddata/Access/ACS/Base/acsbundle.jar +
  /plugin=cldownload /system=localhost +
  /adjustcolumns /colheadings=1 /usecollabels +
  /sheet=2 /update ' *CAT &CLIENTFILE *BCAT &HOSTFILE)

QSHONI/QSHEXEC CMDLINE(&QSHCMD) LOGSTDOUT(*YES) +
  PRTSTDOUT(*YES) PRTUSRDTA(SECONDFILE) +
  PRTTXT('Add Sheet 2')

Advantages:
- Works entirely in CL - no stored procedures needed
- Bypasses SQL statement restrictions in RUNSQLSTM
- Uses the same underlying technology as GENERATE_SPREADSHEET
- Creates native .xlsx files with proper formatting
- Supports multiple sheets in one workbook
- Column headers and labels preserved
- More control over the export process

This approach has worked well for us as an alternative to GENERATE_SPREADSHEET when working in CL programs, giving you direct access to the same ACS functionality without the SQL limitations.

Hope this helps!

Hi all. I borrowed Scott's example and made a generic CL command around it called: GENXLSACS. 

Thanks for the great example Scott.

You can find it here: 
https://github.com/richardschoen/QshOniUtil

As others already have written - you can't use SELECT or VALUES in RUNSQLSTM (or RUNSQL) CL commands.

However, you can wrap your call to SYSTOOLS.GENERATE_SPREADSHEET in different SQL statements that store the result somewhere:

1. CREATE OR REPLACE TABLE will store the output in a (new) table.
2. SYSTOOLS.LPRINTF will write a message to the joblog with the output.
3. BEGIN - END will create a temporary program, and you can store the output in a variable that is not used.

As an example, here is your statement wrapped in SYSTOOLS.LPRINTF. When run, you will get a message in the joblog showing if your call succeeded or failed.

call SYSTOOLS.LPRINTF( 'Call to SYSTOOLS.GENERATE_SPREADSHEET ' concat
case
SYSTOOLS.GENERATE_SPREADSHEET(
   PATH_NAME => '/QOpenSys/ExportedFiles/CitySuiteGL/glupload', 
   FILE_NAME => 'GLTOHARF',
   SPREADSHEET_TYPE => 'xlsx',
   LIBRARY_NAME => 'COALIBF')
when 1 then 'succeeded' else 'failed' end
)

Be aware that the RUNSQLSTM now will always succeed, even if the GENERATE_SPREADSHEET fails - it will succeed with a message saying "...failed" in the joblog.
You can force the RUNSQLSTM to fail by using RAISE_ERROR instead of the constant 'failed', e.g.

call SYSTOOLS.LPRINTF( 'Call to SYSTOOLS.GENERATE_SPREADSHEET ' concat
case
SYSTOOLS.GENERATE_SPREADSHEET(
   PATH_NAME => '/QOpenSys/ExportedFiles/CitySuiteGL/glupload', 
   FILE_NAME => 'GLTOHARF',
   SPREADSHEET_TYPE => 'xlsx',
   LIBRARY_NAME => 'COALIBF')
when 1 then 'succeeded' else cast( raise_error( '99001', 'GENERATE_SPREADSHEET failed' ) as char(1) ) end
)

HTH.

Hello,

As Mr. Scott Schollenberger pointed out in a previous post, I think using the `cldownload` plugin is the simplest approach. If you're interested, there are some examples on my GitHub page. (It's in Japanese, so you may need to translate it.)

That said, if you need to use RUNSQLSTM, since this can be achieved with two RUNSQL commands, you might simply have the RUNSQLSTM command execute them.

> RUNSQL SQL('CREATE OR REPLACE TABLE QTEMP.DUMMY (A CHAR(10)) ON REPLACE D
  ELETE ROWS') COMMIT(*NC)                                                 

> RUNSQL SQL('INSERT INTO QTEMP.DUMMY VALUES SYSTOOLS.GENERATE_SPREADSHEET(
  PATH_NAME => ''/tmp/spreadsheet'', FILE_NAME => ''file name'', LIBRARY_NAME 
  => ''library name'', SPREADSHEET_TYPE => ''xlsx'', COLUMN_HEADINGS => ''LABEL'
  ')') COMMIT(*NC)                                                         

I hope this is of some help.

----------------------
Hideyuki Yahagi @ Japan
----------------------

One of the things I like about the IBM i community is there are lots of very smart people in it!  And they are very willing to help!  Thanks for all the great responses.  I want to add a response from one of the top (if not the top) db2 for i sql gurus, Scott Forstie.  His suggestion seems to me to be the most straight forward - for my simple mind...  Here it is implemented in a CLLE program:

      runsql +
         sql('create or replace table mylib.ssgen(ss_result) as ( +
                 VALUES SYSTOOLS.GENERATE_SPREADSHEET( +
                    PATH_NAME => ''/QOpenSys/ExportedFiles/FinSuiteGL/glupload'', +
                    SPREADSHEET_QUERY => ''select * from applibf.gltoharf'', +
                    SPREADSHEET_TYPE => ''xlsx'', +
                    COLUMN_HEADINGS => ''COLUMN'' +
                 ) +
              ) +
              with data on replace delete rows' +
         ) +
         commit(*NONE)

This method gives you a table that contains the response you can use to verify success, as well as the spreadsheet itself

Thanks Scott and all contributors!