Examples of how to use XL C/C++ programs to request DB2 services

The examples in this section demonstrate how to code C and C++ programs with embedded SQL statements. You can use them with either the XL C/C++ DB2® coprocessor or the DB2 C/C++ precompiler.

Example CCNGDB4 demonstrates how to code a C program with embedded SQL statements. In Figure 1, a program CCNGDB4 creates a table called CTAB1, inserts literal values into the table, and drops the table. You can use this example either by compiling the program with the SQL option in effect or by running the program through the DB2 C/C++ precompiler, and then compiling the generated code with the NOSQL option in effect.

Figure 1. Using DB2 with C

/* this example demonstrates how to use SQL with C */

#include <string.h>
#include <stdio.h>

EXEC SQL INCLUDE SQLCA;

int main(void)
{

    if (CreaTab() == -1)
    {
       printf("Test Failed in table-creation.\n");
       exit(-1);
    }

    if (DropTab() == -1)
    {
       printf("Test Failed in table-dropping.\n");
       exit(-1);
    }
    printf("Test Successful.\n");
    return(0);
}

/*
 * This routine creates the table CTAB1 and inserts some values
 * into it
*/

int CreaTab(void)
{

    EXEC SQL CREATE TABLE CTAB1
             ( EMPNO    CHAR(6) NOT NULL,
               FIRSTNME VARCHAR(12) NOT NULL,
               LASTNME  VARCHAR(15) NOT NULL,
               WORKDEPT CHAR(3) NOT NULL,
               PHONENO  CHAR(7),
               EDUCLVL  SMALLINT,
               SALARY   FLOAT(21) );

    if (sqlca.sqlcode != 0)
    {
       printf("ERROR - SQL code returned non-zero for "
              "creation of CTAB1, received %d\n",sqlca.sqlcode);
       return(-1);
    }

/*  Now insert some values into the table */

    EXEC SQL INSERT INTO CTAB1 VALUES
             ( '097892','John','Adams','003','8883945',3,29500.00 );
    EXEC SQL INSERT INTO CTAB1 VALUES
             ( '000002','Joe','Smith','004','8883791',NULL,25500.00 );
    EXEC SQL INSERT INTO CTAB1 VALUES
             ( '043929','Ralph','Holland','001','8888734',1,NULL);
    EXEC SQL INSERT INTO CTAB1 VALUES
             ( '000010','Holly','Waters','001','8884590',3,29550.00 );

    if (sqlca.sqlcode != 0)
    {
       printf("ERROR - SQL code returned non-zero for "
              "insert into tables, received %d\n",sqlca.sqlcode);
       return(-1);
    }
    return(0);
}

/*
 *  This routine will drop the table.
*/

int DropTab(void)
{
    EXEC SQL DROP TABLE CTAB1;
    if (sqlca.sqlcode != 0)
    {
       printf("ERROR - SQL code returned non-zero for "
              "drop of CTAB1 received %d??\n",sqlca.sqlcode);
       return(-1);
    }
    EXEC SQL COMMIT WORK;
    return(0);
}

Figure 2 is a C++ code example with embedded SQL statements. The sample code creates, populates, updates, and drops a table called CTAB1V. You can use this example either by compiling the program with the SQL option in effect or by running the program through the DB2 C/C++ precompiler, and then compiling the generated code with the NOSQL option in effect.

Figure 2. Using DB2 with C/C++

#include <iostream>

// The test case information
typedef char TestType;

#define NUM_ROWS  3
#define IN_VALUE  {'A', 'B', 'C'}
#define OUT_VALUE {'D', 'E', 'F'}

EXEC SQL INCLUDE SQLCA;

class SqlTestTable {
  public:
    // The constructor and destructor create and drop the test table
    SqlTestTable() {
      EXEC SQL CREATE TABLE CTAB1V (
        ID        INTEGER     NOT NULL,
        TESTVAR   CHAR(1)     NOT NULL
      ) IN DATABASE DSNUCOMP;
    
      if (sqlca.sqlcode != 0) {
        std::cout << "ERROR - SQL code returned " << sqlca.sqlcode
         << " for creation of CTAB1V.\n";
      }
    }
    ~SqlTestTable() {
      EXEC SQL DROP TABLE CTAB1V;    // Clean up the database 
    
      if (sqlca.sqlcode != 0) {
        std::cout << "ERROR - SQL code returned " << sqlca.sqlcode
         << " for drop of CTAB1V.\n";
      }
      EXEC SQL COMMIT WORK;
    }
    
    int insertRow(int idToAdd, TestType inputData) {
      int returnValue = 55;
    
      EXEC SQL BEGIN DECLARE SECTION;
        int idForRow = idToAdd;
        TestType inputValue = inputData;
      EXEC SQL END DECLARE SECTION;
    
      EXEC SQL INSERT INTO CTAB1V
        VALUES ( :idForRow, :inputValue );
    
      if (sqlca.sqlcode != 0) {
        std::cout << "ERROR - SQL code returned " << sqlca.sqlcode
         << " for insert into tables.\n";
        returnValue = 66; // Not returned immediately in case cleanup is needed
      }
    
     return returnValue;
    }

    int updateTable(int idToChange, TestType inputData) {
      int returnValue = 55;
    
      EXEC SQL BEGIN DECLARE SECTION;
        int idForRow = idToChange;
        TestType inputValue = inputData;
      EXEC SQL END DECLARE SECTION;
    
      EXEC SQL UPDATE CTAB1V
        SET TESTVAR = :inputValue
        WHERE ID = :idForRow;
    
      if (sqlca.sqlcode != 0) {
        std::cout << "ERROR - SQL code returned "
         << sqlca.sqlcode << " for update in tables.\n";
        returnValue = 66; // Not returned immediately in case cleanup is needed
      }
    
      return returnValue;
    }
    
  int checkTable(int idToCheck, TestType value) {
      int returnValue = 55;
    
      // Try other format variable names
      EXEC SQL BEGIN DECLARE SECTION;
        int idForRow = idToCheck;
        TestType check_var;
      EXEC SQL END DECLARE SECTION;
    
      EXEC SQL SELECT TESTVAR INTO :check_var 
        FROM CTAB1V
        WHERE ID = :idForRow;
     
      if (sqlca.sqlcode != 0) {
        std::cout << "ERROR - SQL code returned "
         << sqlca.sqlcode << " for SELECT of the data.\n";
        return 66; // Return immediately since no cleanup; nothing else to be done
      }
      
      if (check_var != value) {
        std::cout << "ERROR - Value in table
         " << check_var << " is not the expected value " << value << ".\n";
        returnValue = 66; // Not returned immediately in case cleanup is needed
      }
    
      return returnValue;
    }
};

  int main(void) {
  SqlTestTable testTable;   // Creates the tables
  int i = 0;
  int returnValue = 55;

  TestType aLongVariableName[NUM_ROWS] = IN_VALUE;
  TestType expectedResults[NUM_ROWS] = OUT_VALUE;

  // SQL Declare's Not needed. Added to see what happens if not used as SQL vars.
  EXEC SQL BEGIN DECLARE SECTION;
    TestType inValue;
    TestType outValue;
  EXEC SQL END DECLARE SECTION;

  // Populate the table using non-host variables as function parameters
  for (i = 0; i < NUM_ROWS; i++) {
    returnValue = testTable.insertRow(i, aLongVariableName[i]);

    if (returnValue != 55) {
      return returnValue;
    }
  }

  // Check to see if the insert went OK using host variables as function parms
  for (i = 0; i < NUM_ROWS; i++) {
    inValue = aLongVariableName[i];
    returnValue = testTable.checkTable(i, inValue);

    if (returnValue != 55) {
      return returnValue;
    }
  }

  // Update the values using host variables as function parameters
  for (i = 0; i < NUM_ROWS; i++) {
    outValue = expectedResults[i];
    returnValue = testTable.updateTable(i, outValue);

    if (returnValue != 55) {
      return returnValue;
    }
  }

  // Check to see if the update went OK using non-host variables as function parms
  for (i = 0; i < NUM_ROWS; i++) {
    returnValue = testTable.checkTable(i, expectedResults[i]);

    if (returnValue != 55) {
      return returnValue;
    }
  }

  return returnValue;   // Deletes the table through the destructor
}