Re: As much as I hate showing my own stupidity more than once a day (I am flat out stuck) Embedded SQL

Doug,

Checking late into the discussion, so if this has been
asked before please disregard:

Why do you need to open a cursor? Wouldn't be easier (and
faster) just to do the select and sum into the RPG variable
at once?

Regards,

Luis Rodriguez


--- rpg400-l-request@xxxxxxxxxxxx wrote:

------------------------------

message: 5
date: Thu, 2 Aug 2007 08:28:54 -0500
from: "Doug Palme" <dpalme@xxxxxxxxxxx>
subject: RE: As much as I hate showing my own stupidity
more than once
a day (I am flat out stuck) Embedded SQL

I have commitment control turned off right now, otherwise
I will have record
locking issues...unless you have a suggestion for that
one :) Basically
this data is only held for the one report, when you run
the report a second
time, the CL clears the file and you start fresh......I
like to keep it
after the report is run, in case of data integrity issues
I can still see
the raw information.



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Pete Helgren
Sent: Wednesday, August 01, 2007 8:53 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: As much as I hate showing my own stupidity
more than once a day
(I am flat out stuck) Embedded SQL

Doug,

This is ringing a bell in my head but I can't remember
exactly what. I
vaguely remember something either with looking at
environment settings
in the strsql vs the embedded sql (commitment control?)
OR with the
formatting of the host variables. I think I hard coded
the values in
the embedded SQL to start with and then worked backward
until I figured
it out. Whatever it was, the SQL was returning no
records and that is
what was causing the null value. But it was some SQL
setting that I
added to the RPG.

I wish my memory was better......sheesh!

Pete


Doug Palme wrote:

I am reading a file and using the values within that

file to create an SQL

that will go out and select a sum of sales figures for

a particular

customer.



Here is the relevant SQL code:



C*---Prepare SQL Statement

C/EXEC SQL

C+ DECLARE C1 CURSOR FOR

C+ SELECT SUM(SLEXT$) FROM SLSTRNL5

C+ WHERE SLACCT= :CUSTNO

C+ AND SLTDAT > :ZBDATEN

C+ AND SLTDAT <= :ZEDATEN

C+ AND SLCLAS > 1

C+ AND SLSHRT = ' '

C/END-EXEC





I then open the cursor with the following:



C*---Open the Cursor

C/EXEC SQL

C+ OPEN C1

C/END-EXEC



I then am running a fetch statement with the following:



C*---Fetch the Result and place into Variable

C/EXEC SQL

C+ FETCH C1 INTO :ZSUM

C/END-EXEC



ZSUM is defined as an 9,2 on the D spec



Everything so far, at least to my eyes looks good...



However before I process that ZSUM data (output to a

report) I check the

SQLCOD and I am getting a return code of -305..I have

checked the
variables

that I am passing in, such as CUSTNO (defined on the D

SPEC correctly),

ZBDATEN and ZEDATEN are also defined on the D SPEC

correctly...so I have
no

clue what variable the system is balking at.



Any assistance would be appreciated.



Douglas

Luis Rodriguez

IBM Certified Systems Expert
eServer i5 iSeries Technical Solutions