Specify SQL Table-creation Library at Runtime
April 25, 2012 Hey, Ted
|
I have an RPG programs that creates work tables in QTEMP. I’d like to be able to specify the library at runtime. I tried using a host variable for the library in a CREATE TABLE command, but that didn’t fly. I am using the system naming convention. Can you help? –Ben I wish CREATE TABLE would allow a host variable for the explicit qualifier, but it doesn’t. Here are a few ways that work. 1. Use dynamic SQL. Embed the library name in the SQL command.
D SqlCommand s 256a varying
D WorkLib s 10a
/free
*inlr = *on;
SqlCommand = ('create table ' + %trim(WorkLib) +
'/SomeTable +
(OneFish char(3), +
TwoFish dec (5,0), +
RedFish char(1), +
BlueFish date)');
exec sql
execute immediate :SqlCommand;
2. Use the CURRENT SCHEMA special register with dynamic SQL. Note that the CREATE TABLE command does not include a qualifier.
D SqlCommand s 256a varying
D WorkLib s 10a
/free
*inlr = *on;
exec sql
set Current Schema = :WorkLib;
SqlCommand = ('create table SomeTable +
(OneFish char(3), +
TwoFish dec (5,0), +
RedFish char(1), +
BlueFish date)');
exec sql
execute immediate :SqlCommand;
3. Change the current library. Be aware that the system will not allow you to set the current library to QTEMP. You might want to reset it before the job ends. A portion of the CL caller:
dcl &CurLib *char 10
/* change the current library */
rtvjoba curlib(&CurLib)
chgcurlib SomeLib
/* do the work */
call rpgpgm
/* reset the current library */
if (&CurLib *eq *NONE) +
then( chgcurlib *CRTDFT)
else ( chgcurlib &CurLib)
A portion of the RPG “callee”:
exec sql
create table SomeTable
(OneFish char(3),
TwoFish dec (5,0),
RedFish char(1),
BlueFish date);
|
