/*******************************************************************************
**
** Source File Name = ordrep.c 1.3
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 1999
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
**
** PURPOSE :
** - Uses Array Input and Array Output to generate multiple result sets.
** Each result set is one order.
**
** FUNCTIONS USED :
** SQLAllocHandle
** SQLDisconnect
** SQLExecDirect
** SQLFreeHandle
** SQLFreeStmt
** SQLSetStmtAttr
** SQLBindParameter
** SQLBindCol
** SQLFetchScroll
** SQLEndTran
**
** For more information about these samples see the README file.
**
** For more information on programming in CLI see the:
** - "Building CLI Applications" section of the Application Building Guide, and the
** - CLI Guide and Reference.
**
** For more information on the SQL language see the SQL Reference.
**
*******************************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlcli1.h>
#include "samputil.h" /* Header file for CLI sample code */
/* For the Macintosh environment when generating 68K applications */
#ifdef DB268K
/* Need to include ASLM for 68K applications */
#include <LibraryManager.h>
#endif
/*
Global Variables for user id and password.
To keep samples simple, not a recommended practice.
*/
extern SQLCHAR server[SQL_MAX_DSN_LENGTH + 1] ;
extern SQLCHAR uid[MAX_UID_LENGTH + 1] ;
extern SQLCHAR pwd[MAX_PWD_LENGTH + 1] ;
#define row_array_size 25
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 ;
/* main */
int main( int argc, char * argv[] ) {
SQLHANDLE henv, hdbc, hstmt ;
SQLRETURN rc ;
/*--> SQLL1X26.SCRIPT */
SQLCHAR * stmt = ( SQLCHAR * )
/* 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) */
ord_info ord_array[row_array_size] ;
SQLUINTEGER num_rows_fetched ;
SQLUSMALLINT row_status_array[row_array_size], i, j ;
/*<-- */
/* For the Macintosh environment when generating 68K applications */
#ifdef DB268K
/*
Before making any API calls for 68K environment,
need to initialize the Library Manager
*/
InitLibraryManager(0,kCurrentZone,kNormalMemory);
atexit(CleanupLibraryManager);
#endif
/* macro to initalize server, uid and pwd */
INIT_UID_PWD ;
/* allocate an environment handle */
rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ;
if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
/* allocate a connect handle, and connect */
rc = DBconnect( henv, &hdbc ) ;
if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
/*--> */
/* 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 ) ;
rc = SQLFreeStmt( hstmt, SQL_UNBIND ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) ;
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_array_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 ... */
/*<-- */
rc = SQLBindCol( hstmt,
2,
SQL_C_LONG,
( SQLPOINTER ) & ord_array[0].Cust_Num,
0,
&ord_array[0].Cust_Num_L
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol( hstmt,
3,
SQL_C_LONG,
( SQLPOINTER ) & ord_array[0].Prod_Num,
0,
&ord_array[0].Prod_Num_L
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol( hstmt,
4,
SQL_C_DOUBLE,
( SQLPOINTER ) & ord_array[0].Quant,
0,
&ord_array[0].Quant_L
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol( hstmt,
5,
SQL_C_DOUBLE,
( SQLPOINTER ) & ord_array[0].Amount,
0,
&ord_array[0].Amount_L
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol( hstmt,
6,
SQL_C_DOUBLE,
( SQLPOINTER ) & ord_array[0].Total,
0,
&ord_array[0].Total_L
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/*--> */
/*
NOTE: This sample assumes that an order will never have more
rows than row_array_size. A check should be added below to call
SQLFetchScroll multiple times for each result set.
*/
printf( "\nORD_NUM CUST_NUM PROD_NUM QUANTITY AMOUNT TOTAL\n" ) ;
printf( "--------------------------------------------------------------\n" ) ;
while ( ( rc = SQLFetchScroll( hstmt, SQL_FETCH_NEXT, 0 ) ) == SQL_SUCCESS ) {
i = 0 ;
while ( i < num_rows_fetched ) {
printf( "%ld %ld %ld %12.2lf %12.2lf %12.2lf\n",
ord_array[i].Ord_Num,
ord_array[i].Cust_Num,
ord_array[i].Prod_Num,
ord_array[i].Quant,
ord_array[i].Amount,
ord_array[i].Total
) ;
i++ ;
}
}
if ( rc != SQL_NO_DATA_FOUND )
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/*<-- */
rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* COMMIT, free resources and exit */
rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
printf( "\n>Disconnecting .....\n" ) ;
rc = SQLDisconnect( hdbc ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
rc = SQLFreeHandle( SQL_HANDLE_DBC, hdbc ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
rc = SQLFreeHandle( SQL_HANDLE_ENV, henv ) ;
if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
return( SQL_SUCCESS ) ;
} /* end main */