FreeTDS Development Group

[Andrew Punch <andrew.punch AT smart-associates.biz>]

Hi,

I had a requirement to have properly formatted CSV output from freebcp. 
Unfortunately freebcp (and the original bcp) do not escape characters 
correctly. For example in a comma delimited file commas will not be 
escaped. Also the way NULLs are indicated can be inconvenient.

I hacked together a patch which I have attached. I would like to do a 
pull after a clean up but I have a few questions:

1. is dblib the best place to make the change?
2. what is the best structure to pass parameters and flags? Namely:
    1. Flag for delimiter escaping
    2. The actual delimiter (I notice that the column delimiter changes
       to EOL for the last column)
    3. Flag for NULL string replacement (instead of ASCII NUL)
    4. NULL string replacement

diff --git a/include/dblib.h b/include/dblib.h
index 786ef6a..b85fa5b 100644
--- a/include/dblib.h
+++ b/include/dblib.h
@@ -66,8 +66,11 @@ typedef struct
 	DBINT column_len;
 	BYTE *terminator;
 	int term_len;
+	BYTE *null_value;
+	int null_value_len;
 	int tab_colnum;
 	int column_error;
+	BOOL escape;
 } BCP_HOSTCOLINFO;
 
 typedef struct 
diff --git a/src/dblib/bcp.c b/src/dblib/bcp.c
index a0708fe..9efffc8 100644
--- a/src/dblib/bcp.c
+++ b/src/dblib/bcp.c
@@ -85,6 +85,7 @@ static int rtrim(char *, int);
 static STATUS _bcp_read_hostfile(DBPROCESS * dbproc, FILE * hostfile, int *row_error);
 static int _bcp_readfmt_colinfo(DBPROCESS * dbproc, char *buf, BCP_HOSTCOLINFO * ci);
 static int _bcp_get_term_var(BYTE * pdata, BYTE * term, int term_len);
+static int _bcp_escape(TDS_UCHAR **buf, int buflen, char *terminator, int term_len);
 
 /*
  * "If a host file is being used ... the default data formats are as follows:
@@ -460,6 +461,9 @@ bcp_colfmt(DBPROCESS * dbproc, int host_colnum, int host_type, int host_prefixle
 	hostcol->terminator = terminator;
 	hostcol->term_len = host_termlen;
 	hostcol->tab_colnum = table_colnum;
+	hostcol->null_value = "\\N";
+	hostcol->null_value_len = 2;
+	hostcol->escape = TRUE;
 
 	return SUCCEED;
 }
@@ -739,8 +743,11 @@ _bcp_convert_out(DBPROCESS * dbproc, TDSCOLUMN *curcol, BCP_HOSTCOLINFO *hostcol
 		 * to be confused with a database NULL, which is denoted in the output
 		 * file with an empty string!)
 		 */
-		(*p_data)[0] = 0;
-		buflen = 1;
+		if (!hostcol->null_value_len) { 
+		    (*p_data)[0] = 0;
+		    buflen = 1;
+    } else
+		  buflen = 0;
 	} else if (is_numeric_type(hostcol->datatype)) {
 		TDS_NUMERIC *num = (TDS_NUMERIC *) (*p_data);
 		if (is_numeric_type(srctype)) {
@@ -766,6 +773,7 @@ _bcp_convert_out(DBPROCESS * dbproc, TDSCOLUMN *curcol, BCP_HOSTCOLINFO *hostcol
 		if (buflen < 0)
 			return buflen;
 
+		buflen = _bcp_escape(&cr.c, buflen, hostcol->terminator, hostcol->term_len);
 		if (buflen >= 256) {
 			free(*p_data);
 			*p_data = (TDS_UCHAR *) cr.c;
@@ -938,7 +946,11 @@ _bcp_exec_out(DBPROCESS * dbproc, DBINT * rows_copied)
 
 			curcol = resinfo->columns[hostcol->tab_colnum - 1];
 
-			if (curcol->column_cur_size < 0) {
+			if (curcol->column_cur_size == -1) {
+				// NULL. Assume null_value_len < 256
+				buflen = hostcol->null_value_len;
+				memcpy(data, hostcol->null_value, hostcol->null_value_len);    
+			} else if (curcol->column_cur_size < 0) {
 				buflen = 0;
 			} else {
 				buflen = _bcp_convert_out(dbproc, curcol, hostcol, &data, bcpdatefmt);
@@ -957,7 +969,7 @@ _bcp_exec_out(DBPROCESS * dbproc, DBINT * rows_copied)
 				buflen = buflen > hostcol->column_len ? hostcol->column_len : buflen;
 			}
 
-			if (buflen > 0) {
+			if (buflen > 0) {			  
 				if (fwrite(data, buflen, 1, hostfile) != 1)
 					goto write_error;
 			}
@@ -2305,3 +2317,49 @@ _bcp_free_storage(DBPROCESS * dbproc)
 	}
 }
 
+
+static int _bcp_escape(TDS_UCHAR **buf, int inbuflen, char *terminator, int term_len)
+{
+    TDS_UCHAR *outbuf = NULL, *inbuf = *buf;
+    int in_index, out_index;
+    int outbuflen=inbuflen;
+    
+    outbuf = (TDS_UCHAR*)malloc(MAX(inbuflen*2, 256));
+    
+    for (in_index=0,out_index=0;in_index<inbuflen;in_index++) {
+        BYTE character = inbuf[in_index];
+        
+        /* \\ \n \r \" \<terminator> \0 */
+        switch(character){
+            case '\\':
+            case '\n':
+            case '\r':
+            case '\"':
+            case '\'':
+            case '\t':
+            case ',':
+            case '|':
+                outbuflen++;
+                outbuf[out_index++] = '\\';
+                outbuf[out_index++] = character;
+                break;
+                
+            default:
+                // Escape if first character of terminator matches
+                // With multi-character terminators it may escape unecessarily
+                // which will take more space but will still be safe
+                // TODO: need to escape field delimiter and record delimiter properly
+                if (term_len && character == terminator[0]) {
+                    outbuflen++;
+                    outbuf[out_index++] = '\\';
+                    outbuf[out_index++] = character;
+                }
+                else
+                    outbuf[out_index++] = character;
+        }
+          
+    }
+    free(inbuf);
+    *buf = outbuf;
+    return outbuflen;
+}