使用C语言开发Oracle应用程序通常有两种方法。一是利用嵌入式SQL语言,将SQL作为子语言嵌入到C语言中,借助C语言访问Oracle以及实现过程化控制和复杂计算。二是利用Oracle调用接口(Oracle Call Interface简称OCI),通过调用一系列OCI函数即可访问数据库。我在这里介绍的是前一种方法。另外,在本文中,我重点介绍的是利用C语言开发Oracle应用程序的流程和步骤,而对嵌入式SQL的语法不会过多介绍,如果想了解详细语法,我在这里可以推荐一本书,书名是《Oracle 11g Pro*C\C++编程艺术》,此书对Oracle嵌入式SQL有详细详解,非常适合做新手入门教材,电子版在网上有,请需要的读者自行查找,
一、工具
Oracle 11g、vs2017、win10
二、概述
要用C语言开发Oracle应用程序,首先编写包含嵌有SQL语言的C语言的源代码,后缀名为pc,然后将代码通过Oracle自带的预编译工具Proc将SQL语句转为对Oracle运行库函数(SQLLIB)的调用,预编译后的代码即为C语言源代码,再将预编译后的代码拷到vs中,经过vs编译、链接和运行后,相应的Oracle应用程序即开发完成。过程如下图所示。
三、详细步骤
1.编写pc源文件。注意,编码格式应为ANSI编码,如果使用其他编码,可能会报错。例如,我使用UTF-8编码,会报如下图所示错误。
示例源代码test.pc如下所示。此代码的功能是查询表STUDENT中学号为2005的学生的学生姓名。
#include <stdio.h>
#include<stdlib.h>
#include<string.h>
#include<sqlca.h>
//我最后用C++编译器编译此文件,不加“extern "C"”会报错
//如报错:无法解析的外部符号
extern "C" unsigned int sqlgls(char *, size_t *, size_t *);
//连接数据库
void connect()
{
EXEC SQL BEGIN DECLARE SECTION;
char username[20], password[20];
EXEC SQL END DECLARE SECTION;
strcpy_s(username, strlen("scott") + 1, "scott");
strcpy_s(password, strlen("tiger") + 1, "tiger");
EXEC SQL CONNECT : username IDENTIFIED BY : password;
}
//断开连接,提交事务
void release()
{
EXEC SQL COMMIT RELEASE;
}
//错误处理函数,当访问数据库出错时,会中止程序执行,打印错误信息
void sql_error()
{
char stm[100];
size_t sqlfc, stmlen = 100;
unsigned int i;
i = sqlgls(stm, &stmlen, &sqlfc);
printf("出错语句为:%.*s\n", stmlen, stm);
printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
system("PAUSE");
exit(1);
}
int main()
{
//主变量说明
EXEC SQL BEGIN DECLARE SECTION;
char name[9];
int sno;
EXEC SQL END DECLARE SECTION;
//错误处理
EXEC SQL WHENEVER NOT FOUND DO sql_error();
EXEC SQL WHENEVER SQLERROR DO sql_error();
EXEC SQL WHENEVER SQLWARNING DO sql_error();
connect();
sno=2005;
EXEC SQL SELECT SNAME INTO :name
FROM STUDENT
WHERE SNO=:sno;
printf("学号为2005的学生是%s\n",name);
release();
system("PAUSE");
return 0;
}
2、预编译pc源程序。在命令行中输入命令proc,再加上一些预编译选项即可。常用预编译选项有INAME,INCLUDE,CODE,PARSE,CHAR_MAP等。INAME指定要预编译的文件名,当该选项是命令行第一个选项时,可省略“INAME=”。INCLUDE选项用于指定#include或EXEC SQL INCLUDE所对应的头文件所在路径。CODE选项用于指定预编译器所生成的C函数原型格式,可为ANSI_C,KR_C或CPP。PARSE指定解析pc源文件的方法,PARSE=FULL时,使用C解析器解析;PARSE=NONE或PARTICAL时,使用C++解析器解析,此时必须在定义部分内定义宿主变量和结构(如果不在定义部分内定义结构,当宿主变量是自定义结构时,将无法识别结构名)。CHAR_MAP用于指定char[n]与Oracle外部数据类型的映射关系,它有CHARZ,STRING,VARCHAR2,CHARF四种值,当CHAR_MAP=CHARZ时,字符宿主变量为用空格填充、以null('\0')终止的定长字符串;当CHAR_MAP=STRING时,字符宿主变量为以null终止的变长字符串;当CHAR_MAP=VARCHAR2时,字符宿主变量为以空格填充的定长字符串(包括null);当CHAR_MAP=CHARF时,字符数组变量为以空格填充的定长字符串。若想知道更多预编译选项的介绍,可查看《Oracle 11g Pro*C\C++编程艺术》的附录A。
直接在命令行中指定预编译选项有时候会很麻烦,因为预编译选项有很多,一个个指定太过繁琐。所以,除了在命令行中指定预编译选项,也可在系统配置文件pcscfg.cfg(在%SRCHOME%/precomp/admin目录中)中配置一些常用的、相对固定的预编译选项。当进行预编译时,proc会自动加载此配置文件。pcscfg.cfg配置示例如下。“include=F:\test\include”为程序中所用到的C语言标准库所在的文件目录(我试过用vs中C语言标准库所在的文件目录或Dev-C++中C语言标准库所在目录作为此目录,但都在预编译过程中报错,最终,我下载了vc,将vc中C语言的标准库文件拷入F:\test\include中,才得以预编译成功)。
include=%SRCHOME%/precomp/public
include=F:\test\include
CHAR_MAP=STRING
CODE=CPP
PARSE=NONE
示例命令行命令如下:
proc F:\test\test.pc
预编译pc源代码test.pc后,将生成test.cpp文件,其内容如下。
/* Result Sets Interface */
#ifndef SQL_CRSR
# define SQL_CRSR
struct sql_cursor
{
unsigned int curocn;
void *ptr1;
void *ptr2;
unsigned int magic;
};
typedef struct sql_cursor sql_cursor;
typedef struct sql_cursor SQL_CURSOR;
#endif /* SQL_CRSR */
/* Thread Safety */
typedef void * sql_context;
typedef void * SQL_CONTEXT;
/* Object support */
struct sqltvn
{
unsigned char *tvnvsn;
unsigned short tvnvsnl;
unsigned char *tvnnm;
unsigned short tvnnml;
unsigned char *tvnsnm;
unsigned short tvnsnml;
};
typedef struct sqltvn sqltvn;
struct sqladts
{
unsigned int adtvsn;
unsigned short adtmode;
unsigned short adtnum;
sqltvn adttvn[1];
};
typedef struct sqladts sqladts;
static struct sqladts sqladt = {
1,1,0,
};
/* Binding to PL/SQL Records */
struct sqltdss
{
unsigned int tdsvsn;
unsigned short tdsnum;
unsigned char *tdsval[1];
};
typedef struct sqltdss sqltdss;
static struct sqltdss sqltds =
{
1,
0,
};
/* File name & Package Name */
struct sqlcxp
{
unsigned short fillen;
char filnam[16];
};
static const struct sqlcxp sqlfpn =
{
15,
"F:\\test\\test.pc"
};
static unsigned int sqlctx = 2327307;
static struct sqlexd {
unsigned int sqlvsn;
unsigned int arrsiz;
unsigned int iters;
unsigned int offset;
unsigned short selerr;
unsigned short sqlety;
unsigned int occurs;
const short *cud;
unsigned char *sqlest;
const char *stmt;
sqladts *sqladtp;
sqltdss *sqltdsp;
void **sqphsv;
unsigned int *sqphsl;
int *sqphss;
void **sqpind;
int *sqpins;
unsigned int *sqparm;
unsigned int **sqparc;
unsigned short *sqpadto;
unsigned short *sqptdso;
unsigned int sqlcmax;
unsigned int sqlcmin;
unsigned int sqlcincr;
unsigned int sqlctimeout;
unsigned int sqlcnowait;
int sqfoff;
unsigned int sqcmod;
unsigned int sqfmod;
void *sqhstv[4];
unsigned int sqhstl[4];
int sqhsts[4];
void *sqindv[4];
int sqinds[4];
unsigned int sqharm[4];
unsigned int *sqharc[4];
unsigned short sqadto[4];
unsigned short sqtdso[4];
} sqlstm = {12,4};
// Prototypes
extern "C" {
void sqlcxt (void **, unsigned int *,
struct sqlexd *, const struct sqlcxp *);
void sqlcx2t(void **, unsigned int *,
struct sqlexd *, const struct sqlcxp *);
void sqlbuft(void **, char *);
void sqlgs2t(void **, char *);
void sqlorat(void **, unsigned int *, void *);
}
// Forms Interface
static const int IAPSUCC = 0;
static const int IAPFAIL = 1403;
static const int IAPFTL = 535;
extern "C" { void sqliem(unsigned char *, signed int *); }
typedef struct { unsigned short len; unsigned char arr[1]; } VARCHAR;
typedef struct { unsigned short len; unsigned char arr[1]; } varchar;
/* cud (compilation unit data) array */
static const short sqlcud0[] =
{12,4130,852,0,0,
5,0,0,0,0,0,27,19,0,0,4,4,0,1,0,1,5,0,0,1,5,0,0,1,10,0,0,1,10,0,0,
36,0,0,2,0,0,30,25,0,0,0,0,0,1,0,
51,0,0,3,49,0,4,59,0,0,2,1,0,1,0,2,5,0,0,1,3,0,0,
};
#include <stdio.h>
#include<stdlib.h>
#include<string.h>
#include<sqlca.h>
//我最后用C++编译器编译此文件,不加“extern "C"”会报错
//如报错:无法解析的外部符号
extern "C" unsigned int sqlgls(char *, size_t *, size_t *);
//连接数据库
void connect()
{
/* EXEC SQL BEGIN DECLARE SECTION; */
char username[20], password[20];
/* EXEC SQL END DECLARE SECTION; */
strcpy_s(username, strlen("scott") + 1, "scott");
strcpy_s(password, strlen("tiger") + 1, "tiger");
/* EXEC SQL CONNECT : username IDENTIFIED BY : password; */
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 4;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.iters = (unsigned int )10;
sqlstm.offset = (unsigned int )5;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)4352;
sqlstm.occurs = (unsigned int )0;
sqlstm.sqhstv[0] = ( void *)username;
sqlstm.sqhstl[0] = (unsigned int )20;
sqlstm.sqhsts[0] = ( int )20;
sqlstm.sqindv[0] = ( void *)0;
sqlstm.sqinds[0] = ( int )0;
sqlstm.sqharm[0] = (unsigned int )0;
sqlstm.sqadto[0] = (unsigned short )0;
sqlstm.sqtdso[0] = (unsigned short )0;
sqlstm.sqhstv[1] = ( void *)password;
sqlstm.sqhstl[1] = (unsigned int )20;
sqlstm.sqhsts[1] = ( int )20;
sqlstm.sqindv[1] = ( void *)0;
sqlstm.sqinds[1] = ( int )0;
sqlstm.sqharm[1] = (unsigned int )0;
sqlstm.sqadto[1] = (unsigned short )0;
sqlstm.sqtdso[1] = (unsigned short )0;
sqlstm.sqphsv = sqlstm.sqhstv;
sqlstm.sqphsl = sqlstm.sqhstl;
sqlstm.sqphss = sqlstm.sqhsts;
sqlstm.sqpind = sqlstm.sqindv;
sqlstm.sqpins = sqlstm.sqinds;
sqlstm.sqparm = sqlstm.sqharm;
sqlstm.sqparc = sqlstm.sqharc;
sqlstm.sqpadto = sqlstm.sqadto;
sqlstm.sqptdso = sqlstm.sqtdso;
sqlstm.sqlcmax = (unsigned int )100;
sqlstm.sqlcmin = (unsigned int )2;
sqlstm.sqlcincr = (unsigned int )1;
sqlstm.sqlctimeout = (unsigned int )0;
sqlstm.sqlcnowait = (unsigned int )0;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}
}
//断开连接,提交事务
void release()
{
/* EXEC SQL COMMIT RELEASE; */
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 4;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )36;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)4352;
sqlstm.occurs = (unsigned int )0;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}
}
//错误处理函数,当访问数据库出错时,会中止程序执行,打印错误信息
void sql_error()
{
char stm[100];
size_t sqlfc, stmlen = 100;
unsigned int i;
i = sqlgls(stm, &stmlen, &sqlfc);
printf("出错语句为:%.*s\n", stmlen, stm);
printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
system("PAUSE");
exit(1);
}
int main()
{
//主变量说明
/* EXEC SQL BEGIN DECLARE SECTION; */
char name[9];
int sno;
/* EXEC SQL END DECLARE SECTION; */
//错误处理
/* EXEC SQL WHENEVER NOT FOUND DO sql_error(); */
/* EXEC SQL WHENEVER SQLERROR DO sql_error(); */
/* EXEC SQL WHENEVER SQLWARNING DO sql_error(); */
connect();
sno=2005;
/* EXEC SQL SELECT SNAME INTO :name
FROM STUDENT
WHERE SNO=:sno; */
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 4;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.stmt = "select SNAME into :b0 from STUDENT where SNO=:b1";
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )51;
sqlstm.selerr = (unsigned short)1;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)4352;
sqlstm.occurs = (unsigned int )0;
sqlstm.sqhstv[0] = ( void *)name;
sqlstm.sqhstl[0] = (unsigned int )9;
sqlstm.sqhsts[0] = ( int )0;
sqlstm.sqindv[0] = ( void *)0;
sqlstm.sqinds[0] = ( int )0;
sqlstm.sqharm[0] = (unsigned int )0;
sqlstm.sqadto[0] = (unsigned short )0;
sqlstm.sqtdso[0] = (unsigned short )0;
sqlstm.sqhstv[1] = ( void *)&sno;
sqlstm.sqhstl[1] = (unsigned int )sizeof(int);
sqlstm.sqhsts[1] = ( int )0;
sqlstm.sqindv[1] = ( void *)0;
sqlstm.sqinds[1] = ( int )0;
sqlstm.sqharm[1] = (unsigned int )0;
sqlstm.sqadto[1] = (unsigned short )0;
sqlstm.sqtdso[1] = (unsigned short )0;
sqlstm.sqphsv = sqlstm.sqhstv;
sqlstm.sqphsl = sqlstm.sqhstl;
sqlstm.sqphss = sqlstm.sqhsts;
sqlstm.sqpind = sqlstm.sqindv;
sqlstm.sqpins = sqlstm.sqinds;
sqlstm.sqparm = sqlstm.sqharm;
sqlstm.sqparc = sqlstm.sqharc;
sqlstm.sqpadto = sqlstm.sqadto;
sqlstm.sqptdso = sqlstm.sqtdso;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
if (sqlca.sqlcode == 1403) sql_error();
if (sqlca.sqlcode < 0) sql_error();
if (sqlca.sqlwarn[0] == 'W') sql_error();
}
printf("学号为2005的学生是%s\n",name);
system("PAUSE");
return 0;
}
3、编译C语言源代码,生成可执行文件。首先,要新建一个项目,将C语言源代码导入进去。然后要配置项目属性,点击项目->Project属性,进入Project属性页界面,点击配置属性->VC++ 目录,向包含目录中加入sqlca.h头文件所在目录,我电脑中此目录是D:\oracle\product\11.2.0\dbhome_1\precomp\public,向库目录中加入Oracle运行库(SQLLIB)所在目录,其中Oracle 11g 的运行库名称是orasql11.lib,我电脑中此目录是D:\oracle\product\11.2.0\dbhome_1\precomp\LIB。点击链接->输入,在附加依赖项中加入orasql11.lib。然后点击确定。解决方案配置要一致,所以我将Debug×86改为Debug×64。最后点击生成解决方案即可生成可执行文件。操作过程如图所示。
新建项目
导入test.cpp
点击项目->Project 属性
点击配置属性->VC++ 目录
添加头文件目录
添加库文件目录
添加库文件
选择解决方案平台
4、运行结果。如下图所示。
能力有限,如有错误之处,还望海涵!
更多推荐
C语言中使用嵌入式SQL访问Oracle数据库
发布评论