首页 > 数据库 > Oracle > 正文

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

2024-08-29 13:31:10
字体:
来源:转载
供稿:网友
注册会员,创建你的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

 
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表