How do I populate an SQL table created on the fly in RPGLE?

SteveFletcher

80 pts.

Tags:

Dynamic SQL

Microsoft Excel

RPG

RPGLE

RPGSQL

SQL

SQL error messages

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.

Asked: February 14, 2009 4:20 AM Last updated: February 16, 2009 4:38 PM

Answer Wiki

Last updated: February 14, 2009 7:58 PM GMT

philpl1jb54,090 pts.
History
Contributors
- Ordered by most recent
- philpl1jb54,090 pts.

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 3 Replies

SteveFletcher Feb 14, 2009 11:11 PM GMT
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
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
80 pointsBadges:
report
philpl1jb Feb 15, 2009 7:14 PM GMT

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

54,090 pointsBadges:

report
SteveFletcher Feb 15, 2009 9:54 PM GMT

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

80 pointsBadges:

report