PROC++批量导入导出ORACLE数据库表

类别:数据库 点击:0 评论:0 推荐:

最近在开发一个项目中,为了解决数据库IO瓶颈,不得不把数据库中的数据导出为文本文件。文本传到客户端后又要导入到数据库。本人用C++Builder嵌入PROC++写了一个导入导出的DLL。如果对你有用深感荣幸!详细内容如下:

 

一、准备工作

计算机环境:Win 2000 PRO,ORACLE 9i,C++ Builder 5.5

引入必要的ORACLE内部函数:要用的函数在$(ORACEL_HOME)\bin\sqlora9.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\stat.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[] = ".\\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类型。

 

水平有限还请见谅!!!请多多指点。QQ:5005647

 

本文地址:http://com.8s8s.com/it/it21233.htm