RE: Simple SQL FETCH

David,

Use SQLSTT instead of SQLCOD. The primary reason is that the values in
SQLSTT are controlled by an entity other than IBM and they don't change
between releases. SQLCOD is controlled by IBM and they can (and have)
changed them when they want to. Also, every error has a single SQLSTT
but multiple errors may trigger the same SQLCOD which makes it more
difficult to determine what went wrong when errors happen. SQLSTT's
starting with 00 are success codes and 01's are warnings you can
generally ignore (they are things like automatic data conversion
happening). The InfoCenter has a document that lists SQLCOD's and
SQLSTT's that you should review.

Using Select * is generally a bad idea. The reason is that if the table
you're going after changes (like someone gets the idea that the fields
need to be sorted alphabetically) and you miss compiling a program, you
may end up with weird problems. Also, only selecting the field names you
need generally performs better then selecting everything. This is
especially true when doing remote access to another system or when all
of the fields you care about are in the index the query optimizer
picks).

You need to explicitly close the cursor unless you are absolutely sure
that the OS will close it for you when the program ends. This means
being very careful with compile options and activation groups.

Finally, when you do your selects, always specify the table name and
never use the name of a logical file.

Matt

-----Original Message-----
From: rpg400-l-bounces+matt.haas=cengage.com@xxxxxxxxxxxx
[mailto:rpg400-l-bounces+matt.haas=cengage.com@xxxxxxxxxxxx] On Behalf
Of David Foxwell
Sent: Friday, November 02, 2007 11:22 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: Simple SQL FETCH

Hi

We are all new to imbedded SQL.

I'm looking into the possibility of standardizing a simple loop using a
cursor. What do you think of this code?

Does anyone have any standard ways of dealing with SQL error codes ?

Should I be using SQLCOD or SQLSTATE and why?

My code :


DOW GetClient ( ClientNumber );

more code...

ENDO


P GetClient B

D PI N


D ClientNumber
LIKE ( DFN_CLTNO ) CONST


D wSql S
512 VARYING


D wCursorOpen S N
STATIC


/FREE


IF NOT wCursorOpen;
EXSR OpenCursor;
ENDIF;


EXEC SQL

FETCH NEXT FROM C1 INTO : GlobalDS;


IF SQLCOD <> 0;
EXEC SQL
CLOSE C1;
wCursorOpen= *OFF;
RETURN *OFF;
ENDIF;


RETURN *ON;







BEGSR OpenCursor;

wSql = 'SELECT * 'FROM file, etc ' ;


EXEC SQL
PREPARE S1 FROM : wSql;


EXEC SQL
DECLARE C1 CURSOR FOR S1;


EXEC SQL
OPEN C1;


wCursorOpen= *ON;


ENDSR;

/END-FREE


P GetClient E