Date: prev next · Thread: first prev next last
2012 Archives by date, by thread · List index


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: users+help@global.libreoffice.org
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

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.