Example of retrieving row and collection data from the database

The sample program, rcselect.c, retrieves row and collection data from the database and displays it.

This example also illustrates the fact that the same client functions can use row and collection handles interchangeably.

You can find the rcselect.c file in the %INFORMIXDIR%/demo/clidemo directory on UNIX™ and in the %INFORMIXDIR%\demo\odbcdemo directory in Windows™. You can also find instructions on how to build the odbc_demo database in the same location.
/*
**      rcselect.c
**
** To access rows and collections
**    OBDC Functions:
**       SQLBindParameter
**       SQLConnect
**       SQLDisconnect
**       SQLExecDirect
**       SQLFetch
**       SQLFreeStmt
*/

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

#ifndef NO_WIN32
#include <io.h>
#include <windows.h>
#include <conio.h>
#endif /*NO_WIN32*/

#include "infxcli.h"

#define BUFFER_LEN     25
#define ERRMSG_LEN     200

UCHAR   defDsn[] = "odbc_demo";

int checkError (SQLRETURN     rc,
               SQLSMALLINT    handleType,
               SQLHANDLE      handle,
               char           *errmsg)
{
   SQLRETURN      retcode = SQL_SUCCESS;
 
   SQLSMALLINT    errNum = 1;
   SQLCHAR        sqlState[6];
   SQLINTEGER     nativeError;
   SQLCHAR        errMsg[ERRMSG_LEN];
   SQLSMALLINT    textLengthPtr;

   if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO))
   {
      while (retcode != SQL_NO_DATA)
      {
         retcode = SQLGetDiagRec (handleType, handle, errNum, sqlState,
               &nativeError, errMsg, ERRMSG_LEN, &textLengthPtr);

         if (retcode == SQL_INVALID_HANDLE)
         {
            fprintf (stderr, "checkError function was called with an
                  invalid handle!!\n");
            return 1;
         }

         if ((retcode == SQL_SUCCESS)   ||   (retcode == SQL_SUCCESS_WITH_INFO))
            fprintf (stderr, "ERROR: %d:  %s : %s \n", nativeError,
                  sqlState, errMsg);

         errNum++;
      }

      fprintf (stderr, "%s\n", errmsg);
      return 1;   /* all errors on this handle have been reported */
   }
   else
      return 0;   /* no errors to report */
}

/*
**  Executes the given select statement and assumes the results will be
**  either rows or collections. The 'hrc' parameter may reference either
**  a row or a collection. Rows and collection handles may often be used
**  interchangeably.
**
**  Each row of the select statement will be fetched into the given row or
**  collection handle.  Then each field of the row or collection will be
**  individually converted into a character buffer and displayed.
**
**  This function returns 0 if an error occurs, else returns 1
**
*/

int do_select  (SQLHDBChdbc,
               char*    select_str,
               HINFX_RChrc)
{
   SQLHSTMT    hRCStmt;
   SQLHSTMT    hSelectStmt;
   SQLRETURN   rc = 0;

   short       index, rownum;
   short       position = SQL_INFX_RC_ABSOLUTE;
   short       jump;

   char        fname[BUFFER_LEN];
   char        lname[BUFFER_LEN];
   char        rc_data[BUFFER_LEN];

   SQLINTEGER            cbFname = 0, cbLname = 0, cbHrc = 0;
   SQLINTEGERcbPosition = 0, cbJump = 0, cbRCData = 0;

/* STEP A.  Allocate the statement handles for the select statement and
   **       the statement used to retrieve the row/collection data.
   */

   /* Allocate the statement handle */
   rc = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hRCStmt);
   if (checkError (rc, SQL_HANDLE_DBC, hdbc, "Error in Step A -- Statement
         Handle Allocation failed for row/collection
         statement\nExiting!!"))
      return 0;

   /* Allocate the statement handle */
   rc = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hSelectStmt);
   if (checkError (rc, SQL_HANDLE_DBC, hdbc, "Error in Step A -- Statement
         Handle Allocation failed for select statement\nExiting!!"))
      return 0;

   fprintf (stdout, "STEP A done...statement handles allocated\n");


/* STEP B.  Execute the select statement.
   **       Bind the result set columns -
   **       --  col1 = fname
   **       col2 = lname
   **       col3 = row/collection data
   */

   /* Execute the select statement */
   rc = SQLExecDirect (hSelectStmt, select_str, SQL_NTS);
   if (checkError (rc, SQL_HANDLE_STMT, hSelectStmt, "Error in Step B --
         SQLExecDirect failed\n"))
      return 0;

   /* Bind the result set columns */
   rc = SQLBindCol (hSelectStmt, 1, SQL_C_CHAR, (SQLPOINTER)fname,
         BUFFER_LEN, &cbFname);
   if (checkError (rc, SQL_HANDLE_STMT, hSelectStmt, "Error in Step B --
         SQLBindCol failed for column 'fname'\n"))
      return 0;

   rc = SQLBindCol (hSelectStmt, 2, SQL_C_CHAR, (SQLPOINTER)lname,
         BUFFER_LEN, &cbLname);
   if (checkError (rc, SQL_HANDLE_STMT, hSelectStmt, "Error in Step B --
         SQLBindCol failed for column 'lname'\n"))
      return 0;

   rc = SQLBindCol (hSelectStmt, 3, SQL_C_BINARY, (SQLPOINTER)hrc,
         sizeof(HINFX_RC), &cbHrc);
   if (checkError (rc, SQL_HANDLE_STMT, hSelectStmt, "Error in Step B --
         SQLBindCol failed for row/collection column\n"))
      return 0;

   fprintf (stdout, "STEP B done...select statement executed and result set
      columns bound\n");


/* STEP C. Retrieve the results.
   */

   for (rownum = 1;; rownum++)
   {
      rc = SQLFetch (hSelectStmt);
      if (rc == SQL_NO_DATA_FOUND)
      {
         fprintf (stdout, "No data found...\n");
         break;
      }
      else if (checkError (rc, SQL_HANDLE_STMT, hSelectStmt, "Error in
            Step C -- SQLFetch failed\n"))
         return 0;


      fprintf(stdout, "Retrieving row number %d:\n\tfname -- %s\n\tlname --
         %s\n\tRow/Collection Data --\n", rownum, fname, lname);

      /*  For each row in the result set, display each field of the
            retrieved row/collection */
      for (index = 1;; index++)
      {
         strcpy(rc_data, "<null>");

         /* Each value in the local row/collection will be fetched into a
          * character buffer and displayed using fprintf().
          */

         rc = SQLBindParameter (hRCStmt, 1, SQL_PARAM_OUTPUT, SQL_C_CHAR,
               SQL_CHAR, 0, 0, rc_data, BUFFER_LEN, &cbRCData);
         if (checkError (rc, SQL_HANDLE_STMT, hRCStmt, "Error in Step C --
               SQLBindParameter failed (param 1)\n"))
            return 0;

         rc = SQLBindParameter (hRCStmt, 2, SQL_PARAM_INPUT, SQL_C_BINARY,
               SQL_INFX_RC_COLLECTION, sizeof(HINFX_RC), 0, hrc,
               sizeof(HINFX_RC), &cbHrc);
         if (checkError (rc, SQL_HANDLE_STMT, hRCStmt, "Error in Step C --
               SQLBindParameter failed (param 2)\n"))
            return 0;

         rc = SQLBindParameter (hRCStmt, 3, SQL_PARAM_INPUT, SQL_C_SHORT,
               SQL_SMALLINT, 0, 0, &position, 0, &cbPosition);
         if (checkError (rc, SQL_HANDLE_STMT, hRCStmt, "Error in Step C --
               SQLBindParameter failed (param 3)\n"))
            return 0;

         jump = index;
         rc = SQLBindParameter (hRCStmt, 4, SQL_PARAM_INPUT, SQL_C_SHORT,
               SQL_SMALLINT, 0, 0, &jump, 0, &cbJump);
         if (checkError (rc, SQL_HANDLE_STMT, hRCStmt, "Error in Step C --
               SQLBindParameter failed (param 4)\n"))
            return 0;

         rc = SQLExecDirect(hRCStmt, "{ ? = call ifx_rc_fetch( ?, ?, ? )     }",
               SQL_NTS);
         if (rc == SQL_NO_DATA_FOUND)
         {
            break;
         }  
         else if (checkError (rc, SQL_HANDLE_STMT, hRCStmt, "Error in
               Step C -- SQLExecDirect failed\n"))
            return 0;

         /* Display retrieved row */
         fprintf(stdout, "\t\t%d: %s\n", index, rc_data);
      }    
   }  

   fprintf (stdout, "STEP C done...results retrieved\n");

   /* Free the statement handles */
   SQLFreeHandle (SQL_HANDLE_STMT, hSelectStmt);
   SQLFreeHandle (SQL_HANDLE_STMT, hRCStmt);

   return 1; /* no error */
}

/*
 * This function allocates the row and collection buffers, passes
 * them to the do_select() function, along with an appropriate select
 * statement and then frees all allocated handles.
 */
int main (long argc,
         char *argv[])
{            
   /* Declare variables
   */

   /* Handles */
   SQLHDBC     hdbc;
   SQLHENV     henv;
   SQLHSTMT    hstmt;
   HINFX_RC    hrow, hlist;

   /* Miscellaneous variables */

   UCHAR       dsn[20];/*name of the DSN used for connecting to the
                        database*/
   SQLRETURN   rc = 0;
   int         in;

   int         data_size = SQL_NTS;
   char*       listSelectStmt = "SELECT fname, lname, contact_dates FROM
               customer";
   char*       rowSelectStmt = "SELECT fname, lname, address FROM
               customer";

   SQLINTEGER  cbHlist = 0, cbHrow = 0;


/* STEP 1.  Get data source name from command line (or use default).
   **       Allocate environment handle and set ODBC version.
   **       Allocate connection handle.
   **       Establish the database connection.
   **       Allocate the statement handle.
   */   

   /* If(dsn is not explicitly passed in as arg) */
   if (argc != 2)
   {
      /* Use default dsn - odbc_demo */
      fprintf (stdout, "\nUsing default DSN : %s\n", defDsn);
      strcpy ((char *)dsn, (char *)defDsn);
   }
   else
   {
      /* Use specified dsn */
      strcpy ((char *)dsn, (char *)argv[1]);
      fprintf (stdout, "\nUsing specified DSN : %s\n", dsn);
   }

   /* Allocate the Environment handle */
   rc = SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
   if (rc != SQL_SUCCESS)
   {
      fprintf (stdout, "Environment Handle Allocation failed\nExiting!!");
      return (1);
   }

   /* Set the ODBC version to 3.5 */
   rc = SQLSetEnvAttr (henv, SQL_ATTR_ODBC_VERSION,
         (SQLPOINTER)SQL_OV_ODBC3, 0);
   if (checkError (rc, SQL_HANDLE_ENV, henv, "Error in Step 1 --
         SQLSetEnvAttr failed\nExiting!!"))
      return (1);

   /* Allocate the connection handle */
   rc = SQLAllocHandle (SQL_HANDLE_DBC, henv, &hdbc);
   if (checkError (rc, SQL_HANDLE_ENV, henv, "Error in Step 1 -- Connection
         Handle Allocation failed\nExiting!!"))
      return (1);

   /* Establish the database connection */
   rc = SQLConnect (hdbc, dsn, SQL_NTS, "", SQL_NTS, "", SQL_NTS);
   if (checkError (rc, SQL_HANDLE_DBC, hdbc, "Error in Step 1 -- SQLConnect
         failed\n"))
      return (1);

   /* Allocate the statement handle */
   rc = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmt);
   if (checkError (rc, SQL_HANDLE_DBC, hdbc, "Error in Step 1 -- Statement
         Handle Allocation failed\nExiting!!"))
      return (1);

   fprintf (stdout, "STEP 1 done...connected to database\n");


/* STEP 2.  Allocate an unfixed collection handle.
   **       Retrieve database rows containing a list.
   **       Reset the statement parameters.
   */

   /* Allocate an unfixed list handle */
   rc = SQLBindParameter (hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_BINARY,
         SQL_INFX_RC_LIST, sizeof(HINFX_RC), 0, &hlist, sizeof(HINFX_RC),
         &cbHlist);
   if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 2 --
         SQLBindParameter (param 1) failed\n"))
      goto Exit;

   rc = SQLBindParameter (hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
         0, 0, (UCHAR *) "list", 0, &data_size);
   if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 2 --
         SQLBindParameter (param 2) failed\n"))
      goto Exit;

   rc = SQLExecDirect (hstmt, "{? = call ifx_rc_create(?)}", SQL_NTS);
   if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 2 --
         SQLExecDirect failed\n"))
      goto Exit;

   /* Retrieve databse rows containing a list */
    if (!do_select (hdbc, listSelectStmt, hlist))
      goto Exit;

   /* Reset the statement parameters */
   rc = SQLFreeStmt (hstmt, SQL_RESET_PARAMS);
   if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 2 --
         SQLFreeStmt failed\n"))
      goto Exit;

   fprintf (stdout, "STEP 2 done...list data retrieved\n");
   fprintf (stdout,"\nHit <Enter> to continue...");
   in = getchar ();

/* STEP 3.  Allocate an unfixed row handle.
   **       Retrieve database rows containing a row.
   **       Reset the statement parameters.
   */

   /* Allocate an unfixed row handle */
   rc = SQLBindParameter (hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_BINARY,
         SQL_INFX_RC_ROW, sizeof(HINFX_RC), 0, &hrow, sizeof(HINFX_RC),
         &cbHrow);
   if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 3 --
         SQLBindParameter (param 1) failed\n"))
      goto Exit;

   rc = SQLBindParameter (hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
         0, 0, (UCHAR *) "row", 0, &data_size);
   if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 3 --
         SQLBindParameter (param 2) failed\n"))
      goto Exit;

   rc = SQLExecDirect (hstmt, "{? = call ifx_rc_create(?)}", SQL_NTS);
   if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 3 --
         SQLExecDirect failed\n"))
      goto Exit;

   /* Retrieve databse rows containing a row */
   if (!do_select (hdbc, rowSelectStmt, hrow))
      goto Exit;

   /* Reset the statement parameters */
   rc = SQLFreeStmt (hstmt, SQL_RESET_PARAMS);
   if (checkError (rc, SQL_HANDLE_STMT, hstmt, "Error in Step 3 --
         SQLFreeStmt failed\n"))
      goto Exit;

   fprintf (stdout, "STEP 3 done...row data retrieved\n");


/* STEP 4.  Free the row and list handles.
   */

   /* Free the row handle */
   rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY,
         SQL_INFX_RC_ROW, sizeof(HINFX_RC), 0, hrow, sizeof(HINFX_RC),
         &cbHrow);

   rc = SQLExecDirect(hstmt, (UCHAR *)"{call ifx_rc_free(?)}", SQL_NTS);

   /* Free the list handle */
   rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_BINARY,
         SQL_INFX_RC_LIST, sizeof(HINFX_RC), 0, hlist, sizeof(HINFX_RC),
         &cbHlist);

   rc = SQLExecDirect(hstmt, (UCHAR *)"{call ifx_rc_free(?)}", SQL_NTS);

   fprintf (stdout, "STEP 4 done...row and list handles freed\n");

   Exit:

/* CLEANUP: Close the statement handle.
   **       Free the statement handle.
   **       Disconnect from the datasource.
   **       Free the connection and environment handles.
   **       Exit.
   */

   /* Close the statement handle */
   SQLFreeStmt (hstmt, SQL_CLOSE);

   /* Free the statement handle */
   SQLFreeHandle (SQL_HANDLE_STMT, hstmt);

   /* Disconnect from the data source */
   SQLDisconnect (hdbc);

   /* Free the environment handle and the database connection handle */
   SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
   SQLFreeHandle (SQL_HANDLE_ENV, henv);
   fprintf (stdout,"\n\nHit <Enter> to terminate the program...\n\n");
   in = getchar ();
   return (rc);