No Luck, I gave up for now RE: Recommended PTF's for Dynamic Sql in RPG?

["Hatzenbeler, Tim" <thatzenbeler@xxxxxxxxxxxxx>]

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
>