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?
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);