Skip to Content.
Sympa Menu

freetds - Re: [freetds] A new issue with bcp/freebcp

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Frediano Ziglio <freddy77 AT gmail.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] A new issue with bcp/freebcp
  • Date: Sat, 11 Jun 2011 11:21:09 +0200

Il giorno gio, 09/06/2011 alle 15.09 -0400, Raymond R Rankins ha
scritto:
> I think I may have found another issue with freebcp related to field
> length.
> When running a BCP out with a format file, if the format file does not
> specify a field terminator, than all fields should be padded with spaces to
> the field width specified in the format file (including variable length
> character fields).
> This is how you can create a fixed width export file and is also how
> Sybase's bcp utility works.
>
> However, freebcp in version 0.91RC2 is not padding the variable length
> character fields with spaces, so it's just packing all the fields together
> and the row and field widths are variable rather than fixed.
>
> So, in this case, for a BCP export, the field width specified in the format
> file should take precedence over the column or data width.
>
> So, I think there needs to be a code change when executing a BCP out with a
> format file to set the min (and max) output width to the width specified in
> the format file and pad the data with spaces if it's less than the
> specified field length.
> Anybody know specifically what section of code writes the data to the host
> file?
>

Look for fwrite in src/dblib/bcp.c or src/tds/bulk.c

freddy77

> -Ray
>
> ===============================================
> Ray Rankins
> rrr03 AT health.state.ny.us
> (518) 474-1094
>
>
>
>
> From: "Thompson, William" <bill.d.thompson AT baml.com>
> To: "'FreeTDS Development Group'" <freetds AT lists.ibiblio.org>
> Date: 06/08/2011 08:53 AM
> Subject: Re: [freetds] A new issue with bcp/freebcp
> Sent by: freetds-bounces AT lists.ibiblio.org
>
>
>
> Hi Freddy,
>
> > The field length is taken from database information or from format
> > file ?? It we truncate to database length we will just silently
> > truncate data. I remember I saw a truncation warning/error using bcp
> > so I think truncating always is not correct.
>
> The field length referred to in my snippet from the manual means the
> maximum field length specified in the format file.
> We most definitely shouldn't silently truncate to the database field
> length.
> We should (and do) raise an error if we attempt to load say 6 characters
> into a four character field.
> The point is here that the maximum field length specified in the format
> file is saying:
> "I only want you to consider the first n characters of the data in the data
> file"
>
> > In your example data was "123456 ", perhaps dblib just trim spaces so
> > it does not give error. I don't know relationship between format file
> > and bcp.
>
> That's a subtly different and independent piece of functionality, which
> should be retained.
> The data in the file might have been "12345678"...
> In any case in our code, we call dbconvert (which attempts to convert 8
> characters to 6 and hence fails), before we reach the block which trims off
> the trailing spaces...
>
> Bill
>
> -----Original Message-----
> From: freetds-bounces AT lists.ibiblio.org [
> mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Frediano Ziglio
> Sent: 08 June 2011 13:21
> To: FreeTDS Development Group
> Subject: Re: [freetds] A new issue with bcp/freebcp
>
>
> 2011/5/31 Thompson, William <bill.d.thompson AT baml.com>:
> > Hi All,
> >
> > I've had a new issue reported to me on freebcp.
> > This has come from someone migrating from Sybase to SQL Server, using
> freetds as their "enabler" - an increasing trend in the use of freetds, I'm
> told.
> > So we have some solid evidence - "This works using Sybase bcp, but it
> doesn't work with freebcp" - and although it's an edge case, I think it
> should be fixed.
> >
> > So the problem occurs with freebcp using a format file. It evidences
> itself as a dbconvert() overflow error...
> >
> > The problematic column is CHAR(6) on the database
> > The line from the format file for the column looks like this:
> >
> > 22 SYBCHAR 0 6 "~" 22 cost_center
> >
> > and a snippet from the problematic line from the data file, showing the
> problematic host file data looks like this:
> >
> > ...~123456 ~...
> >
> > So in the format file, we've given a host file data length as 6 (the
> fourth token in the format file), but we've also said the data is
> terminated (by a "~")
> > However, there's a couple of extra spaces on the end of that data in the
> data file, giving it a total length of 8 characters.
> >
> > Well I took a look at this, and the Sybase manual pages for format files
> tells me the following about the host file data length:
> >
> >
> > "Host file data length refers to the maximum number of bytes to copy for
> the field. To decide how much data to copy in or out, bcp uses one of:
> >
> > - The maximum field length
> > - The prefix length, if any
> > - The field terminator string, if any
> >
> > If more than one method of field length specification is given, bcp
> chooses the one that copies the least amount of data."
>
> The field length is taken from database information or from format
> file ?? It we truncate to database length we will just silently
> truncate data. I remember I saw a truncation warning/error using bcp
> so I think truncating always is not correct.
>
> In your example data was "123456 ", perhaps dblib just trim spaces so
> it does not give error. I don't know relationship between format file
> and bcp.
>
> > This caused me to delve into _bcp_read_hostfile() (in src/dblib/bcp.c).
> To be honest, it shows the signs of having "evolved" to deal with the
> myriad of possibilities available to the innocent or pragmatic user. I'm
> probably to blame for much of that ad-hoc evolution, and here's another
> mutation to add to the mix. It works, and doesn't make things significantly
> worse than they they currently are:
> > Add the following lines to the function after approximately line 1421 of
> bcp.c (some context given):
> >
> > /*
> > * FIXME bcpcol->bcp_column_data->data && bcpcol->
> column_size ??
> > * It seems a buffer overflow waiting...
> > */
> >
> > + /* If column length specified (in a format file), then it
> *might* */
> > + /* be less than the data read from file (if the column was
> also */
> > + /* described as a terminated field...) in this case we
> need to only */
> > + /* take the number of characters specified in the column
> length */
> > +
> > + if (hostcol->column_len > 0) {
> > + collen = (hostcol->column_len < collen) ? hostcol->
> column_len : collen;
> > + }
> >
> > bcpcol->bcp_column_data->datalen =
> > dbconvert(dbproc, hostcol->datatype, (const BYTE *)
> coldata, collen, desttype,
> > bcpcol->bcp_column_data->data, bcpcol->
> column_size);
> >
> > if (bcpcol->bcp_column_data->datalen == -1) {
> >
> >
> > In penance, I may re-work the function at some point soon. The code is
> going to keep me awake at nights otherwise...
> >
> > Bill
> >
>
> Frediano
> _______________________________________________
> 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.
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
>
> IMPORTANT NOTICE: This e-mail and any attachments may contain confidential
> or sensitive information which is, or may be, legally privileged or
> otherwise protected by law from further disclosure. It is intended only for
> the addressee. If you received this in error or from someone who was not
> authorized to send it to you, please do not distribute, copy or use it or
> any attachments. Please notify the sender immediately by reply e-mail and
> delete this from your system. Thank you for your cooperation.
>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds






Archive powered by MHonArc 2.6.24.

Top of Page