C语言中使用嵌入式SQL访问Oracle数据库

admin管理员组

文章数量:1794759

C语言中使用嵌入式SQL访问Oracle数据库

    使用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、运行结果。如下图所示。

                                                  

 

     能力有限，如有错误之处，还望海涵！

 

本文标签： 嵌入式语言数据库SQLoracle