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

80 pts.
Dynamic SQL
Microsoft Excel
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.

Answer Wiki

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

In RPGSql you need to do the insert as a prepared statement – that you build on the fly..this is dynamitic SqL

In RPG native mode use OVRDBF to redirect the program to the actualy file.

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.

Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • SteveFletcher
    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;     
    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
    80 pointsBadges:
  • philpl1jb
    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
    53,770 pointsBadges:
  • SteveFletcher
    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:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: