/*
* Disconnect from the database and free the connection handle.
* Note that we create the environment handle in odbc_connect() but
* we don't touch it here.
*/
int _db_disconnect(struct db_context_t *odbcc)
{
SQLRETURN rc;
pthread_mutex_lock(&db_source_mutex);
rc = SQLFreeHandle(SQL_HANDLE_STMT, odbcc->hstmt);
if (rc != SQL_SUCCESS)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
rc = SQLDisconnect(odbcc->hdbc);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_DBC, odbcc->hdbc);
return W_ERROR;
}
rc = SQLFreeHandle(SQL_HANDLE_DBC, odbcc->hdbc);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_DBC, odbcc->hdbc);
return W_ERROR;
}
pthread_mutex_unlock(&db_source_mutex);
return OK;
}
int dbt2_sql_execute(struct db_context_t *dbc, char * query,
struct sql_result_t * sql_result, char * query_name)
{
int i;
SQLCHAR colname[32];
SQLSMALLINT coltype;
SQLSMALLINT colnamelen;
SQLSMALLINT scale;
SQLRETURN rc;
sql_result->num_fields= 0;
sql_result->num_rows= 0;
sql_result->query= query;
rc = SQLExecDirect(dbc->hstmt, query, SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, dbc->hstmt);
return 0;
}
rc = SQLNumResultCols(dbc->hstmt,&sql_result->num_fields);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, dbc->hstmt);
return 0;
}
rc = SQLRowCount(dbc->hstmt, &sql_result->num_rows);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, dbc->hstmt);
return 0;
}
if (sql_result->num_fields) {
sql_result->lengths= malloc(sizeof(int) * sql_result->num_fields);
for (i=0; i < sql_result->num_fields; i++) {
SQLDescribeCol(dbc->hstmt,
(SQLSMALLINT)(i + 1),
colname,
sizeof(colname),
&colnamelen,
&coltype,
&sql_result->lengths[i],
&scale,
NULL
);
}
sql_result->current_row = 1;
sql_result->result_set = 1;
}
return 1;
}
char *dbt2_sql_getvalue(struct db_context_t *dbc,
struct sql_result_t *sql_result, int field)
{
SQLRETURN rc;
char *tmp;
tmp = NULL;
SQLINTEGER cb_var = 0;
if (sql_result->current_row && field < sql_result->num_fields) {
if ((tmp = calloc(sizeof(char), sql_result->lengths[field] + 1))) {
rc = SQLGetData(dbc->hstmt, field + 1, SQL_C_CHAR, tmp,
sql_result->lengths[field] + 1, &cb_var);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, dbc->hstmt);
}
} else {
LOG_ERROR_MESSAGE("dbt2_sql_getvalue: CALLOC FAILED for value from field=%d\n", field);
}
#ifdef DEBUG_QUERY
} else {
LOG_ERROR_MESSAGE("dbt2_sql_getvalue: FIELD %d current_row %d\nQUERY --- %s\n", field, sql_result->current_row, sql_result->query);
#endif
}
return tmp;
}
int getSCDetail( struct db_context_t *odbcc,
struct shopping_cart_line_t data[],
long long sc_id,
short int *sc_size)
{
SQLRETURN rc;
int i = 0 ;
char sql_cmd[512];
/* Create SQL Command */
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
sprintf(sql_cmd,STMT_getSCDetail, sc_id);
/* Execute SQL Command */
rc = SQLExecDirect(odbcc->hstmt,sql_cmd,SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
/* Get data */
for (i=0;i<20;i++) {
SQLBindCol(odbcc->hstmt,1,SQL_C_SBIGINT,&data[i].scl_i_id, 0,NULL);
SQLBindCol(odbcc->hstmt,2,SQL_C_CHAR,&data[i].i_title, sizeof(data[i].i_title),NULL);
SQLBindCol(odbcc->hstmt,3,SQL_DOUBLE,&data[i].scl_cost, 0,NULL);
SQLBindCol(odbcc->hstmt,4,SQL_DOUBLE,&data[i].scl_srp, 0,NULL);
SQLBindCol(odbcc->hstmt,5,SQL_C_CHAR,&data[i].i_backing,sizeof(data[i].i_backing),NULL);
SQLBindCol(odbcc->hstmt,6,SQL_C_SSHORT,&data[i].scl_qty, sizeof(data[i].scl_qty),NULL);
rc = SQLFetch(odbcc->hstmt);
if( rc == SQL_NO_DATA_FOUND ) {
break;
}
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
}
SQLCloseCursor(odbcc->hstmt);
*sc_size = i;
return OK;
}
/* Open an ODBC connection to the database. */
int _connect_to_db(struct db_context_t *odbcc)
{
SQLRETURN rc;
/* Allocate connection handles. */
pthread_mutex_lock(&db_source_mutex);
rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &odbcc->hdbc);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_DBC, odbcc->hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, odbcc->hdbc);
return ERROR;
}
/* Open connection to the database. */
rc = SQLConnect(odbcc->hdbc, servername, SQL_NTS,
username, SQL_NTS, authentication, SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_DBC, odbcc->hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, odbcc->hdbc);
return ERROR;
}
rc = SQLSetConnectAttr(odbcc->hdbc, SQL_ATTR_AUTOCOMMIT,
SQL_AUTOCOMMIT_OFF, 0);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return ERROR;
}
rc = SQLSetConnectAttr(odbcc->hdbc, SQL_ATTR_TXN_ISOLATION,
(SQLPOINTER *) SQL_TXN_REPEATABLE_READ, 0);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return ERROR;
}
/* allocate statement handle */
rc = SQLAllocHandle(SQL_HANDLE_STMT, odbcc->hdbc, &odbcc->hstmt);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return ERROR;
}
pthread_mutex_unlock(&db_source_mutex);
return OK;
}
int commit_transaction(struct db_context_t *dbc)
{
int i;
i = SQLEndTran(SQL_HANDLE_DBC, dbc->hdbc, SQL_COMMIT);
if (i != SQL_SUCCESS && i != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, dbc->hstmt);
return ERROR;
}
return OK;
}
int rollback_transaction(struct db_context_t *dbc)
{
int i;
i = SQLEndTran(SQL_HANDLE_DBC, dbc->hdbc, SQL_ROLLBACK);
if (i != SQL_SUCCESS && i != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, dbc->hstmt);
return ERROR;
}
return STATUS_ROLLBACK;
}
int dbt2_sql_fetchrow(struct db_context_t *dbc,
struct sql_result_t * sql_result)
{
SQLRETURN rc;
rc = SQLFetch(dbc->hstmt);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, dbc->hstmt);
/* result set - NULL */
sql_result->current_row= 0;
return 0;
}
return 1;
}
int dbt2_sql_close_cursor(struct db_context_t *dbc,
struct sql_result_t * sql_result)
{
SQLRETURN rc;
if (sql_result->lengths) {
free(sql_result->lengths);
sql_result->lengths=NULL;
}
rc = SQLCloseCursor(dbc->hstmt);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, dbc->hstmt);
return 0;
}
return 1;
}
int execute_buy_confirm( struct db_context_t *odbcc,
struct buy_confirm_t *data)
{
SQLRETURN rc;
short int ship_day = 0;
char ol_comment[OL_COMMENT_LEN + 1];
char cx_auth_id[CX_AUTH_ID_LEN + 1];
char sql_cmd[2048];
long long c_addr_id = 0;
char c_fname[C_FNAME_LEN+1];
char c_lname[C_LNAME_LEN+1];
short co_id = 0;
long long ship_addr_id = 0;
long long scl_i_id[SHOPPING_CART_ITEMS_MAX];
short scl_qty[SHOPPING_CART_ITEMS_MAX];
double scl_cost[SHOPPING_CART_ITEMS_MAX];
int ol_id = 0;
int i_stock = 0;
int ix,iy; /* Loop Counter */
for ( ix =0 ; ix < SHOPPING_CART_ITEMS_MAX ; ix++ ) {
scl_i_id[ix] = 0;
scl_qty[ix] = 0;
scl_cost[ix] = 0;
}
ix = 0;
bzero(ol_comment,sizeof(ol_comment));
bzero(cx_auth_id,sizeof(cx_auth_id));
bzero(sql_cmd,sizeof(sql_cmd));
bzero(c_fname,sizeof(c_fname));
bzero(c_lname,sizeof(c_lname));
/* Generate random day between 1 to 7 for ship_date. */
ship_day = get_random_int(7) + 1;
/* get authentication id from PGE */
strcpy(cx_auth_id, "012345678912345");
/* generate a_string[20..100] as order_line comment */
get_a_string(ol_comment, 20, 100);
/* SELECT shopping_cart Info */
/* Create SQL Command */
if( data->sc_id != 0 ){
// if data->sc_id not Zero, Select data from Shopping_Cart Table
/* Create SQL Command */
sprintf(sql_cmd,STMT_BUYCONF_selSC,data->sc_id);
/* Execute SQL Command */
rc = SQLExecDirect(odbcc->hstmt,sql_cmd,SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO){
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
/* Get data */
SQLBindCol(odbcc->hstmt,1,SQL_DOUBLE,&data->sc_sub_total,0,NULL);
SQLBindCol(odbcc->hstmt,2,SQL_DOUBLE,&data->sc_tax,0,NULL);
SQLBindCol(odbcc->hstmt,3,SQL_DOUBLE,&data->sc_ship_cost,0,NULL);
SQLBindCol(odbcc->hstmt,4,SQL_DOUBLE,&data->sc_total,0,NULL);
rc = SQLFetch(odbcc->hstmt);
if( rc != SQL_NO_DATA_FOUND ) {
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
}
SQLCloseCursor(odbcc->hstmt);
}else{
// if data-> sc_id is Zero, Get Shopping_Cart iD from Shopping_Cart Table
/* Create SQL Command */
sprintf(sql_cmd,STMT_BUYCONF_selInsVal);
/* Execute SQL Command */
rc = SQLExecDirect(odbcc->hstmt,sql_cmd,SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO){
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
/* Get data */
SQLBindCol(odbcc->hstmt,1,SQL_C_SBIGINT,&data->sc_id,0,NULL);
rc = SQLFetch(odbcc->hstmt);
if( rc != SQL_NO_DATA_FOUND ) {
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
}
SQLCloseCursor(odbcc->hstmt);
//insert data is all Zero
data->sc_sub_total =0 ;
data->sc_tax = 0 ;
data->sc_ship_cost = 0 ;
data->sc_total =0 ;
}
/* SELECT customer Info */
/* Create SQL command */
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
sprintf(sql_cmd,STMT_BUYCONF_selCS,data->c_id);
/* Execute SQL Command */
rc = SQLExecDirect(odbcc->hstmt,sql_cmd,SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO){
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
/* Get data */
SQLBindCol(odbcc->hstmt,1,SQL_CHAR,c_fname,sizeof(c_fname),NULL);
SQLBindCol(odbcc->hstmt,2,SQL_CHAR,c_lname,sizeof(c_lname),NULL);
SQLBindCol(odbcc->hstmt,3,SQL_DOUBLE,&data->c_discount,0,NULL);
SQLBindCol(odbcc->hstmt,4,SQL_C_SBIGINT,&c_addr_id,0,NULL);
rc = SQLFetch(odbcc->hstmt);
if( rc != SQL_NO_DATA_FOUND ) {
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
}
SQLCloseCursor(odbcc->hstmt);
if (data->shipping.addr_street1[0] != 0x00) {
/* SELECT ADDRESS */
/* Create SQL command */
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
sprintf( sql_cmd,STMT_BUYCONF_selADRCNT,
data->shipping.co_name,
data->shipping.addr_zip,
data->shipping.addr_state,
data->shipping.addr_city,
data->shipping.addr_street1,
data->shipping.addr_street2);
/* Execute SQL Command */
rc = SQLExecDirect(odbcc->hstmt,sql_cmd,SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO){
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
/* Get data */
SQLBindCol(odbcc->hstmt,1,SQL_C_SBIGINT,&ship_addr_id,0,NULL);
SQLBindCol(odbcc->hstmt,2,SQL_C_SHORT,&co_id,0,NULL);
rc = SQLFetch(odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
if( rc != SQL_NO_DATA_FOUND ) {
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
} else {
/* Create SQL command */
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
sprintf(sql_cmd,STMT_BUYCONF_getCountry, data->shipping.co_name);
/* Execute SQL Command */
rc = SQLExecDirect(odbcc->hstmt,sql_cmd,SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
/* Get data */
SQLBindCol(odbcc->hstmt,1,SQL_SMALLINT,&co_id,0,NULL);
rc = SQLFetch(odbcc->hstmt);
if( rc != SQL_NO_DATA_FOUND ) {
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
}
SQLCloseCursor(odbcc->hstmt);
//select id from sequense
/* Create SQL command */
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
sprintf(sql_cmd,STMT_BUYCONF_selADRID);
/* Execute SQL Command */
rc = SQLExecDirect(odbcc->hstmt,sql_cmd,SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO){
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
/* Get data */
SQLBindCol(odbcc->hstmt,1,SQL_C_UBIGINT,&ship_addr_id,0,NULL);
rc = SQLFetch(odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
if( rc != SQL_NO_DATA_FOUND ) {
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
}
/* INSERT ADDRESS */
/* Create SQL command */
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
sprintf( sql_cmd,STMT_BUYCONF_insADR,
ship_addr_id ,
data->shipping.addr_street1,
data->shipping.addr_street2,
data->shipping.addr_city,
data->shipping.addr_state,
data->shipping.addr_zip,
co_id);
/* Execute SQL Command */
rc = SQLExecDirect(odbcc->hstmt,sql_cmd,SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO){
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
SQLCloseCursor(odbcc->hstmt);
}
} else {
/* SELECT ADDRESS */
/* Create SQL command */
ship_addr_id = c_addr_id;
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
sprintf(sql_cmd,STMT_BUYCONF_selADR,ship_addr_id);
/* Execute SQL Command */
rc = SQLExecDirect(odbcc->hstmt,sql_cmd,SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO){
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
/* Get data */
SQLBindCol(odbcc->hstmt,1,SQL_SMALLINT,&co_id,0,NULL);
rc = SQLFetch(odbcc->hstmt);
if( rc != SQL_NO_DATA_FOUND ) {
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
}
SQLCloseCursor(odbcc->hstmt);
}
/* INSERT ORDERS */
/* Create SQL command */
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
sprintf(sql_cmd,STMT_BUYCONF_insODR,
data->sc_id,
data->c_id,
data->sc_sub_total,
data->sc_tax,
data->sc_total,
ship_day,
c_addr_id,
ship_addr_id);
/* Execute SQL Command */
rc = SQLExecDirect(odbcc->hstmt,sql_cmd,SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO){
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
SQLCloseCursor(odbcc->hstmt);
/* SELECT Shopping Cart Line */
/* Create SQL command */
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
sprintf(sql_cmd,STMT_BUYCONF_selSCL,data->sc_id);
/* Execute SQL Command */
rc = SQLExecDirect(odbcc->hstmt,sql_cmd,SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO){
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
/* Get data */
for (ix=0;ix<SHOPPING_CART_ITEMS_MAX;ix++) {
SQLBindCol(odbcc->hstmt,1,SQL_C_UBIGINT, &scl_i_id[ix],0,NULL);
SQLBindCol(odbcc->hstmt,2,SQL_DOUBLE, &scl_cost[ix],0,NULL);
SQLBindCol(odbcc->hstmt,3,SQL_C_SHORT,&scl_qty[ix], 0,NULL);
rc = SQLFetch(odbcc->hstmt);
if( rc == SQL_NO_DATA_FOUND ) {
break;
}
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
}
SQLCloseCursor(odbcc->hstmt);
for (iy=0;iy<ix;iy++) {
/* INSERT ORDER Line */
/* Create SQL command */
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
sprintf( sql_cmd,STMT_BUYCONF_insODRL,
++ol_id,
data->sc_id,
scl_i_id[iy],
scl_qty[iy],
data->c_discount,
ol_comment);
/* Execute SQL Command */
rc = SQLExecDirect(odbcc->hstmt,sql_cmd,SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO){
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
SQLCloseCursor(odbcc->hstmt);
/* SELECT Item */
/* Create SQL command */
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
sprintf(sql_cmd,STMT_BUYCONF_selITM, scl_i_id[iy]);
/* Execute SQL Command */
rc = SQLExecDirect(odbcc->hstmt,sql_cmd,SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO){
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
/* Get data */
SQLBindCol(odbcc->hstmt,1,SQL_C_SHORT,&co_id,0,NULL);
rc = SQLFetch(odbcc->hstmt);
if( rc != SQL_NO_DATA_FOUND ) {
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
}
SQLCloseCursor(odbcc->hstmt);
if (i_stock - 10 > scl_qty[iy]) {
i_stock -= scl_qty[iy];
} else {
i_stock = i_stock - scl_qty[iy] + 21;
}
/* UPDATE Item */
/* Create SQL command */
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
sprintf(sql_cmd,STMT_BUYCONF_updITM, i_stock,scl_i_id[iy]);
/* Execute SQL Command */
rc = SQLExecDirect(odbcc->hstmt,sql_cmd,SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO){
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
SQLCloseCursor(odbcc->hstmt);
}
/* INSERT cc_xacts */
/* Create SQL command */
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
sprintf(sql_cmd,STMT_BUYCONF_insXACT,
data->sc_id,
data->cx_type,
data->cx_num,
data->cx_name,
data->cx_expiry,
cx_auth_id,
data->sc_total,
co_id);
/* Execute SQL Command */
rc = SQLExecDirect(odbcc->hstmt,sql_cmd,SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO){
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
SQLCloseCursor(odbcc->hstmt);
rc = getSCDetail( odbcc,
data->scl_data,
data->sc_id,
&data->sc_size);
if (rc == W_ERROR) {
return W_ERROR;
}
return OK;
}
int execute_product_detail(struct db_context_t *odbcc,
struct product_detail_t *data)
{
SQLRETURN rc;
int j;
/* Perpare statement for Product Detail interaction. */
rc = SQLPrepare(odbcc->hstmt, STMT_PRODUCT_DETAIL, SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
/* Bind variables for Product Detail interaction. */
j = 1;
rc = SQLBindParameter(odbcc->hstmt,
j++, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 0, 0,
&data->i_id, 0, NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
rc = SQLBindParameter(odbcc->hstmt,
j++, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR,
0, 0, data->i_title, sizeof(data->i_title), NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
rc = SQLBindParameter(odbcc->hstmt,
j++, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 0, 0,
data->a_fname, sizeof(data->a_fname), NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
rc = SQLBindParameter(odbcc->hstmt,
j++, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 0, 0,
data->a_lname, sizeof(data->a_lname), NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
rc = SQLBindParameter(odbcc->hstmt,
j++, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 0, 0,
data->i_pub_date, sizeof(data->i_pub_date), NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
rc = SQLBindParameter(odbcc->hstmt,
j++, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 0, 0,
data->i_publisher, sizeof(data->i_publisher), NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
rc = SQLBindParameter(odbcc->hstmt,
j++, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 0, 0,
data->i_subject, sizeof(data->i_subject), NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
rc = SQLBindParameter(odbcc->hstmt,
j++, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 0, 0,
data->i_desc, sizeof(data->i_desc), NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
rc = SQLBindParameter(odbcc->hstmt,
j++, SQL_PARAM_OUTPUT, SQL_C_ULONG, SQL_INTEGER, 0, 0,
&data->i_image, 0, NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
rc = SQLBindParameter(odbcc->hstmt,
j++, SQL_PARAM_OUTPUT, SQL_C_DOUBLE, SQL_DOUBLE, 0, 0,
&data->i_cost, 0, NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
rc = SQLBindParameter(odbcc->hstmt,
j++, SQL_PARAM_OUTPUT, SQL_C_DOUBLE, SQL_DOUBLE, 0, 0,
&data->i_srp, 0, NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
rc = SQLBindParameter(odbcc->hstmt,
j++, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 0, 0,
data->i_avail, sizeof(data->i_avail), NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
rc = SQLBindParameter(odbcc->hstmt,
j++, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 0, 0,
data->i_isbn, sizeof(data->i_isbn), NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
rc = SQLBindParameter(odbcc->hstmt,
j++, SQL_PARAM_OUTPUT, SQL_C_SSHORT, SQL_SMALLINT, 0, 0,
&data->i_page, 0, NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
rc = SQLBindParameter(odbcc->hstmt,
j++, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 0, 0,
data->i_backing, sizeof(data->i_backing), NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
rc = SQLBindParameter(odbcc->hstmt,
j++, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 0, 0,
data->i_dimensions, sizeof(data->i_dimensions), NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
/* Execute stored procedure. */
rc = SQLExecute(odbcc->hstmt);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
return OK;
}
int addToSC( struct db_context_t *odbcc,
struct shopping_cart_t *data)
{
SQLRETURN rc;
int itemcount = 0;
int scl_qty = 0;
SQLINTEGER i_related = 0;
double i_cost = 0.0;
double i_srp = 0.0;
char i_title[60+1];
char i_backing[15+1];
char sql_cmd[512];
bzero(i_title, sizeof(i_title));
bzero(i_backing, sizeof(i_backing));
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
if (data->add_flag == 1) {
/* */
/* STEP 1 get Shopping Cart count */
/* */
/* Create SQL Command */
sprintf(sql_cmd,STMT_addToSC_selsumSCL,data->sc_id);
/* Execute SQL Command */
rc = SQLExecDirect(odbcc->hstmt,sql_cmd,SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
/* Get data */
SQLBindCol(odbcc->hstmt,1,SQL_INTEGER,&itemcount,0,NULL);
rc = SQLFetch(odbcc->hstmt);
if( rc != SQL_NO_DATA_FOUND ) {
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
}
SQLCloseCursor(odbcc->hstmt);
if (itemcount < 100) {
/* */
/* STEP 2 get Shopping Cart count */
/* */
/* Create SQL Command */
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
sprintf(sql_cmd,STMT_addToSC_selSCL,data->sc_id,data->i_id);
/* Execute SQL Command */
rc = SQLExecDirect(odbcc->hstmt,sql_cmd,SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
/* Get data */
SQLBindCol(odbcc->hstmt,1,SQL_INTEGER,&scl_qty,0,NULL);
rc = SQLFetch(odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
if( rc != SQL_NO_DATA_FOUND ) {
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
/* STEP 2.1 UPDATE Shopping Cart Line */
/* Create SQL Command */
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
sprintf( sql_cmd,STMT_addToSC_updSCL,
++scl_qty,
data->sc_id,
data->i_id);
/* Execute SQL Command */
rc = SQLExecDirect(odbcc->hstmt,sql_cmd,SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
SQLCloseCursor(odbcc->hstmt);
} else {
/* STEP 2.2 INSERT Shopping Cart Line */
/* Create SQL Command */
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
sprintf(sql_cmd,STMT_addToSC_selITM1,data->i_id);
/* Execute SQL Command */
rc = SQLExecDirect(odbcc->hstmt,sql_cmd,SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
/* Get data */
SQLBindCol(odbcc->hstmt,1,SQL_C_DOUBLE,&i_cost,0,NULL);
SQLBindCol(odbcc->hstmt,2,SQL_C_DOUBLE,&i_srp,0,NULL);
SQLBindCol(odbcc->hstmt,3,SQL_C_CHAR,i_title,sizeof(i_title),NULL);
SQLBindCol(odbcc->hstmt,4,SQL_C_CHAR,i_backing,sizeof(i_backing),NULL);
rc = SQLFetch(odbcc->hstmt);
if( rc != SQL_NO_DATA_FOUND ) {
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
}
SQLCloseCursor(odbcc->hstmt);
/* Create SQL Command */
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
sprintf( sql_cmd,STMT_addToSC_insSCL,
data->sc_id,
data->i_id,
i_cost,
i_srp,
i_title,
i_backing);
/* Execute SQL Command */
rc = SQLExecDirect(odbcc->hstmt,sql_cmd,SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
SQLCloseCursor(odbcc->hstmt);
}
}
} else {
/* Create SQL Command */
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
sprintf(sql_cmd,STMT_addToSC_selSCLC,data->sc_id);
/* Execute SQL Command */
rc = SQLExecDirect(odbcc->hstmt,sql_cmd,SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
/* Get data */
SQLBindCol(odbcc->hstmt,1,SQL_INTEGER,&scl_qty,0,NULL);
rc = SQLFetch(odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
if( scl_qty == 0 ) {
/* Create SQL Command */
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
sprintf(sql_cmd,STMT_addToSC_selITM2,data->i_id);
/* Execute SQL Command */
rc = SQLExecDirect(odbcc->hstmt,sql_cmd,SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
/* Get data */
SQLBindCol(odbcc->hstmt,1,SQL_C_DOUBLE,&i_cost,0,NULL);
SQLBindCol(odbcc->hstmt,2,SQL_C_DOUBLE,&i_srp,0,NULL);
SQLBindCol(odbcc->hstmt,3,SQL_C_CHAR,i_title,sizeof(i_title),NULL);
SQLBindCol(odbcc->hstmt,4,SQL_C_CHAR,i_backing,sizeof(i_backing),NULL);
SQLBindCol(odbcc->hstmt,5,SQL_C_LONG,&i_related,0,NULL);
rc = SQLFetch(odbcc->hstmt);
if( rc != SQL_NO_DATA_FOUND ) {
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
}
SQLCloseCursor(odbcc->hstmt);
/* Create SQL Command */
memset( sql_cmd , 0x00 , sizeof( sql_cmd ) ) ;
sprintf( sql_cmd,STMT_addToSC_insSCL,
data->sc_id,
( long long int )i_related,
i_cost,
i_srp,
i_title,
i_backing);
/* Execute SQL Command */
rc = SQLExecDirect(odbcc->hstmt,sql_cmd,SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
SQLCloseCursor(odbcc->hstmt);
return W_ERROR;
}
SQLCloseCursor(odbcc->hstmt);
}
}
return OK;
}
