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

["Mangavalli, Ramanujam" <RamM@xxxxxxxxxxx>]

Tim,

I am no guru but I am interested in the SQLCOD values immediately after your
prepare and open. If you don't mind, can you check the value of SQLCOD and
SQLERM after prepare and after open?

I am hoping that the SQLCOD will give you a clue.

hth.


-----Original Message-----
From: Hatzenbeler, Tim [mailto:thatzenbeler@clinitech.net]
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