Using dynamic table name in db2

Question

Currently in my project development need of generating the record count based on certain criteria where the table names are stored in separate table.For instance say xx table stores the table name under the column name is tableInfo.

I've written the stored procedure in such a way that

DECLARE FGCURSOR CURSOR FOR SELECT tableInfo FROM xx WHERE col1='PO';

OPEN FGCURSOR;

FETCH FROM FGCURSOR INTO FILEGROUPMEM;

WHILE SQLCODE <> 100
DO

SET COUNTVal =   'SELECT COUNT(*)  FROM  ' ||  FILEGROUPMEM || '  WHERE ICLS=  '  || CLASS  || '  AND  IVEN=  ' || VENDOR  || ' AND ISTY=  ' || STYLE || '  AND ICLR= ' || COLOR || ' AND ISIZ=  ' || SIZE   ; 


IF(COUNTVal  >= 1) THEN 
RETURN 1;
END IF;

FETCH FROM FGCURSOR INTO FILEGROUPMEM;

END WHILE;

CLOSE FGCURSOR;

Getting the exception on executing the procedure saying that

Message: [SQL0420] Character in CAST argument not valid. Cause . . . . . : A character in the argument for the CAST function was not correct. Recovery . . . : Change the result data type to one that recognizes the characters in the CAST argument, or change the argument to contain a valid representation of a value for the result data type. Try the request again.

Besides the SQL Injection issue mentioned in the answer, your loop is dangerous. For one thing, any error would be a negative code - say, the table being missing - which probably isn't what you want. There's other positive "warning" codes, too, although usually the 0/100 would dominate. Also, normally I dislike running SQL statements in response to looping results from a cursor, but there isn't really anything better here to do (given the dynamic table name). — Clockwork-Muse, Mar 29 '16 at 14:38

Charles · Accepted Answer · 2018-02-23T21:04:05.183

This line is not correct:

SET COUNTVal =   'SELECT COUNT(*)  FROM  ' ||  FILEGROUPMEM || '  WHERE ICLS=  '  || CLASS  || '  AND  IVEN=  ' || VENDOR  || ' AND ISTY=  ' || STYLE || '  AND ICLR= ' || COLOR || ' AND ISIZ=  ' || SIZE   ;

To use it the way you are trying, you'd have to use a static SQL statement like so

exec sql SELECT COUNT(*) INTO :COUNTVal  
  FROM  MYTBL 
 WHERE ICLS=  :CLASS  AND  IVEN=  :VENDOR  AND ISTY=  :STYLE 
       AND ICLR= :COLOR  AND ISIZ=  :SIZE;

However, while a static statement can use variables, the table name in the FROM clause can not be variable.

Thus you have to prepare and use a dynamic statement. Unfortunately, SELECT INTO can not be used in a dynamic statement. VALUES INTO can be used dynamically.

set wSqlStmt = 'VALUES ( SELECT COUNT(*)  FROM  ' ||  FILEGROUPMEM 
                || '  WHERE ICLS=  '  || CLASS  || '  AND  IVEN=  ' 
                || VENDOR  || ' AND ISTY=  ' || STYLE || '  AND ICLR= ' 
                || COLOR || ' AND ISIZ=  ' || SIZE ||') INTO ?';

exec sql PREPARE S1 FROM :wSqlStmt;

exec sql EXECUTE S1 USING COUNTVal;

WARNING the above code could be subject to SQL Injection attacks. To protect against SQL injection, dynamic SQL should use parameter markers instead of concatenating input directly to a statement. While you can't use a parameter marker for the table name, you can for the rest of the variables like so:

set wSqlStmt = 'VALUES ( SELECT COUNT(*)  FROM  ' ||  FILEGROUPMEM 
                || '  WHERE ICLS=  ?  AND  IVEN=  ? ' 
                || '  AND ISTY= ? AND ICLR= ?' 
                || '  AND ISIZ= ?) INTO ?';

exec SQL PREPARE S1 FROM :wSqlStmt;

exec SQL EXECUTE S1 USING :CLASS, :VENDOR, :STYLE, :COLOR, :SIZE, :COUNTVal;

fixed...actually missed a couple. Not to mention that if for instance , COLOR is a string, then the second statement would actually need to concatenate quotes into the statement.`||''''||COLOR||''''||` — Charles, Mar 29 '16 at 15:52

Using dynamic table name in db2

1 Answers1

Linked