No Luck, I gave up for now RE: 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 ]
No i'm not journaling, but I do have the commit option set to *none durring
the compile...

I did scuttle the program, and re-wrote it with native io functions in
rpg... I did save the off the code incase I have some extra time in the
future to keep debugging it.

But thanks for your response...  tim

> -----Original Message-----
> From: DeLong, Eric [SMTP:EDeLong@Sallybeauty.com]
> Sent: Tuesday, September 04, 2001 10:58 AM
> To:   'rpg400-l@midrange.com'
> Subject:      RE: Recommended PTF's for Dynamic Sql in RPG?
>
> Tim,
>
> Are you journaling? If not, make sure you're not running into a commitment
> control problem.
> Just a thought......
>
> Eric DeLong
> Sally Beauty Company
> MIS-Sr. Programmer/Analyst
> 940-898-7863 or ext. 1863
>
>
>
> -----Original Message-----
> From: Hatzenbeler, Tim [mailto:thatzenbeler@clinitech.net]
> Sent: Thursday, August 30, 2001 12:08 PM
> To: 'rpg400-l@midrange.com'
> Cc: 'RamM@Mvmills.com'
> Subject: RE: Recommended PTF's for Dynamic Sql in RPG?
>
> This is after the :
> c+ declare  c1 cursor
> c+      for DynSqlStmt
> ************************************
> > EVAL sqlerm
>
>   SQLERM =
>
>             ....5...10...15...20...25...30...35...40...45...50...55...60
>
>        1   ' REPORT QTEMPTIMH      REPORT                             '
>
>       61   '          '
>
> > EVAL sqlcod
>
>   SQLCOD = 000000000.
>
>
> This is after the :
> ***************************************
> c/exec sql
> c+ Prepare DynSqlStmt from :sql
> c/end-exec
> c     sqlcod        dsply
>   SQLCOD = 000000000.
>
> > EVAL sqlerm
>
>   SQLERM =
>
>             ....5...10...15...20...25...30...35...40...45...50...55...60
>
>        1   ' DYNSQLSTMT                                                '
>
>       61   '          '
>
> > EVAL sql
>
>   SQL =
>
>             ....5...10...15...20...25...30...35...40...45...50...55...60
>
>        1   'Select * From (prdjrnusg  a inner join jpldcusgsm b  ON a.pj'
>
>       61   'str# = b.jpstr#) where (a.pjhdat between 37110 and 37115) an'
>
>      121   'd a.pjprod in ('1804','1310','2153') and b.rddid = 'LAB'    '
>
>      181   '                                                            '
>
>      241   '                '
>
>
> Bottom
> This is after the :
> c/EXEC SQL
> c+ open C1
> c/end-exec
> ***************************************
> > EVAL sqlerm
>
>   SQLERM =
>
>             ....5...10...15...20...25...30...35...40...45...50...55...60
>
>        1   ' C1                 PRDJRNUSG  CADBFA                    '
>
>       61   '          '
>
> > EVAL sqlcod
>
>   SQLCOD = 000000000.
>
>
> >
> > 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 line, 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
>