Column-wise and row-wise binding example

An application can bind rows and columns of a result set to a structure.

The following example shows an application that binds rows and columns of a result set to a structure.

Figure 1. An application that retrieves data into an array by column and by row
/* ... */
#define NUM_CUSTOMERS 25
    SQLCHAR         stmt[] =
    {  "WITH "  /* Common Table expression (or Define Inline View) */
         "order (ord_num, cust_num, prod_num, quantity, amount) AS "
         "( "
         "SELECT c.ord_num, c.cust_num, l.prod_num, l.quantity,  "
                 "price(char(p.price, '.'), p.units, char(l.quantity, '.')) "
            "FROM ord_cust c, ord_line l, product p  "
            "WHERE c.ord_num = l.ord_num AND l.prod_num = p.prod_num  "
             "AND cust_num = CNUM(cast (? as integer)) "
         "), "
         "totals (ord_num, total) AS "
         "( "
          "SELECT ord_num, sum(decimal(amount, 10, 2))  "
          "FROM order GROUP BY ord_num "
         ") "
       /* The 'actual' SELECT from the inline view */
       "SELECT order.ord_num, cust_num, prod_num, quantity,  "
              "DECIMAL(amount,10,2) amount, total "
        "FROM order, totals  "
        "WHERE order.ord_num = totals.ord_num "
     };
/* Array of customers to get list of all orders for */
    SQLINTEGER     Cust[]=
    {
        10, 20, 30, 40, 50, 60, 70, 80, 90, 100,
        110, 120, 130, 140, 150, 160, 170, 180, 190, 200,
        210, 220, 230, 240, 250
    };
#define  NUM_CUSTOMERS sizeof(Cust)/sizeof(SQLINTEGER)    
/* Row-wise (Includes buffer for both column data and length) */
    struct {
        SQLINTEGER      Ord_Num_L;
        SQLINTEGER      Ord_Num;
        SQLINTEGER      Cust_Num_L;
        SQLINTEGER      Cust_Num;
        SQLINTEGER      Prod_Num_L;
        SQLINTEGER      Prod_Num;
        SQLINTEGER      Quant_L;
        SQLDOUBLE       Quant;
        SQLINTEGER      Amount_L;
        SQLDOUBLE       Amount;
        SQLINTEGER      Total_L;
        SQLDOUBLE       Total;
    }               Ord[ROWSET_SIZE];
    SQLUINTEGER     pirow = 0;
    SQLUINTEGER     pcrow;
    SQLINTEGER      i;
    SQLINTEGER      j;
/* ... */
    /* Get details and total for each order row-wise */
    rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    rc = SQLParamOptions(hstmt, NUM_CUSTOMERS, &pirow);
    rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER,
                          0, 0, Cust, 0, NULL);
    rc = SQLExecDirect(hstmt, stmt, SQL_NTS);
    /* SQL_ROWSET_SIZE sets the max number  */
    /* of result rows to fetch each time    */
    rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROWSET_SIZE, 
                        (void *)ROWSET_SIZE, 0);
    /* Set size of one row, used for row-wise binding only */
    rc = SQLSetStmtAttr(hstmt, SQL_ATTR_BIND_TYPE, 
                        (void *)sizeof(Ord) / ROWSET_SIZE, 0); 
    /* Bind column 1 to the Ord_num Field of the first row in the array*/
    rc = SQLBindCol(hstmt, 1, SQL_C_LONG, (SQLPOINTER) &Ord[0].Ord_Num, 0,
                    &Ord[0].Ord_Num_L);
    /* Bind remaining columns ... */
/* ... */ 
   /* NOTE: This sample assumes that an order never has more
             rows than ROWSET_SIZE. A check should be added below to call
             SQLExtendedFetch multiple times for each result set.
    */
    do  /* for each result set .... */
    { rc = SQLExtendedFetch(hstmt, SQL_FETCH_NEXT, 0, &pcrow, NULL);
      if (pcrow > 0) /* if 1 or more rows in the result set */
      {
        i = j = 0;
        printf("**************************************\n");
        printf("Orders for Customer: 0].Cust_Num);
        printf("**************************************\n");
        while (i < pcrow)
        {   printf("\nOrder #: i].Ord_Num);
            printf("     Product  Quantity         Price\n");
            printf("     -------- ---------------- ------------\n");
            j = i;
            while (Ord[j].Ord_Num == Ord[i].Ord_Num)
            {   printf("    %8ld %16.7lf %12.2lf\n",
                       Ord[i].Prod_Num, Ord[i].Quant, Ord[i].Amount);
                i++;
            }
            printf("                               ============\n");
            printf("                              j].Total);
        } /* end while */
      } /* end if */
    }
    while ( SQLMoreResults(hstmt) == SQL_SUCCESS);
/* ... */