Transaction Control

You can use two methods to specify the boundaries of transactions with SQL statements. In the most common method,
you specify the start of a multistatement transaction by executing the BEGIN WORK statement. In databases that are created
with the MODE ANSI option, no need exists to mark the beginning of a transaction. One is always in effect;
you indicate only the end of each transaction.

In both methods, to specify the end of a successful transaction, execute the COMMIT WORK statement. This statement tells the database
server that you reached the end of a series of statements that must succeed together. The database server does whatever is necessary
to make sure that all modifications are properly completed and committed to disk.

A program can also cancel a transaction deliberately by executing the ROLLBACK WORK statement. This statement asks the database
server to cancel the current transaction and undo any changes.

Informix SQL Guide

http://www.ics.uci.edu/~dbclass/ics184/htmls/Informix_guide.html

Dynamic SQL - with Informix

• Dynamic SQL allows a program to form an SQL statement during execution, so that the statement can be determined by user input. The action is performed in two steps.

  1. Preparing a statement

     It uses PREPARE statement to have the database server examine the statement text and prepare it for execution.

         EXEC SQL prepare  from '';
     

  2. Executing prepared SQL

     It uses EXECUTE statement to execute the prepared statement.

         EXEC SQL execute  from '';
     

  For instance, if you want to inquire the information of some students, you can use the following dynamic SQL for query:

      EXEC SQL BEGIN DECLARE SECTION;
     int sid;
     char sname[10];
     EXEC SQL END DECLARE SECTION;
  
     EXEC SQL prepare query_stud from 'select id,name from student where name=?';
  
     EXEC SQL execute query_stud into :sid, sname using 'Mike';
     printf("Student: (%d, %s)\n", sid, sname);
  
     EXEC SQL execute query_stud into :sid, sname using 'David';
     printf("Student: (%d, %s)\n", sid, sname);
  

  The above produces the following results:

      Student: (2, Mike)
     Student: (9, David)
  

• Embedded SQL(including cursor)

  SQL statements can be embedded in the C and COBOL program. In C, all the statement is preceded by "EXEC SQL". There is a detail example in the next section.

  A cursor is a special data object that represents the current state of a query. It is used for retrieving multiple resulting rows of query. It is used in 5 steps:

  1. Declaring a Cursor

         EXEC SQL DECLARE  CURSOR FOR ;
     

  2. Opening a Cursor

         EXEC SQL OPEN ;
     

  3. Fetching Rows

         EXEC SQL FETCH ;
     

  4. Closing a Cursor

         EXEC SQL CLOSE ;
     

  5. Freeing a Cursor

         EXEC SQL FREE ;
     

     For instance, the following program lists the information of all students.

         EXEC SQL BEGIN DECLARE SECTION;
        int sid;
        char sname[10];
        EXEC SQL END DECLARE SECTION;
     
        EXEC SQL DECLARE cursor_stud CURSOR FOR select id,name from student;
        EXEC SQL OPEN cursor_stud;
        while ( SQLCODE == 0 ) {
            EXEC SQL OPEN cursor_stud INTO :sid, :sname;
            if ( SQLCODE == 0 )
              printf("Student (%d, %s)\n", sid, sname);
         }
        EXEC SQL CLOSE cursor_stud;
        EXEC SQL FREE cursor_stud;
     

     SQLCODE is set to 0 by the database if the select statement is valid, otherwise set to 100. It is used to detect the end of data.

---

Informix Programming Guide Setting Environment

• setenv INFORMIXSERVER rodan_ius_net
• setenv PATH $INFORMIXDIR/bin:$PATH
• setenv LD_LIBRARY_PATH $INFORMIXDIR/lib:$INFORMIXDIR/lib/esql:$INFORMIXDIR/lib/dmi:$INFORMIXDIR/lib/c++

How do I compile ESQL/C code for use with C++?

There have been a variety of answers, but not any one systematic answer.
Now there is what I believe is a definitive answer -- it's all in the
attached shell archive.

What do you get?
*   Updated ESQL/C prototypes for 4.00, 4.1x, 5.0x and 7.2x
*   An esql++ compiler
*   A pair of patch files for modifying your standard ESQL/C compiler.
   Only use these if your ESQL/C is version 4.1 or 5.x; later versions
   already have the necessary change).
*   A minimal test application.
*   No documentation apart from this message!

What do you need to do?
*   Unpack the shell archive.
*   Place the esql* headers in either $INFORMIXDIR/incl (version 4)
   or $INFORMIXDIR/incl/esql (version 5 or later)
*   Place the esql++ script in $INFORMIXDIR/bin
*   Decide which version of ESQL/C you are using and set the
   ESQLC_VERSION environment variable to a value such as:
   export ESQLC_VERSION=506    # 5.06.UC1
*   Optionally modify esql++ to give the correct default value of
   of ESQLC_VERSION.
*   Decide which C++ compiler you use -- if it isn't CC, set:
   export INFORMIXC=g++
*   Try compiling the test application:
   esql++ -o testapp testmain.ec testsub.ec
*   Run it against your local stores database -- it should say '2'.

What if you can't modify your INFORMIXDIR?
*   Copy $INFORMIXDIR/bin/esql to somewhere where you can modify it
   (eg /some/where/bin/esql)
*   Patch it if necessary.
*   Place the headers in another directory (eg /some/where/include).
*   Modify esql++ so that:
   -- arglist starts of with -I/some/where/include as one component
   -- ESQL defaults to /some/where/bin/esql
*   If you don't have to patch the esql script, you don't have to copy it
   to /some/where/bin and you don't need to make the corresponding
   modification in the esql++ script.

What about other versions of ESQL/C?
*   If you are still using a pre-4.00 version of ESQL/C, the chances are
   that the code which edits the 4.xx code will be useful.  However, the
   prototypes for the ESQL/C interface functions are not known and will
   have to be determined by inspection of the generated code.  Good luck!
   It will probably be easier to upgrade to a later version.
*   I haven't been able to test version 8.xx ESQL/C; it isn't available to
   me.  However, the indications are that it will need the same treatment
   as the other post-5.00 implementations of ESQL/C.
*   Testing with the version 9.10 ESQL/C compiler indicates that this
   script is still necessary.
*   Ideally, you won't need this script at all for post-9.xx versions.  If
   you are using some later version, you will need to revise the script so
   that the case statement on $ESQLC_VERSION handles the new (presumably
   4-digit) version number.
*   Note that ESQL/C 7.24 has some support for C++ built in.  In
   particular, it preprocesses files with the .ecpp extension and then
   compiles them with the C++ compiler.  It may not link the object code
   with the C++ compiler; if it doesn't, you'd have to do that by running
   the link command it with INFORMIXC="C++ compiler" in the environment.

Which versions of ESQL/C are are known to work with this?
*   The code has been tested against Informix-ESQL/C versions 4.12.UC1,
   5.06.UC1, 6.00.UE1, 7.13.UC1, 7.22.UC2, 9.10.UC2 on a Sun Sparc 20
   running Solaris 2.5.1 with both SUNWspro C++ SPARCompiler SC4.0.1 and
   GNU G++ 2.7.2.2.

Are there any known bugs?
*   Life will get very difficult if you pass arguments with quotes or
   spaces in them to your compiler:

       esql++ -DOPENQUOTE="'" -DSOMMAT="string with blanks" thingy.ec

   I do have a solution: it is another program called escape which
   deals with precisely that sort of problem.
       arglist="$arglist `escape $arg`"
   But that requires some more source and isn't generally necessary.
   Also, the underlying esql script does not work reliably with such
   arguments, so the changes needed are even more extensive.

*   The esql7_20.h header is needed until bug B73951 is fixed.