Home > IT Answers > SQL Server > How do I populate an SQL table created on the fly in RPGLE?
SteveFletcher
80 pts.
 How do I populate an SQL table created on the fly in RPGLE?
Dynamic SQL, Microsoft Excel, RPG, RPGLE, RPGSQL, SQL, SQL2JXL
I am writing a report program where I create a table that is then converted to an Excel spreadsheet using SQL2JXL. I am able to dynamically create a table with Execute Immediate but I have been unable to populate it. The problem is the table name is stored in a variable within the program and I can't figure out how to get Insert Into to allow me to pass the table name as a variable to put the data into that table. Any ideas? Thanks.

Software/Hardware used:
ASKED: February 14, 2009  4:20 AM
UPDATED: February 16, 2009  4:38 PM
RELATED QUESTIONS
Answer Wiki:
Yes. In RPGSql you need to do the insert as a prepared statement - that you build on the fly..this is dynamitic SqL or In RPG native mode use OVRDBF to redirect the program to the actualy file. or You could also have the file as USROPN and EXTFILe in the f spec's assign the proper name to the ExTfile variable before opening the file. Phil
Last Wiki Answer Submitted:  February 14, 2009  7:58 pm  by  philpl1jb   44,630 pts.
All Answer Wiki Contributors:  philpl1jb   44,630 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Thanks for your response Phil.

I’m hoping to do this using SQL. I’ve tried several things, but can’t get it to work. This seems the most promising, but I’m not quite there: 

WkLib = 'MYLIB';                                    
WkTable = 'OTR' + %Char(%SubDt(%Timestamp:*MS));        
WkOutFile = WkLib + '/' + WkTable;                      
WkFld = 'Create Table '+ WkOutFile + ' LIKE FULOTRWT';  
Exec Sql Execute Immediate :WkFld;  

Exec Sql PREPARE S1 FROM :SqlStmt;      
Exec Sql DECLARE C1 CURSOR FOR S1;      
Exec Sql OPEN C1;           

WkFld = 'Insert Into ' + WkOutFile + ' Values (@Fulotrwt)';

Exec Sql FETCH NEXT FROM C1 INTO :@Fulotrwt :@IndVar;       
                                                            
Dow SqlCod = 0;                                             

  Exec Sql Execute Immediate :WkFld;                        
                                                            
  Exec Sql FETCH NEXT FROM C1 INTO :@Fulotrwt :@IndVar;     
                                                            
Enddo;

First, I create the table named OTR123000 (varying number) in library MYLIB like the existing table FULOTRWT.

Then I prepare/declare/open a cursor for a passed-in sql statement to retrieve data from an existing table.

Then I create a variable (WkFld) with the sql statement to be executed (@Fulotrwt is a data structure based on the existing table), fetch a record, and attempt to insert it into the dynamic table (repeatedly).

Each time the

    Exec Sql Execute Immediate :WkFld;

runs I get an sql 206 error with the message “Column @FULOTRWT not in specified tables.”

I also tried naming the columns specifically instead of the data structure, but then I get the same error but with the column name instead of the data structure.

I would appreciate it if anyone can tell me what I’m missing here.

Thanks,
Steve

SteveFletcher
 80 pts.
 

Steve
I’m not using RPGSQL enough to be sure
WkFld = ‘Insert Into ‘ + WkOutFile + ‘ Values (@Fulotrwt)’;
1. should there be a : in :@FULOTRWT above?

or expand it

WkFld = ‘Insert Into ‘ + WkOutFile + ‘ Values (‘ + Field1 + ‘,’ + Field2+’,”CharFld3+”’)’
something like this – note I’ve provided double quotes arount the in spots where SQL should be passed a single quote.

Phil

philpl1jb
 44,630 pts.
 

I found the answer over on SystemiNetwork with the help of Scott Klement.

Use parameter markers, one for each field/column. Set the sql statement as:
WkFld = ‘Insert Into ‘ + WkOutFile +
‘ Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)’;

Prepare the statement:
Exec Sql PREPARE S2 from :WkFld;

Then, execute (instead of the Execute Immediate statement) in the loop:
Exec Sql Execute S2 USING :@Fulotrwt :@IndVar;

Thanks again for the assistance.

Steve

SteveFletcher
 80 pts.