FreeTDS Development Group

["Thompson, Bill D (London)" <bill.d.thompson AT baml.com>]

Hi Ed,

It shouldn't be too difficult for you to sort out.
The bcp interface starts off by issuing a "real" SQL command:

"INSERT BULK tablename (column type, column type, column type, ...)"

which I suspect is where the problem occurs with the reserved word being
used for a column name.

AFAICT, we do make an attempt to quote the column names (with []), but
only in cases where non standard characters are used in the column name
(anything outside a-z, A-Z, 0-9, and _)

If you look at src/dblib/bcp.c, in function
_bcp_build_bulk_insert_stmt(), you'll notice that a function called
tds_quote_id is called a couple of times.
This function is doing the business of "quoting" the column names in the
cases described.

I can't really see a reason not to unconditionally quote the column
names in this function, so you could just bypass the calls to
tds_quote_id appropriately and put the quotes around the column names
yourself.


HTH,

Bill




-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Ed Avis
Sent: 14 March 2011 15:38
To: freetds AT lists.ibiblio.org
Subject: [freetds] freebcp bug when column name is 'group'


I use freebcp with MS SQL server 2000.  It works well once you set
TDSVER=8.0.
But MSSQL lets you create tables where the column name is an SQL
reserved word
provided you quote the column name with [], for example

create table test ([group] int not null)

Interestingly if you double-quote it as "group" then you still get a
syntax
error.  I don't know whether Sybase allows this [] style of quoting.
Anyway, I
can bcp out from this table:

% freebcp mydb..test out test -c -S MY_SERVER -U my_user -P my_pass

but when I try to run the same using 'in' instead of 'out', I get

Starting copy...
Msg 156, Level 15, State 1
Server 'WCL-SQL-PROD', Line 1
        Incorrect syntax near the keyword 'group'.
Msg 156, Level 15
General SQL Server error: Check messages from the SQL Server

bcp copy in failed

I wonder whether freebcp could quote the column name somehow to avoid
this bug.
It's odd, because the bcp interface looks like a real API and isn't
based on
gluing together strings of SQL.  But someone who knows more can comment.

If failure on a column name of 'group' is unavoidable, perhaps the 'bcp
out'
command could give a warning that loading the file back in may not work.

-- 
Ed Avis <eda AT waniasset.com>

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

----------------------------------------------------------------------
This message w/attachments (message) is intended solely for the use of the 
intended recipient(s) and may contain information that is privileged, 
confidential or proprietary. If you are not an intended recipient, please 
notify the sender, and then please delete and destroy all copies and 
attachments, and be advised that any review or dissemination of, or the 
taking of any action in reliance on, the information contained in or attached 
to this message is prohibited. 
Unless specifically indicated, this message is not an offer to sell or a 
solicitation of any investment products or other financial product or 
service, an official confirmation of any transaction, or an official 
statement of Sender. Subject to applicable law, Sender may intercept, 
monitor, review and retain e-communications (EC) traveling through its 
networks/systems and may produce any such EC to regulators, law enforcement, 
in litigation and as required by law. 
The laws of the country of each sender/recipient may impact the handling of 
EC, and EC may be archived, supervised and produced in countries other than 
the country in which you are located. This message cannot be guaranteed to be 
secure or free of errors or viruses. 

References to "Sender" are references to any subsidiary of Bank of America 
Corporation. Securities and Insurance Products: * Are Not FDIC Insured * Are 
Not Bank Guaranteed * May Lose Value * Are Not a Bank Deposit * Are Not a 
Condition to Any Banking Service or Activity * Are Not Insured by Any Federal 
Government Agency. Attachments that are part of this EC may have additional 
important disclosures and disclaimers, which you should read. This message is 
subject to terms available at the following link: 
http://www.bankofamerica.com/emaildisclaimer. By messaging with Sender you 
consent to the foregoing.