Using SQL in RPG Programs: An Introduction

1 Your partner in AS/400 and iSeries Education Copyright Partner400, M. @ SQL in RPG Programs: An Introduction OCEAN Technical ConferenceCatch the WaveAgendaWhat is SQL?SQL language overviewAccessing data Using SQLC reating/maintaining databases Using SQLSQL on the AS/400 Using interactive SQLE mbedding SQL in programsQuery Manager Copyright Partner400, - 1-2 .What is SQL on the AS/400?An alternative database interface languageNOT a database management systemHigh level, simple statement formatsA language used for:Data Definition (DDL)Data Manipulation (DML)Completely interchangeable data methodsSQL tables may be accessed with native language DDS created files can be access with SQLOS/400 Integrated Relational Database ManagerDDS definitionSQL DDLIDDUHLL reads/writesDFUQ uery/400 SQL DMLOne Database Manager Copyright Partner400, - 3-4 .NbrNamePosSexSal10 AMY2F120035 JOE5M100030 JON7M150020 DON5M115025 ANN8F1550 SQL TermAS/400 TermTa b l eFileRowRecordColumnFieldTableRowColumnT erminologyBasic statementsSELECT - retrieves data; one row or multipleUPDATE - updates one row or multipleDELETE - deletes one row or multipleINSERT - adds one row or multipleEnvironmentsInteractive SQL - Use STRSQL commandEmbedded SQL - Put into High Level Language (HLL)Query Manager - Report FormatterData Manipulation Language Copyright Partner400, - 5-6.

2 SELECT - some column(s) or * or expressionFROM - some table(s)WHERE - selection criteriaGROUP BY - some column(s)HAVING - selection criteria for groupsORDER BY - Presentation order (sort) SELECT *FROM emplNbrNamePosSexSal10 AMY2F120035 JOE5M100030 JON7M150020 DON5M115025 ANN8F1550 NbrNamePosSexSal10 AMY2F120035 JOE5M100030 JON7M150020 DON5M115025 ANN8F1550 Retrieving Data - the SELECT statementSELECT any number of columns in any orderor *, which means all columnsWHERE clause provides selection criteriaSELECT nbr, nameFROM emplWHERE pos = 5 NbrNamePosSexSal10 AMY2F120035 JOE5M100030 JON7M150020 DON5M115025 ANN8F1550 NbrName35 JOE20 DONR etrieving Data - the SELECT statement Copyright Partner400, - 7-8 .SELECT statementKeywords in the WHERE clause:Greater than (or = ), Less than (or =), EqualNot greater, Not less, Not equal AND, OR, NOTR ange - inclusive constant range (BETWEEN)Values - list of constant values (IN)Pattern matching (LIKE) with wild cards% = any number of characters_ = exactly 1 characterSELECT name, posFROM emplWHERE pos BETWEEN 5 and 7 NbrNamePosSexSal10 AMY2F120035 JOE5M100030 JON7M150020 DON5M115025 ANN8F1550 NamePosJOE5 JON7 DON5 BETWEEN is inclusive of values listed SELECT statement - Examples Copyright Partner400, - 9-10.

3 SELECT name, nbrFROM emplWHERE name LIKE 'A%'NbrNamePosSexSal10 AMY2F120035 JOE5M100030 JON7M150020 DON5M115025 ANN8F1550 NameNbrAMY10 ANN25 Note that you can resequence the column (field) names SELECT statement - ExamplesSELECT name, nbr, posFROM emplWHERE sex = 'M' and (sal * 12) > 12000 NbrNamePosSexSal10 AMY2F120035 JOE5M100030 JON7M150020 DON5M115025 ANN8F1550 More complex conditions, including calculationsNote that selections can be made on columns not selectedNameNbrPosJON307 DON205 SELECT statement - Examples Copyright Partner400, - 11-12 .ORDER BY specifies row orderIf not specified, order is unpredictable! Not always physical orderSELECT name, nbr, posFROM emplWHERE sex = 'M' and (sal * 12) > 12000 ORDER BY nameNbrNamePosSexSal10 AMY2F120035 JOE5M100030 JON7M150020 DON5M115025 ANN8F1550 NameNbrPosDON205 JON307 SELECT statement - ExamplesDerived Columns can be createdIf used for ordering, use relative position number SELECT name, sal * 12 FROM emplORDER BY 2 NbrNamePosSexSal10 AMY2F120035 JOE5M100030 JON7M150020 DON5M115025 ANN8F1550 NameSalJOE12000 DON13800 AMY14400 JON18000 ANN18600 SELECT statement - Examples Copyright Partner400, - 13-14.

4 GROUP BY provides row summaryBuilt-in functions for grouping:AVG, SUM, MAX, MIN COUNTSELECT pos, AVG(sal)FROM emplGROUP BY posNbrNamePosSexSal10 AMY2F120035 JOE5M100030 JON7M150020 DON5M115025 ANN8F1550 PosAVG(Sal)21200510757150081550 SELECT statement - ExamplesSelecting on Groups: HAVINGNbrNamePosSexSal10 AMY2F120035 JOE5M100030 JON7M150020 DON5M115025 ANN8F1550 SELECT pos, AVG(sal)FROM emplGROUP BY posHAVING AVG(sal) > 1200 PosAVG(Sal)7150081550 SELECT statement - Examples Copyright Partner400, - 15-16 .Join: dynamic connection of selected columns from more than one tableNbrNamePosSexSal10 AMY2F120035 JOE5M100030 JON7M150020 DON5M115025 ANN8F1550 PosDesc2 Operator5 Programmer7 Manager8 AnalystJOB TableEMPL TableSELECT name, , descFROM empl, jobWHERE = Data from Multiple TablesChanging Data in a TableSQL StatementsUPDATEINSERT INTO (add a record)DELETEEach can handle eitherOne row at a timeMultiple rows at a time Copyright Partner400, - 17-18.

5 NbrNamePosSexSal10 AMY2F120035 JOE5M100030 JON7M150020 DON5M115025 ANN8F1550 UPDATE empl SET sal = sal + (sal * .10) WHERE pos = 5 NbrNamePosSexSal10 AMY2F120035 JOE5M110030 JON7M150020 DON5M126525 ANN8F1550 Give all programmers (pos = 5) a 10% raise!UPDATE StatementINSERT INTO empl SELECT nbr, name, pos, sex, sal FROM emplnew WHERE pos = 9 INSERT INTO empl(name, nbr, pos, sal, sex)VALUES ('AMY', 10, 2, 1200, 'F')Or multiple rows at a time Using a SELECT statement:INSERT StatementAdd new rows Using INSERTC olumn names and values in one-to-one correspondenceOne row at a time Using the VALUES clause: Copyright Partner400, - 19-20 .NbrNamePosSexSal10 AMY2F120035 JOE5M100030 JON7M150020 DON5M115025 ANN8F1550 DELETE FROM emplWHERE nbr = 10 NbrNamePosSexSal35 JOE5M110030 JON7M150020 DON5M126525 ANN8F1550 Rows can be deleted individually or by sets as wellDELETE StatementDatabase Management with SQLDDL - Data Definition LanguageSQL database objectsCOLLECTION (AS/400 library object)TABLE (Physical file)VIEW (Logical file)INDEX (Logical file)To create SQL database objectsCREATE object_type object_nameTo delete SQL database objectsDROP object_type object_name Copyright Partner400, - 21-22.

6 CREATE TABLE empl(nbr DEC(5,0) NOT NULL,name CHAR(25) NOT NULL,pos DEC(1,0) NOT NULL,sex CHAR(1) NOT NULL,sal DEC(7,2) NOT NULL WITH DEFAULT)Tables are created as physical filesCan be accessed same as any other PFWith or without SQLIf created into an SQL collection, automatically journaledColumns are null-capable by defaultSpecify NOT NULL to mimic DDS behaviorWITH DEFAULT supplies default value in new rowsCreating TablesCREATE VIEW richmen AS SELECT name, sex, sal FROM empl WHERE sex = 'M' and (sal * 12) > 17000 Contain a selection of columns and/or rows from base tableMay be a subset of columns and/or rowsMay be a join viewCreated as a logical file with NO key fieldsViews of views are allowedViews may be summaries ( Using GROUP BY)SQL Views Copyright Partner400, - 23-24 .CREATE INDEX empnbr ON empl (nbr)CREATE INDEX empindxON empl (pos DESC, nbr)Creates a keyed logical file over table(s)Used primarily to enhance performanceNote: ALL keyed logical files may be used to improve performanceEven if not created as SQL indexesMust contain key field(s)May be ascending or descendingMay be specified as UNIQUESQL IndexesDB2/400 Query Manager and SQL Development Kit (or QM & SDK)Interactive SQL interfacePre-compilers for embedding SQL in programsQuery Manager for generating reportsOS/400 contains:Run-time support for SQL and Query ManagerQM & SDK not required to run SQL applications or pre-created QM queriesUsing SQL on the AS/400 Copyright Partner400, - 25-26.

7 A tool for programmers and database administratorsInteractive functionsQuickly maintain databaseTest SQL code before embeddingCreate test data scenariosSTRSQL to beginInteractive SQL Enter SQL StatementsType SQL statement, press >SELECT_____ _____ _____ _____ _____F3=Exit F4=Prompt F6=Insert F9=RetrieveF10=Copy F13=Service F14=Delete F15=SplitF24=More keys Specify SELECT StatementType info for SELECT. F4 for list FROM table(s) .. _____ SELECT column(s .. _____ WHERE conditions.. _____ GROUP BY column(s).. _____ HAVING condition(s) .. _____ ORDER BY column(s).. _____ FOR UPDATE OF column(s) _____F3=Exit F4=Prompt F5=Refresh F12=CancelInteractive SQL Copyright Partner400, - 27-28 .Why embed SQL in programs ?Perform dynamic selection functionsala OPNQRYF, except more flexiblePerform set-at-a-time functions under program controlEven to replace HLL I/O , READ, WRITE, UPDATE, CHAINWhat can be embedded?)

8 The SQL statements we have seen so , SELECT, UPDATE, INSERT, CREATE TABLE, control , DECLARE CURSOR, OPEN, CLOSE, FETCH, COMMIT, ROLLBACKE mbedded SQLUser Source FilePrecompileModified Source FileProcessed SQL StmtsCompileAccess PlansProgram(temporary)SQL Precompiler for Embedded SQL Copyright Partner400, - 29-30 .RPG Interface - SourceRetrieve column/field values into program variablesOne-to-one correspondence between SELECT list and INTO expects only a SINGLE row/recordmultiple rows require the use of cursor operations * No F spec needed ! D EmpNbr S 5 0 D Name S 25 D Job S 1 C/EXEC SQL C+ SELECT NAME, POS C+ INTO :Name, :Job C+ FROM EMPL C+ WHERE NBR =.

9 EmpNbr C/END-EXEC All SQL statements must be coded on a C spec SQL statements begin with /EXEC SQL in positions 7-15with the slash in position 7and end with /END-EXEC in positions 7-15 You can enter SQL statements on the same line as /EXEC SQLH owever, /END-EXEC must be on a separate line Between beginning and ending delimiters, all SQL statements must have + in position 7 SQL statements cannot go past position 80 SQL statements cannot be included via a /COPY statementRules: Embedding SQL in RPG Code Copyright Partner400, - 31-32 .Retrieve column/field values into program variablesOne-to-one correspondence between SELECT list and INTO expects only a SINGLE row/recordmultiple rows require the use of cursor operationsCOBOL Interface - Source WORKING-STORAGE SECTION. 77 EMPNBR PIC S9(5) COMP-3.

10 77 DEPT PIC S9(3) COMP-3. 77 JOB PIC X(25). PROCEDURE DIVISION. EXEC SQL SELECT name, pos INTO :nam, :job FROM empl WHERE nbr = :EmpNbr END-EXEC. Host structures are groups of variablesData structures in RPGG roup items in COBOLS tructures can be used in SQL statementsReplaces list of variablesUsing Structures in SQL D EMP DS D Job 5 0 D Name 25 D Sal 7 2 D EmpNbr S 1 C/EXEC SQL C+ SELECT POS, NAME, SAL C+ INTO :EMP C+ FROM EMPL WHERE NBR =.