Re: freebcp bug when column name is 'group'

Ehm... fixed ;)

freddy77

2011/3/15 Thompson, Bill D (London) <[email protected]>:
> 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: [email protected]
> [mailto:[email protected]] On Behalf Of Ed Avis
> Sent: 14 March 2011 15:38
> To: [email protected]
> 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 <[email protected]>
>
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> 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.
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
>
_______________________________________________
FreeTDS mailing list
[email protected]
http://lists.ibiblio.org/mailman/listinfo/freetds