In GENERATE_SPREADSHEET SQL on IBM i there are problems if a $ character is present in qualified names

Question

If you have a library that begins with a $, such as $BASDTA it seems to throw off the GENERATE_SPREADSHEET scalar function when used in embedded SQL (i.e. exec sql...). At runtime it does not recognise a qualified file name in the SPREADSHEET_QUERY parameter using such a library.

Minimal example is below: First some setup..

CRTLIB $MYLIB
CRTPF FILE($MYLIB/MYFILE) RCDLEN(10) 
RUNSQL SQL('insert into $MYLIB.MYFILE VALUES (''Hello'')') COMMIT(*NONE)

Source code below. Member type is SQLRPGLE

  ctl-opt DFTACTGRP(*NO);                                    
  dcl-s returncode zoned(5);                                                                                      
  exec sql set :returncode = SYSTOOLS.GENERATE_SPREADSHEET(  
    PATH_NAME => '/home/tmcneil/mysheet',                    
    SPREADSHEET_QUERY => 'select * from $MYLIB.MYFILE',      
    SPREADSHEET_TYPE => 'xlsx'   ,                           
    COLUMN_HEADINGS => 'COLUMN')                             
    ;                                                        
  return;

Results from terminal session when running above...

WARNING: Could not create system preferences directory. System preferences ar
e unusable.                                                                  
MSGDB0036 - Server returned SQL error                                        
    ([SQL0104] Token . was not valid. Valid tokens: ( NEW FINAL TABLE UNNEST 
LATERAL XMLTABLE JSON_TABLE <IDENTIFIER>.)                                   
Press ENTER to end terminal session.

It's complaining about the period in the qualified name as above but after exploration I'm sure that it's the $ sign in the library name that throws it. Doing the same thing with no $ in the name works fine. The SELECT statement above works fine in an ACS or STRSQL session but not here. I'm on V7R4. CCSID for jobs and source file is 37 but system CCSID is 65535, if that's relevant. Any suggestions for a workaround would be most welcome. Thanks in advance. Tony

Charles · Accepted Answer · 2024-10-09 19:36:19Z

1

Well I tried (double) quoting the names...

SPREADSHEET_QUERY => 'select * from ''$MYLIB''.''MYFILE'''

Also with a single double quote

SPREADSHEET_QUERY => 'select * from "$MYLIB"."MYFILE"'

Neither worked for me, though I don't see the same error you do.

I could suggest creating a ALIAS for the file..

CREATE ALIAS OTHLIB.MYFILE FOR $MYLIB.MYFILE;

answered Oct 9, 2024 at 19:36

Charles

24.2k3 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tony MST Over a year ago

ALIAS works, thanks very much. so... exec sql create or replace alias qgpl.myfile for $MYLIB.MYFILE; then... SPREADSHEET_QUERY => 'select * from qgpl.myfile', It's implemented as a DDM file.

Collectives™ on Stack Overflow

In GENERATE_SPREADSHEET SQL on IBM i there are problems if a $ character is present in qualified names

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related