Compile error / SQLRPGLE

[PureBluff]

Hello,

Trying to write my first embedded SQLRPGLE in AS/400 & getting a compile error. It's a very simple query I'm trying to run so there's no issues.

SQL: Select * From warefil_ml/warsthd Where CLNTST = 'FD'

In PDM/SEU I have;

*************** Beginning of data *************************************
0022.00 C/EXEC SQL
0023.00 C+ SELECT *
0023.01 C+ INTO :SQLtmp5
0023.02 C+ FROM WARefil_ML/WARSTHD
0024.00 c+ WHERE CLNTST = FD
0025.00 c/end-exec
****************** End of data ****************************************

Now, I'm not sure why I need the into statement, but the compiler throws an error if it's missing (I only want to read the data) With it inserted I get

SQL0312  30       3  Position 15 Variable WARSTHD not defined or not usable.

Can anyone shed light where I'm going wrong? I couldn't reply to my original thread for some reason.

Thanks

[tomliotta]

Essentially, SELECT doesn't "read" data; it "selects" it (usually for reading). Once SELECTed, it needs to be FETCHed (or "read").

SELECT ... INTO not only "selects" but also does the "read".

There can be some confusion because most clients that process SQL statements also automatically add both a "read" and either a "display" or "print" action. When you're writing the client program, you're responsible for adding those actions that you want to happen.

[skattie]

i don't think that is right, PureBluff you need to define WARSTHD, e.g.

dwarsthd          s             20a

I'd give it a different name from the table to make it easier to see where the error lies, and the FD probably needs to be in quotes 'FD'

[PureBluff]

I need to research the D Spec, as try as I may I can't get past the editor erroring that certain things should be blank bit aren't.

The editor isn't the most user friendly and the fields referenced don't seem too easy to identify.

I'll get past it, eventually.

Thanks for your help!

[tomliotta]

One major likelihood is that you're using an old editor that isn't (and never will be) updated for more current syntax. If you're using the "green-screen" editor, you will often get error messages from syntax that the editor doesn't understand.

A second major likelihood is that the requirement for very specific positioning isn't yet automatic to you. As versions of RPG get newer, the reliance on positioning is lessened. From your error, this seems most likely. The editor does have prompting ability that results in statement elements being properly placed on source lines. But even becoming familiar with the meaning of the prompted fields can take time and not seem very sensible.

Using an appropriate editor running on your PC could be some help with both of those possibilities. Whether or not you have such an editor available...?

[PureBluff]

Hi Tom,

I'm using a green screen editor, with the prompts on F23, but the fields are somewhat vague to say the least, that's what I know I need to comprehend before I can progress.

I'm studying this in my free time outside of my contracted work & had a day off from extra curricular things to spend time with my wife tonight so I've not spent any time on learning D Spec / positioning and structures.

This is what the editor looks like, it's rather unsightly!

[PureBluff]

[taherte001]

You either need to define the length and internal data type (a=alpha,s=signed, p=packed, or blank=alpha) or make the field "like" another field. If you move your cursor to any field and press F1 you should get some help in a pop-up window.

[Barbara Morris]

PureBluff, try defining the warsthd variable like this (indent at least 2 spaces, so you start in column 8 or later):

dcl-s warsthd char(20); // or whatever type it should be, see URL below

http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/rzasd/datatypekw.htm

SEU will complain about this syntax, but if you save your changes (enter the SAVE command on the command line at the top of SEU), you can try compiling it. If it compiles, then you can use the free-form version of declarations instead of the icky fixed-form version.

For sure you should be able to use the free-form version of calculations. Instead of coding

C/EXEC SQL whatever bla bla bla
C+ more stuff in the statement
C/END-EXEC

code like this (starting in column 8 or later)

  exec sql whatever bla bla bla
         more stuff in the statement;

To stop SEU from complaining, you can turn off syntax checking using F13 and putting N for "When added/modified".

I think you'll be happier coding in free-form syntax without any help from SEU rather than trying to learn the fixed-form syntax.

You can find lots of examples of free-form RPG code. Try googling like this if you just want free-form examples using embedded SQL:

dcl-s ctl-opt "exec sql" -"free form"

Or like this, if you want to find articles on how to code free-form RPG.

dcl-s ctl-opt

[PureBluff]

Thanks everyone,

Definitely a lot for me to go at when I get back around to it later on. It's very much appreciated.

[skattie]

Hi
If you cut and paste the code as described earlier it will compile

dwarsthd          s             20a

If not post the compile error here
When you exit the editor select Y to change, and N to return to editing

[PureBluff]

It didn't when I tried, only got remote access on my phone at the minute though...

From checking, it did compile somewhat but errored on RNF0723 - compiler cant see a way for a program to end. As I say, only working remotely at the minute so will look later.

Thanks

[B.Hauser]

It is really difficult to write embedded SQL programs in RPG if you don't have any experiences with the programming language RPG.
A RPG program must be explicitly ended with the Last Record Indicator (*INLR) set to on (*INLR = *ON) or with RETURN.

Birgitta

[PureBluff]

Hi Birgitta, I had already got that bit, although I set it at the start of the RPG (I trust this isn't an issue as I understand it, it tells it to end when all executable code had been run?) - Well it didn't error on that.

Finally got around to looking at it again locally, it's passing the SQL pre-compile, but the syntax is definitely way out;

 Columns . . . :    6  76            Edit                     DWMS000/QRPGLESRC
 SEU==>                                                                   SQLV4
 FMT ** ... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+.
        *************** Beginning of data *************************************
0001.00  /free                                                                  
0002.00   *inlr = *on;                                                          
0003.00   dcl-ds sqltemp1 char(10) ;                                            
0004.00   exec sql                                                              
0005.00    SELECT stn#st INTO :sqltemp1 FROM WARefil_ML/WARSTHD WHERE stn#st    
0006.00    = :STEVEN;                                                          
        ****************** End of data ****************************************

Errors:

     31 D  SQFRD          C                   CONST(2)                          
     32 D  SQFCRT         C                   CONST(8)                          
     33 D  SQFOVR         C                   CONST(16)                         
     34 D  SQFAPP         C                   CONST(32)                         
     35  /free                                                                  
     36   *inlr = *on;                                                          
     37   dcl-s sqltemp1 char(10) ;                                            
 ======>     a                                                                  
 *RNF5347 30 a      000300  An assignment operator is expected with the EVAL operator.
    38   exec sql                                                             
======>       aaa                                                             
*RNF5347 30 a      000400  An assignment operator is expected with the EVAL op
    39    SELECT stn#st INTO :sqltemp1 FROM WARefil_ML/WARSTHD WHERE stn#st   
    40    = :STEVEN;                                                          
       * * * * *   E N D   O F   S O U R C E   * * * * *                      
 5722WDS V5R2M0  020719 RN        IBM ILE RPG             DWMS000/SQLV4       
          A d d i t i o n a l   D i a g n o s t i c   M e s s a g e s         
 Msg id  Sv Number Seq     Message text                                       
*RNF7030 30     37 000300  The name or indicator DCL is not defined.          
*RNF7030 30     38 000400  The name or indicator EXEC is not defined.         
*RNF7030 30     38 000400  The name or indicator SQL is not defined.          
 * * * * *   E N D   O F   A D D I T I O N A L   D I A G N O S T I C   M E S S
 5722WDS V5R2M0  020719 RN        IBM ILE RPG             DWMS000/SQLV4       
                          C r o s s   R e f e r e n c e                       
      File and Record References:

[Barbara Morris]

You have to put all your declarations above your calculations. Move the DCL-DS line above the assignment to *INLR.

Except, I think you mean that to be DCL-S (declare standalone field) rather than DCL-DS (declare data structure).

Finally, avoid naming your own variables starting with "SQL" in SQLRPGLE source. The SQL precompiler reserves names starting with "SQL" for itself, although it doesn't enforce it.

(By the way, you don't need to code /FREE or /END-FREE any more, even if you use fixed-form D specs starting in column 6.)