Writing an ESQL Application

Once you determine your application objectives and design how it interacts with your SQL database(s), you are ready to begin coding your ESQL application. These are typical steps to implement an ESQL session using Progress/ESQL:

  1. Declare the C variables that you need for your application, including those needed to execute ESQL statements and return error conditions.
  2. Declare the actions that the program takes when ESQL statements execute with exceptional conditions.
  3. Log into the ESQL-LIB interface to begin your ESQL session using the sqllogin() function.
  4. Execute additional ESQL-LIB functions and Progress/ESQL statements to connect databases, invoke SQL statements, and disconnect databases.
  5. Log out of the ESQL-LIB interface to end your ESQL session using the sqllogout() function.

Figure 2–1 illustrates these steps with a sample ESQL application written to query the sports database using static ESQL.

NOTE: For simplicity, this example assumes that all database connection parameters are specified on the command line and acted on by sqllogin() as part of Step 3. However, a more flexible approach is to control database access from within the application by selectively connecting and disconnecting databases using the ESQL-LIB functions, sqlconn() and sqldiscon(). For more information, see the section "Starting and Ending an ESQL Session."

 main(argc,argv)
        int argc;
        char **argv;
  {
/*1*/  /* Declare variables for error returns. */
       long SQLCODE;
       char *msg;
  
       /* Declare variables for ESQL statements. */
       EXEC SQL BEGIN DECLARE SECTION;
       char nam[30];
       long custnum;
       EXEC SQL END DECLARE SECTION;
  
/*2*/  /* Declare error goto point. */
        EXEC SQL WHENEVER SQLERROR GOTO err;
  
/*3*/  /* Log in to Progress to start an ESQL session. */
       if (sqllogin( argc-1, &argv[1], "", "") == -1)
       {
           printf("Failed to login to Progress ESQL!\n");
           while ((msg = (char *) sqlgetmsg()) != (char *) 0)
               printf("%s\n",msg);
           exit(2);
       }
  
/*4*/  /* Query and display all customer names and numbers. */
       EXEC SQL DECLARE x CURSOR FOR 
           SELECT name, cust\-num FROM customer;
       EXEC SQL OPEN x;
   
       EXEC SQL WHENEVER NOT FOUND GOTO done1;
       while (1)
       {
           EXEC SQL FETCH x INTO :nam, :custnum;
           printf("%-22s%-7ld\n",nam,custnum);
       }
  
/*5*/  /* Log out of Progress to end the ESQL session. */
   err:
       printf("Error %-3ld occured.\n", SQLCODE);
       while ((msg = (char *) sqlgetmsg()) != (char *) 0)
           printf("%s\n",msg);
   
   done1:
       sqllogout(1);
   } 

Figure 2–1: Sample ESQL Application (Not Preprocessed)

  1. ESQL requires the SQLCODE or SQLSTATE variable declaration to return error codes to your application.
  2. The application specifies a branching label to handle error conditions. In general, you declare any label for error branching in each function that contains ESQL statements.
  3. As part of the login process, ESQL connects to the database using Progress startup parameters from the command line passed in argv.
  4. The backslash (\) in cust\-num is required to specify hyphenated identifiers, because SQL does not permit the hyphenated identifiers that are allowed in Progress. However, you can preprocess and execute your application with the ESQL No Padding (-esqlnopad) startup parameter to avoid this restriction. The application also defines a standard exit point (done1) to terminate loops that operate on rows. The variable names, nam and custnum , are preceded by a colon (:).
  5. The application logs out of Progress for both error returns (at err) and normal returns (at done1).

Figure 2–2 shows the same application module after it is preprocessed.

#include "proesql.h"
   static SQLRQHDL *sql0 = (SQLRQHDL *) 0;
   main(argc,argv)
        int argc;
        char **argv;
   {
/*1*/  /* Declare variables for error returns. */
       long SQLCODE;
       char *msg;
  
       /* Declare variables for ESQL statements. */
       /* EXEC SQL BEGIN DECLARE SECTION; */
       char nam[30];
       long custnum;
       /* EXEC SQL END DECLARE SECTION; */
  
/*2*/  /* Declare error goto point. */
       /* EXEC SQL WHENEVER SQLERROR GOTO err; */

/*3*/  /* Log in to Progress to start an ESQL session. */
       if (sqllogin( argc-1, &argv[1], "", "") == -1)
       {
           printf("Failed to login to Progress ESQL!\n");
           while ((msg = (char *) sqlgetmsg()) != (char *) 0)
               printf("%s\n",msg);
           exit(2);
       }
/* Query and display all customer names and numbers. */
      /* EXEC SQL DECLARE x CURSOR FOR 
          SELECT name, cust\-num FROM CUSTOMER; */
      /* EXEC SQL OPEN x; */
      {
/*sqltxt: DECLARE x CURSOR FOR SELECT name, cust-num FROM CUSTOMER*/
  static char sqltxt[] = {
  ’D’,’E’,’C’,’L’,’A’,’R’,’E’,’ ’,’x’,’ ’,’C’,’U’,’R’,’S’,’O’,’R’,  
’ ’,’F’,’O’,’R’,’ ’,’S’,’E’,’L’,’E’,’C’,’T’,’ ’,’n’,’a’,’m’,’e’,’,’, 
’ ’,’c’,’u’,’s’,’t’,’-’,’n’,’u’,’m’,
’ ’,’F’,’R’,’O’,’M’,’,’C’,’U’,’S’,’T’,’O’,’M’,’E’,’R’,0}; 
static char sqlfmt[] = {0};
          sqlcdbind(&SQLCODE, (char *)0);
          sqlrequest(&sql0,(int)0x04,sqltxt,"x",sqlfmt,(char**)0);
          if (SQLCODE < 0) goto err;
      } 
 /* EXEC SQL WHENEVER NOT FOUND GOTO done1; */
      while (1)
      {
          /* EXEC SQL FETCH x INTO :nam, :custnum; */
          {
  /*sqltxt: FETCH x INTO esqlo0, esqlo1
    sqlfmt: c30l,*/
  static char sqltxt[] = {
  ’F’,’E’,’T’,’C’,’H’,’ ’,’x’,’ ’,’I’,’N’,’T’,’O’,’ ’, 
’e’,’s’,’q’,’l’,’o’,’0’,’,’,’ ’,’e’,’s’,’q’,’l’,’o’,’1’,0}; 
static char sqlfmt[] = {’c’,’3’,’0’,’l’,’,’,0};
              static char *sqlv[3];
              sqlv[0] = (char *) nam;
              sqlv[1] = (char *) &custnum;
              sqlv[2] = (char *)0;
              sqlcdbind(&SQLCODE, (char *)0);
              sqlfetch(sql0,(int) 0x08,sqltxt,sqlfmt,sqlv);
              if (SQLCODE == 100) goto done1;
              if (SQLCODE < 0) goto err;
          }
          printf("%-22s%-7ld\n", nam, custnum);
      }
/*5*/  /* Log out of Progress to end the ESQL session. */
   err:
       printf("Error %-3ld occured.\n", SQLCODE);
       while ((msg = (char *) sqlgetmsg()) != (char *) 0)
           printf("%s\n",msg);
   
   done1:
       sqllogout(1);
   } 

Figure 2–2: Sample ESQL Application (Preprocessed)

A practical ESQL application typically includes much more SQL than this, but these fundamental steps form the backbone of any ESQL application. The remaining sections in this chapter explain the techniques available to expand and modify this basic approach.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095