'Re: [freetds] setting connection options in freebcp'

[prev in list] [next in list] [prev in thread] [next in thread] 

List:       freetds
Subject:    Re: [freetds] setting connection options in freebcp
From:       Constantin Vasilyev <vasilyev () ncbi ! nlm ! nih ! gov>
Date:       2006-09-21 17:16:22
Message-ID: Pine.LNX.4.44.0609211000460.19911-200000 () widget1 ! ncbi ! nlm ! nih ! gov
[Download RAW message or body]

> 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@lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
>



["set_options.patch" (TEXT/PLAIN)]

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");



_______________________________________________
FreeTDS mailing list
FreeTDS@lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds


[prev in list] [next in list] [prev in thread] [next in thread] 
