FreeTDS Development Group

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

> perhaps -O for "option" but since that's easily confused with zero

I like that. Small letter (-o) may be used to avoid confusions.

As for myself, there could be 3 convenient ways of doing that. First two
will only set connection options, whereas 3-rd (more generic) - allows to
run arbitrary SQL before actually bcp'ing the data:

1. (simplest one) - pass comma-separated list of options to be set. Like
   -o "ANSI_NULLS[ ,QUOTED_IDENTIFIERS, ...]"

2. use value of @@option (bitmap showing which options are set).
   In MS SQL it is an integer value:
   [101] MSSQL0.master.1> select @@options;
   5432|

   Table option->bit (for MS SQL) may be found here:
   http://msdn2.microsoft.com/en-us/library/ms176031.aspx


3. Pass name of SQL script to be executed:
   freebcp .....  -o path/to/file.sql


Actually, I already implemented #1. See patch file (against 0.64)
attached.

Constantin Vasilyev
NCBI Contractor, DBA

On Wed, 20 Sep 2006, Lowden, James K wrote:

> > From: Constantin Vasilyev
> > Sent: Wednesday, September 20, 2006 3:31 PM
> >
> >  > I wonder how Microsoft and Sybase deal with that in
> >  > their utilities?
>
> > For MS SQL bcp the problem is not solved. The only connection option
> > it allows to control is QUOTED_IDENTIFIER (-q parameter). It also
> > sets some options to ON by default, but not ARITHABORT, which makes
> > it impossible to bcp out of indexed view while taking indexes in
> > account. I'm sure freebcp can do better than native client here
> > (-;
>
> Nice follow-up, thanks.  I wouldn't have constructed an example like
> that in a million years (and I don't expect to live that long).
>
> I was able to reproduce your example using Microsoft's ISQL.EXE and
> BCP.EXE, see below.  The only change was that the objects are owned by
> my account, not dbo.
>
> This would mean a significant change.  freebcp would have to accept an
> option (perhaps -O for "option" but since that's easily confused with
> zero, perhaps -A for "ANSI", or maybe we accept -0 [zero] too).  These
> options would be applied to the connection in a single batch.  Then the
> query -- provided by the user with queryout or implied by the object
> name -- would be issued as a second batch.
>
> Parsing (even constructing) such a -O option string is not for the faint
> of heart.  Unix has a few ugly examples of comma-delimited-no-whitespace
> options; passing linker options to gcc comes to mind.  Maybe -O should
> just take SQL text, to be applied (in its own batch) before the
> extraction query.  Multiple -O strings would be applied as separate
> batches, in the order they appear on the command line.
>
> Another alternative would be to accept a batch delimiter in the query
> text.  A logical choice would be the semicolon.  Then your command would
> look like this:
>
> $ QUERY='SET ANSI_NULLS, \
>       QUOTED_IDENTIFIER, \
>       CONCAT_NULL_YIELDS_NULL, \
>       ANSI_WARNINGS, \
>       ANSI_PADDING, \
>       ARITHABORT ON; \
>       select * from testdb..bar with(noexpand)
>
> $ freebcp "${QUERY}" queryout filename [...]
>
> Of course, a delimiter means we'd need an escape mechanism too....
>
> Anyway, it's something to think about.
>
> --jkl
>
> [== logs ==]
> ISQL:
>
> 1> SET ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL,
> 2> ANSI_WARNINGS, ANSI_PADDING, ARITHABORT ON
> 3> select * from testdb..bar with(noexpand)
> 4> go
> Msg 1934, Level 16, State 1, Server AC2KAMA0848, Line 3
> SELECT failed because the following SET options have incorrect settings:
> 'ANSI_NULLS., CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS,
> ANSI_PADDING, ARITHABORT'.
> 1> exit
>
> BCP:
>
> $ bcp "select * from testdb..bar with(noexpand)" queryout t -T -S
> mpquant -c
> SQLState = 37000, NativeError = 1934
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]SELECT failed
> because the following SET options have incorrect settings: 'QUO
> TED_IDENTIFIER, ARITHABORT'.
> SQLState = 37000, NativeError = 8180
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s)
> could not be prepared.
>
> $ bcp "SET ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL,
> ANSI_WARNINGS, ANSI_PADDING, ARITHABORT ON select * from testdb..
> bar with(noexpand)" queryout t -T -S asdf  -c
> SQLState = 37000, NativeError = 1934
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]SELECT failed
> because the following SET options have incorrect settings: 'ARI
> THABORT'.
> SQLState = 37000, NativeError = 8180
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s)
> could not be prepared.
>
> I almost beat the system, though:
>
> $ bcp "SET ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL,
> ANSI_WARNINGS, ANSI_PADDING, ARITHABORT ON exec ('select * from
> testdb..bar with(noexpand)')" queryout t -T -S asdf  -c
> SQLState = 37000, NativeError = 1934
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]SELECT failed
> because the following SET options have incorrect settings: 'ARITHABORT'.
> [==  end  ==]
>
> -----------------------------------------
> The information contained in this transmission may be privileged and
> confidential and is intended only for the use of the person(s) named
> above. If you are not the intended recipient, or an employee or agent 
> responsible
> for delivering this message to the intended recipient, any review, 
> dissemination,
> distribution or duplication of this communication is strictly prohibited. 
> If you are
> not the intended recipient, please contact the sender immediately by reply 
> e-mail
> and destroy all copies of the original message. Please note that we do not 
> accept
> account orders and/or instructions by e-mail, and therefore will not be 
> responsible
> for carrying out such orders and/or instructions.  If you, as the intended 
> recipient
> of this message, the purpose of which is to inform and update our clients, 
> prospects
> and consultants of developments relating to our services and products, 
> would not
> like to receive further e-mail correspondence from the sender, please 
> "reply" to the
> sender indicating your wishes.  In the U.S.: 1345 Avenue of the Americas, 
> New York,
> NY 10105.
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
>


Index: freebcp.h
===================================================================
RCS file: 
/src/NCBI/vault.ncbi/individual/vasilyev/freetds-0.64/src/apps/freebcp.h,v
retrieving revision 1.1
diff -u -r1.1 freebcp.h
--- freebcp.h   27 Jul 2006 20:50:07 -0000      1.1
+++ freebcp.h   21 Sep 2006 15:03:29 -0000
@@ -48,6 +48,7 @@
        char *pass;
        char *server;
        char *hint;
+       char *options;
        int packetsize;
        int mflag;
        int fflag;
Index: freebcp.c
===================================================================
RCS file: 
/src/NCBI/vault.ncbi/individual/vasilyev/freetds-0.64/src/apps/freebcp.c,v
retrieving revision 1.1
diff -u -r1.1 freebcp.c
--- freebcp.c   27 Jul 2006 20:50:07 -0000      1.1
+++ freebcp.c   21 Sep 2006 15:03:29 -0000
@@ -94,6 +94,18 @@
                return FALSE;
        }
 
+       if (params.options) {
+               if (dbfcmd(dbproc, "SET %s ON", params.options) == FAIL) {
+                        printf("dbfcmd failed\n");
+                        return FALSE;
+                }
+        }
+  
+        if (dbsqlexec(dbproc) == FAIL) {
+                printf("SET OPTIONS command failed. Check value of -o 
parameter\n");
+                return FALSE;
+        }
+
        while (NO_MORE_RESULTS != dbresults(dbproc));
 
        if (params.cflag) {     /* character format file */
@@ -200,7 +212,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:ncEdvV")) != -1) {
                switch (ch) {
                case 'v':
                case 'V':
@@ -272,6 +284,9 @@
                case 'h':
                        pdata->hint = strdup(optarg);
                        break;
+                case 'o':
+                        pdata->options = strdup(optarg);
+                        break;
                case 'T':
                        pdata->Tflag++;
                        pdata->textsize = atoi(optarg);
@@ -620,7 +635,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 
\"connection_option, [,...]\"\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");