0

I'm inserting special characters in Sybase 15.7 (e.g., ñ) using BCP and specifying the cp437 character set. The insert goes fine, and the special character is visible as expected via isql on my linux box (where the bcp was initiated from) - and when I bcp out the data, everything looks fine in the output file as well. However, the character appears as garbage virtually everywhere else, including from native isql on the PC, various other isql clients, and any type of web presentation. Here's what my bcp in command looks like:

bcp mydb..mytable in myfile.dat -U USER -S myserver -Y -c -J cp437 -t "|" -A 16384 -P PWD -e err_$1.dat -b 5000 > out_$1.dat

Any thoughts/suggestions appreciated!

Tiggyboo
  • 522
  • 6
  • 14
  • It sounds like the issue isn't with the (Sybase) database but with the various client-side applications. In your linux environment, where the `isql` session correctly displays the character, are you also calling `isql` with `-J cp437`? if not I'm guessing your linux locale is configured to support `cp437`. As for the other clients that are unable to display the character, do they have the ability to set a character set? can you change the client's (likely OS level) locale? setting the locale/character set is going to be OS dependent so recommend (your favorite) web search ... "Duh, Mark!" ? – markp-fuso Nov 20 '22 at 20:16
  • Yes, in all cases I was able to specify -J cp437, and linux was the only isql client that displayed the character correctly. Note that Sybase isql on a PC, specifying -J cp437, was also unable to display the character correctly. Thanks for your response, but still scratching my head on this one! – Tiggyboo Nov 21 '22 at 14:24
  • It turns out that the ñ character, for instance, starts out as ascii 164 in the bcp file but arrives as 195 in the database. No idea how linux isql is about to read this back as 164 somehow, but there's no question it's 195 in the table. Very strange. – Tiggyboo Nov 22 '22 at 16:34
  • what you describe is valid if the dataserver's default character set is something other than `cp437`, ie, the dataserver's default behavior is to perform character set conversions; client (bcp) says it has `cp437` data, dataserver converts the data to its default character set (eg, `utf8`), this of course assumes the dataserver's character set is a superset of the client's character set otherwise you can end up with garbage being stored in the database (if unable to convert a character to dataserver's character set) ... – markp-fuso Nov 22 '22 at 16:50
  • the dataserver's default character set (and sort order) can be found via `sp_helpsort`; while it's possible to bypass the dataserver's autoconversion of the character set this can get real messy real fast as the dataserver will think the raw data in the table is `utf8` when it reality it is `cp437`-formatted data; and, yeah, it's gets a bit more messy if you've got multiple clients using different character sets and using a mix of enabled/disabled dataserver autoconversions – markp-fuso Nov 22 '22 at 16:54

0 Answers0