Embedded SQL does not work

[tdavis]

I have the following code in my RPG program to create a file in QTEMP. The exact same SQL statement works perfectly from STRSQL. From RPG, it runs without any errors, and does create the file, but the file is always empty. I do not get any errors returned, so I am quite confused.

Here is the SQL statement from STRSQL:

declare global temporary table IS1074FSA6 as (
SELECT * FROM TABLE(
SYSTOOLS.AUDIT_JOURNAL_CP(
STARTING_TIMESTAMP => '2023-09-01',
ENDING_TIMESTAMP => '2023-09-30'))
) WITH DATA 

And here is the RPG program (debug display of variable command follows):

command =
'declare global temporary table IS1074FSA6 as ( ' +
'SELECT * FROM TABLE( ' +
'SYSTOOLS.AUDIT_JOURNAL_CP( ' +
'STARTING_TIMESTAMP => ' + q + parm_FrDate + q + ',' +
'ENDING_TIMESTAMP => ' + q + parm_ToDate + q + ')) ' +
') with data';
exec SQL execute immediate :command; 

Here is the value of the variable command when I am in debug:

declare global temporary table IS1074FSA6 as ( SELECT * FROM
TABLE( SYSTOOLS.AUDIT_JOURNAL_CP( STARTING_TIMESTAMP => '20
23-10-04',ENDING_TIMESTAMP => '2023-10-04')) ) with data 

[B.Hauser]

Did you check the SQLCODE or SQLSTATE after each SQL Statement?
If not please check one (SQLCODE or SQLSTATE) of them and then tell the SQLCODE or/and the SQLSTATE.

[vazymimil]

parm_FrDate and parm_ToDate have the same value in debug. These values are dates but the function receives timestamps, so you are asking what's in the journal at midnight october the fourth.

Try with
ENDING_TIMESTAMP => (date '2023-10-04' + 1 day)

Btw, why don't you use static SQL like below ?

exec sql declare global temporary table IS1074FSA6 as (
SELECT * FROM TABLE(
SYSTOOLS.AUDIT_JOURNAL_CP(
STARTING_TIMESTAMP => :parm_FrDate ,
ENDING_TIMESTAMP => :parm_ToDate ))
) WITH DATA ;