asked on

Updating an ADODB recordset with a foreign language string in VB6

I am trying to update an old package to handle multiple languages. The package is in VB6. The general code flow reads the excel file and then updates an array of columns. I am having a problem when it comes to actually updating the database.

The code:

the variable fileVal contains a foreign language string (I have verified this at the point of the error) from Excel.

The actual code used to update the record set sets a value in the recordset field held in a column array representing the data in the database.:

If (m_dbColumns(p_colIdx).rsField.type = adVarWChar) Then
m_dbColumns(p_colIdx).rsField.Value = fileVal

Open in new window

The code in this method has a number of if clauses for various types of data.

Using this -- I get the correct data in the DB for English Strings and ????? for foreign character (except some eastern european countries where I loose the accents).

I use the same logic to process and update other non (adVarWChar) columns and those update correctly.

the rsField is of type AdVarWChar. The underlying column is nvarchar2.The character set for nvarchar columns in Oracel is UTF16.

Dana Seaman

Are you sure fileVal is OK as UTF16?

You can check using this Unicode aware MsgBox:

MsgBox fileVal

You can also try using this in place of fileVal to check Oracle:
m_dbColumns(p_colIdx).rsField.Value = "CHS: " & ChrW$(&H6B22) & ChrW$(&H8FCE)
It should store as CHS: ¿¿

'Purpose: Unicode aware MsgBox
'Overrides Vb6 MsgBox. HelpFile/Context not supported
Public Function MsgBox(Prompt As String, _
   Optional Buttons As VbMsgBoxStyle = vbOKOnly, _
   Optional Title As String) As VbMsgBoxResult

   Dim WshShell As Object
   Set WshShell = CreateObject("WScript.Shell")
   MsgBox = WshShell.Popup(Prompt, 0&, Title, Buttons)
   Set WshShell = Nothing
End Function

Open in new window

Dana Seaman

It should store as:

CHS.png

acplayer

ASKER

Yes.. I checked fileVal using the Unicode aware messagebox and it shows correctly.

I will try the other suggestion this morning.

Oracle is storing the data correctly in these fields using other methods.

The things I am not sure of: Does ADODB require that the character set be UTF16 for the entire database or is it OK if it is the character set for nchar type fields.

Dana Seaman

For Oracle try NCHAR datatype. Apparently you do not need to set the entire database to UTF16.

See "Implementing a Unicode Solution in the Database"
http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch6unicode.htm

Also see Oracle Database 10g Globalization Support Guide
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14225.pdf

Markus Fischer

I had the same problem 2 weeks ago (Access VBA instead of VB6, but that doesn't make much of a difference). In order to retrieve NVARCHAR2 data from a PL/SQL procedure, I adjusted ADO types to adVarWChar and all was well. But it didn't work on all machines, notably not on the one in the presentation room — typical "demo effect" — and I got the '????' instead. I hot-fixed the problem by reverting to adVarChar, and got Unicode to ANSI conversion, but I was unable to find the underlying cause.

Knowing MS, this could be related to something entirely different (installed fonts, activation of national language support somewhere, regional settings, &c). I'm saying that your syntax could be correct, but that something different is missing.

I'm sorry I don't have the solution.

(°v°)

acplayer

ASKER

I just tried it again.

If I check the value in the rsfield right after the assignment it is correct when using fileVal on the RHS of the assignment.

If I check the value in the rsfield right after the assignement it is wrong (shows as CHS: folowed by two little empty boxes.

I do know from the part of the system that is in Java that I already have working that for the UPDATE statement that needs to go across the wire to Oracle the UNICODE strings must be in the form:

..... SET LASTNAME = UNISTR('\0441\043e')....

So, I am now wondering if the ADODB code that generates the SQL statement during update is correctly generating this string.

fyi... the client workstation is communicating with Oracle using Oracle's ORAOLEDB drivers.

I am waiting for my Oracle server guy to come in so I can hopefully see exactly what SQL is being generated.

acplayer

ASKER

Comment for Harfang: Is it possible that your demo machine was configured using MSDORA? I am quite sure that this will not work with MSDORA as the driver and that you need ORAOLEDB to access Oracle using unicode.

Dana Seaman

What OS are you using?
You may not have support for Asian and RightToLeft languages installed;
On XP and Vista you need to specifically install this via control panel.
I think Windows 7 installs this support automatically (i.e. they are working here).

See this link:
http://www.cyberactivex.com/UnicodeTutorialVb.htm#Wheres_the_Beef_%28Unicode%29

noUnicode.jpg

acplayer

ASKER

The UNICODE aware box correctly shows the string in chinese.with asian language support turned on

SQL developer still shows the string as CHS: ?? (well upside down ??).

More and more looks like ADODB is not generating the correct call to Oracle. Still waiting to see what is Oracle is seeing for the update.

Any other ideas?

Dana Seaman

I don't have Oracle installed so not sure how you would set "NCharBind=1". .
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.dwnet_2.0.dwbparm/html/dwbparm/BABBBABJ.htm

Markus Fischer

> using Oracle's ORAOLEDB drivers.

I'm using ODBC, so our problems are less likely to be related.
[Still monitoring... who knows?]

(°v°)

acplayer

ASKER

My Oracle server guy says that this parameter(NCHARBIND) is only for use in the PowerBuilder datawindow.

ASKER CERTIFIED SOLUTION

acplayer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dana Seaman

According to link provided at Post ID: 32960771 you should be able to do this on a column basis.