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++