An Introduction to developing embedded SQL database applications with Interbase.

By: Conference Speaker

Abstract: Programming efficient SQL applications within hosts languages such as C or C++ can be done with the InterBase GPRE preprocessor on UNIX and Windows 95/NT platforms.

    TABLE OF CONTENTS

An introduction to developing embedded SQL database applications with InterBase.

1. Introduction.

A. Application requirements.

B. GPRE. What is it?

C. The EXEC SQL statement.

2. Host variables.

A. The BEGIN DECLARE SECTION statement.

B. The END DECLARE SECTION statement.

3. Declaring and Initializing Databases.

A. Using the SET DATABASE clause.

B. Using CONNECT.

C. What are database handles.

D. Connecting to multiple databases.

E. Using the CompileTime and RunTime clause.

F. Using the STATIC and EXTERN clause.

G. Using the USER and PASSWORD clause.

H. Changing the character set with SET NAMES.

I. Changing the default CACHE.

4. Closing Databases.

A. Closing databases with DISCONNECT.

B. Closing databases with COMMIT or ROLLBACK.

5. Transactions.

A. Transaction behavior defined.

B. The default transaction behavior.

C. The SET TRANSACTION statement.

D. Using the COMMIT statement.

E. Using the ROLLBACK statement.

6. Declaring and using Cursor.

A. The DECLARE CURSOR statement.

B. How to OPEN a cursor.

C. Using the FETCH statement to retrieve database records.

D. Using INDICATOR to determine if a field has NULL values.

E. How to use the OPEN/CLOSE statement to revisit records.

7. Accessing a Database.

A. The INTO clause.

B. The FROM clause.

C. The WHERE clause.

D. Using the keyword NULL.

E. Selecting a single row or a part of a row.

F. Selecting multiple rows or a part of multiple rows.

G. Selecting related rows from 2 or more tables into a results table (join).

H. Selecting all rows from 2 or more tables into a results table (union).

8. Inserting data into a table.

A. Using the VALUES clause.

B. Using a SELECT statement.

C. Inserting NULLs into fields.

9. Updating fields in a table.

A. The FOR UPDATE clause.

B. Using the SET clause.

C. Position updates.

D. Updating field values with NULLs.

10. Deleting existing rows of a table.

A. Deleting with the Searched method.

B. How to do positioned deletes.

11. Using date field types.

A. Selecting date fields.

B. Inserting date fields.

C. Updating date fields.

D. Using Cast() to convert to date field types.

E. Date Literals.

1. The TODAY literal.

2. The NOW literal.

3. The YESTERDAY and TOMORROW literal.

12. More about transactions.

A. Naming transactions.

B. Using Named transactions with the INSERT statement.

C. Using Named transactions with the UPDATE statement.

D. Using Named transactions with the DELETE statement.

13. Error Handling.

A. Connect errors.

B. Using the WHENEVER statement.

C. The SQLCODE host variable.

D. The isc_print_sqlerr() function call.

E. The isc_sql_interprete() function call.

F. The isc_interprete() function call.

14. Preprocessing, Compiling and Linking.

A. GPRE syntax.

B. Command line switches.

C. File extensions.

D. Compile.

E. Link.

15. Conclusion.

Advanced Topics Not Covered.

1. Use of BLOB field types.

2. Use of ARRAY field type.

3. Using VIEWS.

4. How to do data definition.

5. Use of Security.

6. Creating and using of UDF's.

7. Creating and using of Stored procedures.

8. Use of Events.

9. The differences between SQL and DSQL.

    Introduction

This talk will cover the development of host based language to access an InterBase database. As a developer, you may have a need to write embedded SQL database applications using InterBase. To accomplish this, you need a host based language, such as C or C++, and the preprocessor program, GPRE, provided with InterBase. This introduction should allow you to begin writing host language programs embedded with SQL database statements. You should be able to connect to databases, create transactions, select from the database, update and insert into the database and handle errors.

When you write a host language application, you embed SQL statements within your code. The input to GPRE, the preprocessor program, consists of this host language application source code. These SQL statements are converted by GPRE to the InterBase host language API. The requirements for an application are :

  • Declare host variables to transfer between the database and your application.
  • Declare and set the databases accessed by your application.
  • Optionally create transaction handles.
  • Access the database using SQL statements.
  • Close all transaction and databases.

SQL statements are access or update statements where the database structure is known at compile-time. If the database structure is NOT known at compile-time then Dynamic SQL ( or DSQL ) statements are required. Although this talk will not cover DSQL statements, most of the information about SQL statements does apply to DSQL statements.

    EXEC SQL

Any embedded SQL statement recognized by GPRE must be preceded by the keyword EXEC SQL. In fact GPRE uses the EXEC SQL keywords to determine the statement following is an embedded SQL statement. These statements are terminated by the statement terminator expected by the host language. As expected with C or C++, the host terminator is the semicolon (;). For a complete list of SQL and DSQL statements supported by InterBase, see the Language reference manual ( Chapter 2 ).

For example:	
EXEC SQL		
  CONNECT db1;

    Host variables

A host variable is a standard host-language variable used to hold values read from or written to the database. SQL uses host variables in the following situations. The first is during data retrieval.

For example:	
EXEC SQL		
  BEGIN DECLARE SECTION;			
    Char dept_no[4];	
EXEC SQL		
  END DECLARE SECTION;	
EXEC SQL		
  SELECT dept_no INTO :dept_no FROM department;

You may wonder what would happen in this example if multiple rows were returned. You would only get one row in returned in the dept_no host variable. If you wanted multiple rows returned you should use the FETCH statement. If you knew that multiple rows existed, you should construct your SELECT statement so that only one row will be returned. In addition, host variables can be used to pass data to InterBase from your program during an INSERT or UPDATE statement.

For example:	
EXEC SQL		
  BEGIN DECLARE SECTION;			
    Char dept_no[4];	
EXEC SQL		
  END DECLARE SECTION;	
strcpy( dept_no, "600" );	
EXEC SQL		
  INSERT INTO department( dept_no, department )					
    VALUES( :dept_no, "Your Department");

Another use for host variables is to specify search conditions in the WHERE clause of a SELECT statement.

For example:	
EXEC SQL		
  BEGIN DECLARE SECTION;			
    Char dept_no[4];	
EXEC SQL		
  END DECLARE SECTION;	
strcpy( dept_no, "600" );	
EXEC SQL		
  SELECT department FROM department			
    WHERE dept_no = :dept_no;

The BASED ON declarative clause ensures a host variable is large enough to hold the CHAR or VARCHAR database column. An extra byte is added for the null-terminating character expected by most C string functions. ( Note: the BASED ON declarative is defined by a domain, not a specific table column. )

For example:	
EXEC SQL		
  SET DATABASE emp = "talk.gdb";	
EXEC SQL		
  CONNECT emp;	
EXEC SQL		
  BEGIN DECLARE SECTION;			
    BASED ON emp.firstname fname;	
EXEC SQL		
  END DECLARE SECTION;

For host languages that support structures, EXEC SQL will recognizes data members in structures.

For example:	
struct	
{		
  Char  fname[25];		
  Char  lname[25];	
} billing;	
EXEC SQL		
  SELECT first_name last_name			
    INTO :billing.fname,  :billing.lname			
      FROM employee;

    Declaring and Initializing Databases.

The SET DATABASE statement creates a host variable declaration. This host variable is an alias for the actual database. The host variable is a database handle. This database handle is used in subsequent CONNECT, COMMIT RELEASE and ROLLBACK RELEASE statements. The USER and PASSWORD statements can also be used with the SET DATABASE statement.

For example:	
EXEC SQL		
  SET DATABASE db1 = "talk.gdb"			
    USER "sysdba"    PASSWORD "masterkey";

The CONNECT statement attaches to a database, opens the database and allocates system resources for the database. The USER and PASSWORD statements can also be used with the CONNECT statement. Multiple databases can be connected in the statement.

For example:	
EXEC SQL		
  CONNECT "talk.gdb"  USER "sysdba"    PASSWORD "masterkey",			
       db1;

As mentioned above, database handles are declared with the SET DATABASE statement. Once the database handle is declared, it can be used assigned to different databases.

For example:	
char	db_name[20];	
EXEC SQL		
  SET DATABASE db1 = "talk.gdb";	
strcpy( db_name, "new.gdb");	
EXEC SQL		
  SET DATABASE db2 = :db_name;

If multiple databases are declared in a program and each database has the same table names, database handles are used to qualify the table names.

For example:	
EXEC SQL		
  SET DATABASE db1 = "talk.gdb",				
      db2 = "new.gdb";	
EXEC SQL		
  DECLARE TidMATCH CURSOR FOR			
    SELECT emp_no INTO :emp_no FROM db1.employee					
      WHERE emp_no > 100;	
EXEC SQL		
  DECLARE EidMATCH CURSOR FOR			
    SELECT emp_no INTO :emp_no FROM db2.employee					
      WHERE emp_no > 200;

The CONNECT statement can be used to attach to all previously defined databases by SET DATABASE statements.

For example:	
EXEC SQL		
  CONNECT ALL;

When a program with embedded SQL statements is compiled, access to the database is required. This does not have to be the same database that is used at run-time. This allows GPRE to validate the program's table and column references. At run-time a different database can be accessed by the program.

For example:	
EXEC SQL		
  SET DATABASE db1 = COMPILETIME "talk.gdb";

This code segment allows GPRE to know the structure of the database at CompileTime. The COMPILETIME specification must always be a quoted string. The SET DATABASE statement can be expanded to include a RUNTIME specification. This specification can be a quoted string or a host language variable.

For example:	
strcpy( fname1, "new.gdb");	
EXEC SQL		
  SET DATABASE db1 = COMPILETIME "talk.gdb"					
       RUNTIME :fname1;

The SET DATABASE specification can be also include a STATIC or EXTERN declaration. By default, the SET DATABASE specification creates a database handle that is global to all modules in the application. The STATIC declaration limits reference of this database handle to this host language module.

For example:	
EXEC SQL		
  SET DATABASE db1 = STATIC "talk.gdb";

If the application desires to have a different character set, the program can use the SET NAMES statement before connecting to the database.

For example:	
EXEC SQL		
  SET NAMES DOS437;	
EXEC SQL		
  CONNECT "talk.gdb";

While using the CONNECT statement, the application can specify the number of cache buffers assigned to a database. The number of buffers are used to store accessed database pages to speed performance. The default number of database cache buffers assigned to a database is 75.

For example:	
EXEC SQL		
  CONNECT "talk.gdb" CACHE 125;

    Closing Databases

A database can be closed by a program in one of two ways, either the DISCONNECT statement or the RELEASE option appended to a COMMIT or ROLLBACK statement. Once a database is closed, it must be reopened before it can be accessed. Closing databases with the DISCONNECT statement is the SQL-92 standard.

For example:	
EXEC SQL		
  DISCONNECT db1;	
EXEC SQL		
  COMMIT RELEASE db2;	
EXEC SQL		
  ROLLBACK RELEASE db3;

    Transactions

The SET TRANSACTION statement is used to start a transaction and optionally specify its behavior. See Table 4-2 in the Programmers Guide for a list of the Default Transaction Default Behavior. See Table 4-3 in the Programmers Guide for the list of SET TRANSACTION parameters. There are two reasons to name transactions. The first reason is to use transactions that have different behavior. The second reason is that complex programs may require multiple transactions that exist at the same time.

For example:	
EXEC SQL		
  SET TRANSACTION NAME t1;

This example will create a transaction named t1 using the default behavior.

For example:	
EXEC SQL		
  SET TRANSACTION NAME t2 READ ONLY;

This example will create a transaction named t2 that will allow only the selection of data. Data cannot be inserted, modified or deleted using this transaction.

A transaction name can be used with a COMMIT or ROLLBACK statement.

For example:	
EXEC SQL		
  COMMIT TRANSACTION t1;

This will preserve any database changes under the control of transaction t1.

For example:	
EXEC SQL		
  ROLLBACK TRANSACTION t2;

This will undo any database changes under the control of transaction t2.

    Cursor

The DECLARE CURSOR statement allows a program to build SQL statements that return multiple rows. Without the DECLARE CURSOR statement an application can return only one row.

For example:	
EXEC SQL		
  SELECT last_name INTO :lname FROM employee			
    WHERE dept_no = "123";

This example will return the "first" record found that meets the WHERE clause condition. If there are multiple records that match the WHERE clause, there is no way to determine what the "first" record is. To return multiple rows with a select statement, a cursor must be declared.

For example:	
EXEC SQL		
  DECLARE lnameC CURSOR FOR			
    SELECT last_name FROM employee					
      WHERE dept_no = "123";

In this example the DECLARE CURSOR statement ( lname ) will allow multiple records to be returned. After the DECLARE CURSOR statement, the OPEN CURSOR statement will build the result table based on the selection criteria and position the cursor on the first row of the result set.

For example:	
EXEC SQL		
  OPEN lnameC;

Now we are ready to retrieve the records in the result set. This is accomplished with the FETCH statement. An SQLCODE is returned that indicates the result of the FETCH statement. The SQLCODE variable is automatically declared by the GPRE process.

For example:	
EXEC SQL		
  DECLARE lnameC CURSOR FOR			
    SELECT last_name FROM employee				
      WHERE dept_no = "123";	
EXEC SQL		
  OPEN lnameC;	
SQLCODE = 0;	
while( 1 )	
{		
  EXEC SQL			
    FETCH lname INTO :lname;		
  if ( !SQLCODE )			
    printf( "Last Name is %sn", lname );		
  if ( SQLCODE )		
  {  if ( SQLCODE != 100 )				
       printf( "SQLCODE error %dn", SQLCODE );			
     break;		
  }	
}

This example will print all Last Names that are in department "123". The SQLCODE will be either 0 for a successful FETCH, 100 when no more records are available to be retrieved and Less than 0 if an error occurred.

Any column in an InterBase table can contain a NULL value ( unless it is defined as NOT NULL or UNIQUE in the integrity constraints ). You can determine if a column value is NULL by following each INTO clause with the INDICATOR keyword and the name of a short integer variable. This variable is called an indicator variable. When used in conjunction with the FETCH statement, the program can determine if the column has a NULL value.

For example:	
char	phone[5];	
short	missing_phone;	
EXEC SQL		
  FETCH lnameC INTO :phone INDICATOR :missing_phone;

If the value of missing_phone is -1, the field phone is NULL. If the value of missing_phone is 0, the field phone is NOT NULL.

The only supported cursor movement is forward, in sequential order through the result set. To reposition the cursor to the start of the result set, issue a CLOSE CURSOR statement followed by an OPEN CURSOR statement.

    Accessing a Database

Using the INTO clause will transfer data from the database to the specified host variable. With a SELECT statement that returns only one row ( a singleton select ) the INTO clause is specified in the SELECT statement. If the SELECT statement is not a singleton select, the INTO clause is specified in the FETCH statement. The programmer must determine when to use the INTO clause in the SELECT or the FETCH statement.

For example:	
EXEC SQL		
  SELECT last_name INTO :lname FROM employee			
    WHERE dept_no = "123";

The above example assumes a singleton select. If this is not a singleton select, only the first record will be returned. In the event this is not a singleton select, the FETCH statement must be used.

For example:	
char	phone[5];	
EXEC SQL		
  FETCH lnameC INTO :phone;

The FROM clause within a SELECT statement allows access to one or more tables within by the select.

For example:	
EXEC SQL		
  SELECT last_name INTO :lname FROM employee			
    WHERE dept_no = "123";

The above example selects data from only one table.

For example:	
EXEC SQL		
  SELECT department, last_name emp.emp_no			
    INTO :dept, :lname, :empno			
      FROM department dept, employee emp			
        WHERE dept_no = "123" AND dept.dept_no = emp.dept_no;

Is the example on the top of page 115 correct ???

This example selects data from the department and employee tables and links the two tables by the dept_no field in each table.

The WHERE clause within the SELECT statement, specifies the selection criteria. Without a WHERE clause, the SELECT statement returns all rows in a table. For a CURSOR declaration to return a subset of rows, the WHERE clause must be specified in the declaration.

To select rows that contain NULL field values, use IS NULL in the WHERE clause. Remember NULL values are a special condition. Fields with NULL values are returned as 0 for numbers, blank for characters and 17 November 1858 for dates. See page 131 in the programmer's Guide for limitations on NULL values.

Joining two tables can create an inner join or an outer join. Inner joins can be either the old style joins where the tables to join are listed in the FROM clause and the columns to compare are listed in the FROM clause.

For example:	
EXEC SQL		
  DECLARE inner_join CURSOR FOR			
    SELECT d.department, d.manager, e.salary				
      FROM department d, employee e				
        WHERE d.mngr_no = e.emp_no AND					
              e.salary/5 >= ( SELECT AVG ( s.salary )								
      FROM employee s								
        WHERE d.dept_no = s.dept_no )				
      ORDER BY d.deparment;

InterBase also supports the SQL-92 standard new explicit join syntax. The same inner join as above would be written in the new standard as follows:

For example:	
EXEC SQL		
  DECLARE inner_join CURSOR FOR			
    SELECT d.department, d.manager, e.salary				
      FROM department d				
        JOIN employee e ON d.mngr_no = e.emp_no AND					
             e.salary/5 >= ( SELECT AVG ( s.salary )								
      FROM employee s								
        WHERE d.dept_no = s.dept_no )				
      ORDER BY d.deparment;

The SQL-92 standard allows you to explicitly specify in the SELECT statement the join declaration. This makes your intention in the program more clear than the older style of declaration. You specify the join search conditions in the FROM clause of the SELECT statement. This allows you to further restrict the rows returned by the WHERE clause. Outer joins return every row in one table and a subset of rows in another table.

For example:	
EXEC SQL		
  DECLARE outer_join CURSOR FOR			
    SELECT e.emp_no, e.first_name, e.last_name, d.department,				
      FROM employee e LEFT JOIN department d					
        ON e.dept_no = d.dept_no				
      WHERE e.hire_date > "1/1/95"				
      ORDER BY d.deparment;

This example will return all employees hired after 1/1/95 even if they have do not have a dept_no. If field dept_no in the employee table were changed to allow NULL values, it would make sense for this SELECT. Without that change, the outer join would not be needed. Unions return all rows from the specified tables, appending the results and eliminating all duplicate rows.

    INSERT Data into a table

The INSERT statement adds records to one table. The insert of data can come from one of two sources. The VALUES clause can contain hard-coded values or host language variables.

For example:	
char	department[26];	
strcpy( department, "New Department" );	
EXEC SQL		
  INSERT INTO department (dept_no, department)			
    VALUES ( "567", :department );

Any field not specified in the INSERT statement will default to NULLs. Of course, the INSERT will fail if your database has a constraint on a field as NOT NULL and your INSERT did not specify a value for that field. The second source for data can be a SELECT statement from the same or another table.

For example:	
EXEC SQL		
  INSERT INTO department (dept_no, department, location)			
    SELECT ( "888", "New Department", location ) FROM department				
      WHERE dept_no = "123";

This example will insert a new record into department and the new dept_no will be 888 with a department name of "New Department" and the same location as the dept_no 123. To assign a NULL to a column, use the keyword NULL in the VALUES clause.

For example:	
EXEC SQL		
  INSERT INTO department (dept_no, department, location)			
    VALUES( "999", "New Department", NULL );

INDICATOR can also be used to insert NULLs into a table. Set the indicator variable to -1 to insert NULLs and 0 to insert data.

For example:	
short	ind;	
ind = -1;	
EXEC SQL		
  INSERT INTO department (dept_no, department, location)			
    VALUES( "999", "New Department",					
            "Green House" INDICATOR :ind );	
ind = 0;	
EXEC SQL		
  INSERT INTO department (dept_no, department, location)			
    VALUES( "777", "New Department",					
            "Green House" INDICATOR :ind );

In this example, the new department, 999, will have NULL for the location and the new department, 777, will have "Green House" for the location.

    UPDATE of a table

Updating a table can be done on a global basis or after retrieving data rows. This is called a Positioned Update. The following example shows how to update rows in a table based on certain conditions.

For example:	
EXEC SQL		
  UPDATE department SET location = "Moved"			
    WHERE dept_no >= "200" AND dept_no <= "399";

This example will change the location for all departments between 200 and 399 to "Moved". No other field in the records will be changed. There may be times when you wish to locate records, examine them and the optionally modify them. To accomplish this, you must use the DECLARE CURSOR with the FOR UPDATE clause.

For example:	
char	dept[4],		
location[16];	
EXEC SQL		
  DECLARE changeC CURSOR FOR			
    SELECT dept_no, location FROM department				
      FOR UPDATE OF location;	
EXEC SQL		
  OPEN changeC;	
EXEC SQL		
  FETCH changeC INTO :dept, :location;	
while ( !SQLCODE )	
{  dept_n = atoi( dept );		
   if ( ( dept_n >= 200  ) && ( dept_n <= 399 ) )		
  {  strcpy( location, "Moved" );			
     EXEC SQL				
       UPDATE department SET location = :location					
         WHERE CURRENT OF changeC;		
  }		
  EXEC SQL			
    FETCH changeC INTO :dept, :location;		
  if ( SQLCODE && ( SQLCODE != 100 ) )		
  {  printf( "Fetch errorn");			
     EXEC SQL				
       ROLLBACK RELEASE;			
     exit( 1 );		
  }	
}	
EXEC SQL		
COMMIT RELEASE;

This example accomplishes the same results as the previous example. All locations are changed to "Moved" where the dept_no is >= 200 and <= 399. The second example could be changed to display the records to the user and allow them to change the specific locations to "Moved". When using the FOR UPDATE clause in a FETCH statement, the data records are retrieved a single row at a time. This is done so the records can be updated. Without the FOR UPDATE clause, records are fetched as a batch.

The keyword NULL or the INDICATOR ( ??? check this out ) can be used set fields to NULL with an UPDATE statement.

For example:	
EXEC SQL		
  UPDATE department SET location = NULL			
    WHERE location = "Moved";

    Deleting existing rows

A single DELETE statement may delete multiple rows. The WHERE clause of the DELETE statement specifies which rows are deleted.

For example:	
EXEC SQL		
  DELETE FROM department			
    WHERE location = "Moved";

Records can be deleted through a cursor similar to the Positioned Update statement. Positioned

For example:	
char	dept[4],		
location[16];	
EXEC SQL		
  DECLARE deleteC CURSOR FOR			
    SELECT dept_no, location FROM department				
      FOR UPDATE;	
EXEC SQL		
  OPEN deleteC;	
SQLCODE = 0;	
while( !SQLCODE )	
{		
  EXEC SQL			
    FETCH deleteC INTO :dept, :location;		
  if ( SQLCODE)		
  {  // No more records			
     if ( SQLCODE == 100 )				
       break;			
     printf( "Fetch failedn" );			
     EXEC SQL				
       ROLLBACK;			
     EXEC SQL				
       DISCONNECT ALL;			
     exit(1);		
  }		
  dept_n = atoi( dept );		
  if ( ( dept_n >= 200  ) && ( dept_n <= 399 ) )		
  {  EXEC SQL				
       DELETE FROM department					
         WHERE CURRENT OF deleteC;			
     if ( SQLCODE)			
     {  printf( "Delete failedn" );				
        EXEC SQL					
          ROLLBACK;				
        EXEC SQL					
          DISCONNECT ALL;				
        exit(1);			
     }	
}	
printf( "Deletions Completedn" );	
EXEC SQL		
  COMMIT RELEASE;

This example will attempt to delete all records where the dept_no is >= 200 and <= 399.

    Dates

InterBase stores dates as two long integers, one to hold the date portion and the other to hold the time portion of the date. A date field contains the year, month, day and time. InterBase provides function calls for converting dates from/to InterBase internal format and a C time structure. To get date fields with the SELECT statement use the isc_decode_date() function. GPRE automatically declares this function when date fields are used.

For example:	
#include <time.h>	
struct tm hire_date;	
char	  lname[21],		
          fname[16];	
ISC_QUAD  hire_date_quad;	
EXEC SQL		
  SELECT last_name, first_name, hire_date			
    INTO :lname, :fname, :hire_date_quad			
      FROM employee			
        WHERE emp_no = 123;	
isc_decode_date( &hire_date_quad, &hire_date );

This example finds the last name, first name and hire date for employee 123. The date field is retrieved into the host variable hire_date_quad and then the isc_decode_date() function converts the date to a standard Unix date/time structure. To insert or update a field the standard Unix date/time structure is converted into ISC_QUAD with the isc_encode_date() and then use this host variable to insert or modify the date field.

For example:	
#include <time.h>	
struct tm hire_date;	
char	  lname[21],		
          fname[16];	
ISC_QUAD  hire_date_quad;	
hire_date.year = 1996;	
hire_date.month = 5;	
hire_date.day = 26;	
isc_encode_date( &hire_date, &hire_date_quad );	
EXEC SQL		
  INSERT INTO employee ( emp_no, dept_no, hire_date )			
    VALUES ( 123, "123", :hire_date_quad );

This example fills the host variable for a standard Unix date, converts the variable to an InterBase formatted date and inserts a record into the database.

The built-in CAST() function can be used to convert a character or numeric data type to a date data type for comparison.

For example:	
#include <time.h>	
struct tm hire_date;	
char	  lname[21],		
          fname[16],		
          find_date[ 7 ];	
ISC_QUAD  hire_date_quad;	
strcpy( find_date, "1/1/95" );	
EXEC SQL		
  SELECT last_name, first_name, hire_date			
    INTO :lname, :fname, :hire_date_quad			
      FROM employee			
        WHERE hire_date >= CAST( find_date AS DATE );

This example will find all the employees who have been hired since 1/1/95. Quoted literals can also be used in the WHERE clause of the SELECT statement.

For example:	
#include <time.h>	
struct tm hire_date;	
char	  lname[21],		
          fname[16];	
ISC_QUAD  hire_date_quad;	
EXEC SQL		
  SELECT last_name, first_name, hire_date			
    INTO :lname, :fname, :hire_date_quad			
      FROM employee			
        WHERE hire_date = "TODAY";

This example will find all employees that have a hire_date equal to today. Date literals are "TODAY", "NOW", "YESTERDAY" and "TOMMORROW". If the date string has a / as the separator, InterBase interprets the field as being month, day, and year. If the date string has a . as the separator, InterBase interprets the field as being day, month, and year.

    More about Transactions

InterBase allows an application to start multiple transactions. Multiple transactions are distinguished from each other by the transaction name. A transaction name is a programmer supplied variable. If no transaction name is used in an SQL statement, the behavior of the default transaction is used.

For example:	
EXEC SQL		
  BEGIN DECLARE SECTION;			
    long  *t1,
          *t2;
EXEC SQL		
  END DECLARE SECTION;	
t1 = 0L;	
t2 = 0L;	
EXEC SQL		
  SET TRANSACTION NAME t1;	
EXEC SQL		
  SET TRANSACTION NAME t2 READ ONLY WAIT					
    READ COMMITED RECORD_VERSION;	
EXEC SQL		
  SELECT TRANSACTION t2 last_name, first_name FROM employee

This example creates a result set with all records and will return any records that have the last name or first name changed since the start of the transaction. The following example updates records using the transaction created in the previous example.

For example:	
EXEC SQL		
  UPDATE TRANSACTION t1 employee SET last_name = "Jones"			
     WHERE empno = 123;	

The syntax for using transaction with the DELETE statement is similar.
For example:	
EXEC SQL		
  DELETE TRANSACTION t1 FROM employee			
    WHERE empno = 123;

    Error Handling

The WHENEVER statement allows programs to trap and handle run-time InterBase errors. Database errors can occur from everything between records not found to database corruption. The programmer determines the amount of error handling that is placed into the program and what actions are taken when an error occurs.

The following example shows how to trap errors during a database declaration. After the error is detected, and error message is printed and the program terminates.

For example:	
EXEC SQL
  WHENEVER SQLERROR GOTO error_exit;
EXEC SQL
  CONNECT "talk.gdb";
// Access your database here.
EXEC SQL
  DISCONNECT ALL;
printf( "No errors occurred n" );
exit( 0 );

:error_exit
printf( "An error has occurred n" );
EXEC SQL			
  DISCONNECT ALL;
exit( 1 );

The WHENEVER statement is followed by one the following error types. SQLERROR, SQLWARNING or NOT FOUND. GPRE generates code after every database access to test the value of the host variable SQLCODE. If this value is less than zero, the SQLERROR action is activated. If this value is between 1 and 99, the SQLWARNING action is activated. If the value is 100, the NOT FOUND action is activated. At any time, any of these error types can be active. Error handling should be disabled at the start of the error handling routine. This will ensure that an infinite loop is not caused by an error occurring within the error handling routine.

There are two limitations to using the WHENEVER statement. The first limitation is that errors are trapped indiscriminately. Some errors are more severe and may require drastic actions while other errors are less severe. The second disadvantage to using the WHENEVER clause is that it is not easy to resume processing after an error.

Another approach to error handling is to examine the host variable SQLCODE. This host variable is automatically generated by GPRE. The SQLCODE variable will contain the result of the last database operation and can be tested directly by an application. The following examples shows direct testing of the SQLCODE variable.

For example:	
EXEC SQL		
  SELECT emp_no INTO :emp_no FROM employee			
    WHERE dept_no = "123";	
if ( SQLCODE == 0 )		
  printf( "Found employee %s in department '123'n", emp_no );	
else	
if ( SQLCODE == -1 )		
  printf( "Found more than one employee in department '123'n" );	
else	
if ( SQLCODE == 100 )		
  printf( "Found NO employees in department '123'n" );	
else	
{  printf( "Major database error (%d)n", SQLCODE );		
   EXEC SQL			
     ROLLBACK;		
   EXEC SQL			
     DISCONNECT ALL;		
   exit( 1 );	
}	
EXEC SQL		
  COMMIT;	
EXEC SQL		
  DISCONNECT ALL;

Notice in this example, the SQLCODE is tested and an error message is displayed based on the value of SQLCODE. Of course a switch statement could be used instead of this series of if statements. Another improvement would be to have a series of error processing procedures. One of these procedures could be called based on the database action performed.

GPRE allows the access to three standard error handling routines. The functions are isc_print_sqlerr(), isc_sql_interprete() and isc_interprete(). The isc_print_sqlerr() function prints a single error message.

For example:	
EXEC SQL		
  SELECT emp_no INTO :emp_no FROM employee			
    WHERE dept_no = "123";	
      isc_print_sqlerror( SQLCODE, isc_status );

Be careful using the isc_print_sqlerr() function with windowing systems that do not encourage or permit direct screen writes. Under these conditions, the isc_sql_interrupt() function should be used. This function captures the InterBase error message into a user supplied buffer.

For example:	
char	err_buf[ 256 ];	
EXEC SQL		
  SELECT emp_no INTO :emp_no FROM employee			
    WHERE dept_no = "123";	
isc_sql_interrupt( SQLCODE, err_buf, sizeof( err_buf ) );	
printf( "InterBase error <%s>n", err_buf );

This example displays the same message as the isc_print_sqlerror() example. The program could have done something different with the returned value from the isc_sql_interrupt() function. When a database error occurs with an SQLCODE less than -1, there can be more specific InterBase information available. This information can be retrieved with the isc_interprete() function.

For example:	
char            err_buf[ 256 ];	
int             count;	
ISC_STATUS     *vector;	
FILE           *efile;	
EXEC SQL		
SELECT emp_no INTO :emp_no FROM employee			
  WHERE dept_no = "123";	
if ( SQLCODE < -1 )	
{			
  if ( ( efile = fopen( "Error.dat", "w" ) ) != NULL )		
  {  count = 1;			
     vector = isc_status;			
     isc_interprete( err_buf, &vector );			
     fprintf( efile, "InterBase error #d <%s>n", count, err_buf );			
     *err_buf = '-';			
     while( isc_interprete( err_buf + 1, &vector ) )			
       fprintf( efile, "InterBase error #%d <%s>n", ++count, err_buf );			
     fclose( efile );		
  }		
  EXEC SQL			
    ROLLBACK;		
  EXEC SQL			
    DISCONNECT ALL;		
  exit( 1 );	
}

This example shows how to capture all the error messages that occur in the InterBase status array. This code fragment retrieves each error and writes them to a disk file. This is useful when running under operating systems, such as Windows or NT, that do not permit direct screen writes. This function is also useful for applications that wish to store information about errors that occur into a disk file. The disk file output could be reviewed at a later time.

    Preprocessing, Compiling and Linking

GPRE is a preprocessor which takes a host language source code as input and translates InterBase SQL and DSQL commands into host language source code. The output host language source code can then be complied by the host language compiler. GPRE converts InterBase database variables into host language variables and automatically generates certain function calls and data structures needed by the database access routines. Each hardware platform supports only certain languages.

GPRE uses the file extension and command line switch to determine the host language of the input file. All the command line switches are described in the programmer's Guide on pages 284-286. Some of the command line options are "d[atabase] dbname", "m[anual]", "password pw" and "user username".

The "d[atabase] dbname" options declares and attaches to the database named dbname. This option should be used with programs that do not explicitly attach to a database.

The "m[anual]" command line switch suppress the automatic generation of transactions. This option should be used with all SQL programs that create and maintain their own transactions. This is command line option is required for all DSQL programs.

The "user username" and "password pw" command line switches are used to specify a username and password that may be required to attach to a database. These options can be used if the database requires a username or password and are not specified in the program.

The input file for GPRE can have the following extensions to determine which host language is being used. If the input file is C, the file extension should be .e. If the input file is C++, the file extension should be .exx. The other languages you can license GPRE for are use with other ADA, COBOL, FORTRAN and Pascal. Each of these host languages have a default extension and command line switch.

After preprocessing your program with GPRE and compiling with your host language compiler, you must link your object modules. The link options are different for each operating system. You should look in the on-line readme in the InterBase directory for information about linking on your system.

    Conclusion

This introduction should allow you begin writing host language programs using SQL database statements. You should be able to connect to databases, create transactions, select from the database, update and insert into the database and handle errors.

Server Response from: ETNASC03