Connecting to mySQL in Open Office Using a Macro

These are instructions on how to connect to a mySQL database using Open Office version 3.0.

Step 1:

Select tools and options to verify that you have the Java JRE installed. After you open up the options window, select Java from the list on the left. You should see a selected JRE version.

Step 2:

Downloaded the mySQL jar file.

After selecting Java from the left list, you should press the Class Path. After selecting the Class Path button, you should select add archive and browse to the mySQL Jar file that you downloaded in Step 2.

You should reboot your computer.

Step 3:

You now need to create a database. From the file drop down menu, select File and New and select the Database wizard.

You should make sure your mySQL is running.

You should select "Connect to an Existing Database" and select mySQL from the drop down list. You should press next. You should connect using JDBC and press Next. You already should have a database created in mySQL. You should enter the database name and the server URL. If you are connecting to the mySQL on the computer you are currently using, you can put localhost. Your port number and JDBC driver should remain the default. You should select test to test your JDBC driver. If your connection is successful, press next. Now you should type in your user name and select the password checkbox. That will prompt you to input a password. You can register the database here, or you can do it later. I will show you how to do it later. I am assuming you are saying "do not register the database". Now you should press finish. You should save the database file some where on your computer. This file that you save will be referencing your mySQL database. You now will be prompted to input your password.

Step 4:

To register your database, you should enter the Tools and Options again. Under OpenOffice.org Base, you should see Databases. You should select Databases and browse to the Database file you just saved. You will need to remember the registered name which will be the name you will use to reference your database in your Macro.

Step 5:

Create your macro and paste this code in it:

sUser$ = "UserBook"
sPass$ = "UserBook123"
sURL$ = "sdbc:mysql:jdbc:localhost:3306/catalog"
dataSourceName$="catalog"

DBContext=createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource=DBContext.getByName("TestDB")
ConnectToDatabase=DataSource.GetConnection (sUser$,sPass$)

SQLQuery= "select bookname from book"
SQLStatement=ConnectToDatabase.createStatement
RowSetObj=SQLStatement.executeQuery(SQLQuery)
oSheet = ThisComponent.Sheets.getByName("Sheet1")
ThisComponent.CurrentController.setActiveSheet(oSheet)

While RowSetObj.Next
i=i+1
'RowSetObj.getString(1)
oSheet.getCellByPosition(0,0).setString(RowSetObj.getString(1))

Wend

ConnectToDatabase.close
ConnectToDatabase.dispose()


The "TestDB" is the registered name of your database. It is not the name of your database in mySQL, but the reference to the database. The sUser and sPassword is the user name and password to the database. You will need to modify the query information to test your database connection.