注册会员,创建你的web开发资料库,
最近在开发一个项目中,为了解决数据库io瓶颈,不得不把数据库中的数据导出为文本文件。文本传到客户端后又要导入到数据库。本人用c++builder嵌入proc++写了一个导入导出的dll。如果对你有用深感荣幸!详细内容如下:
一、准备工作
计算机环境:win 2000 pro,oracle 9i,c++ builder 5.5
引入必要的oracle内部函数:要用的函数在$(oracel_home)/bin qlora9.dll链接库中。为了能在c++ builder中使用,先得生成lib:implib sqlora9.lib sqlora9.dll
二、源文件分析
//-------------------------------------------------------------------------
//加入必要的头文件
#include<vcl.h> #include<windows.h> #include<stdio.h> #include<stdlib.h> #include<string.h>
#include<time.h> #include<math.h> #include<fcntl.h> #include<io.h> #include<sys tat.h>
//说明dll的输出函数
extern "c" _declspec(dllexport) int _stdcall connectdb(const char *username,
const char *password, const char *dbname);
extern "c" _declspec(dllexport) int _stdcall importtxtfile(tlist *lengtharray,
string *fieldarray, const char *tablename,
const char *filename);
extern "c" _declspec(dllexport) int _stdcall exporttxtfile(const char *sql,
const char *filename);
#pragma hdrstop
//----------------------------------------------------------------------------
#define max_items 20 //定义最大字段数
#define max_vname_len 30 //定义选择表项最大长度
#define max_iname_len 30 //定义指示器变量名字的最大长度
exec sql include sqlca; //说明sql通讯区
exec sql include oraca; //说明oracle通讯区
exec sql include sqlda; //说明sql语句描述结构/*sqlda结构体请查相关资料*/
exec oracle option (oraca = yes);
exec oracle option (release_cursor = yes);
//说明oracle外部函数
extern "c" _declspec(dllimport) void _stdcall sqlclu(sqlda*);
extern "c" _declspec(dllimport) void _stdcall sqlnul(short*, short*, int*);
extern "c" _declspec(dllimport) void _stdcall sqlprc(int*, int*, int*);
extern "c" _declspec(dllimport) struct sqlda * _stdcall sqlald(int, unsigned int, unsigned int);
sqlda *selectunit; //定义选择项描述
sqlda *bindunit; //定义输入项空间
//定义变量,以存放连接数据库的参数
exec sql begin declare section;
char user[20];//用户名
char pwd[20];//密码
char db[20];//数据库服务名
exec sql end declare section;
bool bconnect = false;//是否连接标志
#pragma hdrstop
#pragma argsused
//c++ builder dll的主函数
bool winapi dllmain(hinstance hinstdll, dword fwdreason, lpvoid lpvreserved)
{
return 1;
}
/*---------------------------------------------------------------------------
连接数据库
---------------------------------------------------------------------------*/
int _stdcall connectdb(const char *username, const char *password,
const char *dbname)
{
strcpy(user, username);
strcpy(pwd, password);
strcpy(db, dbname);
exec sql connect :user identified by :pwd using :db;
if (sqlca.sqlcode < 0)
return -1;
bconnect = true;
return 0;
}
/*---------------------------------------------------------------------------
导出文本函数
因为不确定select语句的表及字段,所以我使用动态语句(oracle dynamic sql)的//第四种方式。动态sql方法四是在不确定sql语句的选择项与输入项,且不知个数与数据类型的情况下使用的一种复杂程序设计技术。
---------------------------------------------------------------------------*/
int _stdcall exporttxtfile(const char *sql/*sql选择语句*/, const char filename/*导出目标文本文件名*/)
{
int null_ok, precision, scale;
int handle;
if ((handle = open(filename, o_creat|o_text|o_append|o_rdwr, s_iread|s_iwrite)) == -1)
{
//文件打开出错
return -1;
}
//定义变量,以存放sql语句
exec sql begin declare section;
char sqlstr[256];
exec sql end declare section;
//检查是否连接数据库
if (bconnect == false) return -2;
strcpy(sqlstr/*.arr*/, sql);
// sqlstr.len = strlen(sql);
//给描述区分配空间
if ((selectunit = sqlald(max_items, max_vname_len, max_iname_len)) == (sqlda *)null)
{
//空间分配失败
return -3;
}
if ((bindunit = sqlald(max_items, max_vname_len, max_iname_len)) == (sqlda *)null)
{
//空间分配失败
return -3;
}
//给查询返回值存储区分配空间
selectunit->n = max_items;
for (int i=0; i < max_items; i++)
{
bindunit->i[i] = (short *)malloc(sizeof(short *));
bindunit->v[i] = (char *)malloc(max_vname_len);
}
for (int i=0; i < max_items; i++)
{
selectunit->i[i] = (short *)malloc(sizeof(short *));
selectunit->v[i] = (char *)malloc(max_vname_len);
}
exec sql whenever sqlerror goto sqlerr;//do sql_error("导出出错");
//设置sql语句
exec sql prepare sqlsa from :sqlstr;
exec sql declare cursorbase cursor for sqlsa;
//输入描述处理
bindunit->n = max_items;
exec sql describe bind variables for sqlsa into bindunit;
if (bindunit->f < 0)
{
return -4;
//输入项过多
}
bindunit->n = bindunit->f;
//打开光标
exec sql open cursorbase using descriptor bindunit;
//选择项处理
exec sql describe select list for sqlsa into selectunit;
if (selectunit->f < 0)
{
return -4;
//选择表项过多
}
selectunit->n = selectunit->f;
//因为所有格式,类型都是不确定的,所以要得到正确的返回值就要处理格式
for (int i=0; i < selectunit->f; i++)
{
sqlnul(&(selectunit->t[i]), &(selectunit->t[i]), &null_ok);
switch (selectunit->t[i])
{
case 1://char
break;
case 2://number
sqlprc(&(selectunit->l[i]), &precision, &scale);
if (precision == 0)
precision = 40;
selectunit->l[i] = precision + 2;
break;
case 8://long
selectunit->l[i] = 240;
break;
case 11://rowid
selectunit->l[i] = 18;
break;
case 12://date
selectunit->l[i] = 9;
break;
case 23://raw
break;
case 24://longraw
selectunit->l[i] = 240;
break;
}
selectunit->v[i] = (char *)realloc(selectunit->v[i], selectunit->l[i]+1);
selectunit->t[i] = 1;//把所有类型转换为字符型
}
exec sql whenever not found goto endfor;
for (;;)
{
exec sql fetch cursorbase using descriptor selectunit;
//输出各字段
for (int i=0; i < selectunit->f; i++)
{
char buffer[256];
if (i != selectunit->f-1)
sprintf(buffer, "%s", selectunit->v[i]);
else sprintf(buffer, "%s/r/n", selectunit->v[i]);
int length = strlen(buffer);
if (write(handle, buffer, length) != length)
{
return -5;
//写文件失败 exit(1);
}
}
}
endfor:
close(handle);
for (int i=0; i < max_items; i++)
{
if (selectunit->v[i] != (char *)null)
free(selectunit->v[i]);
free(selectunit->i[i]);
}
for (int j=0; j < max_items; j++)
{
if (bindunit->v[j] != (char *)null)
free(bindunit->v[j]);
free(bindunit->i[j]);
}
sqlclu(selectunit);
sqlclu(bindunit);
exec sql close cursorbase;
return 0;
sqlerr:
return -6;
}
/*----------------------------------------------------------------------------
导入文本
为了批量导入,在此我调用的sqlldr工具
首先生成sql*loader控制文件,后运行sqlldr
----------------------------------------------------------------------------*/
int _stdcall importtxtfile(tlist lengtharray/*导入文本的字段长度链表*/,
string *fieldarray/*数据库表的了段名数组*/, const char tablename/*导入的目标表*/, const char filename/*导入的源文本文件*/)
{
//产生sql*loader控制文件
file *fout, *fp;
char execommand[256];
char sqlload[] = "./ qlload.ctl";
//检查是否连接数据库
if (bconnect == false) return -2;
if ((fout=fopen(sqlload, "w")) == null)
{
//建立控制文件出错
return -1 ;
}
fprintf(fout, "load data/n");
fprintf(fout, "infile '%s'/n", filename);
fprintf(fout, "append into table %s (/n", tablename);
int istart = 1;
for(int i=0; i < lengtharray->count; i++)
{
fprintf(fout, "%11s position(%d:%d)", fieldarray[i], istart, *(int*)lengtharray->items[i]+istart-1);
istart += *(int*)lengtharray->items[i];
fprintf(fout, " char");
if(i < lengtharray->count-1)
fprintf(fout, ",/n");
}
fprintf(fout, ")/n");
fclose(fout);
sprintf(execommand, "sqlldr.exe userid=%s/%[email protected]%s control=%s",
user, pwd, db, sqlload);
if (system(execommand) == -1)
{
//sql*loader执行错误
return -1;
}
return 0 ;
}
//----------------------------------------------------------------------------
三、编译
用oracle的proc预编译器预编后,放入c++ builder中联编。联编时需加入前面生成的sqlora9.lib。联编时还要注意,所有proc生成的oracle内部函数调用都要说明为extern "c" _declspec(dllexport) type _stdcall类型。
水平有限还请见谅!!!请多多指点。qq:5005647