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


Jay,

You have a good idea which I think I should be able to incorporate. The 
syntax that worked for me is -

UPDATE table_name SET `column_id`=NULL WHERE `column_id` = '0000-00-00';

Thanks for the suggestion!

David ...

On 12/17/2012 7:16 PM, Jay Lozier wrote:
On 12/17/2012 05:35 PM, receiver wrote:
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 ...
I think the problem is with MySQL not the connector. I see this with 
MySQL also.

A possible work around is after the data has been imported to do the 
following:

UPDATE  tablename
SET           datefield IS NULL
WHERE datefield = '0000-00-00'

In principle this should work but I have tried on a database with 
'0000-00-00' as date nulls.

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.







--
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-tp4025045.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.