INF: How to Use DBBCMD to Enable BCP IN Through an ODS Gateway

• Microsoft SQL Server, version 6.5

SUMMARY

This article discusses the use of dbbcmd to enable the use of the Bulk Copy Program (BCP) to use BCP IN functionality through an Open Data Services application.

MORE INFORMATION

A BCP OUT operation through a gateway is already supported. The BCP OUT is simply a SELECT operation that the gateway supports. However, the BCP IN operation is a special operation, and therefore requires the use of dbbcmd. To do this, perform the following steps:

1. Use the following command to expose the dbbcmd function:

         extern "C" RETCODE SQLAPI dbbcmd(DBPROCESS *, CHAR *, ...);
   

2. When the client connects, check for and properly enable BCP with a call to BCP_SETL.

3. BCP operations are handled in the lang_exec event. The start of a BCP operation is signaled by "insert bulk". Check the srv_langptr() for this string. Consider the following example:

         char * strCmd = (char *)srv_langptr(psrvProc);
         if(0 == strncmp((char *)strCmd, "insert bulk", 11))
            .
            .
   
   

4. Submit the "insert bulk" command to the SQL Server with the dbcmd call.

5. The next lang_exec event from the client should be submitted with the dbbcmd() call, as in the following example:

         int iLen = srv_langlen(psrvProc);
   

                 if(iLen == -1L)
                  iLen = 0L;
   
         rc = dbbcmd(pdbProc, strCmd, iLen);
   
         .
         .
   
   

6. Process results normally. See the handle_results() function in the ODS

The pattern of "insert bulk" and a binary packet requiring alternating calls to dbcmd() and dbbcmd() can occur many times in single BCP session. The smaller the BCP batch size, the more often the combination will need to be processed.

NOTE: Remember that you are adding another layer of indirection, so you may not yield the same BCP performance statistics you might achieve by directly bulk copying the data into the SQL Server.