Girvin's reasoning about dates is quite correct in my opinion. In our case the zero valued date fields are logically NULLs and I would prefer that the database store them as NULLs instead. I'm afraid it is MySQL that is choosing to store zero values. The database in question is being loaded with data extracted from other sources. I can get it in either CSV or TAB separated format which I then load using phpMyAdmin - Import which I believe it utilizing what MySQL refers to as "load data infile". Whether I get CSV or TAB separated format there is no data (i.e., they are in my opinion NULLs) supplied for these fields which phpMyAdmin (or possibly MySQL) is choosing to store as (I'd even say convert to) zero values. Therefore, I'm quite happy to have that connector convert these fields (back) to NULL.
If there is a way to get MySQL to store NULLs when the imported files contain no data I'd love to know about it. I should point out that in the case of strings (e.g., fields of type VARCHAR) MySQL will store a string of zero length instead of NULL. However, this is much more reasonable than storing an invalid value such as a zero date. David ... On 12/16/2012 4:48 PM, Girvin R. Herr wrote: > David, > Playing devil's advocate here, I think there may be an inherent > systemic problem with zero dates. Most operating systems count time > in seconds, or finer, from an arbitrary date, usually the date the > operating system was first released or created. All are in the latter > 20th century. Since that date is certainly greater than 0000-00-00 no > matter which OS you have, such a date is outside the valid range of > dates for the system. Additionally, since days and months start at 1, > then a day and/or month of 00 is invalid also. Maybe that is why such > a date produces a fatal error, while a null date is fine, since null > means the date was never set. Although one may assume so, 0000-00-00 > is _not_ null! > It would be an interesting exercise to test if the year, month, or day > being 0 produces the error. > Just my 2-cents. > > Glad to hear you got it working. > Girvin Herr > > > > receiver wrote: >> It works!!! The fact that this rather ugly bit of coding is confined >> to the .odb file is the good news. It can be out of sight and out of >> mind for my technically challenged end user community. >> >> Many thanks for a pretty useful tip. >> >> For the record I'm testing with LO Base 3.6.4, MySQL Connector J >> 5.1.22, MySQL Server 5.5.16 >> >> David ... >> >> On 12/15/2012 6:10 PM, Girvin R. Herr wrote: >>> receiver wrote: >>>> I'm trying to create a LibreOffice (3.6.4) Base document which >>>> utilizes a MySQL database. I have installed the MySQL Connector J >>>> (5.1.22) and have good results with one significant exception. The >>>> subject error prevents display of a table which does contain dates. >>>> I suspect that this may be a problem with the JDBC connector and >>>> have reported it here >>>> <http://forums.mysql.com/read.php?39,576155,576155#msg-576155>, >>>> however it also seems possible that this is caused by LibreOffice >>>> Base. >>>> >>>> Any advice on what I may have overlooked would be appreciated. >>>> >>>> David ... >>>> >>>> >>>> >>>> >>>> -- >>>> View this message in context: >>>> http://nabble.documentfoundation.org/Value-0000-00-00-can-not-be-represented-as-java-sql-Date-tp4024569.html >>>> Sent from the Users mailing list archive at Nabble.com. >>> David, >>> Try this: >>> Edit -> Database -> Properties >>> Select the "Advanced Properties" tab. >>> Under "Name of the MySQL database", enter: >>> >>> <database_name>?zeroDateTimeBehavior=convertToNull >>> >>> where <database_name> is the name of your database. No spaces >>> anywhere. >>> Then click OK. >>> >>> You may have to close and reopen LO/Base for this to take effect. >>> This is the string that LO/Base passes to the MySQL connector to >>> open the database. >>> >>> I had the same problem with zero dates and times when I read about >>> this workaround years ago. I was not sure if it is still a problem >>> with the later versions, but it sounds like it is by your posting. >>> LO 3.5.7.2, MySQL 5.0.67, mysql-connector-java-5.0.7 >>> >>> Hope this helps. >>> Girvin Herr >>> >> >> >> >> >> -- >> View this message in context: >> http://nabble.documentfoundation.org/Fwd-Re-libreoffice-users-Value-0000-00-00-can-not-be-represented-as-java-sql-Date-tp4024589.html >> Sent from the Users mailing list archive at Nabble.com. -- View this message in context: http://nabble.documentfoundation.org/Re-Fwd-Re-libreoffice-users-Value-0000-00-00-can-not-be-represented-as-java-sql-Date-tp4024860.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: [email protected] Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
