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.
/* ... */
#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);
/* ... */