[Solved] Export from OOo to MySQL

[vstarc16]

Due to the data loses (Emergency department database, running 24 h a day) mentioned in my post regarding the problems with exporting CSV file I have to migrate data from embedded HSQL database to some server, my choice was MySQL. I installed MySQL 5.0, JDBC and ODBC driver and migration kit, but I did not find a way to directly export data form OO to MySQL. I also tried exporting data as CSV, then importing into the Access, but could not transfer them (single table) into the MySQL, due to the problem in a set-up of MySQL (my guess based on error message).

I am MD in small ED, and I do not have enough knowledge of administering databases, nor I have time to spend days looking for information I need or help, and at this moment I am considering moving database file to MS Access (at least I manage to do that). I am not very fond of this idea because I really like how OO works with forms, but simply could not risk more data losses. My question is it possible to migrate data from OO directly to MySQL (there is no such an option offered, maybe it coud be done as generic JDBC?), and how to do it. I found a lot of posts on OO Forum and OO community forum related to connecting OO to MySQL database, but nothing useful of transferring database from OO to MySQL.

System: OO 2.3 (could change to 2.4 if it would help) on Win XP machine.

I am hoping for some help.

Vanja

[r4zoli]

Due to the data loses (Emergency department database, running 24 h a day) mentioned in my post regarding the problems with exporting CSV file I have to migrate data from embedded HSQL database to some server, my choice was MySQL. I installed MySQL 5.0, JDBC and ODBC driver and migration kit, but I did not find a way to directly export data form OO to MySQL.

You installed MySQL into server or standallone machine?
You must create a database and set up at least one user with all privileges on that database, then connect to this database by OOo new odb file, open existing database, drag table from odb with data and drop into new MySQL connected file, table part. Install MySQL Administrator for creating new database and user.

I also tried exporting data as CSV, then importing into the Access, but could not transfer them (single table) into the MySQL, due to the problem in a set-up of MySQL (my guess based on error message).

This is the same, create new database and user.
Install 2.4.1, any way it have several security bugfixes. Use MySQL Connector, it is in dvelopment phase not for every day use but usable on simple situation, an don't needs any driver, only one extension (for win).

[vstarc16]

Thanks for guidance r4zoli. MySQL is installed on standalone machine, that is part of only two computers network, In next two days I am going to implement it and shall post results on saturday.

Vanja

[vstarc16]

OK, I am back. As r4zoli suggested I installed MySQL GUI Tools (Query browser, Administrator) made database and associated user to it. Then I connected it to OO via JDBC and then copied tables from my ER database to the MySQL. It was not smooth as described here but I did it,
But what I have found out that I still have problem with Croatian characters, although not with all 10 but with only 6. Problem is in MySQL character set because I tried to edit table directly in Query browser, and it would not accept (invalid character message) characters č, ć, đ and ther capitals. I tried to switch default character set from Latin1 to Latin2 but it was not helpful either.

So now I have problem with MySQL rather than OO: I could transfer tables but it is of no use because character sets does not match. I shall try to look for solution on Croatian SQL forum .

Vanja

[r4zoli]

latin-1 and latin-2 not a best solution, not all accented charaters handled by these codings, use utf-8.
MySQL Admin Startup Variables>Advanced>De. char. Set:

[vstarc16]

Huh, I am back after vacation. As Zoltan suggested I set encoding of mysql to utf-8 and found that than mysql handles all, but 'Đ'/'đ' Croatian accented characters well which is not bad, because 'đ' could be substituted with 'dj'. But despite of change of encoding when I copy table to mysql utf-8 encoded database from my HSQL database it still properly displays only certain accented characters ('š', 'ž') all other are represented by '?' - that is the same as if I use Latin1 or Latin2 with Croatian Collation. I am not certain does this belongs to OO issues but I doubt that problem is somewhere in OO, because mysql handle Croatian characters almost totally well with utf-8 encoding.

Vanja

[r4zoli]

On Windows use East European Code Page 1250, which is a default for most Win systems in this area.

[vstarc16]

Selecting Code Page 1250 solved the problem of Croatian characters. I successfuly migrated data to MySQL and connected Base to it. I hope that there would not be new data losses in case Base crushes.

Vanja

[HappyGuyEpicure]

I tried the export from OO to MySQL following these steps exactly, however there is the following error:

SQL-Status: 42000
Fehler-Code: 1049

Unknown database 'stefan@localhost'

How can I make it work?

[r4zoli]

The user rights must set correctly in MySQL for the user which you try to use.

[HappyGuyEpicure]

And how do I do that?

[r4zoli]

http://dev.mysql.com/doc/

[HappyGuyEpicure]

Sorry, but I still have no idea what's wrong.

I just create a new user and give that user all rights on my new database. But still I get the same error.

[rudolfo]

Are you saying you still get the error: Unknown database 'stefan@localhost'?
This indicates that you haven't configured your connection string in the correct way. The connector (which one are you using? native? JDBC?) attempts to use your user name as database name.
If I configure JDBC with a non-existing database name, I get the same error code, but my user name doesn't show in the detailed error message:

Code: Select all

SQL Status: 42000
Error code: 1049

Unknown database 'no_name'

[HappyGuyEpicure]

Thank you for your answer!

Yes! I still get the same error:Unknown database 'stefan@localhost'?

I am using the native connection my MySQL Connector 1.0.1 . What is a connection string?

In the connection dialogue I enter the name of a scheme. Is that not the same as a database? I am sorry but I am not a programer and the abstraction level is quite high..

The strange thing is that the connection test works and I can even create new tables in mysql from oo. The only thing that does not work is exporting tables from oo to mysql, but that is the only thing I want to do..

Surprisingly, the opposite way works perfectly.. I can drag and drop SQL tables to oo and export them without problems!???

It's very puzzleing....

[rudolfo]

How do you export ... if it is not working? Dragging table icons from one .odb document to table icons of the other .odb document?