Search code examples
sqldb2ibm-midrangerpg

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


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


Solution

  • 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;