DRDA example: C program using embedded SQL
This example program is written in the C programming language.
Note: By using the code examples, you agree to the terms
of the Code license and disclaimer information.
/******************************************************************************/
/* PROGRAM NAME: SAMPEMBC */
/* */
/* DESCRIPTIVE NAME: Sample embedded C application using DRDA */
/* */
/* FUNCTION: This module processes the PART_STOCK table and */
/* for each part below the ROP (REORDER POINT) */
/* creates a supply order. */
/* */
/* LOCAL TABLES: PART_STOCK */
/* */
/* REMOTE TABLES: PART_ORDER, PART_ORDLN, SHIPMENTLN */
/* */
/* COMPILE OPTIONS: */
/* CRTSQLCI OBJ(SAMPEMBC) COMMIT(*CHG) RDB(rdbname) OBJTYPE(*PGM) */
/* RDBCNNMTH(*RUW) */
/* */
/* INVOKED BY: */
/* CALL PGM(SAMPEMBC) PARM('lcldbname' 'rmtdbname') */
/******************************************************************************/
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
EXEC SQL INCLUDE SQLCA;
/******************************************************************************/
/* SQL Variables */
/******************************************************************************/
EXEC SQL BEGIN DECLARE SECTION;
char loc[4] = "SQLA"; /* dealer's database name */
char remote_db[18] = " ";
/* sample remote database */
char local_db[18] = " ";
/* sample local database */
char part_table[5] = " "; /* part number in table part_stock */
long quant_table; /* quantity in stock, tbl part_stock */
long rop_table; /* reorder point , tbl part_stock */
long eoq_table; /* reorder quantity , tbl part_stock */
short next_num; /* next order nbr,table part_order */
short ord_table; /* order nbr. , tbl order_line */
short orl_table; /* order line , tbl order_line */
long qty_table; /* ordered quantity , tbl order_line */
long line_count = 0; /* total number of order lines */
short ind_null; /* null indicator for qty_table */
short contl = 0; /* continuation line, tbl order_line */
EXEC SQL END DECLARE SECTION;
/******************************************************************************/
/* Other Variables */
/******************************************************************************/
char first_time, what_time;
long qty_rec = 0, qty_req = 0;
EXEC SQL WHENEVER SQLERROR GOTO error_tag;
EXEC SQL WHENEVER SQLWARNING CONTINUE; /******************************************************************************/
/* Function Declarations */
/******************************************************************************/
/******************************************************************************/
/* Function For Declaring Cursors */
/******************************************************************************/
declare_cursors() {
/* SQL Cursor declaration and reposition for local UW */
EXEC SQL DECLARE NEXT_PART CURSOR FOR
SELECT PART_NUM, PART_QUANT, PART_ROP, PART_EOQ
FROM DRDA/PART_STOCK
WHERE PART_ROP > PART_QUANT AND
PART_NUM > :part_table
ORDER BY PART_NUM;
/* SQL Cursor declaration and connect for RUW */
EXEC SQL DECLARE NEXT_OLINE CURSOR FOR
SELECT A.ORDER_NUM, ORDER_LINE, QUANT_REQ
FROM DRDA/PART_ORDLN A,
DRDA/PART_ORDER B
WHERE PART_NUM = :part_table AND
LINE_STAT <> 'C' AND
A.ORDER_NUM = B.ORDER_NUM AND
ORDER_TYPE = 'R';
/* upline exit function in connectable state */
goto function_exit;
error_tag:
error_function();
function_exit:
;
} /* function declare_cursor *//******************************************************************************/
/* Function For Reseting Tables */
/******************************************************************************/
reset_tables() {
/* Clean up for rerunability in test environment */
EXEC SQL CONNECT TO :remote_db;
EXEC SQL DELETE FROM DRDA/PART_ORDLN
WHERE ORDER_NUM IN
(SELECT ORDER_NUM
FROM DRDA/PART_ORDER
WHERE ORDER_TYPE = 'R');
EXEC SQL DELETE FROM DRDA/PART_ORDER
WHERE ORDER_TYPE = 'R';
/* Exit function in connectable state */
EXEC SQL COMMIT;
goto function_exit;
error_tag:
error_function();
function_exit:
;
} /* function delete_for_rerun *//******************************************************************************/
/* Function For Declaring Cursors */
/******************************************************************************/
calculate_order_quantity() {
/* Set current connection to local database */
EXEC SQL CONNECT TO :local_db;
/* available qty = Stock qty + qty in order - qty received */
EXEC SQL OPEN NEXT_PART;
EXEC SQL FETCH NEXT_PART
INTO :part_table, :quant_table, :rop_table, :eoq_table;
if (sqlca.sqlcode == 100) {
printf("--------------------------------\n");
printf("NUMBER OF LINES CREATED = %d\n",line_count);
printf("--------------------------------\n");
printf("***** END OF PROGRAM *********\n");
rop_table = 0; /* no (more) orders to process */
}
else {
qty_rec = 0;
qty_req = 0;
EXEC SQL COMMIT;
EXEC SQL CONNECT TO :remote_db;
EXEC SQL OPEN NEXT_OLINE;
do {
EXEC SQL FETCH NEXT_OLINE
INTO :ord_table, :orl_table, :qty_table;
qty_rec = qty_rec + qty_table;
} while(sqlca.sqlcode != 100);
EXEC SQL CLOSE NEXT_OLINE;
EXEC SQL SELECT SUM(QUANT_RECV)
INTO :qty_table:ind_null
FROM DRDA/SHIPMENTLN
WHERE ORDER_LOC = :loc AND
ORDER_NUM = :ord_table AND
ORDER_LINE = :orl_table;
if (ind_null != 0)
qty_rec = qty_rec + qty_table;
} /* end of else branch */
goto function_exit;
error_tag:
error_function();
function_exit:
;
} /* end of calculate_order_quantity *//******************************************************************************/
/* Function For Declaring Cursors */
/******************************************************************************/
process_order() {
/* insert order and order_line in remote database */
if (contl == 0) {
EXEC SQL SELECT (MAX(ORDER_NUM) + 1)
INTO :next_num
FROM DRDA/PART_ORDER;
EXEC SQL INSERT INTO DRDA/PART_ORDER
(ORDER_NUM, ORIGIN_LOC, ORDER_TYPE, ORDER_STAT, CREAT_TIME)
VALUES (:next_num, :loc, 'R', 'O', CURRENT TIMESTAMP);
printf("***** ROP PROCESSING *********\n");
printf("ORDER NUMBER = %d \n\n",next_num);
printf("--------------------------------\n");
printf(" LINE PART QTY \n");
printf(" NBR NBR REQUESTED\n");
printf("--------------------------------\n");
contl = contl + 1;
} /* if contl == 0 */
EXEC SQL INSERT INTO DRDA/PART_ORDLN
(ORDER_NUM, ORDER_LINE, PART_NUM, QUANT_REQ, LINE_STAT)
VALUES (:next_num, :contl, :part_table, :eoq_table, 'O');
line_count = line_count + 1;
printf(" %d %.5s %d\n",line_count,part_table,eoq_table);
contl = contl + 1;
/* Exit function in connectable state */
EXEC SQL COMMIT;
goto function_exit;
error_tag:
error_function();
function_exit:
;
} /* end of function process_order *//******************************************************************************/
/* Function For Declaring Cursors */
/******************************************************************************/
error_function() {
printf("************************\n");
printf("* SQL ERROR *\n");
printf("************************\n");
printf("SQLCODE = %d\n",sqlca.sqlcode);
printf("SQLSTATE = %5s",sqlca.sqlstate);
printf("\n**********************\n");
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK;
/* Reset Current Connection To Local Database */
EXEC SQL CONNECT RESET;
exit(999);
} /* end of function error_function */
/******************************************************************************/
/* Mainline */
/******************************************************************************/
main(int argc, char *argv[]) {
memcpy(local_db,argv[1],strlen(argv[1]));
memcpy(remote_db,argv[2],strlen(argv[2]));
/* Initialization */
declare_cursors();
reset_tables();
/* Main Work */
do {
calculate_order_quantity();
if (rop_table > quant_table + qty_req - qty_rec) {
process_order();
quant_table = qty_req = qty_rec = 0;
}
} while (sqlca.sqlcode == 0);
EXEC SQL COMMIT;
/* Reset Current Connection To Local Database */
EXEC SQL DISCONNECT :local_db;
exit(0);
} /* end of main */