将PROC++批量导入导出ORACLE数据库表的步骤

//说明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("导出出错");

//设置ＳＱＬ语句

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[] = ".//sqlload.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/%s@%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类型。

水平有限还请见谅！！！请多多指点。ＱＱ：5005647