Re: Verifying SQL statement in RPG program.

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.