Call Level Interface Guide and Reference

Column-Wise, Row-Wise Binding Example

/* From CLI sample ordrep.c */
/* ... */
 
    SQLCHAR * stmt =
       /* Common Table expression (or Define Inline View) */
       "WITH 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 cast (cust_num 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,
    } ;
 
    /* Row-Wise (Includes buffer for both column data and length) */
    typedef 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_info ;
    ord_info ord_array[row_array_size] ;
 
    SQLUINTEGER num_rows_fetched ;
    SQLUSMALLINT row_status_array[row_array_size], i, j ;
 
/* ... */
    /* Get details and total for each order Row-Wise */
    rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
    CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
 
    rc = SQLSetStmtAttr( hstmt,
                         SQL_ATTR_PARAMSET_SIZE,
                         ( SQLPOINTER ) row_array_size,
                         0
                       ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindParameter( hstmt,
                           1,
                           SQL_PARAM_INPUT,
                           SQL_C_LONG,
                           SQL_INTEGER,
                           0,
                           0,
                           Cust,
                           0,
                           NULL
                         ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLExecDirect( hstmt, stmt, SQL_NTS ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    /* SQL_ROWSET_SIZE sets the max number of result rows to fetch each time */
    rc = SQLSetStmtAttr( hstmt,
                         SQL_ATTR_ROW_ARRAY_SIZE,
                         ( SQLPOINTER ) row_set_size,
                         0
                       ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    /* Set Size of One row, Used for Row-Wise Binding Only */
    rc = SQLSetStmtAttr( hstmt,
                         SQL_ATTR_ROW_BIND_TYPE,
                         ( SQLPOINTER ) sizeof( ord_info ) ,
                         0
                       ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLSetStmtAttr( hstmt,
                         SQL_ATTR_ROW_STATUS_PTR,
                         ( SQLPOINTER ) row_status_array,
                         0
                       ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLSetStmtAttr( hstmt,
                         SQL_ATTR_ROWS_FETCHED_PTR,
                         ( SQLPOINTER ) &num_rows_fetched,
                         0
                       ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    /* Bind column 1 to the Ord_num Field of the first row in the array */ 
    rc = SQLBindCol( hstmt,
                     1,
                     SQL_C_LONG,
                     ( SQLPOINTER ) & ord_array[0].Ord_Num,
                     0,
                     &ord_array[0].Ord_Num_L
                   ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    /* Bind remaining columns ... */
/* ... */
    /*
     NOTE: This sample assumes that an order will never have more
           rows than row_set_size.  A check should be added below to call
           SQLExtendedFetch multiple times for each result set.
    */
 
    while ( SQLFetchScroll( hstmt, SQL_FETCH_NEXT, 0 ) != SQL_NO_DATA ) {
       printf( "**************************************\n" ) ;
       printf( "Orders for Customer: %ld\n", ord_array[0].Cust_Num ) ;
       printf( "**************************************\n" ) ;
       i = 0 ;
       while ( i < num_rows_fetched ) {
          if ( row_status_array[i] == SQL_ROW_SUCCESS ||
               row_status_array[i] == SQL_ROW_SUCCESS_WITH_INFO
             ) {
             printf( "\nOrder #: %ld\n", ord_array[i].Ord_Num ) ;
             printf( "     Product  Quantity         Price\n" ) ;
             printf( "     -------- ---------------- ------------\n" ) ;
             j = i ;
             while ( ord_array[j].Ord_Num == ord_array[i].Ord_Num ) {
                printf( "    %8ld %16.7lf %12.2lf\n",
                        ord_array[i].Prod_Num,
                        ord_array[i].Quant,
                        ord_array[i].Amount
                      ) ;
                i++ ;
                if ( i >= num_rows_fetched ) break ;
                if ( row_status_array[i] != SQL_ROW_SUCCESS )
                   if ( row_status_array[i] != SQL_ROW_SUCCESS_WITH_INFO )
                      break ;
             }
             printf( "                               ============\n" ) ;
             printf( "                              %12.2lf\n",
                     ord_array[j].Total
                   ) ;
          }
          else i++ ;
       }
    }

[ DB2 List of Books | Search the DB2 Books ]