FreeTDS Development Group

[Constantin Vasilyev <vasilyev AT ncbi.nlm.nih.gov>]

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


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;