Using a variable for a database file in embeded SQL in free RPG

Question

I would like to write an sql statment, that works no matter what data file is wanted. example:

exec sql select * from :variable;

I am getting a SQL0104 Error at the ":" "Token not vaild. Valid Token :(NEW FINAL TABLE UNNEST LATERAL MLTABLE JSON_TABLE"

I tried to do it in different statemenst with "insert into :variable", but I get the same Error. Is it maybe not possible and I have to hard code the databasefile into my code or is there a way around? Thanks

Answer 1

You can't use static SQL with a variable table name.

You'd need to use dynamic SQL via EXECUTE IMMEDIATE or PREPARE and EXECUTE

wMySql = 'insert into ' + wTableName + ' values (1,''ABC'')';

exec SQL
  execute immediate :wMySql;

Note that SELECT * FROM TABLE is not a statement that can be dynamically run. For that matter, a static version doesn't make sense. SELECT ... INTO :myData FROM MYTABLE is a valid static statement, but can't be used dynamically. Instead you'd need to use VALUES (SELECT * from MYTABLE) into :myData which can be run dynamically.

Here's an IBM example using dynamic SQL. Note normally you'd see the FETCH in a loop to return multiple rows, as written this will just return the first row then end. This also show the use of replacement variables ? in the statement. Which is a best practice as it prevents SQL injection attacks. However note that you can't use a replacement variable for the table name or column names.

//**************************************************
//* Declare program variables.                     *
//* STMT initialized to the                        *
//* listed SQL statement.                          *
//**************************************************
DCL-S EMPNUM CHAR(6);
DCL-S NAME CHAR(15);
DCL-S STMT CHAR(500)
  INZ('SELECT LASTNAME FROM CORPDATA.EMPLOYEE +
       WHERE EMPNO = ?');
...
//*************************************************************
//* Prepare STMT as initialized in declare section            *
//*************************************************************
EXEC SQL PREPARE S1 FROM :STMT;

//*************************************
//* Declare Cursor for STMT           *
//*************************************
EXEC SQL DECLARE C1 CURSOR FOR S1;

//*****************************************************
//* Assign employee number to use in select statement *
//*****************************************************
EMPNUM = '000110';

//**********************
//* Open Cursor        *
//**********************
EXEC SQL OPEN C1 USING :EMPNUM;

//***********************************************
//* Fetch record and put value of               *
//* LASTNAME into NAME                          *
//***********************************************
EXEC SQL FETCH C1 INTO :NAME;

//********************************
//* Program processes NAME here  *
//********************************
...
//******************
//* Close cursor   *
//******************
EXEC SQL CLOSE C1;