Error message when opening mysql database

[benst]

Hi,

Using NeoOffice 3.0.1p0 with mysql-connector-java-5.1.10:

I have some MySQL tables with "0000-00-00 00:00:00" as TIMESTAMP, and when opening these tables in Base I get the following error: "cannot convert value '0000-00-00 00:00:00' from column to TIMESTAMP", and it opens an empty document.

From googling this error message, it seems that setting zeroDateTimeBehavior=convertToNull in the connection string might fix this, but I have no idea how to do this.

Thanks for your help,
Ben

[pluby]

I have some MySQL databases on my Mac OS X 10.4.11 machine so I will see if I can reproduce the problem that you are seeing. If I can, I will then investigate how to get "zeroDateTimeBehavior=convertToNull" in the connection string.

I will post again soon with any results that I get.

Patrick

[benst]

Thanks for looking into it.

For your information, the db I'm talking to is MySQL 5.1.39 on a FreeBSD box.

Ben

[pluby]

OK. After I installed mysql-connector-java-5.1.10, I get the same error as you. Fortunately, adding "zeroDateTimeBehavior=convertToNull" to the connection string fixed the problem for me.

Here are the steps that I used to add "zeroDateTimeBehavior=convertToNull" to the connection string. Can you tell us if these steps work for you?:

1. Open your .odb file in NeoOffice and select the Edit :: Database :: Properties menu

2. In the Database Properties dialog that appears, add a "?" character onto the end of the text in the "Datasource URL" field. Please note, however, that if any character other than the last character in the text is already a "?", then add a "&" character.

3. Add "zeroDateTimeBehavior=convertToNull" onto the end of the text in the "Datasource URL" field and press the OK button.

4. Close and reopen your .odb file to force NeoOffice to reconnect to the database with the new Datasource URL.

Patrick

[benst]

Hmm, I must be missing something, because I don't see the datasource url field. I have two tabs in the database properties dialog: Advanced Properties (with name of the MySQL database and user name fields) and Additional Settings (with Host name, Port number, MySQL JDBC driver class, and char set fields).

Ben

[pluby]

I know why you are not seeing the Datasource URL field: you setup your database using the "MySQL (JDBC)" connection type instead of the "JDBC" connection type. The "MySQL (JDBC)" connection type is really the same as the "JDBC" connection type but it has custom properties dialogs to make setup more streamlined.

So, in your case, the steps are different. Here are the steps that should work for databases with the "MySQL (JDBC)" connection type. Do these steps work for you?:

1. Open the .odb file and select the Edit :: Database :: Properties menu.

2. In the dialog that appears, click on the Advanced Properties tab, append "?zeroDateTimeBehavior=convertToNull" onto the end of the "Name of the MySQL database" field, and click the OK button.

3. Save the database and reopen it.

Patrick

[benst]

Thanks Patrick, that works.

Many thanks for the quick response, much appreciated.

Best regards,
Ben