RE: Recommended PTF's for Dynamic Sql in RPG?

Tim,

Is this a cut/paste typo:

c/end-exec

c/EXEC SQL   OPEN C1
c/end-exec

or is it like this in your source?

This is how I debug sql statements:  I set a breakpoint where the SQL
statement is built, then I copy/paste it into Ops Nav's ISQL.  I run it.
(may have to massage it a bit which is fine as long as you don't change
anything material.  For instance, I usually have to insert the library name
into the statement.)

Phil

> -----Original Message-----
> From: rpg400-l-admin@midrange.com [mailto:rpg400-l-admin@midrange.com]On
> Behalf Of Hatzenbeler, Tim
> Sent: Wednesday, August 29, 2001 12:48 PM
> To: 'rpg400-l@midrange.com'
> Subject: Recommended PTF's for Dynamic Sql in RPG?
>
>
> This message is in MIME format. Since your mail reader does not understand
> this format, some or all of this message may not be legible.
> --
> [ Picked text/plain from multipart/alternative ]
>
> Were running r4.5 with just latest cumulative tape..
>
> But for somereason, I can't get a dynamic sql statement to work, it just
> seems to hang on the fetch command...
>
> It's a simple Join, with one field, and the where clause is looking for a
> match using an item code on table a , and a department code on table B.
>
> This works fine from the command link, and also as a declared
> statement with
> the fields hardcoded..
>
> This is the declared version that did work...
>
> c*exec sql
> c* declare  c1 cursor for
> c*      select *
> c*
> c*      from (prdjrnusg a join jpldcusgsm b on
> c*              a.pjstr# = b.jpstr#)
> c*
> c*      where (a.pjhdat between 37110 and 37115) and
> c*      a.pjprod in ('1804','1310','2153') and
> c*      b.rddid = 'LAB'
> c*end-exec
>
> And this is the one that doesn't... I took out all the variables, to test
> this...
>
> And it's very likely (probable) that the reason it's not working,
> is because
> I might have a coding error, so I'm including most of the relavant parts..
>
> Dprdjrnusg      E DS                  extname(PRDJRNUSG) inz
> d                                     prefix(a_)
> Djpldcusgsm     E DS                  extname(jpldcusgsm)  inz
> d                                     prefix(b_)
>
> d sql             s           1000    varying
>
> c/exec sql
> c+ declare  c1 cursor for DynSqlStmt
> c/end-exec
>
> c                  eval      sql=
> c                            'Select * ' +
> c                            'From (prdjrnusg  a  JOIN '+
> c                                  'jpldcusgsm b  '+
> c                                  'ON a.pjstr# = b.jpstr#) '+
> c                            'WHERE (a.pjhdat between 37110'+
> c                            ' AND 37115) ' +
> c                            'and a.pjprod in '  +
> c                            '(''1804'',''1310'',''2153'')' +
> c                            ' and b.rddid = ''LAB'' '
> c/exec sql
> c+ Prepare DynSqlStmt from :sql
> c/end-exec
>
> c/EXEC SQL   OPEN C1
> c/end-exec
>
> c                   dou       Done
> c/EXEC SQL
> c+ FETCH next from c1 INTO :prdjrnusg, :jpldcusgsm
> c/END-EXEC
>
> c                   if        sqlcod <> 0
> c                   leave
> c                   endif
>
>
>
> Below was the orginal non hardcoded version...
> c*                  eval      sql=
> c*                            'Select * ' +
> c*                            'From (PrdJrnUsg  a  JOIN '+
> c*                                  'JPLDCusgsm b  '+
> c*                                  'ON a.pjstr# = b.jpstr#) '+
>
> c*                            'WHERE (a.pjhdat between '+
> c*                                       %editc(startdateh:'Z') +
> c*                            ' AND ' +
> c*                                    %editc(EndDateH:'Z') + ') '+
>
> c*                  if        %trim(dspwhere) <> *blanks
> c*                  eval      sql = sql + 'AND '+%trim(dspwhere)
> c*                  endif
>
> Thanks, tim
> _______________________________________________
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
> or email: RPG400-L-request@midrange.com
>


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com