[freetds] patch: setting connection options in freebcp

Constantin Vasilyev vasilyev at ncbi.nlm.nih.gov
Fri Oct 6 13:33:04 EDT 2006


Adding -O option to freebcp.

This option allows to set/unset connection options:

freebcp ... -O 'set ansi_nulls, quoted_identifier on'

as well as run arbitrary SQL in the same connection which will be used for
bcp'ing the data:

freebcp '#t' out file ... -O 'select 1 id into #t'


Name of a file containing SQL batch to be executed is also accepted:

freebcp ... -O ~/my/file.sql


Constantin Vasilyev
NCBI Contractor, DBA


-------------- next part --------------
Index: freebcp.c
===================================================================
RCS file: /cvsroot/freetds/freetds/src/apps/freebcp.c,v
retrieving revision 1.46
diff -u -r1.46 freebcp.c
--- freebcp.c	4 Oct 2006 23:49:34 -0000	1.46
+++ freebcp.c	6 Oct 2006 17:19:09 -0000
@@ -56,6 +56,7 @@
 int file_character(BCPPARAMDATA * pdata, DBPROCESS * dbproc, DBINT dir);
 int file_native(BCPPARAMDATA * pdata, DBPROCESS * dbproc, DBINT dir);
 int file_formatted(BCPPARAMDATA * pdata, DBPROCESS * dbproc, DBINT dir);
+int setoptions (DBPROCESS * dbproc, BCPPARAMDATA * params);
 
 int err_handler(DBPROCESS * dbproc, int severity, int dberr, int oserr, char *dberrstr, char *oserrstr);
 int msg_handler(DBPROCESS * dbproc, DBINT msgno, int msgstate, int severity, char *msgtext, char *srvname, char *procname,
@@ -84,17 +85,8 @@
 		exit(1);
 	}
 
-	if (dbfcmd(dbproc, "set textsize %d", params.textsize) == FAIL) {
-		printf("dbfcmd failed\n");
+	if (!setoptions(dbproc, &params)) 
 		return FALSE;
-	}
-
-	if (dbsqlexec(dbproc) == FAIL) {
-		printf("dbsqlexec failed\n");
-		return FALSE;
-	}
-
-	while (NO_MORE_RESULTS != dbresults(dbproc));
 
 	if (params.cflag) {	/* character format file */
 		ok = file_character(&params, dbproc, params.direction);
@@ -198,7 +190,7 @@
 	 * Get the rest of the arguments 
 	 */
 	optind = 4; /* start processing options after table, direction, & filename */
-	while ((ch = getopt(argc, argv, "m:f:e:F:L:b:t:r:U:P:I:S:h:T:A:ncEdvV")) != -1) {
+	while ((ch = getopt(argc, argv, "m:f:e:F:L:b:t:r:U:P:I:S:h:T:A:O:0:ncEdvV")) != -1) {
 		switch (ch) {
 		case 'v':
 		case 'V':
@@ -279,6 +271,10 @@
 		case 'h':
 			pdata->hint = strdup(optarg);
 			break;
+		case 'O':
+		case '0':
+			pdata->options = strdup(optarg);
+			break;
 		case 'T':
 			pdata->Tflag++;
 			pdata->textsize = atoi(optarg);
@@ -621,6 +617,51 @@
 	return TRUE;
 }
 
+
+int
+setoptions (DBPROCESS * dbproc, BCPPARAMDATA * params){
+	FILE *optFile;
+	char optBuf[256];
+
+	if (dbfcmd(dbproc, "set textsize %d ", params->textsize) == FAIL) {
+		printf("dbfcmd failed at file %s line %d\n", __FILE__, __LINE__);
+		return FALSE;
+	}
+
+	if (params->options) {
+		if ((optFile = fopen(params->options, "r")) == NULL) {
+			if (dbfcmd(dbproc, params->options) == FAIL) {
+				printf("dbfcmd failed at file %s line %d\n", __FILE__, __LINE__);
+				return FALSE;
+			}
+		} else {
+			while (fgets (optBuf, sizeof(optBuf), optFile) != NULL) {
+				if (dbfcmd(dbproc, optBuf) == FAIL) {
+					printf("dbfcmd failed at file %s line %d\n", __FILE__, __LINE__);
+					return FALSE;
+				}
+			}
+			if (!feof (optFile)) {
+        			printf ("error reading options file %s at file %s line %d\n", params->options, __FILE__, __LINE__);
+				return FALSE;
+			}
+			fclose(optFile);
+		}
+	
+	}
+	
+	if (dbsqlexec(dbproc) == FAIL) {
+		printf("dbsqlexec failed at file %s line %d\n", __FILE__, __LINE__);
+		return FALSE;
+	}
+	
+	while (NO_MORE_RESULTS != dbresults(dbproc)) {
+		while (dbnextrow(dbproc) != NO_MORE_ROWS);
+	}
+
+	return TRUE;
+}
+
 void
 pusage(void)
 {
@@ -629,7 +670,7 @@
 	fprintf(stderr, "        [-F firstrow] [-L lastrow] [-b batchsize]\n");
 	fprintf(stderr, "        [-n] [-c] [-t field_terminator] [-r row_terminator]\n");
 	fprintf(stderr, "        [-U username] [-P password] [-I interfaces_file] [-S server]\n");
-	fprintf(stderr, "        [-v] [-d] [-h \"hint [,...]\" \n");
+	fprintf(stderr, "        [-v] [-d] [-h \"hint [,...]\" [-O \"set connection_option on|off, ...]\"\n");
 	fprintf(stderr, "        [-A packet size] [-T text or image size] [-E]\n");
 	fprintf(stderr, "        \n");
 	fprintf(stderr, "example: freebcp testdb.dbo.inserttest in inserttest.txt -S mssql -U guest -P password -c\n");
Index: freebcp.h
===================================================================
RCS file: /cvsroot/freetds/freetds/src/apps/freebcp.h,v
retrieving revision 1.11
diff -u -r1.11 freebcp.h
--- freebcp.h	12 Jun 2006 19:45:59 -0000	1.11
+++ freebcp.h	6 Oct 2006 17:19:09 -0000
@@ -50,6 +50,7 @@
 	char *pass;
 	char *server;
 	char *hint;
+	char *options;
 	int packetsize;
 	int mflag;
 	int fflag;


More information about the FreeTDS mailing list