Re: Fwd: Re: [libreoffice-users] Value '0000-00-00' can not be represented as java.sql.Date

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