RE: First pure SQL program not running.

Bob,

I realize that the code below is a snippet, but you need to OPEN the cursor that you've created and then process the rows within by FETCHing them. Most likely in a do-loop of some kind.

D mbb2cp
d mbb2cp e ds extname(mbb2cp)

/free
EXEC SQL Declare @C1 CURSOR for SELECT B2OEMN FROM CMALIB/MBB2CP;
Exec sql OPEN @C1;

// SQLCODE = 0 means good read, 100 means row not found, EOF

Dou SQLCode <> 0; SQLCODE = 0 means good read, 100 means row not found, EOF

Exec sql FETCH @C1 into :MBB2CP;

if sqlcode <> 0;
iter;
endif;

...do work

Enddo;

exec sql close @C1;

Return;
/END-FREE

Additionally, if you are not on V7R1, you should only reference the physical file, not a logical, in your FROM clause. Using the Logical causes your SQL to be processed by the less-efficient CQE (Classic Query Engine) rather that the SQE engine.

Steve Needles

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Bob Anderson
Sent: Friday, June 04, 2010 7:30 AM
To: RPG programming on the IBM i / System i
Subject: First pure SQL program not running.

Hello,
I am writing new from scratch SQL program. I have created the source as SQLRPGLE and written the non EXEC SQL sections in free form. This program builds a cursor on a logical file that is attached to a MAPICS file to read the entire file and update another with the results from the first cursor.
It has compiled cleanly with the create sql program.
However when I do a call program it starts and just sits there I have changed the program to declare the cursor and then set *INLR to on and do a return but still the program just sits there I have let get to 1.1 on the CPU in wrkactjob and then killed it. This really has me stumped.
Here is what I am talking about:
//********************************************************************
// Calculate UPC (SCC-14) check digit.
//*******************************************************************
/Free
BEGSR UPC5Main;
/END-FREE
C/EXEC SQL
C+ Declare @C1 CURSOR for SELECT B2OEMN FROM CMALIB/MBB2CP99
C/END-EXEC
/Free
*INLR = *ON;
RETURN;
/END-FREE


Thanks in advance for any help you can supply


Bob Anderson

IT Manager
Kent Sporting Goods Inc.
433 Park Ave. S
New London OH 44851
419-929-7021 x315
ï Please consider the environment before printing this e-mail.