sql_embedded [read-only].pdf

EMBEDDED SQL

2/10/2003

DMC Consulting, 1993

EMBEDDED SQL

Copyright 1992, 1993 BY DMC CONSULTING, INC.ALL RIGHTS RESERVED

5650 West Central Avenue, Suite B, Toledo, OH 43615AS/400 is a trademark of International Business Machines Corporation

EMBEDDED SQL

2/10/2003


Embedded SQL Basics

NEW

COMMANDS

RPG IV

SQL

COMMANDS

PRE-COMPILERNEW

SOURCE

TYPE

EMBEDDED SQL

2/10/2003


Why Embed SQL in your RPG?

COMPILE TIMEVs.

RUN TIME

F Sp

ecs

EMBEDDED SQL

2/10/2003


Work with Members Using PDM DMC41

File . . . . . . QRPGLESRC

Library . . . . DMCSQLLIB Position to . . . . .

Type options, press Enter.

2=Edit 3=Copy 4=Delete 5=Display 6=Print 7=Rename

8=Display description 9=Save 13=Change text 14=Compile 15=Create module...

Opt Member Type Text

SQLPROC SQLRPGLE Test program containing SQL statements

SQLTEST SQLRPGLE Test program containing SQL statements

STOREDPRC1 SQLRPGLE Test program containing SQL statements




TSTRPGPRC SQLRPGLE Test program containing SQL statements

TSTRPGPRC2 SQLRPGLE Test program containing SQL statements

More...

Parameters or command

===>

F3=Exit F4=Prompt F5=Refresh F6=Create

F9=Retrieve F10=Command entry F23=More options F24=More keys

New Source Type

EMBEDDED SQL

2/10/2003


Syntax

C/EXEC SQL

C+

C/END-EXEC

:field-name

C/EXEC SQL INCLUDE member-name

C/END-EXEC

?

EMBEDDED SQL

2/10/2003


Syntax

RPG IV variables you can’t use as a HOST variable:

Pointers

Tables

UDATE

UDAY

UMONTH

UYEAR

Look-ahead fields

Named constants

Definitions requiring the resolution of %SIZE or %ELEM

Avoid creating fields that start with:

'SQ', 'SQL', 'RDI', or 'DSN’

EMBEDDED SQL

2/10/2003


SQL Pre-Compiler

CRTSQLxxx

The pre-compiler will do the following:

Looks for SQL statements and for the definition of host variable names.

Verifies that each SQL statement is valid and free of syntax errors.

Validates the SQL statements using the description in the database.

EMBEDDED SQL

2/10/2003


SQL Pre-Compiler

SQLAIDSQLABC

SQLCOD / SQLCODE

SQLERLSQLERM

SQLERPSQLERR

SQLSTT / SQLSTATESQLWRN

The SQL pre-compiler adds fields such as:

EMBEDDED SQL

2/10/2003


Create SQL ILE RPG Object (CRTSQLRPGI)

Type choices, press Enter.

Object . . . . . . . . . . . . . > RPG100 Name

Library . . . . . . . . . . . > DMCSQLLIB Name, *CURLIB

Source file . . . . . . . . . . > QRPGLESRC Name, QRPGLESRC

Library . . . . . . . . . . . > DMCSQLLIB Name, *LIBL, *CURLIB

Source member . . . . . . . . . > RPG100 Name, *OBJ

Commitment control . . . . . . . *CHG *CHG, *ALL, *CS, *NONE...

Relational database . . . . . . *LOCAL

Compile type . . . . . . . . . . > *PGM *PGM, *SRVPGM, *MODULE

Listing output . . . . . . . . . *NONE *NONE, *PRINT

Text 'description' . . . . . . . *SRCMBRTXT

Bottom

F3=Exit F4=Prompt F5=Refresh F10=Additional parameters F12=Cancel

F13=How to use this display F24=More keys

CRTSQLRPGI

EMBEDDED SQL

2/10/2003




Target release . . . . . . . . . *CURRENT *CURRENT, *PRV, VxRxMx

INCLUDE file . . . . . . . . . . *SRCFILE Name, *SRCFILE

Library . . . . . . . . . . . *LIBL Name, *LIBL, *CURLIB

Allow copy of data . . . . . . . *YES *YES, *NO, *OPTIMIZE

Close SQL cursor . . . . . . . . *ENDACTGRP *ENDMOD, *ENDACTGRP

Allow blocking . . . . . . . . . *READ *READ, *NONE, *ALLREAD

Delay PREPARE . . . . . . . . . *NO *YES, *NO

Severity level . . . . . . . . . 10 0-40

Date format . . . . . . . . . . *JOB *JOB, *USA, *ISO, *EUR...

Date separator character . . . . *JOB *JOB, /, ., ,, -, ' ', *BLANK

Time format . . . . . . . . . . *HMS *HMS, *USA, *ISO, *EUR, *JIS

Time separator character . . . . *JOB *JOB, :, ., ,, ' ', *BLANK

Replace . . . . . . . . . . . . > *YES *YES, *NO

RDB connect method . . . . . . . *DUW *DUW, *RUW

Default Collection . . . . . . . *NONE Name, *NONE

More...

F3=Exit F4=Prompt F5=Refresh F12=Cancel F13=How to use this display

F24=More keys

CRTSQLRPGI

EMBEDDED SQL

2/10/2003




Package . . . . . . . . . . . . *OBJ Name, *OBJ

Library . . . . . . . . . . . *OBJLIB Name, *OBJLIB

IBM SQL Flagging . . . . . . . . *NOFLAG *NOFLAG, *FLAG

ANS flagging . . . . . . . . . . *NONE *NONE, *ANS

Print file . . . . . . . . . . . QSYSPRT Name

Library . . . . . . . . . . . *LIBL Name, *LIBL, *CURLIB

Debugging view . . . . . . . . . *NONE *SOURCE, *NONE

User profile . . . . . . . . . . *NAMING *NAMING, *USER, *OWNER

Dynamic User Profile . . . . . . *USER *USER, *OWNER

Sort Sequence . . . . . . . . . *JOB Name, *HEX, *JOB, *JOBRUN...

Library . . . . . . . . . . . Name, *LIBL, *CURLIB

Language id . . . . . . . . . . *JOB *JOB, *JOBRUN...

To source file . . . . . . . . . QSQLTEMP1 Name

Library . . . . . . . . . . . QTEMP Name, *LIBL, *CURLIB

Bottom

F3=Exit F4=Prompt F5=Refresh F12=Cancel F13=How to use this display

F24=More keys

CRTSQLRPGI

EMBEDDED SQL

2/10/2003


Embedding SQL Statements

C KEY CHAIN FILE

C IF %FOUND

C/Exec SQL SELECT MDESC

C+ INTO :W_DESC

C+ FROM ITMMST

C+ WHERE MITEM = :W_ITEM

C/End-exec

C ENDIF

C RETURN

EMBEDDED SQL

2/10/2003


Embedding SELECT

*

DW_ITEM S 9P 0

DW_DESC S 40

* Run the SQL select

C 'ITEM?' DSPLY W_ITEM

C/Exec SQL SELECT MDESC

C+ INTO :W_DESC

C+ FROM ITMMST


C/End-exec

C W_DESC DSPLY

C RETURN

Selecting ONE field from ONE record

EMBEDDED SQL

2/10/2003


Embedding SELECT

DW_ITEM S 9P 0

DW_PRICE S 7P 2

DW_DESC S 40


C 'ITEM?' DSPLY W_ITEM

C/Exec SQL SELECT MDESC, MPRICE

C+ INTO :W_DESC, :W_PRICE

C+ FROM ITMMST


C/End-exec

C W_DESC DSPLY

C W_PRICE DSPLY

C RETURN

Selecting TWO fields from ONE record

EMBEDDED SQL

2/10/2003


Embedding SELECT

DW_DATA DS EXTNAME(ITMMST)


C 'ITEM?' DSPLY MITEM

C/Exec SQL SELECT *

C+ INTO :W_DATA

C+ FROM ITMMST

C+ WHERE MITEM = :MITEM

C/End-exec

C MDESC DSPLY

C MPRICE DSPLY

C RETURN

Selecting MANY fields from ONE record

EMBEDDED SQL

2/10/2003


EXAMPLE #1

DW_CUST S 6P 0

DW_NAME S 40

C 'CUST' DSPLY W_CUST

C/EXEC SQL

C+ SELECT CNAME INTO :W_NAME

C+ from CUST WHERE CNUMBER = :W_CUST

C/END-EXEC

C 'NAME IS' DSPLY W_NAME

C EVAL *INLR = *ON

C RETURN

EMBEDDED SQL

2/10/2003


Embedding INSERT

* Run the SQL INSERT W/VALUES

C/Exec SQL INSERT INTO CUST

C+ VALUES(7, ’The Fruit Stand’,’’,’’,

C+ ’Miami’,‘FL’,’’,’’)

C/End-exec

* Run the SQL INSERT W/SUB-SELECT

C/Exec SQL INSERT INTO TESTLIB/CUST

C+ SELECT * FROM PRODLIB/CUST

C/End-exec

EMBEDDED SQL

2/10/2003


Embedding INSERT

* Run the SQL INSERT W/n ROWS

DW_FIELDS DS OCCURS(10)

D W_ITEM 9 0

D W_DESC 40

D W_WGT 4 2

D W_PRICE 9 2

or

D W_FIELDS E DS EXTNAME(FILE)

D OCCURS(10)


C/Exec SQL INSERT INTO ITMMST

C+ 10 ROWS VALUES(:W_FIELDS)

C/End-exec

EMBEDDED SQL

2/10/2003


Embedding DELETE & UPDATE

* Run the SQL DELETE

C/Exec SQL DELETE FROM CUST

C+ WHERE CNUMBER = :CUSTNBR

C/End-exec

* Run the SQL UPDATE

C/Exec SQL UPDATE CUST

C+ SET CSTATE = ‘FL’

C+ WHERE CCITY = ‘Miami’

C/End-exec

C/Exec SQL

C+ UPDATE CUSTWORK SET ROW = (SELECT * FROM CUST WHERE

C+ CUSTWORK.NAME = CUST.NAME)

C/End-exec

EMBEDDED SQL

2/10/2003


EXAMPLE #2

DW_CUST E DS EXTNAME(CUST)

C EVAL CNUMBER = 1111

C EVAL CNAME = 'The Garden Grill'

C EVAL CSTATE = 'OH'

C EVAL CCITY = 'Toledo'

C/EXEC SQL

C+ INSERT INTO CUST

C+ VALUES(:W_CUST)

C/END-EXEC

EMBEDDED SQL

2/10/2003


RPG200 - LAB #2

C/EXEC SQL

C+ UPDATE CUST

C+ SET CCITY = 'Miami' WHERE CNUMBER = 1111

C/END-EXEC

C/EXEC SQL

C+ DELETE FROM CUST

C+ WHERE CNUMBER = 1111

C/END-EXEC

C EVAL *INLR = *ON

C RETURN

EMBEDDED SQL

2/10/2003


Using PREPARE

DW_SQL S 1000

DW_SELECT C ‘SELECT MITEM ’

DW_FROM C ‘FROM ITMMST ’

DW_WHERE C ‘WHERE ’

DW_FIELD S 10

DW_VALUE S 30

C ’FIELD?' DSPLY W_FIELD

C ’VALUE?' DSPLY W_VALUE

C EVAL W_SQL= W_SELECT + W_FROM +

C W_WHERE + W_FIELD +

C ‘ = ‘ + W_VALUE

OR

C ‘ = ‘’’ + W_VALUE + ‘’’’

EMBEDDED SQL

2/10/2003


Using PREPARE

C/Exec SQL PREPARE @S1 from :W_SQL

C/End-exec

C/Exec SQL DECLARE @C1 CURSOR FOR @S1

C/End-exec

C/Exec SQL OPEN @C1

C/End-exec

C DOU SQLCOD <> *zero

C/Exec SQL FETCH @C1 INTO :W_VALUE

C/End-exec

C ENDDO

C/Exec SQL CLOSE @C1

C/End-exec

EMBEDDED SQL

2/10/2003


Using PREPARE

DW_SQL S 1000

DW_DELETE C ‘DELETE ’


DW_WHERE C ‘WHERE ’

DW_FIELD S 10

DW_VALUE S 30

C ’FIELD?' DSPLY W_FIELD

C ’VALUE?' DSPLY W_VALUE

C EVAL W_SQL= W_DELETE + W_FROM +

C W_WHERE + W_FIELD +

C ‘ = ‘ + W_VALUE

EMBEDDED SQL

2/10/2003


Using PREPARE


C/End-exec

C/Exec SQL EXECUTE @S1

C/End-exec

C/Exec SQL EXECUTE IMMEDIATE :W_SQL

C/End-exec

EMBEDDED SQL

2/10/2003


EXAMPLE #3 (1 of 3)


DW_CITY S 20

DW_SQL S 1000

C 'CITY' DSPLY W_CITY

C EVAL W_SQL = 'SELECT * FROM +

C CUST WHERE CCITY = '

C + '''' + W_CITY + ''''

C/EXEC SQL

C+ PREPARE S1 FROM :W_SQL

C/END-EXEC

C/EXEC SQL

C+ DECLARE C1 CURSOR FOR S1

C/END-EXEC

EMBEDDED SQL

2/10/2003


EXAMPLE #3 (2 of 3)

C/EXEC SQL

C+ OPEN C1

C/END-EXEC

C/EXEC SQL

C+ FETCH C1 INTO :W_CUST

C/END-EXEC

C DOW SQLCOD <> 100

C 'NAME IS' DSPLY CNAME

C/EXEC SQL


C/END-EXEC

C ENDDO

EMBEDDED SQL

2/10/2003


EXAMPLE #3 (3 of 3)

C/EXEC SQL

C+ CLOSE C1

C/END-EXEC

C/FREE

W_SQL = 'UPDATE CUST SET +

CPHONE = ' + '''555-1212''' +

' WHERE CZIP <> ''32509'''

C/END-FREE

C/EXEC SQL

C+ EXECUTE IMMEDIATE :W_SQL

C/END-EXEC

C EVAL *INLR = *ON

C RETURN

EMBEDDED SQL

2/10/2003


Embedding SELECT

Multiple Row Fetch

It is possible to read more than one record at a time, loading a MODS with values for several records at once. This is more efficient than the standard one record at a time method.

* Run the SQL SELECT for n ROWS


D W_ITEM 9 0

D W_DESC 40

D W_WGT 4 2

D W_PRICE 9 2

C/EXEC SQL FETCH C1 FOR 10 ROWS

C+ INTO :W_FIELDS

C/END-EXEC

EMBEDDED SQL

2/10/2003


Using Parameter Markers

DW_SQL S 1000

DW_SELECT C ‘SELECT MITEM ’


DW_WHERE C ‘WHERE MITEM = ?’

DW_VALUE S 30

C ’ITEM?' DSPLY W_VALUE

C EVAL W_SQL= W_SELECT + W_FROM +

C W_WHERE

EMBEDDED SQL

2/10/2003




C/End-exec


C/End-exec

C/Exec SQL OPEN @C1 USING :W_VALUE

C/End-exec

C DOU SQLCOD <> *zero

C/Exec SQL FETCH @C1 INTO :W_VALUE

C/End-exec

C ENDDO

C/Exec SQL CLOSE @C1

C/End-exec

EMBEDDED SQL

2/10/2003



DW_SQL S 1000



DW_WHERE C ‘WHERE MITEM IN (?, ?)’

DW_VALUE1 S 30

DW_VALUE2 S 30

C ’ITEM?' DSPLY W_VALUE1



C W_WHERE


C/End-exec

C/Exec SQL EXECUTE @S1 USING :W_VALUE1, :W_VALUE2

C/End-exec

EMBEDDED SQL

2/10/2003



DW_SQL S 1000



DW_WHERE C ‘WHERE MITEM LIKE ?’

DW_VALUE1 S 30

DW_COMP S 30 VARYING


C EVAL %LEN(W_COMP) = %LEN(%TRIM(

C W_VALUE1))

C EVAL W_COMP = %TRIM(W_VALUE1)


C W_WHERE


C/End-exec

C/Exec SQL EXECUTE @S1 USING :W_COMP

C/End-exec

EMBEDDED SQL

2/10/2003


EXAMPLE #4 (1 of 2)


DW_CITY S 20

DW_STATE S 2

DW_SQL S 1000

C 'CITY' DSPLY W_CITY

C 'STATE' DSPLY W_STATE

C EVAL W_SQL = 'SELECT * FROM +

C CUST WHERE CCITY = '

C + '? OR CSTATE = ?'

C/EXEC SQL

C+ PREPARE S1 FROM :W_SQL

C/END-EXEC

C/EXEC SQL

C+ DECLARE C1 CURSOR FOR S1

C/END-EXEC

EMBEDDED SQL

2/10/2003


EXAMPLE #4 (2 of 2)

C/EXEC SQL

C+ OPEN C1 USING :W_CITY, :W_STATE

C/END-EXEC

C/EXEC SQL


C/END-EXEC

C DOW SQLCOD <> 100

C 'NAME IS' DSPLY CNAME

C/EXEC SQL


C/END-EXEC

C ENDDO

C/EXEC SQL

C+ CLOSE C1

C/END-EXEC

C EVAL *INLR = *ON

C RETURN

EMBEDDED SQL

2/10/2003


SQL Error Handling

SQLCA

The SQLCA is a set of variables that is updated after the execution of every SQL statement.

For RPG, this data structure is automatically added by the Pre-compiler.

The most commonly used SQL variable is SQLCOD.

It can be tested as follows:

0 - Success

Positive - WarningNegative - Error

EMBEDDED SQL

2/10/2003


SQL Error Handling

SQLERL - Contains the length of the text loaded into SQLERM.

SQLERM - Contains Message text for the SQLCOD value.

SQLERP - Contains the name of the Product and Module returning an

error.

SQLERRD - Array containing 6 diagnostic codes.

(1) - Last four digits of the CPF message if SQLCOD is negative.

(2) - Last four digits of the CPD message if SQLCOD is negative.

(3) - Number of rows affected.

(4) - Cost of execution, error message, or length of the row.

(5) - Position of a syntax error, +100 if EOF, or the number of parameter markers.

(6) - SQL completion code when SQLCOD is 0.

SQLWRN - String of 11, 1 byte flags.

SQLSTT - A return code for the most recent SQL statement.

EMBEDDED SQL

2/10/2003


Complex SQLs

Multiple Cursors

DOU SQLCOD = 100

FETCH @C1 INTO :W_VALUE

DOU SQLCOD = 100

FETCH @C2 INTO :W_VALUE

ENDO

ENDO

?... SQLCOD = ?

EMBEDDED SQL

2/10/2003


Complex SQLs

ADVANCED CURSORS

SCROLL - Allows forward and back movement of the

cursor.

DYNAMIC SCROLL - As Scroll but also allows update.

WITH HOLD - Allows COMMIT to be performed without

closing the cursor.

EMBEDDED SQL

2/10/2003


Complex SQLs

ADVANCED CURSORS

FETCH NEXT - positions cursor ON next record.

PRIOR - positions cursor ON previous record.

FIRST - positions cursor ON the first record

in the result table.

LAST - positions cursor ON the last record

in the result table.

EMBEDDED SQL

2/10/2003


Complex SQLs

BEFORE - positions cursor BEFORE the first

record in the result table.

AFTER - positions cursor AFTER the last

record in the result table.

CURRENT - no change.

RELATIVE - positions cursor on the record

specified by a host variable. A

negative number moves back x rows.

A positive value moves forward X

rows.

EMBEDDED SQL

2/10/2003


C+ DECLARE @QC1 DYNAMIC SCROLL CURSOR WITH HOLD

C+ FOR SQLSTMT

C+ FETCH PRIOR FROM @QC1 INTO :W_DATA1 :W_IND1,

C+ :W_DATA2 :W_IND2, :W_DATA3 :W_IND3

C+ FETCH RELATIVE -1 FROM @QC1 INTO :W_DATA1:W_IND1,

C+ :W_DATA2:W_IND2, :W_DATA3:W_IND3

C+ FETCH BEFORE FROM @QC1

Complex SQLs

ADVANCED CURSORS

EMBEDDED SQL

2/10/2003


C+ DECLARE @C2 DYNAMIC SCROLL CURSOR WITH HOLD FOR

C+ SELECT * FROM DATA FOR UPDATE OF LONGNAM2

C+ FETCH FIRST FROM @QC2 INTO :W_FIELDS

C+ UPDATE DATA SET LONGNAM2 = ‘FRED’

C+ WHERE CURRENT OF @C2

Complex SQLs

ADVANCED CURSORS

EMBEDDED SQL

2/10/2003


Complex SQLs

COMMITMENT CONTROL

UPDATE MAST SET OHQTY = OHQTY + :TRQTY

INSERT INTO HIST VALUES(:W_FIELDS)

DELETE FROM TRAN WHERE CURRENT OF @QC1

UPDATE MAST SET OHQTY = OHQTY + :TRQTY

INSERT INTO HIST VALUES(:W_FIELDS)

DELETE FROM TRAN WHERE CURRENT OF @QC1

UPDATE MAST SET OHQTY = OHQTY + :TRQTY UPDATE MAST SET OHQTY = OHQTY + :TRQTY

INSERT INTO HIST VALUES(:W_FIELDS) INSERT INTO HIST VALUES(:W_FIELDS)

DELETE FROM TRAN WHERE CURRENT OF @QC1DELETE FROM TRAN WHERE CURRENT OF @QC1

COMMIT

COMMIT

EMBEDDED SQL

2/10/2003


Complex SQLs

COMMITMENT CONTROL

+ DECLARE @QC1 DYNAMIC SCROLL CURSOR WITH HOLD

FOR SQLSTMT

C+ FETCH NEXT FROM @QC1 INTO :W_FIELDS

C DOW SQLCOD = *ZERO

C+ UPDATE MAST SET OHQTY = OHQTY + :TRQTY

C+ WHERE MAST.PART = :PART

C+ INSERT INTO HIST VALUES(:W_FIELDS)

C+ DELETE FROM TRAN WHERE CURRENT OF @QC1

C COMMIT

C+ FETCH NEXT FROM @QC1 INTO :W_FIELDS

C ENDDO

EMBEDDED SQL

2/10/2003


C/EXEC SQL

C+ UPDATE DATA SET LONGNAM2 = NULL WHERE NAME IS NOT NULL

C/END-EXEC

C/EXEC SQL

C+ SELECT * FROM DATA WHERE LONGNAM2 IS NULL

C/END-EXEC

Null Values

EMBEDDED SQL

2/10/2003


* Single Row FETCH testing for NULL values

DW_NAME S 30

DW_LONGNAM1 S 120

DW_LONGNAM2 S 120

DW_IND1 S 5U 0

DW_IND2 S 5U 0

C/EXEC SQL

C+ FETCH NEXT FROM @QC1 INTO :W_NAME :W_IND1,

C+ :W_LONGNAM1, :W_LONGNAM2 :W_IND2

C/END-EXEC

Null Values

EMBEDDED SQL

2/10/2003


* Multi Row FETCH testing for NULL values


D W_NAME 30

D W_LONGNAM1 120

D W_LONGNAM2 120

DW_IND DS OCCURS(10)

D W_IND1 5U 0

D W_IND2 5U 0

D W_IND3 5U 0

C/EXEC SQL FETCH @QC1 FOR 10 ROWS

C+ INTO :W_FIELDS :W_IND

C/END-EXEC

Null Values

EMBEDDED SQL

2/10/2003


SQL Descriptors

The SQLDA is used to determine

what fields will be returned by a

prepared SELECT statement before performing the fetch,

allowing you to tailor the number and size of values used in the

FETCH or OPEN statement.

SQLDA

SELECT * from ?

EMBEDDED SQL

2/10/2003


SQL Descriptors

SQLN - The number of occurrences of SQLVAR.

SQLD - Contains the number of occurrences of SQL_VAR being

used.

SQLVAR - Array containing descriptions of each variable

being used.

SQLTPE - Contains data type. Odd types are NULL capable.

SQLLEN - Contains the length of the variable.

SQLRES - Reserved area for boundary alignment.

SQLDATA - Contains a pointer to the address of the

variables.

SQLIND - Contains a pointer to an integer. A negative

value indicates NULL, otherwise it is NOT NULL.

SQLNAME - Contains column name, label, or system name.

SQLNAMELEN - Contains the length of SQLNAME.

EMBEDDED SQL

2/10/2003


SQL Descriptors

To use the IBM supplied SQLDA specify the following code in

your program:

C/EXEC SQL INCLUDE SQLDA

C/END-EXEC

SQL_VAR: is an array which holds the description of each

variable used in a FETCH or OPEN statement. The data

structure SQLVAR is used to parse the values in SQL_VAR.

SQL_NUM: is a numeric constant that must be defined before

the SQLDA is defined. It determines the number of variables

which will be dimensioned by the SQL_VAR array.

EMBEDDED SQL

2/10/2003


SQL Descriptors

DESCRIBE statement INTO data-area USING SYSTEM NAMES

The USING clause determines what values are used for column

labels.

USING NAMES (default if USING is omitted)

USING SYSTEM NAMES

USING LABELS

USING BOTH (NAMES and LABELS)

USING ALL (NAMES, LABELS, and SYSTEM NAMES)

For BOTH and ALL you must double or triple

the size of the SQLVAR array.

EMBEDDED SQL

2/10/2003


C/Exec SQL PREPARE @S1 from :W_SQL1

C/End-exec


C/End-exec

C/Exec SQL OPEN @C1 USING :W_FILE

C/End-exec

C/Exec SQL DESCRIBE @S1 INTO :SQLDA

C/End-exec

SQL Descriptors

DESCRIBE

EMBEDDED SQL

2/10/2003


C/Exec SQL FETCH @C1 USING DESCRIPTOR :SQLDA

C/End-exec

C DOW SQLCOD = *ZERO

C DO SQLD X

C EVAL PTR_DATA = SQLDATA(X)

C SELECT

SQL Descriptors

FETCH w/SQLDA

EMBEDDED SQL

2/10/2003


SQL Descriptors

SQLDA is only for programs that are

so dynamic that the program

knows nothing about the files and fields being used, at the time it is

written.

EMBEDDED SQL

2/10/2003


Summary

SQL contains powerful update capabilities. It provides the ability to

perform updates to specific records or make massive updates to every

record in a table.

Statements can be created statically, or dynamically. Static SQLs offer

better performance. Dynamic SQLs offer more flexibility.

The pre-compiler adds a significant amount of code to the program,

providing features such as the SQLCA, SQLCODE, and SQLSTATE.

A prepared SELECT statement must be processed with the DECLARE,

OPEN, and FETCH statements. Other prepared statements can simply be

executed.

Combining the flexibility of SQL with the robust RPG IV language, creates

a hybrid program of great potential. Also remember that SQL is used in

ODBC and JDBC. So learning other languages such as VB and JAVA, will

be easier.

sql_embedded [read-only].pdf

Documents

f3exit f4prompt

close c1

dsply dsply

s1 open c1

open c1

prepare s1

fetch c1

prepared select