ESQL Example Program

ESQL Example Program

/**************************************************************************    
   * test program: 1. use sqlca, dbenvca as global variable 
   * 2. connect to database 
   * 3. insert (name, telno) to info table    
   * 4. select 
   * 5. delete 
   * 6. update 
   * 7. fetch all data from info table    
   * 8. disconnect db 
   **************************************************************************/    
/* The following structure must include in program and must be accessible *in your whole program by either global or pass in func */ 
   exec sql include sqlca; /* declare global    */ 
   exec sql include dbenvca; /* declare    global */

/* This macro is for checking and showing error and warning messages */ 
   #define chkErr() \ 
    { \ 
    if (SQLCODE && SQLCODE != SQL_NO_DATA_FOUND)    \ 
    { \ 
    if (SQLCODE == SQL_SUCCESS_WITH_INFO)    \ 
    printf("WARN(%d):%s\n", sqlca.sqlerrd[0],    sqlca.sqlerrmc); \ 
    else \ 
    { \ 
    printf("ERR(%d):%s\n", sqlca.sqlerrd[0],    sqlca.sqlerrmc); \ 
    goto program_exit; \ 
    } \ 
    } \ 
    }

main() 
    { 
    EXEC SQL begin declare section;    
    char user[8], passwd[8]; /* connection information */ 
    char dbname[18]; /* char type is fix length string */ 
    char name[21], tel[21]; 
    EXEC SQL end declare section;

 strcpy(dbname, "TESTDB");    /* get db,user,password info */ 
    strcpy(user, "SYSADM");    
    strcpy(passwd, "");

 /* you can also directly type exec sql connect to db_name user_id 
    * passwd */ 
    exec sql connect to :dbname :user :passwd;

 /* clear all tuples in info table     
 /* it's for making sure every time you run this program you will get  
    * the same result */ 
    exec sql delete from info;

 /* set autocommit mode off */    
    exec sql set autocommit off;

 /* test EXEC SQL INSERT (name, tel) */ 
    strcpy(name, "John Smith");    
    strcpy(tel, "7654321");    
    exec sql insert into info values (:name, :tel); 
    chkErr(); /* if any error occur, print out error */

 /* test EXEC SQL INSERT (name, tel) */ 
    strcpy(name, "Mary Black");    
    strcpy(tel, "7418010");    
    exec sql insert into info values (:name, :tel); 
    chkErr(); /* if any error occur, print out error */

 /* test EXEC SQL INSERT (name, tel) */ 
    strcpy(name, "Jane White");    
    strcpy(tel, "1111111");    
    exec sql insert into info values (:name, :tel); 
    chkErr(); /* if any error occur, print out error */

 /* test EXEC SQL SELECT (query John Smith's telephone number */ 
    strcpy(name, "John Smith");    
    exec sql select tel from info where name = :name into :tel; 
    chkErr(); /* if any error occur, print out error */

 printf("John's telphone is %s\n", tel);

 /* test EXEC SQL DELETE (delete John Smith's tuple) */ 
    strcpy(name, "John Smith");    
    exec sql delete from info where name = :name; 
    chkErr(); /* if any error occurs, print out error */

 /* test EXEC SQL UPDATE (update Jane White's telephone number */ 
    strcpy(name, "Jane White");    
    strcpy(tel, "7214346");    
    exec sql update info set tel = :tel where name = :name; 
    chkErr(); /* if any errors occur, print out error */ 
     
    /* test CURSOR, fetch all tuple in info table */ 
    /* test EXEC SQL DECLARE CURSOR for query all data in info table */ 
    exec sql declare myCur cursor for     
    select name,tel from info into :name, :tel; 
    chkErr();

 /* test EXEC SQL OPEN CURSOR */    
    exec sql open myCur; /* open cursor */ 
    chkErr();

 while (1) 
    { 
    /* test EXEC SQL FETCH CURSOR */    
    Exec SQL fetch myCur; /* fetch cursor */ 
    chkErr(); /* print out error, if it's not SQL_NO_DATA_FOUND */ 
    if (SQLCODE) /* If any SQLCODE occurs */ 
    { /* If not warning (SQL_SUCCESS_WITH_INFO), break from 
    loop */ 
    if (SQLCODE != SQL_SUCCESS_WITH_INFO)    
    break; 
    } 
    printf("Name = %s, Tel = %s\n", name, tel); 
    }

 /* test EXEC SQL CLOSE CURSOR */    
    exec sql close myCur; 
    chkErr();

program_exit: 
    /* if no error occurs, then commit transaction */ 
    if (SQLCODE == SQL_SUCCESS || SQLCODE == SQL_NO_DATA_FOUND || 
    SQLCODE == SQL_SUCCESS_WITH_INFO)    
    { 
    exec sql commit work; 
    if (SQLCODE) 
    printf("ERR(%d):%s\n", sqlca.sqlerrd[0], sqlca.sqlerrmc); 
    } 
    else /* if any error occurs, then rollback transaction */ 
    exec sql rollback work;

 exec sql disconnect; 
    if (SQLCODE) 
    printf("ERR(%d):%s\n", sqlca.sqlerrd[0], sqlca.sqlerrmc);  
    }