RE: About float fields - Jordan Sparks - com.mysql.lists.dotnet

7 messages in com.mysql.lists.dotnetRE: About float fields

From	Sent On	Attachments
Frank	21 Dec 2004 16:40
James Moore	21 Dec 2004 17:32
stre...@tiscali.it	22 Dec 2004 00:33
SGr...@unimin.com	22 Dec 2004 06:35
Jordan Sparks	22 Dec 2004 07:11
Patrick Questembert	22 Dec 2004 07:35
Frank	22 Dec 2004 13:17

Subject:	RE: About float fields
From:	Jordan Sparks (jspa...@free-dental.com)
Date:	12/22/2004 07:11:58 AM
List:	com.mysql.lists.dotnet

Very well put. And if I could add one more personal preference... I do not use parameters because then I can not see the actual SQL that's going out. I just assemble the queries myself from scratch. Of course that means I have to have a few functions to add slashes and format dates properly, but that doesn't take long to set up. And it future proofs the code against any changes that might me made to the connecter as happened recently. I didn't have to change a single line of code, although I did update all the date fields in my database to default to 0001-01-01 instead of 0000-00-00.

Let me explain why: I make heavy use of dates in some of my tables. A recordset comes back and gets loaded into class objects by looping through each row in the record set and extracting one field at a time. Recently, when I added some more date fields, it was taking 1 1/2 seconds to convert 10 rows into a corresponding array of objects. I could sew a scarf in the time it took to load a small table. The problem turned out to be that zero dates were coming back from the database as 0/0/00 (or something like that) rather than 0000-00-00. And the exact format depended on the current language settings of the user. So I couldn't just test for "0/0/00" and convert that to DateTime.MinValue, and I couldn't test for "0000-00-00". Because 0/0/00 is not a valid date format in .NET, it was going through my try catch statement, and that's where all the slowdown was. By changing all the zeros to ones in the database, my application sped right up again. Notice that none of the recent discussions regarding dates affected me because I do not use the MySQL datatypes that come with the connector. Again, this future proofs my code.

-----Original Message----- From: SGr...@unimin.com [mailto:SGr...@unimin.com] Sent: Wednesday, December 22, 2004 6:36 AM To: stre...@tiscali.it Cc: dot...@lists.mysql.com Subject: RE: About float fields

If you are worried about concurrency. you could add a TIMESTAMP field to

your tables. It works like this. When you read the record you also read the TIMESTAMP value of the last time the record was updated. When you attempt to commit your changes into the database, compare that datetime to the one currently in the record. If it doesn't match, you know that someone (or something) already changed the record since you started reading it.

There are several ways to detect the change: an UPDATE of 0 records or - a transaction wrapped around a read check of the current date followed by an update statement (InnoDB) or - a LOCK/UNLOCK wrapper around a read check of the date with UPDATE.

How you handle edit conflicts is rather application specific. Your options can be summarized as: 1) Forget the changes you are attempting to make and start over with the

new information 2) Merge your changes with those already made (requires field by field comparisons) 3) Overwrite the existing changes with your changes.

I concur with James, I don't databind any elements. I found that database automation only works reliably for the most trivial of cases and I firmly believe that I have saved time by constructing my own SQL statements. Not only do I *know* that it will be an optimal statement, I won't have to worry too much about a component upgrade automagically writing platform specific statements because it's trying to optimize for MS Access or MS SQL Server and not MySQL.

My recommendation too, use the connections and recordsets for reading data and manage your own inserts and updates through direct SQL statements.

stre...@tiscali.it wrote on 12/22/2004 03:33:22 AM:

Thanks James, I'm beginning to think that your suggestion looks to be a correct solution... CommandBuilder lets me save some coding but it doesn't look particularly flexible, or maybe I don't know it well enough... :-)

The same happens with automatic data binding with TextBoxes (Binding, BindingContext, Parse and Format events, etc), it doesn't look much flexible apart from basic operations, or at least it doesn't make me reduce code size.

Any other suggestions for the float problem? Frank

My personal solution is to not use automatically generated SQL (ie, ditch CommandBuilder.) I find that for the simple case building the commands myself is simple anyway, and in the more complex case CommandBuilder doesn't do what I need. Not really a solution to your problem, but it's something to think about.

__________________________________________________________________ Tiscali Adsl 2 Mega Free: l'adsl piu' veloce e' gratis! Naviga libero dai costi fissi con Tiscali Adsl 2 Mega Free, l'adsl Free piu' veloce in Italia. In piu', se ti abboni entro il 7 gennaio 2005, navighi gratis fino al 31 marzo. E il costo di adesione e' GRATIS. http://abbonati.tiscali.it/adsl/