MIDRANGE dot COM Mailing List Archive



Home » WDSCI-L » September 2006

Re: Verifying SQL statement in RPG program.



fixed

Hi Joe,

As previously mentioned, the code that runs syntax checking for embedded
SQL runs on the iSeries, and is the same code as in SEU, so syntax errors
in the two should be the same.
I could not reproduce with the previously specified command:
     C/EXEC SQL
      +  Declare C2 Cursor for
      +   select a.fitemf, b.wkctrf, sum(a.qtyrcf),
      +   sum(b.sslhuf), sum(b.slhtdf), sum(b.srlhuf * a.qtyrcf),
      +   sum(b.rlhtdf), c.pfdsc2
      +   from cstsum a join labeff b
      +   on a.ordnof = b.ordnof and a.fitemf = b.fitemf
      +   join facmstl0 c on b.wkctrf = c.pfidc2
      +   where a.ostatf between '45' and '55' and a.qtyrcf > 0
      +   and a.latdtf >= :SDATE and a.latdtf < :LRDATE
      +   and a.fitemf = :ITEM# and b.opstcf <> 00
      +   group by a.fitemf, b.wkctrf, c.pfdsc2
     C/END-EXEC
using a V5R4 host.

Also previously mentioned, I would also note that for the RPG parser to
properly detect SQL, the member type be SQLRPGLE.  We already have a
requirement to remove this restriction.

thanks,

Violaine Batthish
WebSphere Development Studio Client, IBM Toronto Lab



wdsci-l-bounces@xxxxxxxxxxxx wrote on 09/15/2006 04:21:04 PM:

Actually, I think it's a "bug" (if you want to call it that) in the
editor/compiler.  According to the SQL reference:

"SQL statements coded in an RPG for iSeries program must be placed in the
calculation section. This requires that a C be placed in position 6. SQL
statements can be placed in detail calculations, in total calculations,
or
in an RPG for iSeries subroutine. The SQL statements are run based on the
logic of the RPG for iSeries statements."

That tells me that ALL SQL statements require a C in position 6.  The
page
goes on to say:

"The keywords EXEC SQL indicate the beginning of an SQL statement. EXEC
SQL
must occupy positions 8 through 16 of the source statement, preceded by a
/
in position 7. The SQL statement may start in position 17 and continue
through position 74.

The keyword END-EXEC ends the SQL statement. END-EXEC must occupy
positions
8 through 16 of the source statement, preceded by a slash (/) in position
7.
Positions 17 through 74 must be blank."

Note that they don't repeat the bit about C in position 6; it's assumed
by
the previous paragraph.  Later, though, the definition of continuation
specs
is a little ambiguous, because it's on a different page:

"When additional records are needed to contain the SQL statement,
positions
9 through 74 can be used. Position 7 must be a + (plus sign), and
position 8
must be blank."

Because the original paragraph about the C in position 6 isn't on this
page,
I guess you COULD interpret that to mean that position 6 could be blank,
but
it really doesn't say that.  I think it was still meant to require a "C"
in
that position; you certainly can't put any other character there.  So the
idea of the C being optional is something that really isn't spelled out
and
so I can see why the WDSC developers don't allow it.

Joe

From: Alan Campin

Just wondering... does it work if you put a C in position 6 of every
line?
It may be that WDSC is a little stricter about requiring a
specification
type in non-freeform SQL.
Just wondering... does it work if you put a C in position 6 of every
line?
It may be that WDSC is a little stricter about requiring a
specification
type in non-freeform SQL.

Oh, putting making it c+ definitely fixes the problem but I was more
interested to know if other people had the same problem and is a it a
bug or a feature. I would guess it is a bug since the compiler has no
problem with it.

I just have a lot of code from previous people done this way. I am so
ready for free format SQL.







Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact