Re: [fpc-devel] MySQL 5.1 and Double (trouble)
Andrew Brunner wrote / napísal(a): On Mon, Jul 11, 2011 at 3:02 AM, Mark Morgan Lloyd markmll.fpc-de...@telemetry.co.uk wrote: 1.) Update Value : 40734.825668912039 2.) Actual Value after update : 40734.8256689120 3.) Actual Value on read :40734.825668912003 Does MySQL come with an official program that you can use to run queries manually? What happens when you use it to store and retrieve that number? 1.) is a value declared as double and viewed under GDB. 2.) is a value viewed in table data in MySQL Console App under Linux. 3.) Is the value retrieved from the table by the Application using the Select statement and Fields.FieldByName(field_name).AsFloat() I am still experiencing this issue and seeking alternatives b/c I'm using all 12 digits - and am expecting all 12 digits. Can someone update the (Fields.Data variant as double) with a binary send instead of converting to a string? IMO it will require use in mysqlconn.inc new API prepare family functions introduced in MySQL 5 http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-function-overview.html In current implementation (backward compatible with older MySQL versions) there is no only problem with sending values, but also retrieved values are strings (see http://dev.mysql.com/doc/refman/5.0/en/mysql-fetch-row.html) -Laco. ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: [fpc-devel] MySQL 5.1 and Double (trouble)
On Mon, Jul 11, 2011 at 3:02 AM, Mark Morgan Lloyd markmll.fpc-de...@telemetry.co.uk wrote: 1.) Update Value : 40734.825668912039 2.) Actual Value after update : 40734.8256689120 3.) Actual Value on read : 40734.825668912003 Does MySQL come with an official program that you can use to run queries manually? What happens when you use it to store and retrieve that number? 1.) is a value declared as double and viewed under GDB. 2.) is a value viewed in table data in MySQL Console App under Linux. 3.) Is the value retrieved from the table by the Application using the Select statement and Fields.FieldByName(field_name).AsFloat() I am still experiencing this issue and seeking alternatives b/c I'm using all 12 digits - and am expecting all 12 digits. Can someone update the (Fields.Data variant as double) with a binary send instead of converting to a string? ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
RE : RE : RE : [fpc-devel] MySQL 5.1 and Double (trouble)
May be, that this can be fixed in sqldb.pp in function TSQLConnection.GetAsSQLText(Param: TParam) : string; ... -ftFloat: Result := FloatToStr(Param.AsFloat, FSQLFormatSettings); +ftFloat: Result := FloatToStrF(extended(Param.AsFloat), ffGeneral, 16, 0, FSQLFormatSettings); ... for me it seems, that works ... See atached program. What do you think ? -Laco. But that won't solve the writing of truncated data as long as doubles are passed as a string to mysql. Ludo ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: RE : RE : RE : [fpc-devel] MySQL 5.1 and Double (trouble)
Ludo Brands schrieb: But that won't solve the writing of truncated data as long as doubles are passed as a string to mysql. Right. When the actual DB interface doesn't allow to transfer floating point values as binary numbers, you should fall back to storing such numbers as text, with the required number of digits, or (safer) as binary BLOBs. Then you can store even Extended values instead of only Doubles. Both workarounds may not be compatible with other applications, that expect numbers stored in the DB-specific number format. DoDi ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: RE : RE : RE : [fpc-devel] MySQL 5.1 and Double (trouble)
In our previous episode, Ludo Brands said: But that won't solve the writing of truncated data as long as doubles are passed as a string to mysql. Moreover, it is x86(_64) only. ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: RE : RE : RE : RE : [fpc-devel] MySQL 5.1 and Double (trouble)
In our previous episode, Michael Van Canneyt said: that is sent to the db and TConnectionName.PrepareStatement doesn't send anything to the db. The Oracle component fe. uses bindings. So does Firebird, and I think postgres, but I'm not sure about the latter. As far as I can see, postgres is textprocessing based. Params are asstringed ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: [fpc-devel] MySQL 5.1 and Double (trouble)
Andrew Brunner wrote: Can someone check the status of update if the bind parameter for double decimal values? I'm getting unexpected rounding errors after updating - the data in the column looks different than what was specified during the update statement. Value: double; Query.Params.ParamByName(sName).AsFloat:=Value; 1.) Update Value : 40734.825668912039 2.) Actual Value after update : 40734.8256689120 3.) Actual Value on read :40734.825668912003 Does MySQL come with an official program that you can use to run queries manually? What happens when you use it to store and retrieve that number? I think that SQL specifies that a double has 53 bits in the mantissa which is a bit better than 15 digits which is about what you're seeing. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
RE : [fpc-devel] MySQL 5.1 and Double (trouble)
1.) Update Value : 40734.825668912039 2.) Actual Value after update : 40734.825668912 3.) Actual Value on read : 40734.825668912003 Several things here: - floattostr(40734.825668912039) equals 40734.825668912. - update table set t_double = 40734.825668912039 where id =123; in the mysql client or the MySQL browser returns 40734.825668912 on a subsequent select (mysql 5.0.51 ubuntu) - 40734.825668912003 and 40734.825668912039 are beyond the precision of ieee double (2^52 = 4503599627370496 values which is 15 digits) How do you get the 40734.825668912003 value? Ludo ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: RE : [fpc-devel] MySQL 5.1 and Double (trouble)
On 11 Jul 2011, at 13:41, Andrew Brunner wrote: On Mon, Jul 11, 2011 at 4:53 AM, Ludo Brands ludo.bra...@free.fr wrote: How do you get the 40734.825668912003 value? Fields.FieldByName(Value).AsFloat(); You are probably assigning the result to a variable of the type extended, or directly passing it a routine that accepts parameters of the type extended. The digits beyond the precision guaranteed by the double type are undefined in that case (at least in case the number cannot be represented exactly by a double precision value). Jonas___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: RE : [fpc-devel] MySQL 5.1 and Double (trouble)
Declaraions are all double. Value: double; Query.Params.ParamByName(sName).AsFloat:=Value; Also: Test:double; Test:=Query.Params.ParamByName(sName).AsFloat; Test=Value ok here. Problem must be somwhere else. 1.) Update Value : 40734.825668912039 2.) Actual Value after update : 40734.825668912 3.) Actual Value on read : 40734.825668912003 As far as I know this is a relatively new problem. Could be with Ubuntu but was someone doing MYSQL work lately? ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: RE : [fpc-devel] MySQL 5.1 and Double (trouble)
On 11 Jul 2011, at 15:02, Andrew Brunner wrote: Declaraions are all double. You don't show what you do with the value before writing it out, nor how you write it out and determine that its value is 40734.825668912003 (which is probably also what Ludo meant with How do you get the 40734.825668912003 value?). As I mentioned in my previous reply, the value is probably simply getting converted to extended in the mean time and that is where the other digits come from. Jonas___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
RE : RE : [fpc-devel] MySQL 5.1 and Double (trouble)
How do you get the 40734.825668912003 value? Fields.FieldByName(Value).AsFloat(); Floattostr limits precision to 15 digits (40734.8256689120). When updating the mysql database it converts the float to a string and looses the last 2 digits. The last digits are returned by gdb or the str procedure with precision 15, but won't be displayed in data controls. Ludo ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: RE : RE : [fpc-devel] MySQL 5.1 and Double (trouble)
Ludo Brands schrieb: How do you get the 40734.825668912003 value? Fields.FieldByName(Value).AsFloat(); Floattostr limits precision to 15 digits (40734.8256689120). When updating the mysql database it converts the float to a string and looses the last 2 digits. The last digits are returned by gdb or the str procedure with precision 15, but won't be displayed in data controls. It doesn't make sense to display more digits than stored in the binary variable. There is no loss, only rounding errors in the conversion from binary to decimal text. DoDi ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: RE : RE : [fpc-devel] MySQL 5.1 and Double (trouble)
On Mon, Jul 11, 2011 at 10:19 AM, Hans-Peter Diettrich It doesn't make sense to display more digits than stored in the binary variable. There is no loss, only rounding errors in the conversion from binary to decimal text. I need to compare values with complete precision. I don't think this problem was present a while ago. When the memory is copied is there a missing 12bits? Is there going to be a fix for this? ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
RE : RE : RE : [fpc-devel] MySQL 5.1 and Double (trouble)
How do you get the 40734.825668912003 value? Fields.FieldByName(Value).AsFloat(); Floattostr limits precision to 15 digits (40734.8256689120). When updating the mysql database it converts the float to a string and looses the last 2 digits. The last digits are returned by gdb or the str procedure with precision 15, but won't be displayed in data controls. It doesn't make sense to display more digits than stored in the binary variable. I agree. I just tried to explain what happens behind the scenes. There is no loss, only rounding errors in the conversion from binary to decimal text. There is a small loss. 2^52 = 4503599627370496 which is almost 16 digits. 15 digits can be represented by a 50 bits mantissa. So you're losing 2 bits in precision by truncating to 15 digits. I wrote earlier that the mysql client returned 40734.825668912 when set to 40734.825668912039. That is mysql truncating to 15 characters the string representation of the double for displaying. But when I do a select t_double - 40734.825668 I do get 9.1204e-07. In case of fpc setting the same value select t_double - 40734.825668 returns 9.1200e-07. And that is because fpc converts to a string when updating the value losing the last 2 bits. Ludo ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: RE : RE : RE : [fpc-devel] MySQL 5.1 and Double (trouble)
On Mon, Jul 11, 2011 at 10:14 AM, Ludo Brands ludo.bra...@free.fr wrote: So you're losing 2 bits in precision by truncating to 15 digits. Ok, so I'm going to go out on a limb here and propose the question: How to we fix this 2bit loser ;-) ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
RE : RE : RE : [fpc-devel] MySQL 5.1 and Double (trouble)
It doesn't make sense to display more digits than stored in the binary variable. There is no loss, only rounding errors in the conversion from binary to decimal text. I need to compare values with complete precision. I don't think this problem was present a while ago. When the memory is copied is there a missing 12bits? Is there going to be a fix for this? Exact compare with double is never a good idea. For MySQL read more here: http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: RE : RE : [fpc-devel] MySQL 5.1 and Double (trouble)
Andrew Brunner schrieb: It doesn't make sense to display more digits than stored in the binary variable. There is no loss, only rounding errors in the conversion from binary to decimal text. I need to compare values with complete precision. I don't think this problem was present a while ago. Floating point numbers never should be compared for equality. Better: if abs(a-b) delta then ... with delta corresponding to the available accuracy (e.g. 1E-15). As a rule of thumb: 10^3 ~ 2^10, i.e. 3 decimal digits require 10 bits in the significand, so that 52 bits allow for 52*3/10 ~ 15 digits. When the memory is copied is there a missing 12bits? Is there going to be a fix for this? The binary value consists of an exponent and an significand (mantissa), most probably you forgot to count the exponent and sign bits. I'd suggest that you read a bit about using floating point numbers. They never are accurate, and every decimal representation adds more inaccuracy :-( DoDi ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: RE : RE : [fpc-devel] MySQL 5.1 and Double (trouble)
On Mon, Jul 11, 2011 at 11:55 AM, Hans-Peter Diettrich drdiettri...@aol.com wrote: The binary value consists of an exponent and an significand (mantissa), most probably you forgot to count the exponent and sign bits. The problem I'm trying to describe is with double precision floating point values. When the query is open and one of the parameters is double MySQL does not receive all of the memory. It's missing two bits. That is a problem with either the mysql5.1 driver or with the mysql51 component w/r/t the double data type. I'd suggest that you read a bit about using floating point numbers. They never are accurate, and every decimal representation adds more inaccuracy I could understand if you wanted to bring this fact into argument over arithmetic and cumulative error. But the fact is - if I send a double precision data value to a double precision field in the SQL database - that value had better be the same - bit for bit. Now if someone is being bit shifty on the conversion - that needs to be a addressed. ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
RE : RE : RE : [fpc-devel] MySQL 5.1 and Double (trouble)
I could understand if you wanted to bring this fact into argument over arithmetic and cumulative error. But the fact is - if I send a double precision data value to a double precision field in the SQL database - that value had better be the same - bit for bit. Now if someone is being bit shifty on the conversion - that needs to be a addressed. The only way to transfer double as binary data to the server is by using prepared statements and bound parameters. This is currently not implemented in fcl. All data are transferred as text to the database and are subject to conversion errors. I don't think zeoslib uses prepared statements for mysql either. Ludo ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: RE : RE : RE : [fpc-devel] MySQL 5.1 and Double (trouble)
On Mon, Jul 11, 2011 at 12:55 PM, Ludo Brands ludo.bra...@free.fr wrote: The only way to transfer double as binary data to the server is by using prepared statements and bound parameters. This is currently not implemented in fcl. All data are transferred as text to the database and are subject to conversion errors. Hmmm... I seem to recall prepare and bind parameters in fcl-db/mySQL component source?. Are you sure about this? I really, really, really need double precision data to work. I know i've used binary binds with Int64 arrays and things well beyond 8 bytes binary. ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
RE : RE : RE : RE : [fpc-devel] MySQL 5.1 and Double (trouble)
Hmmm... I seem to recall prepare and bind parameters in fcl-db/mySQL component source?. Are you sure about this? I really, really, really need double precision data to work. I know i've used binary binds with Int64 arrays and things well beyond 8 bytes binary. ___ I see that the functions and structures are defined in mysql.inc but they aren't used anywhere. Parameter values are substituted in the sql string that is sent to the db and TConnectionName.PrepareStatement doesn't send anything to the db. The Oracle component fe. uses bindings. Ludo ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: RE : RE : RE : RE : [fpc-devel] MySQL 5.1 and Double (trouble)
On Mon, 11 Jul 2011, Ludo Brands wrote: Hmmm... I seem to recall prepare and bind parameters in fcl-db/mySQL component source?. Are you sure about this? I really, really, really need double precision data to work. I know i've used binary binds with Int64 arrays and things well beyond 8 bytes binary. ___ I see that the functions and structures are defined in mysql.inc but they aren't used anywhere. Parameter values are substituted in the sql string that is sent to the db and TConnectionName.PrepareStatement doesn't send anything to the db. The Oracle component fe. uses bindings. So does Firebird, and I think postgres, but I'm not sure about the latter. The reason is that when sqldb was created, MySQL (4.0 or 4.1) didn't support parameters yet. Support for this would mean that the mysql connection component needs to be rewritten for MySQL 5.0 and higher. Michael. ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: [fpc-devel] MySQL 5.1 and Double (trouble)
On Monday 11 July 2011 19:55:25 Ludo Brands wrote: I don't think zeoslib uses prepared statements for mysql either. MSEgui tmysqlconnection uses prepared statements and binary parameters. Martin ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
[fpc-devel] MySQL 5.1 and Double (trouble)
Can someone check the status of update if the bind parameter for double decimal values? I'm getting unexpected rounding errors after updating - the data in the column looks different than what was specified during the update statement. Value: double; Query.Params.ParamByName(sName).AsFloat:=Value; 1.) Update Value : 40734.825668912039 2.) Actual Value after update : 40734.825668912 3.) Actual Value on read : 40734.825668912003 As far as I know this is a relatively new problem. Could be with Ubuntu but was someone doing MYSQL work lately? ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel