FreeTDS Development Group

["James K. Lowden" <jklowden AT freetds.org>]

Cedric ROUVRAIS wrote:
> 
> If ansi warnings are set to off then the result of a bcp in should be a
> truncation when the data is to large for the destination column.  Sybase
> bcp works like this for sure, so I added the feature in freebcp.c

Microsoft's bcp.exe refuses to load a row with an overlong value
regardless of the value of ANSI warnings.  Either way, you get this
message:

  $ bcp testdb..a in a.txt -S mpquant -c -T

  Starting copy...
  SQLState = 22001, NativeError = 0
  Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation

  0 rows copied.

IMO this is the correct behavior, and I see no particular advantage to
Sybase's choice.  Failure is *better*: with it, the user can edit the data
file or alter the table, and consistently finds errors in the -e error
file.  The change you propose forces the user to look (sometimes,
depending on the database option) in the output log too, and the message
doesn't bother to indicate which column is at fault.  

I'm not sure full compatibility with Sybase is all that desirable for
freebcp.  For example, rather than truncating your data, wouldn't it be
better if the user could specify a "rejected row" file?  freebcp could
separate its input into to two piles: rows sent to the database, and rows
not sent, for one reason or another.  The user could peruse the .err file
(-e output) for problems, and modify & re-process the .rej file, knowing
anything not in the .rej is already in the table. 

Another weakness of both the vendor and FreeTDS bcp utilities is that the
don't always return an error to the OS if any row was not loaded.  That
makes it useless for batch operations.  :-/

--jkl