PROC＋＋批次匯入匯出ORACLE資料庫表 (轉)

PROC＋＋批次匯入匯出ORACLE資料庫表 (轉)[@more@]

最近在開發一個專案中，為了解決ＩＯ瓶頸，不得不把資料庫中的資料匯出為文字。文字傳到客戶端後又要匯入到資料庫。本人用Ｃ＋＋Builder嵌入PROC++寫了一個匯入匯出的DLL。如果對你有用深感榮幸！詳細內容如下：

 

一、準備工作

環境：Win 2000 PRO, 9i,C++ Builder 5.5

引入必要的ORACLE內部：要用的函式在$(ORACEL_HOME)inora9.dll連結庫中。為了能在C++ Builder中使用，先得生成LIB：implib sqlora9.lib sqlora9.dll

 

二、原始檔分析

//-------------------------------------------------------------------------

//加入必要的標頭檔案

#include #include<.h> #include #include #include

#include #include #include #include #include

//說明DLL的輸出函式

extern "C" _declspec(dllexport) int _stdcall Connect(const char *Username,

  const char *Pass, 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  //定義指示器變數名字的最大長度

 :namespace prefix = o ns = "urn:schemas--com::office" />

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 *Unit;  //定義選擇項描述

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 WIN 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 NTIFIED 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 ", 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 ");

  fprintf(fout, "INFILE '%s' ", FileName);

  fprintf(fout, "APPEND INTO TABLE %s ( ", 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, ", ");

  }

  fprintf(fout, ") ");

  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