2

Our application uses H2Sharp to access a H2 database from C# (Framework v4). H2Sharp inherits DBDataAdapter and implements IDbDataAdapter. As such, what I am asking about most likely applies to SqlDataAdapter, but I wanted to clarify the environment details.

Recently we noticed an issue in accessing the same database on different computers. We have observed the following code sometimes works, sometimes throws an error:

DataRow row;
// Fill the row
String s = row["id"];

The error is "Column id does not belong to table Abcde".

The application works on a computer running Win7 64-bit, Visual Studio 2010 with all the patches. Same binary and database copied to a similar computer gives this error. Interestingly, the code works OK if the line is changed to

String s = row["ID"];

The DataTable has the "CaseSensitive" property set to false so initially we could not figure out what is happening.

It turns out that the DataTable has a "Locale" property and this has a default of the logged user's Locale (as set under the "Format" tab in the "Region and Language" control panel settings on Windows 7). On machines that it does not work, the user locale is set to Turkish and we are seeing a case of the "Turkish I problem" (*).

Now I understand the nature of the problem, I am trying to understand what options I have to solve it. I first thought about if there's a way that I can specify the column names etc be compared in an Invariant culture but the data itself is compared in a given locale. H2 documentation is not very explicit about it but the way it is written, it sounds like the whole database is governed by one main setting. In addition, SELECT * FROM INFORMATION_SCHEMA.COLLATIONS does not return Unicode, so this option does not seem possible.

In addition, DataTable documentation also suggests the locale is to be used "for string comparisons within the table" so there does not seem to be an option to specify different behavior for column/table names vs user data.

I found this post that mentions this issue and the accepted solution was to use "I" character instead of "i" in the table/column names. However, I think this solution assumes that the code is doing a ToUpper() instead of ToLower() before string comparison, therefore it may be broken if DataTable code is modified in the future.

That post also has a suggested solution of setting SQL server settings to UTF-8. This looks like a more viable solution. However, I could not find a way to do it for H2 database (both because the setting seems to impact the whole database and there's no UNICODE option).

I appreciate any input/help/guidance.

(*) Turkish I problem: In Turkish, ToUpper(i) == İ, ToLower(I) == (ı)

Community
  • 1
  • 1
alokoko
  • 1,335
  • 4
  • 19
  • 32

2 Answers2

1

We went ahead with the suggested solution of using all capital letters in the table/column names. Even though this assumes that the code behind the DataTable is doing a ToUpper() instead of ToLower() for case-insensitive comparison, it seemed unlikely that this code will change its behavior (which would break all the code that uses DataTable with case-insensitive comparison).

Community
  • 1
  • 1
alokoko
  • 1,335
  • 4
  • 19
  • 32
0

Does the exception has a stack trace? Could you post it?

Setting the collation will not help, as the collation only affects data (column values) and not identifiers.

For identifiers, H2 uses toUpperCase(Locale.ENGLISH) so it is not affected by the Turkish problem (any more; it used to be a problem but that's years ago).

I had a look at the H2Sharp source code and I didn't find a problem there (ToUpper / ToLower is not used). I also didn't find the error message "Column ... does not belong to table ..." there or in H2.

Thomas Mueller
  • 44,960
  • 12
  • 100
  • 124
  • Thanks for replying. It is good to know how H2 & H2Sharp behave with respect to identifier names. I checked the exception stack and there was only my application. Turning on first-chance exceptions, I see it is System.Data.DataRow that is throwing the message with this stack: `System.Data.dll!System.Data.DataRow.GetDataColumn(string columnName)` `System.Data.dll!System.Data.DataRow.this[string].get(string columnName)` It looks like H2 does not have a part in this issue. Still, if you have any guidance or suggestions on how I should solve the problem, I would love to hear them. – alokoko Nov 18 '11 at 17:43