/*---------------------------------------------------------------------------
FileName : proc3.pc
Function : connect to scott/tiger and get emp datas
use cursor_variable
SQL> desc emp;
name NULL? type
----------------------------------------- -------- -------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
-------------------------------------------------------------------------*/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>
int prt_data_each(char *,float,float,short,int);
#define DEBUG
#ifdef DEBUG
#define prt_stt(stt) printf("%s] %d rows pre_rows=%d ecode=%d\n",\
stt,rows,pre_rows,sqlca.sqlcode)
#else
#define prt_stt(stt)
#endif
#define USER_NAME "SCOTT"
#define USER_PASS "TIGER"
#define MAX_DATA 4
#define SZ_MSG 512
#define PWD_LEN 11
typedef struct emp_info {
char emp_name[PWD_LEN];
float salary;
float commission;
} EMP_INFO;
#define SZ_EMPINFO (sizeof(EMP_INFO))
typedef struct emp_info_ind {
short ind_enm,
ind_sal,
ind_com;
} EMP_INFO_IND;
EXEC SQL BEGIN DECLARE SECTION;
char username[PWD_LEN];
char password[PWD_LEN];
EMP_INFO emp_info1;
EMP_INFO emp_info[MAX_DATA];
EMP_INFO_IND ind_emp_info1;
EMP_INFO_IND ind_emp_info[MAX_DATA];
SQL_CURSOR salespeople;
SQL_CURSOR all;
EXEC SQL END DECLARE SECTION;
void sql_error(char *msg) {
char err_msg[SZ_MSG];
size_t buf_len=SZ_MSG, msg_len;
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\n%s\n", msg);
/* Call sqlglm() to get the complete text of the error message. */
sqlglm(err_msg, &buf_len, &msg_len);
printf("%.*s\n", msg_len, err_msg);
EXEC SQL ROLLBACK RELEASE;
exit(EXIT_FAILURE);
}
int main(int argc,char *argv[]) {
connect();
get_1rec();
get_Nrec();
get_1rec_all();
/* Close to transaction */
EXEC SQL COMMIT WORK RELEASE;
exit(EXIT_SUCCESS);
}
/*---------------------------------------------------------
Function : connect to oacle database
---------------------------------------------------------*/
int connect() {
strcpy(username, USER_NAME);
strcpy(password, USER_PASS);
EXEC SQL WHENEVER SQLERROR DO sql_error("ERROR in connect : ");
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\nConnected to ORACLE as user: %s\n\n", username);
}
/*--------------------------------------------------------------
Function : get 1 record from database with host structure
--------------------------------------------------------------*/
int get_1rec() {
EXEC SQL WHENEVER SQLERROR CONTINUE;
/* allocate cursor */
EXEC SQL ALLOCATE :salespeople;
/* open cursor */
EXEC ORACLE OPTION(select_error=no);
EXEC SQL SELECT CURSOR
(SELECT ENAME, SAL, COMM FROM EMP WHERE JOB LIKE 'SALES%')
INTO :salespeople FROM DUAL;
EXEC ORACLE OPTION(select_error=yes);
/* Get ready to print results. */
printf("The company's sales people are followings. [get 1]\n\n");
printf("SalesPerson Salary Commission\n");
printf("----------- ------- ----------\n");
/* Loop, fetching all salesperson's statistics.
* Cause the program to break the loop when no more
* data can be retrieved on the cursor.
*/
EXEC SQL WHENEVER NOT FOUND DO break;
for (;;) {
EXEC SQL FETCH :salespeople INTO :emp_info1 :ind_emp_info1;
prt_data(&emp_info1,&ind_emp_info1,1);
}
printf("\n");
EXEC SQL CLOSE :salespeople;
}
/*---------------------------------------------------------
Function : get N records from database with host array
---------------------------------------------------------*/
int get_Nrec() {
int i,rows,pre_rows=0;
/* allocate cursor */
EXEC SQL ALLOCATE :salespeople;
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL WHENEVER NOT FOUND CONTINUE;
/* open cursor */
EXEC ORACLE OPTION(select_error=no);
EXEC SQL SELECT CURSOR
(SELECT ENAME, SAL, COMM FROM EMP WHERE JOB LIKE 'SALES%')
INTO :salespeople FROM DUAL;
EXEC ORACLE OPTION(select_error=yes);
/* Get ready to print results. */
printf("The company's sales people are followings. [get %d]\n\n",MAX_DATA);
printf("SalesPerson Salary Commission\n");
printf("----------- ------- ----------\n");
/* Loop, fetching all salesperson's statistics.
* Cause the program to break the loop when no more
* data can be retrieved on the cursor.
*/
EXEC SQL WHENEVER NOT FOUND DO break;
for (;;) {
EXEC SQL FETCH :salespeople INTO :emp_info :ind_emp_info;
rows = sqlca.sqlerrd[2] - pre_rows;
printf("in] %d rows pre_rows=%d ecode=%d\n",rows,pre_rows,sqlca.sqlcode);
prt_data(emp_info,ind_emp_info,rows);
pre_rows = sqlca.sqlerrd[2];
}
rows = sqlca.sqlerrd[2] - pre_rows;
printf("out] %d rows pre_rows=%d ecode=%d\n",rows,pre_rows,sqlca.sqlcode);
prt_data(emp_info,ind_emp_info,rows);
printf("\n");
EXEC SQL CLOSE :salespeople;
}
/*--------------------------------------------------------------------------
Function : get 1 record from database with host variables and indicator
--------------------------------------------------------------------------*/
int get_1rec_all() {
/* allocate cursor */
EXEC SQL ALLOCATE :all;
/* open cursor */
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL WHENEVER NOT FOUND CONTINUE;
EXEC ORACLE OPTION(select_error=no);
EXEC SQL SELECT CURSOR
(SELECT ENAME, SAL, COMM FROM EMP) INTO :all FROM DUAL;
EXEC ORACLE OPTION(select_error=yes);
/* Get row to print results. */
printf("The company's people are followings. [get 1]\n\n");
printf("Name Salary Commission\n");
printf("----------- ------- ----------\n");
/* Loop, fetching all salesperson's statistics.
* Cause the program to break the loop when no more
* data can be retrieved on the cursor.
*/
EXEC SQL WHENEVER NOT FOUND DO break;
for (;;) {
EXEC SQL FETCH :all INTO :emp_info1 :ind_emp_info1;
prt_data(&emp_info1,&ind_emp_info1,1);
}
printf("\n");
EXEC SQL CLOSE :all;
}
/*---------------------------------------------------------
Function : print records
---------------------------------------------------------*/
int prt_data(EMP_INFO *emp,EMP_INFO_IND *ind,int row) {
int i;
for (i=0; i<row; i++) {
printf("%s %9.2f ", emp->emp_name, emp->salary);
if (ind->ind_com == 0)
printf("%12.2f\n", emp->commission);
else
printf("%12s\n", " ");
emp++;
}
}