Paul Tuohy

Through the use of Stored Procedures, Functions and Triggers, SQL (Structured Query Language) provides a means for other languages (and platforms) to take advantage of existing code or to access SQL capabilities from RPG or COBOL applications. Stored procedures provide a means of calling routines that may or may not return a result set. Functions provide a means of writing your own scalar functions. Triggers provide a means of having code execute based on database events. This seminar shows you how to code and create SQL Stored Procedures, Functions and Triggers that utilize existing code (external) and how and how to code and create SQL Stored Procedures, Functions and Triggers that are written in SQL (SQL). The seminar will cover the following topics:- • What are Stored Procedures, Functions & Triggers and where and why you might use them • Common components • Development tools • Creating Functions from existing RPG code (programs and subprocedures) • SQL and sourced function • Parameter styles • Overloading functions • Table Functions • Stored Procedures * RPG programs to return one or more result sets • Returning result sets to the caller or the client • SQL control statements (how to program in SQL) • Writing stored procedures using only SQL • Handling exception/errors in external and SQL routines • Triggers • How they are attached to a table • How they are activated • Coding External Triggers in RPG • The benefits of SQL Triggers (and there are benefits) and how to code them

Through the use of Stored Procedures, Functions and Triggers, SQL (Structured Query Language) provides a means for other languages (and platforms) to take advantage of existing code or to access SQL capabilities from RPG or COBOL applications. Stored procedures provide a means of calling routines that may or may not return a result set. Functions provide a means of writing your own scalar functions. Triggers provide a means of having code execute based on database events. This seminar shows you how to code and create SQL Stored Procedures, Functions and Triggers that utilize existing code (external) and how and how to code and create SQL Stored Procedures, Functions and Triggers that are written in SQL (SQL). The seminar will cover the following topics:- • What are Stored Procedures, Functions & Triggers and where and why you might use them • Common components • Development tools • Creating Functions from existing RPG code (programs and subprocedures) • SQL and sourced function • Parameter styles • Overloading functions • Table Functions • Stored Procedures * RPG programs to return one or more result sets • Returning result sets to the caller or the client • SQL control statements (how to program in SQL) • Writing stored procedures using only SQL • Handling exception/errors in external and SQL routines • Triggers • How they are attached to a table • How they are activated • Coding External Triggers in RPG • The benefits of SQL Triggers (and there are benefits) and how to code them

You have been using embedded SQL in RPG. You have mastered the syntax, tried a few selects, declared a cursor, performed an update - you may even have tried some dynamic SQL! Time to take it to the next stage and see what you can do with embedded SQL - beyond the basics Topics covered in this session include:- • Discussion of the key points in writing powerful embedded SQL What tools should you use to write and test SQL prior to embedding it? • Controlling CRTSQLRPGI • SQLCA and SQLCODE considerations Getting data without SELECT - GET DIAGNOSTICS and VALUES INTO • Mastering Multi Row Fetch • Handling NULL • Dates, Times and Timestamps Prerequisite: This session assumes you are familiar with the topics covered in Embedded SQL – the Basics

You have been using embedded SQL in RPG. You have mastered the syntax, tried a few selects, declared a cursor, performed an update - you may even have tried some dynamic SQL! Time to take it to the next stage and see what you can do with embedded SQL - beyond the basics Topics covered in this session include:- • Discussion of the key points in writing powerful embedded SQL What tools should you use to write and test SQL prior to embedding it? • Controlling CRTSQLRPGI • SQLCA and SQLCODE considerations Getting data without SELECT - GET DIAGNOSTICS and VALUES INTO • Mastering Multi Row Fetch • Handling NULL • Dates, Times and Timestamps Prerequisite: This session assumes you are familiar with the topics covered in Embedded SQL – the Basics