proc++ A batch Import export Oracle database table

oracle| Data | database
Recently, in developing a project, in order to solve the database IO bottleneck, the data in the database has to be exported as a text file. Text is uploaded to the client and then imported to the database. I embed proc++ with C++builder to write a DLL for import and export. If it's of any use to you, I am honored! Detailed contents are as follows:



First, preparatory work

Computer environment: Win-pro,oracle 9i,c++ Builder 5.5

Introduce the necessary Oracle internal functions: The function to be used is in the $ (oracel_home) \ bin Qlora9.dll link library. In order to be able to use in C + + builder, first have to generate Lib:implib sqlora9.lib Sqlora9.dll



Second, source file analysis

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

Add the necessary header files

#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 >

Describes the output function of a 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 maximum number of fields

#define Max_vname_len 30//define Maximum length of Select table entry

#define Max_iname_len 30//define maximum length of indicator variable name



EXEC SQL INCLUDE Sqlca; Description SQL Communication Area

EXEC SQL INCLUDE Oraca; Description Oracle Communication Area

EXEC SQL INCLUDE Sqlda; Description of the SQL statement structure/*SQLDA structure Please check the relevant information * *



EXEC ORACLE OPTION (oraca = YES);

EXEC ORACLE OPTION (release_cursor = YES);



Description Oracle External functions

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; Define the selection description

SQLDA *bindunit; Define the input space

Define variables to hold the parameters of the connection database

EXEC SQL BEGIN DECLARE section;

Char user[20];//user Name

Char pwd[20];//password

Char db[20];//database service Name

EXEC SQL end DECLARE section;



BOOL Bconnect = false;//whether the connection flag

#pragma hdrstop



#pragma argsused

Main functions of C + + Builder DLL

BOOL WINAPI DllMain (hinstance hinstDLL, DWORD Fwdreason, LPVOID lpvreserved)

{

return 1;

}



/*---------------------------------------------------------------------------

Connecting to a database

---------------------------------------------------------------------------*/

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:P WD USING:D B;



if (Sqlca.sqlcode < 0)

return-1;



Bconnect = true;

return 0;

}

/*---------------------------------------------------------------------------

Exporting text functions

Because the tables and fields of the SELECT statement are not determined, I use the fourth way of the dynamic statement (ORACLE dynamic SQL). Dynamic SQL method Four is a complex program design technique that is used without knowing the number and data type of the selection and entry of SQL statements.

---------------------------------------------------------------------------*/

int _stdcall exporttxtfile (const char *SQL/*SQL SELECT statement */, const char filename/* export target text file name * * *)

{

int NULL_OK, precision, scale;



int handle;



if (handle = open (FileName, o_creat| O_text| o_append| O_rdwr, s_iread| S_iwrite)) = =-1)

{

File Open Error

return-1;

}

Define variables to store SQL statements

EXEC SQL BEGIN DECLARE section;

Char sqlstr[256];

EXEC SQL end DECLARE section;



Check to see if the database is connected

if (bconnect = = false) return-2;



strcpy (sqlstr/*.arr*/, SQL);

Sqlstr.len = strlen (sql);



Allocating space to the description area

if ((Selectunit = Sqlald (Max_items, Max_vname_len, max_iname_len)) = = (SQLDA *) NULL)

{

Space allocation failed

return-3;

}



if ((Bindunit = Sqlald (Max_items, Max_vname_len, max_iname_len)) = = (SQLDA *) NULL)

{

Space allocation failed

return-3;

}

Return a value store allocation space to a query

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 ("Export error");

Set up SQL statements

EXEC SQL PREPARE Sqlsa from:sqlstr;

EXEC SQL DECLARE cursorbase CURSOR for Sqlsa;



Input description Processing

Bindunit->n = Max_items;

EXEC SQL DESCRIBE BIND VARIABLES for Sqlsa into Bindunit;



if (Bindunit->f < 0)

{

return-4;

Too many input items

}

Bindunit->n = bindunit->f;

Open cursor

EXEC SQL OPEN cursorbase USING descriptor bindunit;



Select Item Handling

EXEC SQL DESCRIBE SELECT LIST for Sqlsa into Selectunit;



if (Selectunit->f < 0)

{

return-4;

Select too many table items

}

Selectunit->n = selectunit->f;

Because all formats, types are indeterminate, so to get the correct return value you need to process the format

for (int i=0 i < selectunit->f; i++)

{

Sqlnul (& (Selectunit->t[i]), & (Selectunit->t[i]), &AMP;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;//converts all types to character type

}



EXEC SQL whenever not FOUND goto endfor;



for (;;)

{

EXEC SQL FETCH cursorbase USING descriptor selectunit;



Output each field

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;

Write file failed 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;

}



/*----------------------------------------------------------------------------

Import text

For bulk Import, here I call the Sqlldr tool

First generate Sql*loader control file, then run Sqlldr

----------------------------------------------------------------------------*/

int _stdcall importtxtfile (tlist lengtharray/* The field length list of imported text * * *,

String *fieldarray/* a segment of the database table array/, const char tablename/* imported target table/, const char filename/* import Source Text File * * * *

{

Generate Sql*loader Control files

FILE *fout, *FP;

Char execommand[256];



Char sqlload[] = ". \ qlload.ctl";



Check to see if the database is connected

if (bconnect = = false) return-2;



if ((Fout=fopen (Sqlload, "w")) = = NULL)

{

Error establishing control File

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 Execution Error

return-1;

}



return 0;

}





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

Third, compiling

Using Oracle's proc pre-compiler PostScript, put into C + + Builder in the binder. The sqlora9.lib should be added to the previous generation. Also note that all Oracle internal function calls generated by Proc are described as extern "C" _declspec (dllexport) type _stdcall types.



The level is limited also please forgive!!! Please give me a lot of advice. qq:5005647