FreeTDS Development Group

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

ananthbv AT gmail.com wrote:
> 
> 
> Here is the hexdump of the freebcp out datafile. The '!#' you see is
> the field separator I have used.
> 
> $ hexdump -C tmpreq.txt
> 
> 00000000 31 31 39 31 35 35 37 21 23 80 34 34 2e 33 30 0a
> |1191557!#.44.30.|
...
> The table definition is:
> 
> CREATE TABLE dbo.tmpreq
> (
>   request_id       int           NOT NULL,
>   buy_price    varchar(20)   NULL
> )

[For those following along at home, ananthbv sent me the log off-list.]

Well, the logs are both for TDS 8.0:

> net.c:974:instance port is 10501
> net.c:210:Connecting to 10.60.166.133 port 10501 (TDS version 8.0)

but we can look at the upload to the Microsoft server.  Indeed, the file
misencoded by the lights of the program.  It may very well be that the
data are encoded as cp1252, but that's not how FreeTDS is interpreting it.
 At the top of the file we find:

> iconv.c:363:iconv to convert client-side data to the "ISO-8859-1"
> character set

That's the default.  I wouldn't change it just yet.  What does tsql report
about your environment when you first log in?  

When the line is read, we see this:

> bcp.c:1169:parsing host column 2
> bcp.c:1237:prefix_len = 0 collen = 0 
> bcp.c:1501:_bcp_measure_terminated_field(0x115c5940, 0x115c5920, 1)
> bcp.c:1268:_bcp_measure_terminated_field returned 6
> bcp.c:1277:Adjusted collen is 6.
> iconv.c:882:tds_iconv_fread: read 6 of 6 bytes; outbuf has 6 left.
> iconv.c:899:tds_iconv_fread: error 84: Invalid or incomplete multibyte
> or wide character.

As far as freebcp is concerned, *all* client side data are encoded as ISO
8859-1.  When it reads the file and finds that "Euro" sign as 0x80:

> $ hexdump -C tmpreq.txt
> 
> 00000000 31 31 39 31 35 35 37 21 23 80 34 34 2e 33 30 0a
                                      ^^ "euro!"

It fails because that value -- 0x80 -- is not in the ISO 8859-1 domain.  

When it tries to write the error data to the error file, it can't do that
either.  The log doesn't state why, but the error message should have
decoded the errno set by the stdio library.  Not the biggest problem, but
a little distracting:  

> dblib.c:7782:dbperror(0x11590380, 20088, 84)
> dblib.c:7835:20088: "I/O error while writing bcp error file"
> dblib.c:7856:"I/O error while writing bcp error file", client returns 2
> (INT_CANCEL)

That shouldn't happen unless you used the -e option invoking freebcp.  

In sum:

1.  We still need the Sybase extraction log to understand how the 0x80 got
there in the first place.  

2.  Likely is that the Sybase extraction also uses ISO 8859-1 for the
client encoding and the server contains mis-encoded data.   

3.  The data sent by Sybase and written to the file by freebcp are
supposed to be 8859-1, but in fact are probably cp1252.  

4.  When freebcp attempts to read the "8859-1" data to send them to the
Microsoft server, it finds bad data.  

To test if the file is indeed cp1252, you can use iconv(1) to attempt to
convert it to 8859-15 (yes, 15) or UTF-8.  If it doesn't complain, the
file is at least interpretable as cp1252.  In that case, the quickest hack
is to treat it as such: in the section of your freetds.conf that describes
your Microsoft server, add a "client charset" line setting it to cp1252. 
(The literal name varies by iconv implementation.)  Then upload. 
Everything should Just Work.  

If this is a read-once operation because you're retiring the Sybase
server, I don't see a problem with a quick hack.  If you want to keep
using the Sybase box, then it's important to get to the bottom of the
encoding issues on that side.  

HTH.  

--jkl