We help IT Professionals succeed at work.

Updating an ADODB recordset with a foreign language string in VB6

acplayer
acplayer asked
on
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.



Comment
Watch Question

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

It should store as:

CHS.png

Author

Commented:
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.
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 
 
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°)

Author

Commented:
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.

Author

Commented:
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.
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

Author

Commented:
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?

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


> using Oracle's ORAOLEDB drivers.

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

(°v°)

Author

Commented:
My Oracle server guy says that this parameter(NCHARBIND) is only for use in the PowerBuilder datawindow.
Commented:
I think I have figured it out.

I added the NDataType=True to the connection string which sets the property to treat the strings as NChar.Now, I am going to see if this can be done on a column basis to avoid too much overhead.. though in the present project they dont really care.

I am now wondering if this would help with not having to send these strings as: UNISTR('\0442\043e') type encoded strings from my Java app as well.
According to link provided at Post ID: 32960771 you should be able to do this on a column basis.