Recently, in a project, in order to solve the database IO bottleneck, you have to export data in the database into text files. The text is passed to the client and imports to the database. I use C Builder to embed a import-exported DLL with C Builder. If you are useful for you! The details are as follows:
First, ready to work
Computer environment: Win 2000 Pro, Oracle 9i, C Builder 5.5
Introducing the necessary Oracle internal functions: The function you want is in the $ (ORACEL_HOME) /bin/sqlora9.dll link library. In order to be used in C Builder, first you build lib: IMPLIB SQLORA9.LIB SQLORA9.DLL
Second, the source file analysis
/ / -------------------------------------------------------------------------------------------- -------------------------
// Add the necessary header file
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
/ / Describe the output function of the 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 the maximum number of fields
#define max_vname_len 30 // Define the maximum length of the selection entry
#define max_INAME_LEN 30 // Defines the maximum length of the indicator variable name
EXEC SQL include Sqlca; // Description SQL communication area
EXEC SQL include ORACA; / / Description Oracle Communication Area
EXEC SQL include Sqlda; // Description SQL statement description structure / * SQLDA structure please check the relevant information * /
EXEC ORACLE OPTION (ORACA = YES);
EXEC ORACLE OPTION (Release_Cursor = YES);
// Describe the Oracle external function
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; // Definition selection description
Sqlda * bindunit; / / Define Enter Int Space
/ / Define the variable to store 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 to join the sign
#pragma HDRSTOP
#pragma argsused
// C Builder DLL main function
Bool WinApi Dllmain (Hinstance Hinstdll, DWord Fwdreason, LPVOID LPVRESERVED)
{
Return 1;
}
/ * ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------
Connect to the 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: pwd using: db;
IF (Sqlca.sqlcode <0)
Return -1;
Bconnect = true;
Return 0;
}
/ * ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------
Export text function
Since the table and field of the SELECT statement is not determined, I use the dynamic statement (Oracle Dynamic SQL) // fourth way. Dynamic SQL Method 4 is a complex program design technology that does not determine the selection item and input items of the SQL statement, and unknown with a number of complex programming techniques used in the case of data type.
-------------------------------------------------- ------------------------- * /
INT _STDCALL EXPORTTXTFILE (const char * sql / * sql selection statement * /, const char filename / * export target text file name * /)
{
INT NULL_OK, Precision, Scale;
INT HANDLE;
IF ((",",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
{
// File open error
Return -1;
}
/ / Define the variable to store SQL statements
EXEC SQL Begin Declare Section;
Char sqlstr [256];
Exec SQL End Declare Section;
/ / Check if you connect the database
IF (bconnect == false) return -2;
STRCPY (SQLSTR / *. Arr * /, SQL);
// SQLSTR.LEN = Strlen (SQL); // Assign space to the description area
IF ((SelectUnit = SQLALD (MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN) == (SQLDA *) NULL)
{
/ / Spatial distribution failed
Return -3;
}
IF (BindUnit = SQLALD (MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN) == (SQLDA *) NULL)
{
/ / Spatial distribution failed
Return -3;
}
// Give the query return value storage area allocation space
SelectUnit-> n = max_items;
For (int i = 0; i { BindUnit-> i [i] = (short *) malloc (sizeof (short *)); Bindunit-> V [I] = (char *) malloc (max_vname_len); } For (int i = 0; i { SelectUnit-> i [i] = (short *) Malloc (SIZEOF (Short *)); SelectUnit-> v [I] = (char *) malloc (max_vname_len); } EXEC SQL WHENEVER SQLEC GOTO SQLERR; // DO SQL_ERROR ("Export Error"); / / Set SQL statement 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; // Input items too much } BindUnit-> n = bindunit-> f; // Open the cursor Exec SQL Open CursorBase Using Descriptor Bindunit; // Select item processing Exec SQL Describe Select List for Sqlsa Into SelectUnit; IF (selectUnit-> f <0) { Return-4; // Select too much item } SelectUnit-> n = selectUnit-> f; // Because all formats, types are uncertain, so you have to get the correct return value. For (int i = 0; i { SQLNUL (& (SelectUnit-> T [I]), & (SelectUnit-> T [I]), & null_ok); Switch (SelectUnit-> T [I]) { Case 1: // charr 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; // Convert all types to characteristics } EXEC SQL WHENEVER NOT FOUND GOTO ENDFOR; For (;;) { EXEC SQL FETCH CURSORBASE Using Descriptor SelectUnit; / / Output of each field For (int i = 0; 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 { IF (selectUnit-> v [i]! = (char *) NULL) Free (SelectUnit-> V [I]); Free (SelectUnit-> i [i]); } For (int J = 0; 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 mass import, here I call SQLDR tools First become a SQL * LoAder control file, then run SQLLDR -------------------------------------------------- -------------------------- / INT _STDCALL IMPORTTXTFILE (TLIST Length "imported field length chain table * /, String * FieldArray / * Database table's Segment name * /, const char Tablename / * Import Target Table * /, Const Char FileName / * Import Source text file * /) { // Generate SQL * Loader Control File File * fout, * fp; Char Execommand [256]; Char Sqlload [] = ".//sqlload.ctl"// Check if the database is connected IF (bconnect == false) return -2; IF ((Fout = FOPEN)) == null) { // Establish a control file error 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 { FPRINTF (Fout, "% 11S Position (% D:% D)", FieldArray [i], iStart, * (int *) LengthTharray-> items [i] iStart-1); iStart = * (int *) LengthTharray-> items [i]; FPRINTF (Fout, "char"); IF (i FPRINTF (fout, ", / n"); } FPRINTF (Fout, ") / N"); Fclose (fout); Sprintf (execommand, "sqlldr.exe userid =% s /% s @% s constroL =% s", User, PWD, DB, SQLLOAD; IF (System (Execommand) == -1) { // SQL * Loader Execute Error Return -1; } Return 0; } / / -------------------------------------------------------------------------------------------- ---------------------------- Third, compile After pre-edoing the Oracle's Proc Precipcherator, add C Builder. The coupled sqlora9.lib is required to join the previously generated. Pay attention to the connection, all the Oracle internal function calls generated by all PROCs must be described as the extern "c" _declspec (dllexport) Type_stdcall type. I will forgive me! ! ! Please have a point. QQ: 5005647
