Skip to Content.
Sympa Menu

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

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Constantin Vasilyev <vasilyev AT ncbi.nlm.nih.gov>
  • To: "Lowden, James K" <james.k.lowden AT alliancebernstein.com>
  • Cc: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] setting connection options in freebcp
  • Date: Thu, 21 Sep 2006 13:16:22 -0400 (EDT)

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



Archive powered by MHonArc 2.6.24.

Top of Page