PRB: When to Use bcp_columns() and bcp_colfmt()

  - Microsoft SQL Server version 4.2 for OS/2

SYMPTOMS

Using the default data formats of the bcp_init() function does not work correctly while trying to bulk copy a file that exactly matches the following table column for column:

   dbfcmd (dbproc, "CREATE TABLE %s ", argv[1]);
   dbcmd  (dbproc, "(col1       char(11) NULL,");
   dbcmd  (dbproc, " col2 char(40) NULL,");

CAUSE

Columns that allow nulls are being used. According to page 219 of the "Microsoft SQL Server Programmer's Reference for C" version 1.11 manual, the default data formats for columns that allow nulls are as follows:

   If a given database column's date is variable length or can
   contain null values, the operating system file's data column is
   prefixed by a... 1 byte length value...

WORKAROUND

Because nulls are being used in the table definition, the 1-byte prefix length must be included in the data file, or the following code fragment, which uses nondefault (that is, no prefix) formatting, can be used instead:

   if ( ! (bcp_columns(dbproc,13) )  ) exit(0);
               //        file data pre data       term tab
               //dbproc, col# type len leng term  len  col#
   bcp_colfmt  (dbproc , 1  , 0 , 0 , 11 , "\0" , -1 , 1  );
   bcp_colfmt  (dbproc , 2  , 0 , 0 , 40 , "\0" , -1 , 2  );