REQUEST A DEMO

Fun, fun, fun with ADO and Unicode characters

We recently ran into an issue with ADO rendering Unicode characters.

To be honest, we’re doing some pretty weird stuff inside the fcSDK. We’re performing database queries using ADO.NET, and then we take the DataRow and convert it into an ADO Recordset, which we return to our COM based applications. We’re using ADO recordsets to maintain compatibility with previous objects that were written in VB6 that returned ADO recordsets.

When we were populating the ADO recordset, if the data was basic, standard English characters, then all works perfect.

ADO recordsets with Unicode data

But if the data contained Unicode characters (such as じゃらん), then we would get the ever-so-helpful error:

Multiple-step operation generated errors. Check each status value.

Basically, this is ADO throwing up its hands in failure.

The issue came down to the fact that when we were building up the recordset, we were creating the string fields with a type of adVarChar (200). This works find for basic characters, but not for Unicode characters. Instead, we needed to be using a type of adVarWChar (202). More on ADO Data Types can be found here.

Changing the field type to adVarWChar resolved our Unicode character issue. Success.

Recordset.Sort

But it opened up another issue – sorting on these recordsets was now failing once we changed from adVarChar to adVarWChar.

For example, this code:

var TimeZoneList = FCApp.GetTimeZoneList();
TimeZoneList.Sort = "full_name";

would fail with this error:

Microsoft Cursor Engine: Relate, Compute By, and Sort operations cannot be performed on column(s) whose defined size is unknown or too large.

I did a bunch of Google searching, and came up with very little details on this error.

ADO Field defined size

The error itself seemed to indicate that the data might be too large. Well I knew that the data itself wasn’t too large, but perhaps the defined size of the ADO field was too large.

So, I created a simple test script, without any database access:

Dim rs
Set rs = WScript.CreateObject("ADODB.Recordset")

adVarWChar = 202
adVarChar = 200

Size = 8000

With rs
    .CursorLocation = 3
    .Fields.Append "a", adVarWChar, Size
    .Open
    .ActiveConnection = Nothing
End With

With rs
    .AddNew
    .Fields("a").Value = "Some Value"
End With

rs.Sort= "a"

This failed with the same Sort operations cannot be performed on column(s) whose defined size is too large error. Great, a failing test. Now we can try to make it pass.

If I changed the Size variable to just 100, it succeeded. So, somewhere there’s a limit.

A few experiments later, and I find out that if the Size is 4679, it succeeds. If the Size is 4680, it fails.

I have no idea why. I don’t know what’s special about the 4679/4680 threshold. If anyone does, please chime in.

Tying this back to the ADO recordset generation within fcSDK

Digging closer into the fcSDK, it turns out that the ADO string fields were all being defined with a size of 8000. So, once we adjusted this below the 4680 threshold, all was well. We could return Unicode data, and the Sort method now works as expected.

On a related note, even if we defined the field size to 4000, we could still put more than 4000 characters into the field, and it handled it just fine. It appears that the size is more of a suggested initial size. Weird.

Summary

I realize that much of this is esoteric, but hopefully it will help someone else should they run into a similar issue.