#include <sqlca.h>
#include <stdio.h>
#include <string.h>
/* 1. get an instance of struct SQLCA */
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
typedef struct {
int empno; /* NUMBER(4) */
char ename[10 + 1]; /* VARCHAR2(10) */
char job[9 + 1]; /* VARCHAR2(9) */
int mgr; /* NUMBER(4) */
char hiredate[30]; /* DATE */
double sal; /* NUMBER(7,2) */
double comm; /* NUMBER(7,2) */
int deptno; /* NUMBER(4) */
} Emp02;
typedef struct {
short empno;
short ename;
short job;
short mgr;
short hiredate;
short sal;
short comm;
short deptno;
} IdcEmp02;
EXEC SQL END DECLARE SECTION;
int perr02(const char * msg){
printf("Error occured when %s\n", msg);
printf("{\n");
printf(" sqlcaid = %s\n",sqlca.sqlcaid);
printf(" sqlabc = %d\n",sqlca.sqlabc);
printf(" sqlcode = %d\n",sqlca.sqlcode);
printf(" sqlerrm.sqlerrml = %d\n",sqlca.sqlerrm.sqlerrml);
printf(" sqlerrm.sqlerrmc = %s\n",sqlca.sqlerrm.sqlerrmc);
printf(" sqlerrp = %s\n",sqlca.sqlerrp);
printf(" sqlerrd = %d\n",sqlca.sqlerrd);
printf(" sqlwarn = %s\n",sqlca.sqlwarn);
printf(" sqlext = %s\n",sqlca.sqlext);
printf("}\n");
return 1;
}
int notfound02(){
return 2;
}
void select02() {
printf("----------------------------select02()\n");
char buf[1024];
int errorFlag = 0;
int notFoundFlag =0;
/* 2. declare host variables */
EXEC SQL BEGIN DECLARE SECTION;
/* using for connect DB*/
char user[20] = "scott";
char passwd[20] = "123456";
char dbStr[20]="JAL";
int empno=7902;
Emp02 emp;
IdcEmp02 idcEmp;
EXEC SQL END DECLARE SECTION;
/* 3. connect DB server */
char * msg = "connect db";
EXEC SQL WHENEVER SQLERROR DO errorFlag=perr02(msg);
EXEC SQL CONNECT :user IDENTIFIED BY :passwd USING :dbStr ;
if(errorFlag){
exit(errorFlag);
}
/* 4. query DB */
EXEC SQL WHENEVER SQLERROR DO errorFlag = perr02("query DB");
EXEC SQL WHENEVER NOT FOUND DO notFoundFlag = notfound02();
/*EXEC SQL SELECT EMPNO, ENAME, JOB, MGR, TO_CHAR(HIREDATE,'YYYY/MM/DD HH24:MI:SS'), SAL, COMM, DEPTNO
INTO :emp.empno:idcEmp.empno, :emp.ename:idcEmp.ename,
:emp.job:idcEmp.job, :emp.mgr:idcEmp.mgr, :emp.hiredate:idcEmp.hiredate,
:emp.sal:idcEmp.sal, :emp.comm:idcEmp.comm, :emp.deptno:idcEmp.deptno
FROM EMP WHERE EMPNO = :empno;*/
EXEC SQL SELECT EMPNO, ENAME, JOB, MGR,
TO_CHAR(HIREDATE,'YYYY/MM/DD HH24:MI:SS'), SAL, COMM, DEPTNO
INTO :emp INDICATOR :idcEmp
FROM EMP
WHERE EMPNO = :empno;
if(errorFlag){
exit(errorFlag);
}
/* 5. handle data */
if (notFoundFlag) {
printf("Not found employee[EMPNO=%d]\n", empno);
} else {
printf("%-5s %-10s %-10s %-5s %-20s %-10s %-10s %-5s \n", "empno",
"ename", "job", "mgr", "hirdate","sal", "comm", "deptno");
emp.hiredate[19]=NULL;
printf("%-5d %-10s %-10s %-5d %-20s %-10.2f %-10.2f %-5d \n",
emp.empno, emp.ename, emp.job,
-1 == idcEmp.mgr ? -9999 : emp.mgr,
-1 == idcEmp.hiredate ? "" : emp.hiredate,
-1 == idcEmp.sal ? -99999.99 : emp.sal,
-1 == idcEmp.comm ? -99999.99 : emp.comm,
-1 == idcEmp.deptno ? -99 : emp.deptno);
}
/* 6. disconnect DB */
EXEC SQL ROLLBACK WORK RELEASE ;
}
|
