|
|
A short introduction of how to use, a MySQL Database using JDBC without ODBC.
At http://www.mysql.com/ you will find the database, a graphical front end: MySQLGUI and the newest JDBC-Driver named: Connector/J. Be shure to use a version above 3.0.3-Beta, because with this you can use Auto-Increment Fields und you can get full write-access from SO/OOo.
For Windows:
A very good choice to manage MySQL is PHPMyAdmin. For this you need a running WebServer with PHP. But it's simple if you use PHPTriad or FOXServer, since both Tools are made to install and configure, very simply, an Apache Webserver with MySQL, PHP, and PHPMYADMIN.
You can find them at: http://sourceforge.net/projects/foxserv bzw.
http://sourceforge.net/projects/phptriad
Here, I use PHPTriad because it is smaller and is all inclusive without the JDBC-Drivers.
After running the install-Program you have an Apache Webserver with PHP, PHPMyAdmin, and MySQL as the database. As Master-User is root without a password, this is not safe, but for a local PC very useful for learning. Later on, you can make Users and passwords, but after all is running and you have more experience with a real database and their features.
Start a browser and try: localhost, whether Apache and PHP is running. With PHPMYADMIN check the MySQL-Database.
If something is missing, start with Start => programs => PHPTriad => Apache and MySQL.
Also the programs winmysqladmin.exe, mysqlmanager.exe, and perhaps mysql.exe (out of a DOS-Shell) can be useful. You will find them in the /bin Directory at c:\apache\mysql\bin.
With these programs only, we have a running system, because the Frontend via PHPMYADMIN is quite capable and simpler.
For Linux:
In most Linux - Systems Apache, MySQL and PHPAdmin are included. Check your Manuals how to do this. If anybody writes this down, I can include it.
Both Systems:
Since OOo 1.0.2 we can build our Tables with OOo very well. The only thing to do before is to create a Database.
Now with PHPMyAdmin, we create a new DB, perhaps named: mydb
Since OOo 1.0.2 this is the only step that must be done before. But with PHPAdmin you have a good Tool to manage your Database, if something goes wrong with OOo.
Next you have to install the JDBC-Driver. You have got a file named: mysql-connector-java-3.0.3-beta.zip (or a newer version). This is a compressed file wich includes the binaries and the sources you need to compile them by yourself. But you donīt need to do this. If you donīt know how to unzip this file, take a look at FILZIP. Then you anywhere have a directory called: mysql-connector-java-3.0.3-beta There are the source-files and the Driver: mysql-connector-java-3.0.3-beta-bin.jar . This is the only file we need. Copy this file in a directory you want, perhaps to: c:\javaclasses which you created before. Or you can use the ../lib/ext/.. directory of your java-installation. If you do this, you do not need to set the classpath, because in this directory the java-environment looks for their classes. But you have to look for using the right java.exe on your system. On Windows sometimes their is a java.exe in your /system32 directory, rename or delete this file.
Only if you do not use the /jre/lib/ext/ directory:
OOo must know the path to find this driver. Under Tools => Options => Security you can switch the ClassPath to this file. Note that you have to take this file, not only the directory where it is.
After that we have to close OOo (also the Quickstarter in the Taskbar).
Now we can start OOo again.
Via Tools ==> Data Sources you get the Menu to configure the DataSources for OpenOffice.org.
Here we choose [New Data Source]
Now take [General]
Name: JDBCTest1 (=it is a free Name inside OOo)
Connection:
Database type: JDBC
Data Source-URL: jdbc:mysql://localhost/mydb
where localhost can also be a DNS-Name in the local-net or the DNS-Name of your own local PC. mydb is the name of the Database we created before with PHPMyAdmin.
Now choose [JDBC:]
JDBC Driverclass: com.mysql.jdbc.Driver
URL: jdbc:mysql://localhost/myDB?useHostsInPrivileges=false (normally inserted now)
Note: without the Parameter: useHostsInPrivileges=false you cannot get Write-access with the JDBC-Driver.
Username: root (when we have not changed the Users in MySQL)
Password required: we leave empty
Character Set: System (you can switch them later)
Under [Tables] you can create more tables, queries, etc. Save the Table under a good Name (without Blanks etc., e.g. MYTABLE1) and close the Database-Explorer. In this Menu, you come quite simply with the F4 Key.
Now you only need the right Settings in the tables_priv of MySQL. To do this, do a Right-Mouse-Click on your Database and choose SQL. Then you get a Window where you can send native SQL-Commands to your Database.
Insert a:
grant all privileges on mytable1 to root
(If your Username is Root and your TableName is MYTABLE1 as I said above).
Note that you have to do this GRANT-Statement on all Tables that you wish to write with OOo.
So you now have a running MySQL-Database via JDBC and can use it for your Work.
If something here is wrong or could be made better, let me know.