RE: Building an Embedded SQL Statement based on a prompt screen using a variable SELECT

When using prepared statements you can't use host variables.

You need either the following:
c                   if        pname <> *blanks 
c                   eval      pname ='%'+%trim(pname)+'%' 
c                   eval      mainstatement = 
c                             'select * from wmmstr where ' + 
c                             'wmname like '   + 
c                             '''' + %trim(pname) + '''' +   //here's the 
difference
c                             'order by wmname' 
c                   endif 

or use parameter markers

c                   if        pname <> *blanks 
c                   eval      pname ='%'+%trim(pname)+'%' 
c                   eval      mainstatement = 
c                             'select * from wmmstr where ' + 
c                             'wmname like '   + 
c                             '?' 
c                             'order by wmname' 
c                   endif 


c/exec sql 
C+ open MAINSTATEMENT using :pname 
c/end-exec 


Prepared statements are what you want to use if you're going to constantly be 
closing and reopening the cursor using a different pname.


HTH,

Charles Wilt
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of PHarte@xxxxxxxxxx
> Sent: Monday, May 16, 2005 11:09 AM
> To: rpg400-l@xxxxxxxxxxxx
> Subject: Building an Embedded SQL Statement based on a prompt screen
> using a variable SELECT
> 
> 
> Thanks for the previous replys guys your suggestions worked great. 
> 
> I have run into another problem....I have multiple fields on a prompt 
> screen.  I have to build a SELECT statement in SQL/RPG to 
> load my subfile 
> based on the prompt screen selections, this could be name or address.
> 
> Here's how I build part my select statement which doesn't 
> seem to work.
> 
>  * 
> c                   if        pname <> *blanks 
> c                   eval      pname ='%'+%trim(pname)+'%' 
> c                   eval      mainstatement = 
> c                             'select * from wmmstr where ' + 
> c                             'wmname like '   + 
> c                             'trim(:pname) ' + 
> c                             'order by wmname' 
> c                   endif 
>  * 
>  * Value of "mainstatement after eval statement.... 
>  *  select * from wmmstr where wmname like trim(:pname) order by' 
>  *   wmname                                                     ' 
>  *                      
> c/exec sql 
> C+ DECLARE MAINCURSOR CURSOR FOR MAINSELECT 
> c/end-exec 
> c/exec sql 
> C+ PREPARE MAINSELECT FROM :mainstatement 
> c/end-exec 
> 
> Here's my FETCH statement which I use when loading the subfile
> 
> c/exec sql 
> c+ fetch next from mainCursor into :main_ds 
> c/end-exec 
> 
> Th
> -- 
> 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.
> 
>