注冊會員，創(chuàng)建你的web開發(fā)資料庫，
最近在開發(fā)一個項目中，為了解決數(shù)據(jù)庫ｉｏ瓶頸，不得不把數(shù)據(jù)庫中的數(shù)據(jù)導(dǎo)出為文本文件。文本傳到客戶端后又要導(dǎo)入到數(shù)據(jù)庫。本人用ｃ＋＋builder嵌入proc++寫了一個導(dǎo)入導(dǎo)出的dll。如果對你有用深感榮幸！詳細內(nèi)容如下：
一、準備工作
計算機環(huán)境：win 2000 pro,oracle 9i,c++ builder 5.5
引入必要的oracle內(nèi)部函數(shù)：要用的函數(shù)在$(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的輸出函數(shù)
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 //定義最大字段數(shù)
#define max_vname_len 30 //定義選擇表項最大長度
#define max_iname_len 30 //定義指示器變量名字的最大長度
exec sql include sqlca; //說明sql通訊區(qū)
exec sql include oraca; //說明oracle通訊區(qū)
exec sql include sqlda; //說明sql語句描述結(jié)構(gòu)/*sqlda結(jié)構(gòu)體請查相關(guān)資料*/
exec oracle option (oraca = yes);
exec oracle option (release_cursor = yes);
//說明oracle外部函數(shù)
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; //定義輸入項空間
//定義變量，以存放連接數(shù)據(jù)庫的參數(shù)
exec sql begin declare section;
char user[20];//用戶名
char pwd[20];//密碼
char db[20];//數(shù)據(jù)庫服務(wù)名
exec sql end declare section;
bool bconnect = false;//是否連接標志
#pragma hdrstop
#pragma argsused
//c++ builder dll的主函數(shù)
bool winapi dllmain(hinstance hinstdll, dword fwdreason, lpvoid lpvreserved)
{
return 1;
}
/*---------------------------------------------------------------------------
連接數(shù)據(jù)庫
---------------------------------------------------------------------------*/
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;
}
/*---------------------------------------------------------------------------
導(dǎo)出文本函數(shù)
因為不確定select語句的表及字段，所以我使用動態(tài)語句(oracle dynamic sql)的//第四種方式。動態(tài)sql方法四是在不確定sql語句的選擇項與輸入項，且不知個數(shù)與數(shù)據(jù)類型的情況下使用的一種復(fù)雜程序設(shè)計技術(shù)。
---------------------------------------------------------------------------*/
int _stdcall exporttxtfile(const char *sql/*sql選擇語句*/, const char filename/*導(dǎo)出目標文本文件名*/)
{
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;
//檢查是否連接數(shù)據(jù)庫
if (bconnect == false) return -2;
strcpy(sqlstr/*.arr*/, sql);
// sqlstr.len = strlen(sql);
//給描述區(qū)分配空間
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;
}
//給查詢返回值存儲區(qū)分配空間
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("導(dǎo)出出錯");
//設(shè)置ｓｑｌ語句
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;//把所有類型轉(zhuǎn)換為字符型
}
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;
}
/*----------------------------------------------------------------------------
導(dǎo)入文本
為了批量導(dǎo)入，在此我調(diào)用的sqlldr工具
首先生成sql*loader控制文件，后運行sqlldr
----------------------------------------------------------------------------*/
int _stdcall importtxtfile(tlist lengtharray/*導(dǎo)入文本的字段長度鏈表*/,
string *fieldarray/*數(shù)據(jù)庫表的了段名數(shù)組*/, const char tablename/*導(dǎo)入的目標表*/, const char filename/*導(dǎo)入的源文本文件*/)
{
//產(chǎn)生sql*loader控制文件
file *fout, *fp;
char execommand[256];
char sqlload[] = "./ qlload.ctl";
//檢查是否連接數(shù)據(jù)庫
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執(zhí)行錯誤
return -1;
}
return 0 ;
}
//----------------------------------------------------------------------------
三、編譯
用oracle的proc預(yù)編譯器預(yù)編后，放入c++ builder中聯(lián)編。聯(lián)編時需加入前面生成的sqlora9.lib。聯(lián)編時還要注意，所有proc生成的oracle內(nèi)部函數(shù)調(diào)用都要說明為extern "c" _declspec(dllexport) type _stdcall類型。
水平有限還請見諒！??！請多多指點。ｑｑ：5005647
