Pro*C连接KingbaseES V8R6

概述

KingbaseES V8R6兼容了Oracle的Pro*C接口，但使用时依赖Oracle的proc编译环境，KingbaseES官方文档没有这块内容的配置说明；本人在项目实践中遇到了，本文就kes proc环境配置及使用做一分享，望对大家有用。

测试环境：
CPU：X86_64
操作系统：CentOS 7
KES版本: KingbaseES V8R6C006B0021
Oracle 11g proc编译环境

Pro*C 简介

嵌有 SQL 语句的 C 语言程序称为 Pro*C 程序，使用的 SQL 语言称为嵌入式 SQL，嵌入在主语言程序中的 SQL语句并不能直接被主语言编译程序识别，需要预编译方能被识别。

KingbaseES 的 Pro*C 功能

kes提供了CPU 架构为 X86 架构或者 arm 架构的 Linux 平台下的 驱动，但不包含proc编译环境，需要依赖Oracle proc编译环境，不需要完整安装Oracle，需要安装Oracle以下3个客户端包：
1）oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm (包含运行OCI, OCCI, and JDBC-OCI 程序的所有文件，proc环境依赖oci)
2) oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm (包含开发Oracle程序所需的头文件和makefile示例)
3）oracle-instantclient11.2-precomp-11.2.0.4.0-1.x86_64.rpm （包含预编译proc程序所需要proc二进制程序以及相关文件）
oracle客户端安装包分享：
链接：https://pan.baidu.com/s/1hRJDXqEa_e2iTiyXhO1GhA
提取码：5405
–来自百度网盘超级会员V6的分享

Oracle proc编译环境安装

note: 上述三个依赖要按以上次序安装

安装完成后，Oracle客户端程序位于路径：/usr/lib/oracle/11.2/client64
配置编译proc程序所在操作系统用户的环境变量，本人使用kingbase用户：

cd /home/kingbase
mkdir kbproc
vi  /home/kingbase/.bash_profile
export KINGBASE_CONFDIR=/home/kingbase/kbproc

export ORACLE_HOME=/usr/lib/oracle/11.2/client64

export LD_LIBRARY_PATH=/home/kingbase/kbproc/proc/lib:$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export PATH=$PATH:$HOME/.local/bin:$HOME/bin:$ORACLE_HOME/bin

Oracle proc预编译选项配置文件

如果在预编译时不指定配置文件, 则默认配置文件是 $ORACL{E}_{H}OME/precomp/admin/pcscfg.cfg,该配置文件实际位于$ORACLE_HOME/lib/precomp/admin/pcscfg.cfg , 我们在$ORACLE_HOME下新建precomp目录的软链接

[root@localhost client64]# ln -s  ./lib/precomp/ precomp
[root@localhost client64]# ls -l
total 4
drwxr-xr-x. 2 root root   68 Oct 18 15:19 bin
drwxr-xr-x. 3 root root 4096 Oct 18 15:19 lib
lrwxrwxrwx. 1 root root   14 Oct 18 15:32 precomp -> ./lib/precomp/
[root@localhost client64]# pwd
/usr/lib/oracle/11.2/client64
[root@localhost client64]# 

pcscfg.cfg默认内容如下，其中有些目录是不存在的：

[root@localhost admin]# cat pcscfg.cfg 
sys_include=($ORACLE_HOME/sdk/include,/usr/include,/usr/lib/gcc-lib/x86_64-redhat-linux/3.2.3/include,/usr/lib/gcc/x86_64-redhat-linux/4.1.1/include,/usr/lib64/gcc/x86_64-suse-linux/4.1.2/include,/usr/lib64/gcc/x86_64-suse-linux/4.3/include,/usr/lib/gcc/x86_64-redhat-linux/4.4.4/include)
sys_include=/usr/lib/gcc/i686-redhat-linux/4.4.6/include
sys_include=/usr/lib/gcc/i686-redhat-linux/4.4.7/include
ltype=short
define=__x86_64__
[root@localhost admin]# 

pcscfg.cfg文件中的路径有些不存在，需要根据自已实际环境修改，否则预编译时会报错找不到头文件，本人环境修改后的内容如下：

[kingbase@localhost admin]$ cat pcscfg.cfg 
sys_include=(/usr/include,/usr/lib/gcc/x86_64-redhat-linux/4.8.2/include,/usr/lib/gcc/x86_64-redhat-linux/4.8.5/include)
sys_include=/usr/include/oracle/11.2/client64
ltype=short
define=__x86_64__
sys_include=/usr/include
sys_include=/$ORACLE_HOME/lib/
[kingbase@localhost admin]$

把KingbaseES proc驱动放在指定路径

KingbaseES proc驱动默认在数据库安装目录Interface/proc子目录下，include文件夹下是所需的头文件，lib目录下是所需依赖库

[kingbase@localhost proc]$ pwd
/home/kingbase/KingbaseES/Interface/proc
[kingbase@localhost proc]$ ls
include  lib  
[kingbase@localhost proc]$

在/home/kingbase下新建子目录kbproc, 测试程序放在kbproc子目录下，kes proc驱动也放在此目录下

[kingbase@localhost proc]$ pwd
/home/kingbase/kbproc/proc
[kingbase@localhost proc]$ ls
include  lib  
[kingbase@localhost proc]$ 

配置OCI服务sys_service.conf

proc依赖oci, 连库时用的服务名，服务名需要在sys_service.conf里配置，kingbaseES会通过环境变量$KINGBASE_CONFDIR寻找sys_service.conf所在路径，此处配置export KINGBASE_CONFDIR=/home/kingbase/kbproc, 在kbproc子目录下新建sys_service.conf, 内容如下：

[kingbase@localhost kbproc]$ pwd
/home/kingbase/kbproc
[kingbase@localhost kbproc]$ cat sys_service.conf 
[KingbaseES]
host=192.168.40.105
dbname=test
port=54321
[kingbase@localhost kbproc]$

新建proc测试程序proctest.pc

proc程序后缀一般为.pc, 新建proctest.pc内容如下：

#include        <stdio.h>
#include        <stdlib.h>
#include        <string.h>

EXEC SQL INCLUDE SQLCA; /* 说明一个 SQL 通信区 */
EXEC SQL INCLUDE ORACA; /* 说明一个 SQL 通信区 */

EXEC SQL WHENEVER SQLERROR do print_sqlca();

void print_sqlca();
int do_main_select();

char * main_stmt="SELECT id,name FROM tab01 WHERE id>:id ";
char name_data[100];
long SQLCODE = 0;

void print_sqlca()
{
printf("=== SQLDA ===\n");
printf("SQLCODE:[\e[31m%d\e[0m], SQLERR:[\e[31m%.*s\e[0m]\n",
sqlca.sqlcode,
sqlca.sqlerrm.sqlerrml,
sqlca.sqlerrm.sqlerrmc);
}


int main(){
  EXEC SQL BEGIN DECLARE SECTION;
  char auser[20] = "system";   //连库用户名
  char apwd[20] = "111111";    //连库密码
  char adbname[20] = "KingbaseES";  //sys_service.conf中配置的服务名
  int ids[8] = {0};
  char sNames[10][10] = {0};
  int iId = 0;
  char nName[20] = {0};

  typedef struct{
	int iBatchNO;
        char    sTranType[3];
  }TmpList;
  
  TmpList	stTmpList;
  char    sSqlString[2048];
  EXEC SQL END DECLARE SECTION;

  EXEC ORACLE OPTION ( ORACA = YES ) ;

  EXEC SQL CONNECT :auser IDENTIFIED BY :apwd USING :adbname;
  if ( SQLCODE != 0)
  {
    printf("CONNECT Database error.\n");
    exit(0);
  }
  else{
    printf("CONNECT database succ.");
  }

  strcpy(sSqlString , "select * from tmplist;");
  printf("sSqlString=%s",sSqlString);
        EXEC SQL PREPARE CurTranPre FROM :sSqlString;
  if ( SQLCODE!=0 )
  {
     printf("prepare error.");
     exit(0);
  }

  EXEC SQL DECLARE CurTranCur CURSOR FOR CurTranPre;

  if ( SQLCODE!=0 )
  {
     printf("Declare cursor error.");
     exit(0);
  } 

  EXEC SQL OPEN CurTranCur;

  if ( SQLCODE!=0 )
  {
     printf("Open cursor error.");
     exit(0);
  }

  memset( &stTmpList, 0, sizeof( TmpList ) );

  EXEC SQL FETCH CurTranCur INTO :stTmpList;

  printf("iBatchNO=%d,sTranType=%s\n",stTmpList.iBatchNO,stTmpList.sTranType);
  EXEC SQL CLOSE CurTranCur;
  return 0;
}

在数据库中新建测试数据：

CREATE TABLE "public"."tmplist" (

"ibatchno" integer NULL,

"strantype" character(3 char) NULL

);

insert into tmplist values(1,'abc');

预编译proctest.pc, 成功后会生成c文件proctest.c

[kingbase@localhost proc]$ proc proctest.pc 

Pro*C/C++: Release 11.2.0.4.0 - Production on Tue Oct 18 16:49:10 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

System default option values taken from: /usr/lib/oracle/11.2/client64/precomp/admin/pcscfg.cfg

[kingbase@localhost proc]$ ls
include  lib  proctest.c  proctest.lis  proctest.pc
[kingbase@localhost proc]$ 

编译proctest.c, 编译成功后会生成可执行文件test

[kingbase@localhost proc]$ gcc -I /home/kingbase/kbproc/proc/include/ -I /home/kingbase/kbproc/proc/include/oci/ -L /home/kingbase/kbproc/proc/lib/ -lclntsh -lsqllib -o test proctest.c
[kingbase@localhost proc]$ ls
include  lib  proctest.c  proctest.lis  proctest.pc  test
[kingbase@localhost proc]$ 

执行测试程序，程序功能比较简单，连接test库并查询表tmplist表的一条记录，通过结构体stTmpList来保存查询结果：

[kingbase@localhost proc]$ ./test 
CONNECT database succ.sSqlString=select * from tmplist;iBatchNO=1,sTranType=abc
[kingbase@localhost proc]$

结束