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

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

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

Solution

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;