Search code examples
c#globalizationh2

Syntax error in H2

Our application uses H2Sharp to access a H2 database from C# (Framework v4). Recently we bumped into the following issue: The following code

H2DataAdapter adapter;
DataTable table;
//initialize values
H2Command updateCommand = new H2Command("UPDATE MYTABLE SET NAME=@NAME, DOUBLECOLUMN=@DOUBLECOLUMN WHERE ID = @ID)", connection);
updateCommand.Parameters.Add(new H2Parameter("@NAME", DbType.String, 100, "NAME"));
updateCommand.Parameters.Add(new H2Parameter("@DOUBLECOLUMN", DbType.Double, 8, "DOUBLECOLUMN"));
updateCommand.Parameters.Add(new H2Parameter("@ID", DbType.StringFixedLength, 50, "ID"));
adapter.UpdateCommand = updateCommand;

leads to a syntax error later on when used in the following form when used under a culture with "," as the decimal separator instead of ".":

DataRow row = somehow_get_the_row();
row["NAME"] = "name";
row["DOUBLECOLUMN"] = some_double_with_fractional_part;
adapter.Update(table);

The code works fine if user input is a double without fractional part (i.e. 39 works but 39,5 does not work). I assume 39.5 would also work but the UI does not allow characters that are not included in the CurrentCulture.NumberFormat.NumberDecimalSeparator so it is not possible to enter "." in this case.

The top of the stack (until our code) is like this:

h2-1.3.160.dll!org.h2.jdbc.JdbcConnection.prepareStatement(string str) + 0x1f4 bytes    
H2Sharp.dll!System.Data.H2.H2Command.CreateStatement() Line 335 + 0x35 bytes
H2Sharp.dll!System.Data.H2.H2Command.EnsureStatment() Line 368 + 0x8 bytes
H2Sharp.dll!System.Data.H2.H2Command.ExecuteNonQuery() Line 438 + 0x8 bytes
System.Data.dll!System.Data.Common.DbDataAdapter.UpdateRowExecute(System.Data.Common.RowUpdatedEventArgs rowUpdatedEvent, System.Data.IDbCommand dataCommand, System.Data.StatementType cmdIndex) + 0x4f bytes  
System.Data.dll!System.Data.Common.DbDataAdapter.Update(System.Data.DataRow[] dataRows, System.Data.Common.DataTableMapping tableMapping) + 0x6bc bytes 
System.Data.dll!System.Data.Common.DbDataAdapter.UpdateFromDataTable(System.Data.DataTable dataTable, System.Data.Common.DataTableMapping tableMapping) + 0x2a bytes    
System.Data.dll!System.Data.Common.DbDataAdapter.Update(System.Data.DataTable dataTable) + 0xb2 bytes

and the error message is

org.h2.jdbc.JdbcSQLException occurred
Message=Syntax error in SQL statement "UPDATE MYTABLE SET NAME=?, DOUBLECOLUMN=? WHERE ID = ?)"
Source=h2-1.3.160

I assume the "," from the string representation of double in this culture is breaking the SQL statement.

What would be a proper way to get this work? Should the database table contain columns of type double but arguments passed as strings formatted with an invariant culture? Will H2 do the parsing correctly in this case?

The code is running under Windows 7, x64 and H2 Database is v1.3.160.

Thanks in advance for any help.

Solution

  • The problem seems to be the ) at the very end of the update statement:

    UPDATE MYTABLE SET NAME=?, DOUBLECOLUMN=? WHERE ID = ?)

    should be

    UPDATE MYTABLE SET NAME=?, DOUBLECOLUMN=? WHERE ID = ?