Design and implementation of Sybase bulk operations (BCP)

This paper mainly describes the design and implementation of Sybase database bulk operation (BCP), as well as the key points in the process of development, and provides a technical basis for the following developers.

In the project development process, need to develop a database batch operation dynamic link library (DLL), The previous implementation is mainly in the program directly call Bcp.exe, this way by the application creation subprocess, not control the bulk operation process, failure tracking is relatively difficult, so want to use bcp.exe called function to achieve the operation process. By analyzing the Bcp.exe program, I got the DB Library API function of batch operation, and then consulted the data of API function to realize the dynamic link library.

Second, realize

The bulk operation dynamic Link library implements only one output function, the application dynamically loads the DLL, obtains the function address, then can call the function to implement the bulk operation.

The output functions are defined as follows: Libbcp_api BOOL bcp_transfer_2 (const char *task, const char *step, const char *config, long *copiedrow);

Two classes are defined in the dynamic-link library: Cinteriorglobal and CSYBBCP. Cinteriorglobal completes the global initialization operation, CSYBBCP implements the batch operation of the database. When calling the DB LIBRARY API function for the Sybase database for database-related operations, you first need to call the Dbsetversion function to set the version information, which can only be invoked once, and an error will be made if you call again. While class CSYBBCP are dynamically created and released in the Bcp_transfer_2 function, calling dbsetversion directly in CSYBBCP can cause multiple calls to occur. Therefore, a mechanism is needed to allow dbsetversion to be invoked only once, where the singletom pattern in design mode is used, and the Singletom mode is to ensure that the instance is unique, and I use the class to initialize the Sybase client version information with only one instantiation operation.

The following is the definition of Cinteriorglobal:

Class Cinteriorglobal

{

Public

Static Cinteriorglobal *instance ();

Private

Cinteriorglobal ();

Private

Static Cinteriorglobal *_instance;

};

Cinteriorglobal implementation, setting version information in the constructor: Cinteriorglobal::cinteriorglobal ()

{

Dbsetversion (dbversion_100);

}

Cinteriorglobal *cinteriorglobal::_instance = 0;

Cinteriorglobal * Cinteriorglobal::instance ()

{

if (0 = _instance)

_instance = new Cinteriorglobal;

return _instance;

}

To complete the bulk operation, define the class csybbcp, which is defined as follows: Class CSYBBCP

{

Public

CSYBBCP ();

~CSYBBCP ();

BOOL doconnect (int taskindex, int stepindex, char *server, Char *database, Char *username,

Char *password, Char *charset, char *language);

BOOL Doquery (Char *sql, char **buf, int *rowcount, int *fieldcount);

BOOL doupdate (Char *sql, char *database = NULL);

BOOL bcp_connect (int taskindex, int stepindex, char *server, Char *database,

Char *username, Char *password, Char *charset, char *language);

BOOL bcp_transfer_db (Char *sql, Char *fldterminator, char *rowterminator, int direction,

Char *datafile, Char *errfile, long *copiedrow);

Private

BOOL m_isbcpout;

int m_stepindex;

int m_taskindex;

Char M_viewname[max_string_num];

Char M_database[max_string_num];

DBPROCESS *m_dbproc;

Private

int gettablefieldnums (char *table);

BOOL Dodisconnect ();

};

In class csybbcp, the main function bcp_transfer_db is to import and export the large amount data of the database, and to complete the data transfer operation, the following steps are needed://initialization: Specifying indication and data file

if (Bcp_init (M_dbproc, TableName, datafile, NULL, direction) = = FAIL)

{

return FALSE;

}

Set control parameters for bulk operations, set the number of records per batch

if (Bcp_control (M_dbproc, Bcpbatch, (dbint) 1000) = = FAIL)

{

return FALSE;

}

Set the number of columns

if (Bcp_columns (M_dbproc, ccols) = = FAIL)

{

return FALSE;

}

Set column style

for (ii = 1; II < ccols; ii++)
{
if (Bcp_colfmt (M_dbproc, II, Sybchar, 0,-1, (UINT8 *) Fldterminator, _strlen (Fldterminator), ii) = = FAIL)
{
return FALSE;
}
}
if (Bcp_colfmt (M_dbproc, II, Sybchar, 0,-1, (UINT8 *) Rowterminator, _strlen (Rowterminator), ii) = = FAIL)
{
return FALSE;
}
Perform bulk operations
while (Bcp_exec (M_dbproc, & cRows) = = FAIL)
{
return FALSE;
}

Bulk Operation End

Retcode=bcp_done (M_DBPROC);

Before using the Sybase12.5 client, the program did not call the Bcp_control function, while executing the BCP_EXEC function instead of using the while, but using the If judgment, the code was as follows:

if (Bcp_exec (M_dbproc, & cRows) = = FAIL)
{
return FALSE;
}

Program to complete the function, when the use of Sybase12.5 client, in the implementation of the discovery program suddenly quit, exception processing also failed to log, after tracking Discovery program is in the implementation of Bcp_exec exit, but failed to find out why, consulting Sybase company technicians, also failed to solve the problem. Later in a test accidentally found that sometimes can import data, so the test data file under what circumstances can be imported, experiment its critical point, after several tests found that the file 1000 records as a critical point, more than the problem. So I call the Bcp_control function in the program, set the batch record to 1000, if the data file records more than 1000, you need to bcp_exec execution several times to complete, so use while, instead of if, so the problem solved. Third, the end

In the above discussion, also only involves the DB Library, for Sybase client programming, as well as CT LIBRARY method, the current CT has supported the export, but does not support imports.