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.