Geek Answers Handbook

Using C #, is it possible to get System.Data.OleDb to handle special characters?

I have an existing C # project that reads a text file and uploads it to Oracle DB using System.Data.OleDb as a provider. The problem is that if the file has upper ascii characters (for example, ÀÑÓ, non-breaking space), it always generates an error when trying to load it into Oracle:

Error: OLEDBConnection command. Parameter value cannot be converted for reasons other than character mismatch or data overflow.

Our Oracle can accept upper ascii characters (inserting through SQL * PLUS works fine), this is a problem with System.Data.OleDb.

Does anyone know if there is a way to change this? I can’t believe it only takes AZ-0-9. I looked through the documentation, but found nothing.

If this is not possible, how can you tell OLEDB to escape the character. Tired of putting \ in the file before special characters, but still errors with the same message.

+4
source share
3 answers

You may need to use HEX conversions. For example,% c4 Ä.

Try this and let me know if that works. Here are the conversion functions you can use.

internal String convertAsciiTextToHex(String asciiText) { StringBuilder sBuffer = new StringBuilder(); for (int i = 0; i < asciiText.Length; i++) { sBuffer.Append(Convert.ToInt32(asciiText[i]).ToString("x")); } return sBuffer.ToString().ToUpper(); } internal String convertHexToAsciiText(String hexString) { StringBuilder sb = new StringBuilder(); for (int i = 0; i < hexString.Length; i += 2) { string hs = hexString.Substring(i, 2); sb.Append(Convert.ToChar(Convert.ToUInt32(hs, 16))); } String ascii = sb.ToString(); return ascii; }
+2
source

This is usually possible without any problems ... since you do not provide much detail, only some common pointers:

  • Database cipher must be set to AL32UTF8
    you can verify this by doing SELECT parameter, value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';
  • Client encoding must be set to AL32UTF8
    see the settings of this registry for NLS_LANG
  • Columns must be VARCHAR2
  • The connection string must include OLEDB.NET=True
    for more help see http://download.oracle.com/docs/cd/B28359_01/win.111/b28431/using.htm#i1017221

Other points to check may be OS versions and clients and OLEDB ... some have bugs or strange behavior ...

+3
source

Is your data type and parameter type nvarchar2?

+1
source

More articles:


All Articles