SQLRPGLE: How to update/delete without host variables? (sql is in a string)

[aliceraunsbaek]

Can anyone tell me how to best run a delete/inster/update that has been stored in a string?

E.g.:
I can code the following:

Exec SQL delete from table1 where lastUse < current timestamp - 7 days;

But, I would like something like:

MyStatement = 'delete from table1 where lastUse < current timestamp - 7 days';
Exec SQL :MyStatement;

Can I do that?/What do I have to do to make it work?

[GLS400]

Re: SQLRPGLE: How to update/delete without host variables? (sql is in a string)

Hi aliceraunsbaek:

I think i stole this from Bob Cozzi:

0001.00 H DFTACTGRP(*NO) ACTGRP(*NEW) OPTION(*SRCSTMT)                   
0002.00 D RunSQL          PR                  EXTPGM('RUNSQLLE')         
0005.00 D  stmt                       3000A   Const Varying              
0006.00 D RunSQL          PI                                             
0007.00 D  stmt                       3000A   Const Varying              
0008.00                                                                  
0009.00 D hostSQLStmt     S           3000A     
0013.00  /free    
0013.01      *INLR = *ON;                            
0014.00      EXEC SQL                                             
0015.00         SET OPTION                                        
0016.00         commit = *NONE,                                   
0017.00         CLOSQLCSR = *ENDMOD,                              
0018.00         NAMING = *SYS;                                    
0019.00                                                           
0020.00         if (%len(stmt) > 0 and stmt <> *blanks);          
0021.00             hostSqlStmt = stmt;                           
0022.00            exec sql execute immediate :hostSqlStmt;       
0023.00         endif;                                            
0024.00  /end-free

Best of Luck
GLS

[tomholden]

Re: SQLRPGLE: How to update/delete without host variables? (sql is in a string)

why use dynamic SQL for this?

Exec SQL
  Delete From Table1 where lastUse < current timestamp - 7 days

that should work and perform better...

[aliceraunsbaek]

Re: SQLRPGLE: How to update/delete without host variables? (sql is in a string)

0020.00         if (%len(stmt) > 0 and stmt <> *blanks);          
0021.00             hostSqlStmt = stmt;                           
0022.00            exec sql execute immediate :hostSqlStmt;       
0023.00         endif;

Ahh! Thank you! That is the one =0)

why use dynamic SQL for this?

Exec SQL
  Delete From Table1 where lastUse < current timestamp - 7 days

that should work and perform better...

I'm creating a 'clean up' program. It reads from a table how to clean up each file:

Format - FILOPRYD            ....+...10....+...20....+...30....+...40...
FIL...........K01.....(10)   SYSOVSYS  :                                
OPRYDHYP.............(4.0)     10 :                                     
REORGHYP.............(4.0)      0 :                                     
SQLQUERY.............(256)    SidstOpdat > (current timestamp - 1 days) 
SIDSTKOERT............(26)   0001-01-01-00.00.00.000000:                
STATUSKODE...........(5.0)       0 :

As I want to read the where clause from the table I cannot put it directly in the code.