/*---------------------------------------------------------------------------
FileName : proc1.pc
Function : connect to scott/tiger and get emp datas
use host_variable,struct host_variable,variable_indicator
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>
#include <sqlcpr.h>
#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
int prt_data_each(char *,float,float,short,int);
int connect(),get_1rec(),get_Nrec(),get_1rec_ind();
int prt_subttl(),prt_data();
#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))
EXEC SQL BEGIN DECLARE SECTION;
char username[PWD_LEN];
char password[PWD_LEN];
EMP_INFO emp_info1;
EMP_INFO emp_info[MAX_DATA];
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE salespeople CURSOR FOR
SELECT ENAME, SAL, COMM FROM EMP WHERE JOB LIKE 'SALES%';
EXEC SQL DECLARE clerkpeople CURSOR FOR
SELECT ENAME, SAL, COMM FROM EMP WHERE JOB LIKE 'CLERK%';
void sql_error(char *msg) {
char err_msg[SZ_MSG];
size_t buf_len=SZ_MSG, msg_len;
printf("\n%s\n", msg);
sqlglm(err_msg,&buf_len,&msg_len);
EXEC SQL WHENEVER SQLERROR CONTINUE;
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_ind();
/* 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);
return 0;
}
/*--------------------------------------------------------------
Function : get 1 record from database with host structure
--------------------------------------------------------------*/
int get_1rec() {
EXEC SQL WHENEVER SQLERROR DO sql_error("ERROR in get_1rec : ");
/* Open the cursor. */
EXEC SQL OPEN salespeople;
/* Get ready to print results. */
printf("The company's sales people are followings. [get 1]\n\n");
prt_subttl("SalesPerson");
/* 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;
prt_data(&emp_info1,1);
}
printf("\n");
/* Close the cursor. */
EXEC SQL CLOSE salespeople;
return 0;
}
/*---------------------------------------------------------
Function : get N records from database with host array
---------------------------------------------------------*/
int get_Nrec() {
int i,rows,pre_rows=0;
EXEC SQL WHENEVER SQLERROR DO sql_error("ERROR in get_Nrec : ");
EXEC SQL OPEN salespeople;
/* Get ready to print results. */
printf("The company's sales people are followings. [get %d]\n\n",MAX_DATA);
prt_subttl("SalesPerson");
/* 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;
rows = sqlca.sqlerrd[2] - pre_rows;
prt_stt("in");
prt_data(emp_info,rows);
pre_rows = sqlca.sqlerrd[2];
}
rows = sqlca.sqlerrd[2] - pre_rows;
prt_stt("out");
prt_data(emp_info,rows);
printf("\n");
EXEC SQL CLOSE salespeople;
return 0;
}
/*--------------------------------------------------------------------------
Function : get 1 record from database with host variables and indicator
--------------------------------------------------------------------------*/
int get_1rec_ind() {
EXEC SQL BEGIN DECLARE SECTION;
char emp_name[PWD_LEN];
float salary;
float commission;
short ind_com; /* indicator variable */
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER SQLERROR DO sql_error("ERROR in get_1rec_ind : ");
/* Open the cursor. */
EXEC SQL OPEN clerkpeople;
/* Get row to print results. */
printf("The company's clerk people are followings. [get 1 with indicator]\n\n");
prt_subttl("ClerkPerson");
/* 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 clerkpeople
INTO :emp_name, :salary ,:commission INDICATOR :ind_com;
prt_data_each(emp_name,salary,commission,ind_com,1);
}
printf("\n");
/* Close the cursor. */
EXEC SQL CLOSE clerkpeople;
return 0;
}
/*---------------------------------------------------------
Function : print records
---------------------------------------------------------*/
int prt_data(EMP_INFO *emp_info,int row) {
int i;
for (i=0; i<row; i++) {
printf("%s %9.2f %12.2f\n", emp_info->emp_name,
emp_info->salary, emp_info->commission);
emp_info++;
}
return 0;
}
/*---------------------------------------------------------
Function : print records
---------------------------------------------------------*/
int prt_data_each(char *emp_name,float salary,float comm,short ind,int row) {
int i;
for (i=0; i<row; i++) {
if (! ind)
printf("%s %9.2f %12.2f\n", emp_name,salary, comm);
else if (ind == -1)
printf("%s %9.2f %12s\n", emp_name, salary,"NULL");
}
return 0;
}
int prt_subttl(char *t1) {
printf("%11.11s Salary Commission\n",t1);
printf("----------- ------- ----------\n");
return 0;
}