RE: dynamic SQL in RPG

[CWilt@xxxxxxxxxxxx]

Looks ok to me at first glance.

Where does it fail and what is the error message?

Charles


> -----Original Message-----
> From: James Rich [mailto:james@xxxxxxxxxxx]
> Sent: Wednesday, March 31, 2004 7:16 PM
> To: RPG programming on the AS400 / iSeries
> Subject: RE: dynamic SQL in RPG
> 
> 
> On Wed, 31 Mar 2004, Bob Cozzi wrote:
> 
> > Look at the PREPARE and DECLARE SQL statements. They will 
> do what you want.
> 
> That's exactly what I've been looking at.  Everything that I've tried
> doesn't work.  I'll post some actual code so maybe someone 
> will point out
> where I'm being stupid.
> 
> C                   if        (SHVNDNAME = *blanks)
> C                   eval      sqlstatement = 'SELECT 
> POHDRP.POCOMP#,' +
> C                                     'POHDRP.POLOC#,POHDRP.POPO#,' +
> C                                     'POHDRP.POPOSUF,POHDRP.POWO#,' +
> C                                     
> 'POHDRP.POPODATE,POHDRP.POSUPNAME '+
> C                                     'FROM POHDRP ' +
> C                                     'WHERE POHDRP.POPO# >= ' +
> C                                     %trim(%editc(SHPO#:'3')) + ' ' +
> C                                     'AND POHDRP.POCOMP# IN (' +
> C                                     %trim(%editc(co#0:'3')) + ',' +
> C                                     %trim(%editc(co#1:'3')) + ',' +
> C                                     %trim(%editc(co#2:'3')) + ') ' +
> C                                     'ORDER BY POHDRP.POCOMP#,' +
> C                                     'POHDRP.POLOC#,POHDRP.POPO#,' +
> C                                     'POHDRP.POPOSUF'
> C*
> C                   else
> C                   eval      sqlstatement = 'SELECT 
> POHDRP.POCOMP#,' +
> C                                     'POHDRP.POLOC#,POHDRP.POPO#,' +
> C                                     'POHDRP.POPOSUF,POHDRP.POWO#,' +
> C                                     
> 'POHDRP.POPODATE,VENDORP.VMNAME ' +
> C                                     'FROM POHDRP,VENDORP ' +
> C                                     'WHERE POHDRP.POPO# >= ' +
> C                                     %trim(%editc(SHPO#:'3')) + ' ' +
> C                                   
> 'VENDORP.VMVNDRID=POHDRP.POSUPPLIER'+
> C                                     ' AND VENDORP.VMNAME LIKE ' +
> C                                     searchvnd + ' ' +
> C                                     'AND POHDRP.POCOMP# IN (' +
> C                                     %trim(%editc(co#0:'3')) + ',' +
> C                                     %trim(%editc(co#1:'3')) + ',' +
> C                                     %trim(%editc(co#2:'3')) + ') ' +
> C                                     'ORDER BY POHDRP.POCOMP#,' +
> C                                     'POHDRP.POLOC#,POHDRP.POPO#,' +
> C                                     'POHDRP.POPOSUF'
> C                   endif
> C*
> C/EXEC SQL
> C+ PREPARE S1 FROM :sqlstatement
> C/END-EXEC
> C/EXEC SQL
> C+ DECLARE C1 CURSOR FOR S1
> C/END-EXEC
> C/EXEC SQL
> C+ OPEN C1
> C/END-EXEC
> 
> Notice that the difference between the two if conditions is 
> that the first
> does use the vendor name and part of the selection criteria 
> and the second
> does (as well and joining with another file).  When I run this it does
> work.  I tried putting the PREPARE statement inside the if 
> conditionals
> and that doesn't work, either.
> 
> I don't want to just have variables in the select statement 
> (like using
> :variablename for instance) but I want to dynamically change 
> the entire
> sql statement.
> 
> This is a piece of cake with C and MySQL:
> 
> if (condition)
> {
>       sprintf (query,
>       "SELECT custnum,description FROM todolistheader WHERE id = %d",
>       job_id);
> }
> else
> {
>       sprintf (query,
>       "SELECT name, nickname FROM customerlist WHERE id = %d",
>       cust_id);
> }
> 
> mysql_query (&mysql, query);
> 
> Can RPG with SQL really be that much harder?  Am I just being stupid?
> 
> James Rich
> 
> Zvpebfbsg vf abg gur nafjre.
> Zvpebfbsg vf gur dhrfgvba.
> AB (be Yvahk) vf gur nafjre.
>         -- Gnxra sebz n .fvtangher sebz fbzrbar sebz gur HX, 
> fbhepr haxabja
> _______________________________________________
> This is the RPG programming on the AS400 / iSeries (RPG400-L) 
> mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>