pcmain.pc
/********************
首先更改Makefile里面对应的环境配置#include 和PROCFLAGS中的include
Makefile和ph.h中更改：用户名、密码、数据库名
数据库中创建一个表CREATE TABLE xjytab(col1 number,col2 varchar2(20),col3time date,d date);
Compile: make
run:./XJYEASY
根据要求输入1继续其他退出；
如果继续输入：
数值
20个以内的字符串包括空格
年月日
年月日
*******************/
#include<stdio.h>
char g_dbconStop=1;
extern int mainPc();
int main()
{
int i=0;
while(i++<10)
{
printf("print 1 contintue ,others quit\n");
scanf("%c",&g_dbconStop);
getchar();
if('1'==g_dbconStop)
mainPc();
else break;
}
printf("successful\n");
return 0;
}
mypc.pc
#include"ph.h"
int getConnection(void *ctx,const char *username,const char*password,const char*dbname)
{
#undef SQLCA
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR USER[21];
VARCHAR PSW[21];
VARCHAR DB[21];
EXEC SQL END DECLARE SECTION;
char szSQLErr[SQLERRMSG_LEN + 1];
int nSQLErrNo = E_SQL_SUCCEED; /*定义全局数据库错误码*/
sql_context actx=ctx;
struct timespec waitTime;
USER.len=strlen(username);
memcpy(USER.arr,username,USER.len);
PSW.len=strlen(username);
memcpy(PSW.arr,password,PSW.len);
DB.len=strlen(dbname);
memcpy(DB.arr,dbname,DB.len);
AGAIN:
EXEC SQL CONTEXT USE :actx;
EXEC SQL WHENEVER SQLERROR GOTO ERRMETHOD;
EXEC SQL CONNECT :USER
IDENTIFIED BY :PSW
USING :DB;
return 1;
ERRMETHOD:
GETSQLERROR(szSQLErr,nSQLErrNo);
if(!g_dbconStop)
{
waitTime.tv_sec=5;
waitTime.tv_nsec=0;
nanosleep(&waitTime,NULL);
goto AGAIN;
}
return 0;
}
int inserttb(void*ctx)
{
#undef SQLCA
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
int C1;
VARCHAR C2[21];
VARCHAR C3[9];
VARCHAR C4[9];
EXEC SQL END DECLARE SECTION;
char szSQLErr[SQLERRMSG_LEN + 1];
int nSQLErrNo = E_SQL_SUCCEED; /*定义全局数据库错误码*/
sql_context actx=ctx;
struct timespec waitTime;
//********************************************
//要插入的数据键盘输入，没有进行数据校验
printf("int , char[20],yyyymmdd,yyyymmdd\n");
scanf("%d",&C1);//only num
getchar();//get wrap
fgets(C2.arr,21,stdin);//string and wrap and '\0';
scanf("%s%s",C3.arr,C4.arr);//blank or wrap in string ang string end
C2.len=strlen(C2.arr)-1;
C3.len=strlen(C3.arr);
C4.len=strlen(C4.arr);
C2.arr[C2.len]='\0';
printf("input just now：%d %s %s %s \n",C1,C2.arr,C3.arr,C4.arr);
//*********************************************
AGAIN:
EXEC SQL CONTEXT USE :actx;
EXEC SQL WHENEVER SQLERROR GOTO ERRMETHOD;
EXEC SQL INSERT INTO XJYTAB(COL1,COL2,COL3TIME,D) VALUES(:C1,:C2,to_date(:C3,'yyyymmdd'),to_date(:C4,'yyyymmdd'));
EXEC SQL COMMIT WORK;
return 1;
ERRMETHOD:
EXEC SQL WHENEVER SQLERROR continue;
GETSQLERROR(szSQLErr,nSQLErrNo);
if(!g_dbconStop&&E_SQL_SUCCEED==nSQLErrNo)
{
EXEC SQL ROLLBACK WORK RELEASE;
getConnection(ctx,sn,sp,sdb);
goto AGAIN;
}
return 0;
}
int selecttb(void*ctx)
{
#undef SQLCA
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
int C1;
VARCHAR C2[21];
VARCHAR C3[9];
VARCHAR C4[9];
EXEC SQL END DECLARE SECTION;
char szSQLErr[SQLERRMSG_LEN + 1];
int nSQLErrNo = E_SQL_SUCCEED; /*定义全局数据库错误码*/
sql_context actx=ctx;
struct timespec waitTime;
AGAIN:
EXEC SQL CONTEXT USE :actx;
EXEC SQL WHENEVER SQLERROR GOTO ERRMETHOD;
EXEC SQL DECLARE MYCursor CURSOR FOR
select COL1,COL2,to_char(COL3TIME,'yyyymmdd'),to_char(d,'yyyymmdd')
FROM XJYTAB;
EXEC SQL OPEN MYCursor;
EXEC SQL WHENEVER NOT FOUND DO break;
for(;;)
{
EXEC SQL FETCH MYCursor INTO :C1,:C2,:C3,:C4 ;
//*******************************************************
//select 表格，结束符'\0'要自己截断，否则尾部保留上次读取的
C2.arr[C2.len]='\0';
C3.arr[C3.len]='\0';
C4.arr[C4.len]='\0';
printf("%d %s %s %s\n",C1,C2.arr,C3.arr,C4.arr);
//*******************************************************
}
EXEC SQL CLOSE MYCursor;
EXEC SQL COMMIT WORK;/*会提交事务/释放所有的锁定及其资源*/
return 1;
ERRMETHOD:
EXEC SQL WHENEVER SQLERROR continue;//少了这一句会error 标识RRMETHOD 未定义
GETSQLERROR(szSQLErr,nSQLErrNo);
if(!g_dbconStop&&E_SQL_SUCCEED==nSQLErrNo)
{
EXEC SQL ROLLBACK WORK RELEASE;
getConnection(ctx,sn,sp,sdb);
goto AGAIN;
}
return 0;
}
int mainPc()
{
sql_context ctx;
/*下面不可以有分号‘；’*/
#undef SQLCA
EXEC SQL INCLUDE SQLCA;
EXEC SQL CONTEXT ALLOCATE :ctx;
EXEC SQL CONTEXT USE :ctx;
if(0==getConnection(ctx,sn,sp,sdb))
return 0;
inserttb(ctx);
selecttb(ctx);
EXEC SQL COMMIT RELEASE;/*提交事务/释放所有的锁定及其资源/断开与数据库的连接*/
EXEC SQL CONTEXT FREE :ctx;
return 1;
}
ph.h
#ifndef __PH_H
#define __PH_H
#include<stdio.h>
#include<string.h>
#include<time.h>
#include <stdlib.h>
#define sn "用户名"
#define sp "密码"
#define sdb "数据库名"
#define SQLERRMSG_LEN 50 /*数据库错误信息最大长度*/
#define E_SQL_SUCCEED 0 /*数据库执行成功*/
#define E_SQL_KEY_DUPLICATE 1 /*重复键值错误*/
#define E_SQL_NOT_CONNECTED 2 /*数据库未连接*/
#define E_SQL_BUSY 3 /*数据库忙*/
#define E_SQL_OTHER 4 /*其他错误*/
/*根据sqlca.sqlcode获取错误类型及错误信息*/
#define GETSQLERROR(pszErrMsg, nSQLErrNo) \
{ \
printf("%s%d%d",__FILE__,sqlca.sqlcode,__LINE__); \
printf("%s%s%d",__FILE__,sqlca.sqlerrm.sqlerrmc,__LINE__); \
if ( -1 == sqlca.sqlcode ) \
{ \
nSQLErrNo = E_SQL_KEY_DUPLICATE; \
if(pszErrMsg) \
strcpy(pszErrMsg, "重复键值错误"); \
} \
else if (((sqlca.sqlcode == -1012)||(sqlca.sqlcode == -1041) \
||(sqlca.sqlcode == -3114)||(sqlca.sqlcode == -3113) \
||(sqlca.sqlcode == -1003)) \
||((sqlca.sqlcode <= -12150)&& (sqlca.sqlcode >= -12285)) \
||((sqlca.sqlcode >= -12699)&&(sqlca.sqlcode <= -12500))) \
{ \
nSQLErrNo = E_SQL_NOT_CONNECTED; \
if(pszErrMsg) \
strcpy(pszErrMsg, "数据库未连接"); \
} \
else if (sqlca.sqlcode==-54) \
{ \
nSQLErrNo = E_SQL_BUSY; \
if(pszErrMsg) \
strcpy(pszErrMsg, "数据库忙"); \
} \
else \
{ \
nSQLErrNo = E_SQL_OTHER; \
if(pszErrMsg) \
strcpy(pszErrMsg, "其他错误"); \
} \
printf("%s%s%d",__FILE__,pszErrMsg,__LINE__); \
}
extern char g_dbconStop;
#endif
Makefile
#include ${ORACLE_HOME}/precomp/lib/env_precomp.mk
LIBPATH=-L$(ORACLE_HOME)/lib
TARGET=XJYEASY
OJBCTS:=pcmain.o mypc.o
INCLUDES = -I.
CC=gcc
CFLAGS=
PROC=proc
SYSLIBS= -lclntsh
PROCFLAGS= sqlcheck=semantic userid=用户名/密码@数据库名 \
include={$(ORACLE_HOME)/precomp/public,/usr/include,/usr/lib/gcc/x86_64-redhat-linux/4.1.2/include} \
$(INCLUDES) \
code=ANSI_C parse=FULL \
iname=$^
.SUFFIXES:
.SUFFIXES:.o .c .pc
$(TARGET):$(OJBCTS)
$(CC) $^ $(SYSLIBS) $(LIBPATH) $(INCLUDES) -o $@
@echo "$(TARGET) make successfull"
.c.o:
$(CC) $(CFLAGS) -c $< $(INCLUDES)
.pc.c:
$(PROC) $(PROCFLAGS)
@echo 'get mypc.c success'
clean:
rm *.o