Training offering

Developing Applications Using IBM Informix ESQL/C

Overview

If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course.

http://www.ibm.com/training/terms

In this course, you will use IBM Informix ESQL/C tools to write applications that contain embedded SQL commands and queries. You will write applications to perform queries that return single and multiple rows; insert, update, and delete rows; create and use forms to display one or more rows of data; manage cursors, and handle various types of data, including large objects.

This course replaces US Course Developing Applications Using IBM Informix ESQL/C (L1112).

Public

This intermediate course is designed for application developers.

Prerequisits

You should have:

• IBM Informix Structured Query Language or equivalent knowledge
• ANSI C programming or C programming experience

Objective

• Include SQL statements in a C program to add, retrieve, and alter data in an Informix database
• Set up a cursor structure for manipulating a set of rows
• Work with all SQL data types
• Set up and execute dynamically defined SQL statements
• Write effective, efficient SQL-based applications in C

Topics

Introduction to IBM Informix ESQL/C

• Identify the components of ESQL/C
• Install ESQL/C as part of Client SDK
• Set environment variables and execute the ESQL/C preprocessor

The Demonstration Database

• The tables in the stores_demo demonstration database
• The relationships between these tables
• Installing a copy of this database

Embedding SQL Statements

• Embed SQL statements in C
• Identify C variables for use in accessing SQL databases
• Describe the structure of an ESQL/C program

Using CONNECT TO

• Use the CONNECT TO, DISCONNECT, and SET CONNECTION SQL statements
• Describe when optional syntax is appropriate
• Describe how the syntax works with pre-6.0 version syntax and functionality

Compiling an ESQL/C Program

• Incorporate other files into your source code
• Conditionally preprocess SQL statements
• Compile a program by using IBM Informix ESQL/C
• Explain the basic use of the make utility

ESQL/C Data Types

• Determine which C data type to use as a host variable
• Explain problems in converting between data types
• Use functions to convert variables of different types

Character and String Data Types

• Declare host variables for CHAR, CHAR *, VARCHAR, and LVARCHAR data types
• Explain the storage needs of character and string data
• Interface with LVARCHAR data through library functions

Numeric Data Types

• Recognize the storage needs for numeric data
• Declare host variables for INT8, SERIAL8, BOOLEAN, and DECIMAL data types
• Describe the structure for the DECIMAL data type
• Use ESQL/C library functions to access data

Inserting Rows

• Code an appropriate embedded SQL statement that inserts a row into a database

The SQL Communications Area

• Explain the need for and the structure of the SQL Communication Area
• Effectively use information contained in this structure

SQL Exception Testing

• Describe the kinds of exceptions that might occur during execution of an SQL statement
• Simplify exception testing after every SQL statement

Using GET DIAGNOSTICS

• Describe the preferred error detection methods
• Describe how to obtain warning and error information

Singleton Queries and Lookups

• Use the SELECT statement to retrieve a row from your database
• Determine whether SQL NULLs were fetched or character data was truncated
• Ensure referential integrity using application logic

An Overview of Cursors

• Name the three types of cursors
• Use the appropriate cursor for a given task

Using Scroll Cursors

• Use SQL statements to set up a scrolling cursor
• Use a scrolling cursor to browse the selected rows
• Change the size of FETCH and INSERT buffers
• Automatically free a cursor
• Use the OPTOFC feature to reduce network messaging

Using Scroll Cursors Effectively

• Use a scrolling cursor to select a primary key
• Solve the stale data problem by using the primary key to select the current row
• Declare a cursor from a prepared statement
• Defer execution of a PREPAREd statement

Using an Update Cursor

• Use an update cursor to lock rows that might be updated

Using a Insert Cursor

• Determine if an INSERT cursor is needed for adding rows to a database
• Use an INSERT cursor to insert rows into a database

Time Data Types

• Declare host variables for SQL DATE, DATETIME and INTERVAL data types
• Explain how data is converted as it is stored
• Use pre-defined DATETIME and INTERVAL macros

Simple Large Objects

• Declare host variables for BYTE and TEXT data types
• Describe the locator structure
• INSERT and SELECT simple large objects from a file or from memory

Dynamic SQL

• Use dynamic SQL and the associated data structures and commands

Dynamic SQL: Constructing INSERT Statements

• Use dynamic SQL to construct insert statements at runtime

Working with the Database Server

• Explain how to control the database server process with these functions:
  • sqlexit
  • sqldetach
  • sqlbreak
• Work with multiplexed connections

Agenda
Day 1

• Welcome
• Unit 1: Introduction to IBM Informix ESQL/C
• Exercise 1
• Unit 2: The Demonstration Database
• Exercise 2
• Unit 3: Embedding SQL Statements
• Exercise 3
• Unit 4: Using CONNECT TO
• Exercise 4
• Unit 5: Compiling an ESQL/C Program
• Exercise 5
• Unit 6: ESQL/C Data Types
• Unit 7: Character and String Data Types
• Exercise 6
• Unit 8: Numeric Data Types
• Exercise 7

Day 2

• Unit 9: Inserting Rows
• Exercise 8
• Unit 10: The SQL Communications Area
• Exercise 9
• Unit 11: SQL Exception Testing
• Exercise 10
• Unit 12: Using GET DIAGNOSTICS
• Exercise 11
• Unit 13: Singleton Queries and Lookups
• Exercise 12

Day 3

• Unit 14: An Overview of Cursors
• Exercise 13
• Unit 15: Using Scroll Cursors
• Exercise 14
• Unit 16: Using Scroll Cursors Effectively
• Unit 17: Using an Update Cursor
• Exercise 15
• Unit 18: Using an Insert Cursor
• Unit 19: Time Data Types
• Exercise 16

Day 4

• Unit 20: Simple Large Objects
• Exercise 17
• Unit 21: Dynamic SQL
• Exercise 18
• Unit 22: Dynamic SQL: Constructing INSERT Statements
• Unit 23: Working with the Database Server
• Exercise 19