Repository navigation
DbValue.GetDateTime throws a FormatException when converting datetime string to a DateTime value. #1173
Description
Activity
The usage of
CurrentCultureis certainly wrong. On the other hand this method is not expected to work with strings. If you storeDateTimein database as a string, you're doing it wrong. Firebird has dedicated datatype(s) for date/time.Obviously it is not be advisable to store a DateTime in a text column. However there are likely cases where users have done so. When given a DateTime object as a parameter to insert, Firebase silently converts the value to text. The expectation here is that the driver should be able to reverse the conversion from text when asked to do so.
You can not convert simply the string to datetime. Look at the stored format (the default is depending from current culture), but in multi computer scenarieos each can have another culture and so a different format.
Change the datetime to string with own format before and use DateTime.TryParse() with the stored format.Better is, to change the table column to timestamp, which is fully supported.
@BFuerchau At the end of the day a database is nothing more than a storage implementation. How the data is stored is not important so long as what you put in you are able to get back out again. This bug is an example of FirebirdSQL client failing to store and retrieve data in a consistent manner.
A DateTime parameter is interpreted as a DbDataType.Timestamp as described here:
NETProvider/src/FirebirdSql.Data.FirebirdClient/FirebirdClient/FbParameter.cs
Lines 128 to 132 in fcec803
The DateTime value is converted to a timestamp using DbValue.GetDate() and DbValue.GetTime() and then sent to the server. The server converts the timestamp it receives to text using the datetime_to_text function.
This conversion is straight forward. Given a dbtype_timestamp value, it decodes it into a struct tm. It then forms a string in the form of "yyyy-mm-dd hh:mm:ss.tttt" or "dd-MMM-yyyy hh:mm:ss.TTTT" where TTTT is optionally provided depending on database version. More importantly, the conversion to string ignores the locale on the server and the client. As such, two clients with different locales and the same DateTime value will result in the same text value stored in the database. Using Convert.ToDateTime with the current date/time format of the locale of the local machine is incorrect. The FirebirdSQL client should only attempt to parse the date time according to the forms used by the server.
If you have date/time stored in a database (or on the client side) as a string, you're asking for trouble. It is dangerous and wrong on so many levels (and yes, the
CurrentCulturemakes it even harder to round trip).I would personally like to remove the support to convert
stringtoDateTimefrom the code, but that would be huge breaking change for no clear win. In regular usage it's basically unused code path anyway.I'm going to close this issue now as I don't see value changing anything here.
@cincuranet At minimum, the use of
CurrentCulturehere should be removed in favor of using theInvariantCulture. UsingCurrentCultureresults in inconsistent behavior. An application deployed on one system may work perfectly fine while fail on another. Any opinion about how you think users should store their data is irrelevant. If the FirebirdSQL database did not want to allow users to store a Timestamp in a character field, then they would have explicitly forbid it and thrown an error when a conversion was needed. The client driver must not throw an exception on one system vs another given the exact same data simply because one client uses a different culture or locale than the other. The value gained by this change is a method that works reliably and consistently.For reference, this bug was discovered while I was working on changes for NHibernate, a common database abstraction layer similar to Entity Framework. NHibernate currently accesses data from a DataReader using the
Item[int]indexer. The indexer returns an object which requires a cast to the requested type. Any cast here is problematic because there are many cases where there is not enough information available about the data to perform the cast correctly. For instance, SQLite does not have a DateTime type and requires storing the value in a TEXT, REAL, or INTEGER field where the format is dependent on connection specific settings. Accessing the indexer in these cases returns a string, double, or integer value which must be converted to a DateTime. The consumer of the data should not be responsible for determining how to convert the data in those fields back to the original type. That is purpose of theDataReader.Get*methods of the DataReader. These methods provide a consistent way to request a specific data type from the result set according to database specific rules. If the database performs a conversion on the data given, the DataReader must perform the appropriate conversion back to the original type. That conversion should work on all machines regardless of the culture or locale. As such, NHibernate should call the type specific methods of the DataReader to obtain values instead of using the indexer. However, the GetDateTime() implementation for the FirebirdSQL client driver was found to be problematic in the aforementioned case because it does not produce a consistent result.As demonstrated, this issue is not inherently specific to FirebirdSQL. Many databases silently convert data upon insert to the destination field type according to a particular set of rules. Those rules vary from database to database. Some rely upon connection string parameters, while others rely on schema properties or server variables. The Get* methods of the DataReader were designed to read data from a result set in a database agnostic way and to perform any conversions according to the database specific rules. The FirebirdSQL client driver is not adhering to the rules of the FirebirdSQL database in this case. The expected result here should be the same as if the query had performed an explicit cast from the character field to a timestamp type.
Yes, this is known practice, if a type isn't specified and values are stored in text columns.
In this case, the database manager ist not the responsable to convert data!
I'm programming databases now since over 50 years and it's always practice to convert the data by myself to put in the database or retrieve from the database.
The database manager doesn't know from wich client data comes and to which client data must be sent.
If you take a timestamp with a current datetime object on any client, normally you get a value from the local timezone.
So if you send this value to the database, you have to convert the value to an apropriate value which is compareable to each value at any time and any location in the world. For this exists, since my early years in programming since 1975, already the UTC-Time.
So put the right value in the datebase you can do all, what you want.
If a database doesn't support a specific value so you must store the value in text or binary columns. Than the database can only transport the value as is from and to database.
If your runtime of any language does also not support the correct type, so my be, you choose the wrong software.
I know also, that SQLite stores in the past only text columns. So SQLite was neven an option to use it as a datastore.
If you use JSON/XML for data exchange, is it yours to choose the correct format, the correct codepage (ANSI, UTF8, ...) so the data will by used from anyone everywhere. For this we have invariant culture to do so.
And also, if frameworks like EF doesn't work correct with textcolumns, if you store other values than texts, than not the framework must have an eye on this.
And the Getter of any type must match the type, which is stored in the database.
If i use a GetInt() from Text, the value is tried to convert and i get an error if the value isn't pure numeric.
If i use a GetDouble() from text, the decimal point can be comma or dot and the grouping character vice versa. So which value should come back? Is the value strored as 1,000,000 or 1.000.000 or 1e+6?It is not a problem of the driver or database, it is a problem of the developer how the software has to be designed.
The problem isn't whether or not frameworks like Entiy Framework or NHibernate work with text columns correctly. They do what they are told to do. If a developer tells Entity Framework or NHibernate that a column contains a DateTime value these frameworks generate a parameterized statement with a DateTime object that is passed to the client driver and sent to the database. When selecting the value from the database, these frameworks expect to be able to get a DateTime value back that was successfully insert into the database. The problem here is that the
FbDataReader.GetDateTimemethod only works correctly if the date time format ofCurrentCulturematches that of the en-US culture or theInvariantCulture. You are arguing over a single line change that is the difference between the client driver doing the right thing vs the inconsistent behavior it has now when it unexpectedly throws an exception.If i use a GetInt() from Text, the value is tried to convert and i get an error if the value isn't pure numeric.
If an integer value is inserted as an int and then converted to a text value by the database, the client driver should never have a problem converting the textual value back to an int. There is never a case where inserting an integer via a parameterized query would ever result in a value that could not be parsed back as an integer by the client according to the database specific cast performed. The integer_to_text function defines how an integer value is converted to text.
If i use a GetDouble() from text, the decimal point can be comma or dot and the grouping character vice versa. So which value should come back? Is the value strored as 1,000,000 or 1.000.000 or 1e+6?
This is no different than the above. The float_to_text function defines how a floating point value is converted to text. This function always uses the printf %f and %g specifiers. The format of these specifiers is well defined and always use a dot as the decimal separator when appropriate. These format specifiers never result in a number in scientific notation. In order to preserve as much of the number as possible, commas are not included in the format used by the FirebaseSQL database. At minimum, the
GetDoublemethod of the FbDataReader should be able to convert numbers stored as text in this form back to a double value, which it does because it uses theInvariantCulture.You may have no probem, when you set at runtime the CurrentCulture to InvariantCulture, because you have also the CurrentUICulture to show data to the client.
The standard casts of database i know, uses in most cases the currentculture of the system, where the server runs. This may be another than the client use. Normally the cast of datatime accepts currentculture and ISO which is the InvariantCulture. The Timestamp from SQL-Server contains an additional "T" between date and time.
If i read DateTime from the SQL-Sever i must often use the SQL-Servers own convert-function, to get an iso string instead of the stored datetime.And when you tell the framework, that a text column is another type than string, than is this a bad design. Make your own wrapper for converting. The main problem is the communication between the client and the server. Parameters are serialized automatically with the typed GetValue, also the GetDate()/GetTime() and this values are converted to an integervalue. which is than invariant.
Did you have a look at the database, which value is really stored in the textcolumn?If you dont accept this driver as is, you can download a copy, change them und use this. You can also create a nuget package for your application.
As you can see, the ticket is closed, the problem would not be solved, what i understand, because it is not an error.
If a database type doesnt match the parameter/fieldtype, the result may by not usable.Have you looked at:
https://dev.to/adrianbailador/automapper-mastering-complex-mappings-in-c-46am@BFuerchau What you're overlooking here is that the user is not passing strings to the the database to store into the text column. As stated in the original issue, they are inserting a DateTime object. That object is ultimately stored as text by the database due to the column type. If they were inserting a string and retrieving a string, then the format and the parsing of that string is up to them. The following are not the same:
- Inserting a DateTime object value using a parameterized query with a DateTime parameter type into a text column.
- The conversion to text happens at the database layer. The FirebirdSQL client driver converts the DateTime object to a timestamp and then the server converts it to text using the datetime_to_text function and inserts it into the database field.
// For the purposes of this example the table must be initially empty. // Insert a DateTime object directly into the database. DateTime dtNow = DateTime.Now; var sqlInsert = "INSERT INTO SomeTable (textfield) VALUES (@dtParam)"; using (var cmd = new FbCommand(sqlInsert, conn)) { var dtParam = cmd.CreateDbParameter(); dtParam.ParameterName = "@dtParam"; // Note: The parameter type is a DateTime and the value is a DateTime! dtParam.DbType = DbType.DateTime; dtParam.Value = dtNow; cmd.Parameters.Add(dtParam); cmd.ExecuteNonQuery(); } // Query the DateTime value from the database. This currently fails if the current culture is not en-US!! var sqlSelect = "SELECT textfield FROM SomeTable"; using (var cmd = new FbCommand(sqlSelect, conn)) using (var reader = cmd.ExecuteReader()) { // Read the first and only result in the table. reader.Read(); DateTime dtThen = reader.GetDateTime(reader.GetOrdinal("textfield")); }
- Inserting a DateTime string value using a parameterized query with a string parameter type into a text column.
- The user converts the DateTime object to a string in whatever format they want. The FirbirdSQL client driver sends the string to the database which inserts it into the database field unmodified.
// For the purposes of this example the table must be initially empty. // Insert a DateTime as a specially formatted string into the database. DateTime dtNow = DateTime.Now; var sqlInsert = "INSERT INTO SomeTable (textfield) VALUES (@dtParam)"; using (var cmd = new FbCommand(sql, conn)) { var dtParam= cmd.CreateDbParameter(); dtParam.ParameterName = "@dtParam"; // Note: The parameter type is a String and the value is a String! dtParam.DbType = DbType.String; dtParam.Value = dtNow.ToString("yyyy'/'MM'/'dd HH':'mm':'ss.ffffff", CultureInfo.InvariantCulture); cmd.Parameters.Add(dtParam); cmd.ExecuteNonQuery(); } var sqlSelect = "SELECT textfield FROM SomeTable"; using (var cmd = new FbCommand(sqlSelect, conn)) using (var reader = cmd.ExecuteReader()) { // Read the first and only result in the table. reader.Read(); // The value was inserted using a custom formatted string. // The value must be read as a string and parsed manually! string sThen = reader.GetString(reader.GetOrdinal("textfield")); DateTime dtThen = DateTime.ParseExact(sThen, "yyyy'/'MM'/'dd HH':'mm':'ss.ffffff", CultureInfo.InvariantCulture); }
In the first case, the user should be able to call FbDataReader.GetDateTime() to retrieve the original DateTime object value that was stored in the database. In the second case, the user would be expected to call FbDataReader.GetString() and parse the value they originally stored. FbDataReader.GetDateTime is not expected to correctly parse data from case 2 above. In other words, if a user formats an DateTime, integer, double, or whatever else into a string and inserts it into the database using a string parameter it is their responsibility to parse the string value from the database back into the original object. If they insert the value using the appropriate DbType, then they should be able to retrieve the original value using the Get* methods of the FbDataReader. FbDataReader.GetDateTime is the only method that fails to do this properly.
- Inserting a DateTime object value using a parameterized query with a DateTime parameter type into a text column.
All is clear, but as you can see in the answer from the developer, this can work only, if all clients use the same cultureinfo.
It is with all databases the same thing. This happens also with strings, when you doesnt hase the same codepage between clients and the column doesn't support unicode. The dateformat is the same thing.Make your own copy and everything works.
11 remaining items
@BFuerchau The FirebirdSQL datetime text format is not en-US. That format used is well defined in the FirebirdSQL source code as I described above. In standard mode, the client driver would always store datetime values into character fields in that form to match what the server does. This is an issue in the client driver because:
- The client sends the DateTime value as text instead of as a timestamp (according to Row BLR (Binary Language Representation) the client may send types that are not what the server expects, but the types must be convertible).
- The client currently converts non-string objects to strings by calling ToString() which defaults to using the client's current culture for string conversions. This format does not match the form used by the FirebirdSQL datatbase. The bulk of the conversions happens in GdsStatement.WriteRawParameter with some modifications being made during FbCommand.UpdateParameterValues for Binary, Text, Array, and Guid destination field types. Once the statement is prepared, a DbDataType of Char or VarChar will cause any non-string values to be converted to strings by the Object.ToString() method.
- a culture doesnt work, because the conversion is done on the server after the prepare is done and only than, if the parameter type doesn't match the required type. Most DBM's, i know, in case of query or calculations try to cast the column or expression to the parameter type, which may than fail. The parameter is always sent as type you specify, any client culture isn't relevant and nothing would be converted.
The client doesn't currently work this way. The client currently receives a set of field descriptors from the server after preparing the parameterized statement. The client then writes the BLR used for the execute statement based upon the field types of the descriptors. A DbType.DateTime parameter being insert into a DbDataType.VarChar field is currently converted to a string by the client using the current culture. Providing a culture to use for this conversion and for other conversions would partially solve the conversion issue.
- How should this work, to specify a method in connection_string_? As i told in 1, the decision, if a conversion must happen, depends on the context. How should the server call the method? For this, you can write a before trigger, to perhap correct the content. And should this method used for all datetime parameters and results? I don't think so!
Obviously, the user of the client driver can't provide a conversion method. They could however specify how conversions should happen in the client driver. In non-standard mode, data conversions in the client driver would use either the current culture or the culture specified in the connection string. In standard mode, conversions would either be offloaded to the server (recommended), or performed in the client as they would be in the server.
- The prepare is always done at the server side, also if you don't call them explicit. If you define parameters, that wouldn't change. If you don't define parameters, the parameters are created automatically, depending on the context where a parameter is used. So in most cases for your problem you get a string parameter. If you store than values, you get perhaps a type mismatch.
Correct, the server always prepares a statement before it is executed. However, most databases allow you to skip statement preparation from the client prior to execution. In most cases, the client never has any knowledge of what the destination field types are and all conversions are handled by the server. FirebirdSQL however is not like other databases as it tells the client what types the server expects for the fields. This driver currently sends parameter types and values according to the field descriptors returned from preparing the statement. This differs from how most other database implementations work. The BLR documentation states that the client may send different BLR than what the server expects so long as the types are convertible.
The BLR documentation also hints at the existence of a op_exec_immediate2 instruction which accepts parameters. This op code is currently undocumented, but the wire protocol probably wouldn't be too difficult to reverse engineer or brute force. I suspect this operation allows executing a statement without calling prepare first. In this case, the BLR sent would be generated from the client specified parameter types.
From what you get your knowledge?
The main class for communication is
XdrReaderWriter
Each type has a specific serialize/deserialize function, which converts type to/from byte-Array.
You never find a ToString()-call.Prepare on the server can't be supressed. This is technologie depended, for all databases. What prepare does and why it is needed, i have already told.
How else can the server decide, what you want, which parameter maps to field or expression?
The firebird server tells me the parameter types, when you explict prepare without any own parameter, this is also standard for most databases.
The driver handles the comandtext independet from the parameters. It makes no sence, that the driver should make a syntaxcheck and retrieve table informations to handle a type conversion for parameters. This is a normal server task.
The firebind client writes the parameters with only nessesary conversions for serializing values. The driver doesn't know any over the usage of parameters on the server. So conversions are done so, that the server get the same value on deserialization.
Any individual function or format will break the protocoll.
What you can do here is, define all parameters as string and look what happens. The server must than interpret, how strings are than back converted to corresponding types.May be you have worked in the past with SQLite. This database works without types and stores any value in any column.
Than it is your responsibility, how to process with data.
But Firebird is strongtyped!Than you didn't looked at the source of the driver, how parameters are sent.
Finally all parameters are converted with the TypeEncoder class.
For Date and Time, you will find the encoder, which calculates int, which is sent to the server.` static int EncodeDateImpl(int year, int month, int day)
{
if (month > 2)
{
month -= 3;
}
else
{
month += 9;
year -= 1;
}var c = year / 100; var ya = year - 100 * c; return ((146097 * c) / 4 + (1461 * ya) / 4 + (153 * month + 2) / 5 + day + 1721119 - 2400001); } public static int EncodeTime(TimeSpan t) { return (int)(t.Ticks / 1000L); }`
Don't ask me, why."Correct, the server always prepares a statement before it is executed. However, most databases allow you to skip statement preparation from the client prior to execution. In most cases, the client never has any knowledge of what the destination field types are and all conversions are handled by the server. "
Sorry, but this can only say, who nothing knows over database programming.
The skip prepare means, that you don't need an explicit prepare before execute.
The server must prepare each statement before first execute.
After the first execute, the statement is prepared and cached.May be, the client does not know the types, but the developer should know!
If you don't know the structure of tables and types of a destination field, all automatically conversion can be failed.When you use frameworks like EF or strongtyped datatable objects, like supported from VS, you have automatically classes, with properties for each field in the corresponding type of the server.
So in communication between program and database all works fine, because the client knows everything about types.
You must know, that a string can not be longer than the defined size of the field, or a fixed decimal as 10 digits with 2 fractions.And thats your problem. I don't understand why you not let alter the table to the correct fieldtype and all problems are gone, without any changes to the driver, database or your program.
The execute immediate is an operation which comes from languages with embedded sql. There you have often explicit language statements like
`
commandString = "Select * from mytable where key = ? and status = ?";
exec sql prepare mystmt :commandString;
exec sql call mystmt using :p1, :p2;or
exec sql execute immediate commandString using :p1, :p2;
`
With the modern languages you don't need it anymore. But the serverside prepare must always happen.@BFuerchau My knowledge comes from reading and testing the source code of this client driver. Here is all the information I know about the FirebirdSQL Wire Protocol.
The main class for communication is
XdrReaderWriter
Each type has a specific serialize/deserialize function, which converts type to/from byte-Array.
You never find a ToString()-call.Here a trace of what happens when you call
FbCommand.ExecuteReader- FbCommand.ExecuteReader
- FbCommand.ExecuteCommand
- FbCommand.Prepare
- Creates and prepares a statement by sending the SQL to the server.
- Retrieves the field and parameter descriptors.
- Upon success, sets FbCommand._statement to the prepared statement.
- _statement.Execute -> GdsStatement.Execute
- GdsStatement.SendExecuteToBuffer
- GdsStatement.GetParameterData
- IDescriptorFiller.Fill -> FbCommand.Fill
- FbCommand.UpdateParameterValues
- This updates the parameter descriptors of the statement with the values of the FbCommand parameters.
- The VarChar descriptor's DbValue.Value is set to the DateTime object passed as the parameter value.
- FbCommand.UpdateParameterValues
- GdsStatement.WriteParameters
- GdsStatement.WriteRawParameter - field.DbDataType is DbDataType.VarChar
- DbValue.GetString
- Object.ToString - DbValue.Value is converted to a string based on the current culture.
- DbValue.GetString
- GdsStatement.WriteRawParameter - field.DbDataType is DbDataType.VarChar
- IDescriptorFiller.Fill -> FbCommand.Fill
- _parameters.ToBlr -> Descriptor.ToBlr
- GdsStatement.GetParameterData
- GdsStatement.SendExecuteToBuffer
- FbCommand.Prepare
- FbCommand.ExecuteCommand
As you can see above, the client fills the parameter descriptors from the server using the client values. It then generates the BLR of those descriptors. It then sends the BLR along with the parameter data when executing the statement. The parameter types sent to the server are the same as those received when preparing the statement. As such, the parameter is sent as a VarChar after the DateTime value was converted to a string by the client.
Because of the way this client operates, the following test using a double fails.
[Test] public async Task InsertDoubleIntoVarChar() { var r = new Random(); double d = r.NextDouble() * 1e9; var culture = CultureInfo.CurrentCulture; try { CultureInfo.CurrentCulture = new CultureInfo("de-DE", false); await using (var command = new FbCommand("insert into TEST (int_field, varchar_field) values (1234, @d)", Connection)) { var param = command.CreateParameter(); param.DbType = DbType.Double; param.Value = d; param.ParameterName = "@d"; command.Parameters.Add(param); var ra = await command.ExecuteNonQueryAsync(); } await using (var command = new FbCommand("select varchar_field from TEST where int_field = 1234", Connection)) await using (var reader = await command.ExecuteReaderAsync()) { reader.Read(); var j = reader.GetDouble(0); Assert.AreEqual(d, j); } } finally { CultureInfo.CurrentCulture = culture; } }
This test fails is due to the same issue as I originally reported, just using a different type. In this case, the test fails because the ToString() call in the DbValue.GetString() method returns a value with a comma as the decimal separator and FbDataReader.GetDouble() parses the string retrieved using the InvariantCulture. No exception is thrown, because the InvariantCulture ignores the comma but returns a number magnitudes larger than the original.
In my opinion, the client should ignore the parameter descriptors received and generate them directly from the parameters of the FbCommand. This would avoid converting from the parameter type to the descriptor type in the client. Thus a Double or DateTime value would always be sent to the server as a Double or a Timestamp instead of as whatever parameter descriptor type the prepare statement returns. If the server is not able to perform the conversion, then the execution fails as expected. This is also consistent with how one would execute a statement without preparing it first if implemented.
Notes
While stepping through and testing the above call sequence, I noticed some potentially error prone code in the DbValue.GetString implementation. As implemented, if the field type is DbDataType.Text and the stored value is a long then the value of the DbValue is converted to a string using DbValue.GetClobData which assumes that there is a statement associated with the DbValue. This could lead to a NullReferenceException when the DbValue is not associated with a statement. It could also result in a long value being incorrectly converted to a string when inserting into a Text column.
Update: While this could be a problem, actual tests don't get that far. An invalid cast exception occurs in the
FbCommand.UpdateParameterValuesmethod due to trying cast a 64bit long value to a string. The cast here causes the client driver to crash if you attempt to insert any data type other than a string or a byte array into a BLOB SUBTYPE TEXT field.- FbCommand.ExecuteReader
Ok, sorry you are right.
The internal prepare retrieves the parameters from the firebird, with additional roundtrip, what other databases don't do.
This seams done to support named parameters, what originally isn't supported by the firebird itself.Than merge existing parameters from the command to the retrieved parameters from statement.
This explanes also other misbehavior, that you get no error, when you execute a query or nonquery and forgot to create the needed parameters.
In this case, the retrieved parameters get there defaults or dbNull, but it should raise an error of missing parameters.But as i told, when both, server and client uses the same CurrentCulture, you have no problem.
So my Insert with parameter inserts a date in german format "tt.MM.yyyy".
If you make an "insert into mytable values(current_tmestamp)", this is written in the field in ISO-Format "yyyy-MM-dd".The GetDateTime Supports both, CurrentCulture and ISO.
But the problem starts than with the order by, also indexes can't by used, which degrades performanceYour explains confirm, that it is urgent to use the correct column type for the requirement data, because all others get problems in further processing.
I don't understand, that you will create work arounds with a lot of other problems and not correct the real cause, which solves all your problems. If you will use any value in text columns, you should convert it by yourself.@BFuerchau In my opinion, there are numerous problems with the current approach. Attempting to convert the client provided values to the server expected types in the client driver is the wrong approach in my opinion. This leads to unexpected behavior as demonstrated by the many examples I have provided. The expected behavior would be for the server to receive the same DateTime value or timestamp regardless of the client culture, and for the server to then convert it if required or return an error. The behavior of the server in this case is well defined and results in data stored in a consistent manner. This issue isn't related to just object to string conversions. String to numeric conversions have issues as well that result in a FormatException being thrown from the client rather than an FbException from the result of a server based conversion error.
The BLR specification states that the client is permitted to send the data however it wants. In my opinion that's what the client should do. Then the client only needs to coerce the parameter value to the client provided parameter type and let the server handle any other conversions. For a DateTime value, that means coercing the value to a timestamp and sending the value as a timestamp. For floats, double, long, int, decimal, the client should merely send those values to the server.
I don't understand, that you will create work arounds with a lot of other problems and not correct the real cause, which solves all your problems. If you will use any value in text columns, you should convert it by yourself.
I understand you think this is a case of the user is doing it wrong. My issue with that opinion is that if that were the case, then the driver should throw a reasonable exception with an appropriate error message. E.g. Something along the lines of "Cannot insert a DateTime value into VarChar field {field}" with an exception type of FbException. This however would be a major breaking change with no advantages other than to mask the issues of this client driver. The better approach is to address the behavior of the client and let the server deal with any data conversions that may be necessary.
I did not have any problem with the driver, if i use the correct column type. And i think, all other developers, which uses these driver have also no problems. The driver exists since years. It seams, you are the only one. Sorry.
@BFuerchau I'm probably the first one that noticed because I develop software that is meant for international use. I've run into these sort of issues before and am more aware of them. Many of these issues never crop up until the culture changes. The proper thing to do here is to write tests around every possible conversion that verifies expected functionality according to behavior of the server. If the server doesn't support a particular conversion, the client shouldn't either.
The solution I've recommended would correct these issues while maintaining backwards compatibility with existing databases.
As i explained. your solutions arn't possible or has many works on client and server!
Also many other versions of drivers, Java, Python, ODBC or else, must do these also to stay compatible.I'm using my solution also in multinational cultures, but i always use the correct type of column for the data.
Cause your wish for exception, i have checked that many database allows you to store any value in a textcolumn and convert between types, but throws errors if type isn't convertibale or the result doesn't match.
But it is always your responsible, to correct use the textcolumn in case of reading the value, so this is standard behavior.As you have seen, that the _value of text column is a long, thats belongs to the protocoll of the firebird server. Long strings (i don't know at which lenght), arrays, clobs or binary aren't loaded in the fetch, so the _value contains a handle. To load the original value, you have to call the corresponding GetValue, GetString, or GetValues, to load the remaining data before you can convert.
In case of other getters you get an error, long to DataTime, or the long value itself as int, double, and so on.If any other developer works e.g. with frameworks like EF, Typed DataSets or TypedTables, your columns than are always text columns.
I don't think your wishes in this case becomes true.
To work correct with each database manager (DB2, Oracle, SQL-Server and also Firebird), you must use correct typed columns to avoid problems, special for multinational software and use of multinational clients in parallel.@BFuerchau The solutions I have suggested could all be completed from the client side. The amount of work varies from the method used. Here are the client-side changes required to make the client driver behave in a consistent manner:
-
Add culture to connection string for client side data conversions.
- Difficulty: Easy
- Impact on existing code: Low
-
Add operational mode parameter to connection string for client side driver for data conversions.
a. Option 1 - Update client to send parameters based on client provided types.- Difficulty: Moderate
- Impact on existing code: Moderate
b. Option 2 - Update client to format and parse strings according to FirebirdSQL server conversions.
- Difficulty: Low
- Impact on existing code: Low
Just because other implementations have the same bugs, doesn't mean this isn't worth fixing. Anything fixed here can always be ported to other drivers. Progress isn't made by accepting failure.
Anyone who uses this driver and accidentally uses a VarChar field for storing anything but text, currently would have a hard time migrating the data to a strictly typed column. The built-in CAST function is designed to only handle conversions that the server performs. When operating in standards mode, these changes would align all conversions with those performed by the server allowing server side casts to correctly convert the data.
-
You can do it by yourselve and make pushrequestes for this changes.
Be shure, that all modifications runs the tests, included by the project, complete errorfree.
Than perhaps your changes may be accepted.In the past, a few years, i have done similar things concerning performance. My modifications allows more faster download of data from firebird. All tests run fine.
The answer: no one has any performance issues, so this suggestions aren't accepted and included.
So you can do the same way like i do: i load sometimes new versions of the project, reimplement my modifications so i have the fastes driver.
A few days before i have seen an issue, that someone needs long time to load 1 Million rows withe a single integer.
I have checked this with my version. Depending on other processes in my environment, my query of 1 single value give me 120.000 to 150.000 Rows per second! A Query with 55 Columns of mixed types give me 30.000 Rows per second.
I'm developing a data analysis application for web clients, i need performant reads from the database, which i get only with my own version of the driver.This is interesting conversation, but I think it is going nowhere. Let me express where I stand on this.
Storing types in string/varchar is wrong (let alone when locales are involved) and sooner or later you'll experience issues (both server-side and client-side). The best way would be to reject the conversion in the provider and throw an exception. It is not like doing the conversion manually is difficult. But sooner or later people would ask for it anyway, with some reasonably looking reason. Anyway, that ship has already sailed.
That said, I'll open an issue and go through the codebase and make sure all the implicit conversions to/from string happen with invariant locale. It is going to be breaking change, but low priority in my eyes.
No settings, etc. Polluting connection string or some API for this already wrong approach is not worth it.
@BFuerchau If I find time I will submit a pull request for changes. I've done a few tests locally already. Some tests have been more fruitful than others.
One glaring issue I found yesterday that I overlooked was the implementation of the FbDataReader. All the Get* methods of the FbDataReader currently call the
FbDataReader.GetFieldValue<T>method which then calls the type specific Get* method of the underlying DbValue. This leads to unexpected behavior. The DbValue class should only return the correct FirebirdSql data type for the underlying field/column. As you know, blobs, clobs, and arrays require special handling when reading the value. The initial value for these types is a 64bit int which is a handle to retrieve the actual value stored. CurrentlyFbDataReader.GetInt64results in a call toDbValue.GetInt64, just asFbDataReader.GetStringresults in a call toDbValue.GetString. When a statement returns a clob, callingFbDataReader.GetInt64will return the handle used to retrieve the actual string value without actually reading the value of the clob. Likewise, callingFbDataReader.GetStringon a statement that returns an Int64 will attempt to read a non-existent clob.FbDataReader.GetFieldValue<T>should call theFbDataReader.Get*and allFbDataReader.Get*methods should merely callDbValue.GetValue, they should not call theDbValue.Get*methods except in a few instances like maybeFbDataReader.GetStream. Any conversions from the database specific types should occur in the FbDataReader, not in the DbValue class.I still have reservations about the side effects of calling
DbValue.GetStringand other methods that deal with clobs, blobs, and arrays but I don't have a good idea for how to address them just yet. I have concerns there may be issues in the client/server functionality if a query returns a blob, clob, or an array but the actual value is never retrieved. I have not checked if this causes a resource leak or other client/server issues.@cincuranet As stated above, I think all this can be done without causing breaking changes. The initial defaults for the connection parameters can be to preserve backwards compatibility and then be changed in a major release which breaks backward compatibility when the connection parameters are not explicitly set.
@deAtog This behavior describes, what happens in case of incorrect type handling.
Correct your type in the table or use GetValues(objectArray), than you have a result and can do what ever you want.
The GetValue are only helpers to get directly an unboxed value, depending on the stored Type, which also is incorrect, if the value is NULL or DbNull.
I work with many different databases and always i must handle the content depending of the columntype.
This is the reason to use always the GetValues() or load it in a DataTable-Object.
I don't understand why you are reluctant to change the table appropriately!@cincuranet
On the other hand, when i look to the driver, in case of absent of named parameters, you don't need a prepare before execute, because you can send the parameters as is in the defined types. Perhaps, the database throws than an error or may try convert automatically to the destination type, which may also throw errors. The database must prepare anyway.
The benefit is also, if you have more "?" in the commandtext as in the parameters, than i get an error. At the moment, you fill null or default, if a parameter is missing, thats also incorrect.
Under certain circumstances, the call to Convert.ToDateTime in this method will throw a FormatException when attempting to retrieve a DateTime value inserted into a text column:
NETProvider/src/FirebirdSql.Data.FirebirdClient/Common/DbValue.cs
Lines 246 to 255 in 73b0082
Steps to reproduce this issue are as follows:
CultureInfo.CurrentCulture = CultureInfo.InvariantCulture;CultureInfo.CurrentCulture = CultureInfo.GetCulture("ar-SA");A format exception is generated because the call to Convert.ToDateTime uses the current culture of the calling application and does not correctly parse the value that was stored in the database. This method should either attempt a direct cast to a DateTime and throw an InvalidCastException or perform an implict conversion from the string value to a DateTime value using the inverse conversion as performed by the database upon insert. The latter provides consistent insert and select functionality.
It may be sufficient to use the InvariantCulture in the call to Convert.ToDateTime here, but I personally do not know how Firebase converts DateTime values to text. Additional tests may be required for other locale combinations to ensure correct functionality.