Developing Applications Using IBM Informix ESQL/C

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.

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.

Profil uczestnika

This intermediate course is designed for application developers.

Agenda

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

Wymagane przygotowanie uczestników

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

Zagadnienia

• 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