I decided it would be fun to write down a few simple tips on how to use MySQL in Office applications. Yup, you heard me: using MySQL as "Desktop Database", quite like MS Access is being used on many, many windows desktops.
Of course, I do not want to suggest that MySQL "...is a Desktop Database", because it's not. Rather, MySQL is a general-purpose database, so there's no reason at all to dismiss it for typical "Desktop Database" purposes.
Even though MySQL is a server product, MySQL resembles a typical Desktop Database in a number of ways. For example, MySQL is not extravagantly resource-hungry, and does not require much maintenance - (not for this purpose anyway). So, there's not much against using MySQL here.
In fact, it's probably a good idea to back your Desktop Database applications with MySQL. A lot of the typical problems (resource contention due to locking, file corruption due to crashing application programs, scale limits, and much much more) that you encounter as these applications grow larger, are either not-existent or easily solved using a server solution such as MySQL.
So - let's dive in, and see what it takes to use MySQL in the daily office. Oh yeah - I once did a write-up on connecting MS Access and MySQL. This time, we won't be using MS Office at all. We'll use OpenOffice.org instead.
Some Prerequisites
All products described here are available for most popular architectures and operating system. Both MS Windows and Linux users should be able to get this to work.
The client computer should be able to directly access a local or remote MySQL Server instance. You need to know the hostname (or IP address) of the MySQL Server host. If the MySQL Server is configured to use a non-default port, you must know the portnumber too. You also need the name of the database you want to access. Finally, you also need to know the user name and password of an existing user that has privileges to acces the database.
I'll be using the sakila sample database in the following examples. It's not absolutely necessary to install it if you already have another database to play with. If you want it, you can download it here.
Client Software
Before you start, a few things need to be in place on the client computer. It may already have these items, or you might have to download and install them:
A Java runtime environment (jre) or Java Development Kit (JDK, if you also want to develop Java programs). In most cases, your operating system ships with some pre-installed implementation. However, I found out that a pre-installed implementation might not be exactly what you need. Personally, I'm quite happy with Sun's own implementation.
The OpenOffice.org suite. You can download it here or look for a package tailored for your operating system yourself.
The MySQL JDBC driver, Connector/J. You can download it here.
As for the MySQL JDBC driver: there's not much to install. Just unpack the download in a location you're comfortable with. You will find a
.jar (Java archive) in the unpacked directory, and you will need the full path and filenename of the archive. I like to keep several versions around of JDBC drivers, and to make life easier, I created a symbolic link named
mysql-connector-java.jar to the latest version of the .jar file. That way, I can easily upgrade or downgrade as needed by flipping the symbolic link to another location. You don't need to do it this way - the symbolic link is purely for my convenience. (So what do you do with your JDBC drivers? Where do you store them? How do you upgrade?)
Configure OpenOffice.org to run Java
First, you need to enable OpenOffice.org to use Java. To do that, follow these instructions:- Open one of the OpenOffice.org applications, such as Calc or Writer.
- Use Menu \ Tools \ Options to configure OpenOffice.org
- In the Options dialog, find the "Java" Category. Check the "Use a Java runtime environment" checkbox. You might see a list there of the Java runtime environments available on you system. It seems to automatically pick up all the Java runtime environments I installed in
/usr/lib/jvmbut I don't dare say it will be the same on your system. However, if you don't see any Java runtime environments there, and you are sure you installed one, you can use the "Add" button and browse to the corresponding directory:
(Note that if you add or change a Java Runtime environment, you will be prompted to restart open office:
Configure OpenOffice.org to use MySQL Connector/J
Although OpenOffice.org is now able to run Java, it is still unaware of the MySQL JDBC driver. Follow these instructions to enable OpenOffice.org to use the MySQL JDBC driver:
- Open the OpenOffice.org options dialog, and select the "Java" category as described in the previous section. If you followed the instructions in the previous section, the dialog should still be open.
- Use the "Class Path..." button to open the Class Path dialog.
- In the Class Path dialog, add a reference to the MySQL Connector/J .jar file using the "Add Archive..." button:
- Close the OpenOffice.org options dialog, close the application if you are prompted to restart.
Ok, now we're all set. Everything discussed so far was just one-time preparation, and you won't have to go through it again (except perhaps after upgrading java or OpenOffice.org).
Registering your database with OpenOffice.org Base
OpenOffice.org 2 includes Base, a MS Access like "Desktop Database" application. OpenOffice.org Base acts as a front end for a variety of datasources commonly found in office environments. Typical examples include text documents, spreadsheets, and LDAP addressbooks.
Base can also be used to design and develop simple relational database applications using the HSQLDB database embedded in OpenOffice.org. Base also provides the means to connect to remote rdbms servers through an ODBC or JDBC connection. Right now, we will explore this last usage of Base.
Follow these instructions to register your MySQL database in OpenOffice.org Base:
- Start OpenOffice.org Base to enter the "Database Wizard".
- In the "Set up a MySQL Connection", select the "Connect using JDBC" radiobutton:
- A dialog appeart to specify the connection details for the MySQL server to which you want to connect:
- Before filling out the remaining details, we should test if we set up the MySQL JDBC driver properly. Hit the "Test class" button to do that. If the driver can be loaded, you should see the following messagebox:
- In the "Name of the database" field, type the name of the schema (database) you want use. It is required to specify a database: you cannot access other databases that might be managed by the Server simultaneously from one "Base" database file.
In the "Server URL" field, type the hostname or IP address of the MySQL Server host. You can use the "port number" field to specify a non-default port if necessary, but if you did not configure the MySQL Server to use another port than the default one, it should remain3306. - Type the name of a user that can access the database. Check the "Password required" checkbox of the user is required to identify itself with a password.
- Hit the "Test connection" button to verify that the connection can be established.
- Hit the "next" button to finish off:
- Finally, we can hit the "Finish" button. You are prompted for a name and a location to save the "Base" database file.
If you followed all the instructions, the "Base" main screen will open, and you'll see something like this:
On the left side, we have a sidebar that shows the four different kinds of things you kan work with in "Base":
- Tables - here, you'll find the base tables and views defined in your database.
- Queries - here, you can define database queries, and save them for repeated use.
- Forms - you can use forms to create simple database applications.
- Reports allow you to layout data for presentation purposes
The right, main area of the window shows a task pane (top) and a content pane (bottom). When you select an item in the sidebar, the task pane shows all the tasks you can perform with these types of objects, and the content pane shows all instances available for this type of object.
Great! Now what?
Working with Tables
One of the great strengths of "Desktop Database" applications is ease of use - even for non-technical users. For example, once your database is registered in Base, end-users can directly open a table and work with the data it contains. Let's try that right away.
In the sidebar, single click the "Tables" icon to select it. The "Tasks" pane shows a few taks to create new tables, and the content pane shows all tables in the "Sakila" database. In the content pane, double click the "actor" table. You should see something like this:
Ok, nice. This little grid gives us direct access to the table data. Navigation is quit straightforward: use the mouse to point and click an arbitrary cell in the grid. Use the "tab" key to move to the next cell. Use shift+tab to navigate to the previous cell. The "down" arrow key takes you to the next row, and the "up" arrow key to the previous row.
Changing data is easy too: just navigate to cell and type to edit the data. If you're not happy with the edits, use the "Undo" toolbar button to undo them. Navigating out of the row will permanently store the edit. If you are afraid to accidentally change the data and uncheck the "Edit Data" checkbox on the "Edit" menu.
How about adding a new row? Scrolldown to the last record in the grid, and find the empty row at the end. Navigate into it, and edit. A new empty row is automatically appended. Delete is also easy: click on the left before the first field of the row to select it, and then hit the "delete" key.
The grid also some functionality that might help the end user to work with the data in a more effective manner.
- Filtering - only those records are retrieved from the underlying database that match a condition. Use the toolbar buttons with the funnel icon to filter data.
- Searching - the local set of rows in the grid is searched for records that match a particular condition. Use the toolbar button with the looking glass to search for particular records.
- Sorting - the local set of rows in the grid is reordered according to a sorting key (and order).
My impression of the "Base" data grid is that it is a very useful tool to quickly inspect or enter data. It is far from perfect, and it is also unsuitable for normal application development, but it sufficient for maintaining and entering address lists and the like. Most end-users that are used to maintaining such lists in a spreadsheet application will be able to effectively use this interface instead. Also, besides registering the database in OpenOffice.org, it requires no effort at all to obtain this interface: no coding required.
Yeah sure, nifty grid. Now what?
The "Base" application offers two more items that can be interesting for end-users in an office environment: Forms and Reports.
Forms can be developed to build small end-user applications. For a number of reasons, the data grid just is not suitable to build a genuine end-user application. A lot of the limitations you have with grids can be lifted by creating an suitable form. I will discuss this topic on another occasion more thoroughly, and demonstrate how this can be done.
Reports are useful if you want to present data to the end user in a particular layout or grouping. Reports are also useful if you want to export your data to a pdf format, or if you want to print it. Personally, I think that "Base" reports are less suitable for typical analytical BI applications.
Databases registered in "Base" can be used throughout OpenOffice.org. So, although "Base" offers some functionality which might be used in an office environment, it is really only the start. For example, if you want to perform some kind of analysis using the OpenOffice.org spreadsheet application "Calc", you can draw that from one of the registered databases.
Another typical example for the OpenOffice.org wordprocessor "Write" would be to combine data from the database with letters, or address labels to automate postal mail communication.
Right now, I won't discuss these applications in any detail, but I'll try and write up a tutorial on those subjects soon. Of course, if anybody else wants to share their story on integrating their database in open office, please do not hesitate and add a comment to this post.
68 comments:
Thanks Roland,
great article about a topic that is rarely mentioned. I look forward to more articles involving MySQL and OOo.
Keep up the good work!
K<o>
Supporting non technical users through Plan-B for OpenOffice.org
Hi Roland, thanks a lot for this great article! This is something I was looking for all the time. I added a link to your document to the
MySQL Forge Wiki now.
Bye,
LenZ
Great little article. I've used ODBC->MySQL before in a much older copy of OpenOffice. As of version 2.1 the Base application itself it much improved and make a nifty interface to a remote MySQL server now... excellent!
Thanks a lot,
after 3 years of trying to connect OOo to MySQL in Windows I finally succeeded, thanks to your clear article.
DiGro
hi,
Is there a possibility to use BOTH local database AND remote datasource (via JDBC or ODBC, MySQL for exaple) in the same database (like in m*.* access "get external data" feature)? I miss it, or I miss something obvious ;-)
Raphael
Hi People,
thanks all for yur kind comments, and my apologies for a late reply (I was on vacation - miracles do happen!)
Raphael: Good Question! (That means I only have bad answers ;) I tried, and it looks like you can't. So, it's different from what you see in Microsoft Access, where you can have local base tables and 'linked' tables side by side. However, I'm not an ooo expert so I maybe wrong but so far, I have been unable to do this.
Hi Roland, this is a subject I'm keenly interested in. I'm a relative newbie to databases on a website but want to use one on a new one I'm setting up. I want a MySQL database at the hosted site and was thinking of using OO Base to be my front end for populating and working with the data. The reason for this is I'll probably have a variety of people making their own entries with Base and sending them to me to add to the MySQL database. Do you think it would be worth having a local MySQL database or just connect OO to the hosted one?
This is all encouraging. Thanks!
Hi Dan,
thanks for your comments. Your question is quite interesting as I can imagine that more people are struggling with the question of how to actually make good use of a Base front end.
Ok - your question has two important facets:
1) there's a remote MySQL database that powers the website, which is the final target.
2) a variety of people provide either directly or indirectly input for the remote database.
You yourself suggest a solution where the external people make their changes in their own copy of Base, and send that to you. If I understand correctly, you are wondering whether you should have a local MySQL database at your home to collect all the changes in before you send them to the remote MySQL database, right?
Loading the changes that you receive in a local MySQL database can't hurt. In fact, it is an excellent way to test of the data can be loaded at all in the remote MySQL server. This set up will even enable you to do some extra checking.
For example, suppose your remote database uses only MyISAM tables. (This makes sense, as it will power a website and will probably have a lot of read activity.)
The structure and data of your local MySQL database could (and should) be an exact copy of the remote one, except, for the local one, you could use InnoDB tables (or any other transactional storage engine such as Falcon, SolidDB or PBXT).
Depending upon the amount of data being delivered to you, you could use one transaction to read in all the changes corresponding to one delivery. This ensures that all changes must fit into the database, or else, no changes will be made. Maybe it's better for your particular application to use one transaction per table or to use some other unit, but you get the idea.
Also, using InnoDB for the local database will allow you to use foreign key constraints to perform an extra check on the data.
There is one thing I'd like to mention. You must be very careful with how you will reconcile the changes delivered to you with the MySQL database. At first, it seems one would want to only receive and merge the changes found in one Base document with the data in the MySQL instance. However, this is much more difficult than it seems at a glance.
To illustrate why this is so difficult, think about this for a while. Suppose you have a person that delivers you a Base database for the first time. We find one record in it, and clearly,we must add that to the MySQL database. Now, next week, he delivers the Base database again. This time it has 2 records in it. Since the last time, he deleted the first original record, and added 2 new ones. We know that the desired result of a merge will be to add the 2 new records, and the delete the old one, but how are you going to detect the DELETE?
Of course, this problem is quite solvable. In the simplest case, it means that for each person that delivers a Base database to you, you must always recieve their entire database. You should also be able to remove all of that persons data from the MySQL database, and then read the new delivery into the MySQL database. This is the simplest method. In many cases it will also be the fastest way.
More subtle methods are possible too, and this involves creating the Base application so that it records all changes (all INSERTS,DELETES,UPDATES) happening to the data, and making an audit trail of that. The audit trail is then used to recreate the changes. However, it's best not to consider this method unless you need it. It is far from simple.
Another approach you might want to consider is this. You could try and setup your local MySQL server, and allow outside access to that. The people that provide the changes will then simply use their Base instance as a frontend to your local MySQL instance.
Of course, you will need to set up security measures to prevent unauthorized people from accessing your database and making a mess or wrecking it. Depending on what you regard to be secure this could involve a lot of things, but I'd go for at least SSL encrypted JDBC connections (http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-using-ssl.html), proper user and privileges definitions (http://dev.mysql.com/doc/refman/5.1/en/grant.html) and some form of firewall.
When considering this set up, you might ask yourself 'why not do away with the local MySQL database, and use the remote one instead'. Well, assuming you can't benefit from locally checking the data (depends on your application if that approach works for you), a lot of service providers will not allow direct access to your mysql database. Instead they often provide only a phpMyAdmin web interface or so. If they do provide direct access, and you can use SSL encryption for that, you might consider directly connecting your users to the remote database.
Good luck, and let us know how it went. Kind regards,
Roland Bouman
First of all thanks for this article.
I was trying to get this going the whole morning until i found your article.
it is not working yet because of this:
"
SQL-Status: S1000
Unknown initial character set index '48' received from server. Initial client character set can be forced via the 'characterEncoding' property.
"
Database and Settings in Openoffice are both set to UTF-8 so i don't know where to continue...
-dan
Hi dan,
first, let me say that I never encountered this problem, so my suggestions might be completely off.
However, my guess is that there is a mismatch between the character set used by the mysql server and the jdbc driver connection. I don't think -or at least I think it's likely- that the OpenOffice.org character settings have no bearing on the configuration of the Connector/J jdbc driver. They will almost certainly have no effect on the settings of the remote mysql server.
I think there are a few things that need to be done to troubleshoot this problem. I think it's best if you read up on the subject here:
http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-charsets.html
That is:
-online mysql manual
--Connectors
---MySQL Connector/J
----Connector/J (JDBC) Reference
-----Using Character Sets and Unicode
Tip: Googling "Using Character Sets and Unicode" (use the quotes) returns it as the first link.
You need to find out what the version is of the mysql server that you are connecting to using Connector/J. There is a difference in character set handling prior to MySQL Server 4.1 as compared to newer versions.
Also, find out exactly which version of Connector/J you are using. If you have an older version, try and upgrade Connector/J.
Don't forget to check your OOo settings too! Maybe in OOo is configured to use an older version of the driver - this is discussed in the third section of my blog entry, "Configure OpenOffice.org to use MySQL Connector/J"
You can try and see if it helps to perform some additional coniguration on the connection too. To do that, open the Base file that's registered to connect to your MySQL database. Then, in the menu, navigate to:
Edit/Database/Properties
This opens a dialog with two tabs. In the "Additional Settings" Tab, you can choose a particular Character Set. My suggestion would be to choose 'utf8' here, but maybe you've done so already? Other options in the Edit/Database menu look promising but don't lead to any solutions afaics.
What would really be nice if OOo would allow us to access the Connector/J driver options (google, with quotes: "Configuration Properties for Connector/J", first link). If that would be possible, you could explicitly set the characterEncoding property mentioned in the error message.
I do know a way around it, but it would involve connection Base to a generic jdbc database instead of a mysql database. Essentially, it's the same thing.
Quick start for connecting MySQL as generic jdbc database:
As first step under the heading "Registering your database with OpenOffice.org Base" in my blog entry, choose "JDBC" rather than "MySQL". For the datasource url, type:
jdbc:mysql://$host[:$port]/$db?characterEncoding=utf8
with $host the host name ("Server URL" in the blog entry), the optional port entry $port (3306 by default, "Port Number" in the blog entry) and $db the database name ("Name of the Database" in the blog entry).
PLease try and post back to let us all benefit from your experiences solving this issue.
kind regards, and good luck,
Roland Bouman
I am by no way an expert in this field so bare with me if i say some obvious wrong things.
here we go:
i use XAMPP because i am lazy/have no time at home, which uses 5.0.27 MySQL Server/Client.
I downloaded mysql-connector-java-5.0.4-bin.jar from the MySQL website.
Open Office version 2.1.0 which uses the SUN JRE 1.5.0_02.
I created a new database from scratch to ensure it is completely encoded in utf-8. My old one started out with another encoding which i changed completely by foot/hand so i might have missed a piece.
I also created a new user for the database with following rights: SELECT, INSERT, UPDATE, DELETE, FILE . These should be enough since i won't do more in Open Office then showing, adding or deleting data entrys.
After i did that i got a exception message and thought that it can come only from the JDBConnector. I downloaded and tried the ODBC connector and it works as far as i can see but im not sure if i want to stick to it. But it works! Are there any things to watch out for when using the ODBC Connector. I read something about indices which shouldn't be a problem if every table has a primary key. But if it has not? As far as i know indices are only some kind of efficency tool, right?.
-dan again ;)
Hi dan!
No need to be an expert, and no need to be afraid to ask the wrong things. Wrong questions are the ones not being asked ;)
Your software and databse setup looks fine - I don't expect that to be the problem.
"Are there any things to watch out for when using the ODBC Connector?"
Well, I tend to use JDBC where possible because it's more portable. I have heard rumours about JDBC being faster than ODBC, but my hunch is it all depends. For a small database and modest queries, it should not matter too much. In other words, if ODBC works for you, go ahead and use that!
"I read something about indices which shouldn't be a problem if every table has a primary key."
For the OpenOffice.org Base application, you will need primary keys anyhow. That's because Base must have some way to tell MySQL: "Hey, I want you to change THAT record". A primary key makes it possible to identify one particular record in a table, and there are frankly few if any things you can do with a database without some means to identify the rows in a table.
"But if it has not?"
Base will complain when you try to add, edit or delete such a record. Try it, it's insightful.
"As far as i know indices are only some kind of efficency tool,right?."
An index is indeed an access structure. It's quite like an index in a book: a list of single values that you want to be able to browse quickly to look up all the places where it is used.
A Primary Key is not the same as an index: rather, a primary key is a CONSTRAINT (a restrictive rule) that specifies that items are identifyable; this does not automatically mean that there must be such an associated access structure.
However, all database products I know always use an index to be able to enforce a primary key constraint. In mysql, there is not even a way to physically separate a primary key from the index that implements it, but in other database products (Oracle), the distinction is more clear.
Could you possible try and connect to your database using JDBC like I described in my reply yesterday? Just for the hell of it to see if my suggestion works.
Thanks in advance and kind regards,
Roland Bouman.
I tried it both ways, including your suggestion with same effect. Must be something one my side going wrong. I think i will stick to the ODBConnector until i find a solution.
I'll post it when i found out what the problem was.
Thx again for this article and your quick and insightful answers.
Much appreciated!!
-dan
Hello Roland,
Weet u soms een manier om die URL te pakken te krijgen ?
server URL : van de velde gilbert
Geschreven op 20-02-2007 17:48:28
ik wil vanuit open office een verbinding maken
naar MySQL, hiervoor heb ik de URL van de
server nodig. Waar kan ik die vinden of verkrijgen
?
server URL : ONE.COM NEDERLANDS PS
Geschreven op 21-02-2007 09:17:40
Geachte heer,
Dank u voor uw schrijven.
U kunt alleen De database benaderen vanuit
uw website en/of PhPMyadmin.
Externe toegang is niet mogelijk.
We vertrouwen erop u hiermee voldoende geïnformeerd
te hebben. Mocht u nog verder vragen hebben,
kunt u uiteraard altijd weer contact met
ons opnemen.
Met vriendelijke groet / Best Regards
One.com Support
----------------------------------
One.com
Website: http://www.one.com
Thanks Roland - the OpenOffice documentation was giving me a headache; your article got me working in five minutes, no hassles.
Thanks again for sharing.
Dear Roland,
After a month of experimenting with Access 2003, 2007 and Oo Base, this morning i decided to use your approach.
Before i read your article i had already installed MySQL 5.0.3 Oo 2.2.0 and ODBC connector. I was disappointed by the delay that ODBC Connector had. After installing JBDC everything's perfect! The speed is really incredible. Thanks
Roland,
nice clear article. Wish I had read it first. I was almost there, except OO didnt mention where to get jdbc, so I may have contaminated my JAVA Classes settings - as I have all the JAR files of OO itself (i.e. the folder) and test jbdc always fails.
How can I delete the classes entries and start anew. Uninstall of OO 2.1 and install of OO 2.2 dint clean it up, and even searching for timestanped file in both java and OO program directories I have not found it yet.
And google search just keeps bringing me back to your article, hence - my question here!
Thanks,
Michael
Hi Michael,
glad you liked the article! Thanks for your kind words.
I admit I'm not too much of an OOo guru - I have never really dealt with installation issues.
That said, I don't think I understand entirely what you did with your classes. Did you copy files somewhere?
If OpenOffice itself is still running, you should be ok I think.
What error message do you get for your test?
Ah, Windows is wonderful. Come back a day later, and it works. :)
Hi Roland
Thank you. I've been trying to figure out how to connect my spreadsheet to mysql and this explained it very nicely.
Steffan
Roland;
If it weren't for your kind offering in this tutorial, I would have never set this up correctly. Thanks a million.
Wayne Bjorken
Thank you!
I got exactly what I needed from this article!
This is a very informative article and I have to say cudos to those of you who assist those who know not such as myself, I have yet to try to connect open office to mysql however I was wondering how I would go about it and I believe you have answered my questions. Thank you very much for the time you donated to assist me. "perhaps not personally but you did have to donate your time to this article and I wish to show my appreciation"
Excellent : used to do this between MS Access & MySQL with MySQL ODBC, but request admin rights to install MySQL ODBC driver.
With OpenOffice and JDBC you can freely do this : PortableOpenOffice (http://portableapps.com/apps/office/openoffice_portable) you can install Base in your home or USB key, all the same with JDBC jar file.
Only trick is for the JRE : need to install somewhere you've admin rights, then copy the location given at install time on your USB disk/key : in OpenOffice when requested to select Java options, you just select Add button and point to your JRE location, and it works.
Thanks for sharing Roland !
Hello Roland,
Your article seems to be what I was looking for..
But I'm having a problem... when I add the JRE with the class path in the Java options by scalc, everything seems to be ok. But when I close the options, leave scalc open, and reopen the options, the ''use a JRE'' checkbox is unchecked and there's no more JRE in the window. I work with the following versions: windows XP, jdk1.6.0_04 and Open office 2.3.1 (latest versions...). I suspect a bug in OOo and would like to try with an older version. Do you have an idea of my problem? Do you know where I could get an older version of OOo? I have tried with different versions of Java but they always disappear anyway...
thanks you very much,
Remi
Hi there!
Yeah - it sounds like a bug anyway. Unfortunately I don't know where to get older versions of OOo.
I guess I would join one of the open office IRC channels on freenode and ask there if somebody knows something.
Good Luck!
Remi,
When try it with calc in ooo 2.3.1, I cannot simply close the dialog and leave calc open - it wants to restart open office.
I am running it on Kubuntu Gutsy. What's your OS?
another use for this took me long while to ferret out. The default Hsql? engine in base doesnt have rand(). This creates a random number for you.
Failure after failure in trying to randomize some database with what I thoght was valid SQL. So I ran through most of this tutorial on setting up the environment.. and POOF it worked just great.
So if you want the environment you're more familiar with, this is a great way to get it.
Hi David b,
thanks for the comment! I appreciate it a lot.
I just googled a bit and it turns out you should be able to do this semi-natively in HSQLDB:
CREATE ALIAS RAND FOR "java.lang.Math.random"
See: http://hsqldb.org/doc/guide/ch09.html#create_alias-section
Interestingly, the syntax docs say it should be a built-in:
http://hsqldb.org/doc/guide/ch09.html#N1251E
wow.. an answer so quickly.. thank you!
I googled extensively, using every perm. of openoffice base rand and a fwe other terms.. and came up essentially with only that doc page saying that indeed HSql does have rand() in it. ..all the more frustrating then to have it act just like it doesnt.. hehe
You found an interesting solution with the alias. I kinda figured that using a call to an outside language like that would work, but Ive never had necessity to do it. Also saw another solution for Base that specifically used a macro to similar effect.
..yours is much better.
Anyway.. thanks for the ideas.
Hi there,
Using your instructions, I was finally able to get OpenOffice 2.3 Calc to connect from my Centos 5.1 desktop machine to my remote MySQL database, also a Centos Linux 5.1 Box.
The problem I am having, is that when I open up a large table with 400 rows and maybe 25 columns in it (a table for products in an on line shopping cart), the scroll bars move down the table extremely slowly, and after a minute or two the table freezes up, as does OpenOffice, and I end up having to CtlAltDel and restart Open office again.
From a different desktop machine running Windows 2000, I am able to connect to the very same database using Navicat and I don't have this problem, I can scroll through and edit data very fast with no crashing.
Any suggestions as to what I can do to solve this problem? I'd like to start using Linux exclusively if I can get OpenOffice 2.3 to work without crashing. Maybe I just don't have my Linux desktop and or openoffice configured corectly?
Hi Anonymous,
What jvm are you using?
Hi,
I am using the jre1.6.0_05 32 bit version for Linux.
Hi Anonymous,
I asked about the jvm because I had bad experiences with the jvm included by default in my distro. I use the sun JVM as well, so that should be ok.
I just created and registered a new db pointing to my local MySQL and I created a 16000+ row, 18 column view in there to test.
I am not really sure in what OOo view you are looking, but inside Calc, I can scroll pretty comfortable through the entire set, provided I scroll once to the far end (using the last button in the "Data Sources" view)
I am also not experiencing the freezing up you mentioned.
Importing the data into Calc does take a little while, but not more than half a minute.
May be I am not looking in the right view. Can you please describe in detail how you hooked up Cacl and MySQL? Personally i haven't really spotted something in Calc to "link" to the data rather than import it, maybe you have found a way that I am not noticing?
kind regardsa,
Roland
Hi there,
I connected in two ways. First way was to go to File>New Database, and then go through the dialog boxes, I chose connect to a new database, chose MySQL, then selected JDBC, then entered in the database name, then the server URL, left it at port 3306, left the com.mysql.jdbc.Driver default as is, then entered in the user name of the database and then clicked finish. I also click the test connection button and it tested OK.
Second way was the same except I entered in localhost, after I issued the command ssh -L 3307:localhost:3306 username@myservername.net so that I could get an ssh tunnel for secure connections to database.
I have the same problem in both scenarios. In both cases, I had to disable SELinux because OpenOffice won't run with SELinux enabled.
I entered in localhost for server URL in Calc - after I ran that command in Terminal that is (for the second case).
Maybe the problem is, that I need ODBC driver instead of the JDBC driver? I don't think I have the ODBC driver installed.
Or, is the mysql-connector-java-5.0.8 driver the same as the ODBC driver? I do have the sql-connector-java-5.0.8 driver; I set up OpenOffice to point to that one as I did for the JDBC Driver.
Roland
I cant get past step 3 on the wizard when I hit Test Class I get JDBC Driver could not be loaded but I have followed your instructions meticuously. Any Ideas
Thanks
Adam
Hi Adam,
I am not entirely sure which step you are referring to exactly - the steps are not numbered in my post, but you refer to "step 3"
What's the location of your mysql connector/J jar file?
Did you register it in Open Office?
Roland
By step 3 I mean the third page of the wizard where the Test connection button is.
The mysql connector/J jar file is located at C:\Program Files\Java\
MySqlConnector\mysql-connector-java-5.0.8
Thanks
Adam
Oh and yes I did register it in Open Office
Adam
Adam, can you send me an email with the Open Office Options/Java/Classpath dialog?
Can you also pls try and put the connector in a shallow path without spaces, like C:\temp\mysql-connector-java-xxx.jar
and try again?
thx for your tutorial~ it's very userful for me...
thx agine ! :)
Your article might be a solution to everyone that want to migrate to non-MS application like me.
Currently, I'm using MS Excel with MySQL ODBC Connector and VBA to create automated report generator.
Too bad, VBA support in OO still in development.
Hi William Anthony,
can you tell me a bit more about your report generator?
Is it essential to build it in a desktop office environment, or is that merely a development platform you find convenient?
I create it under MS Excel because the end-user that using the reports may do advanced data operations they need using the features of MS Excel.
On other word, I "borrow" Excel's features, so I don't have to create them myself.
Great Article. I'm experiencing the same problem like Adam. I follow the described steps carefully and get the error message,that the JDBC driver can't be loaded. Why? I have no white spaces or anything in the path?
Thanks very, very much.
Could I use this tutorial for connect OOo Base to a data base MySQL in my website (remote hosting)?
If not which is the best way to do that?
Hi anonymous,
If you start your tutorial with proper attribution then its ok.
The best way to do that is by clearly stating the fact that you are reproducing material. Be sure to include my full name and the link to this blogpost.
kind regards.
Hey Roland,
I suppose Anonymous @ 1054PM was asking wether your approach would allow him to connect from Base to his remote database.
Nothing to do with him writing a tutorial based on yours.
That said, it would be really cool to be able to use a remote MySQL database as a data source in Calc, but I guess I'm asking too much :-)
Hi robertomuggli,
err...I think you are right...I must've misread it in one of those sleepy hours past 1 in the morning.
Anyway - yes you can use this to connect to a remote host. In fact, my hosting provider (Servage) allows me to create databases and access them directly from outside the webenvironment.
Awesome... this functionality took only a few moments to setup, but it will give me the ability to generate custom reports from the desktop without having to create html tables to extract data from MySQL.
First off, this is a great article. I wish that I would have found it before struggling through tons of web sites which didn't describe it as clearly as you did. However, I have a problem that I haven't been able to solve.
Using OOo I'm connecting to a db on shared server for which I have no control. I can connect to the db using ODBC or JDBC. After 120 seconds of inactivity, the link drops, I get the following using JDBC:
SQL Status: 08003
No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error:
** BEGIN NESTED EXCEPTION **
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
MESSAGE: Communications link failure
or using ODBC I get
[MySQL][ODBC 3.51 Driver][mysqld-4.1.18-standard]MySQL server has gone away
It appears that the issue is that the MySQL server has wait_timeout = 120, which causes the connection to be dropped after 120 of inactivity.
I have yet to find a solution to this. Do you have any suggestions?
-- Geoff
First off, this is a great article. I wish that I would have found it before struggling through tons of web sites which didn't describe it as clearly as you did. However, I have a problem that I haven't been able to solve.
Using OOo I'm connecting to a db on shared server for which I have no control. I can connect to the db using ODBC or JDBC. After 120 seconds of inactivity, the link drops, I get the following using JDBC:
SQL Status: 08003
No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error:
** BEGIN NESTED EXCEPTION **
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
MESSAGE: Communications link failure
or using ODBC I get
[MySQL][ODBC 3.51 Driver][mysqld-4.1.18-standard]MySQL server has gone away
It appears that the issue is that the MySQL server has wait_timeout = 120, which causes the connection to be dropped after 120 of inactivity.
I have yet to find a solution to this. Do you have any suggestions?
-- Geoff
Hi Geoff!
thanks for the kind words, glad it wsa of use to you.
Re the timout: tough one. Somehow you must keep your connection alibve by issuing some statement. I can't boast any real experience building OOo applications so I daren't promise it is feasible to implement this.
Perhaps you can set up mysql proxy on the localhost, and have some wicked lua script automatically refresh the connection periodically. Let me know if that worked...
regards, Roland
Hey Roland,
Another great post! Thank you so much for the time you put into preparing this. OpenOffice Base sounds like it might make a better front-end for MySQL than MS Access (would you agree?).
I had no problem connecting up OOBase to my MySQL server (well, really a single database on the MySQL server) thanks to your clear instructions. However, I don't appear to be able to create any Forms or Reports in OOBase for the tables that load through the JDBC connector. All the Form building and Report building buttons and options are grayed-out. Did you ever get a chance to write that post on creating Forms in OOBase for JDBC connected MySQL databases? I searched your blog for it but may have missed it. Any help would be much appreciated. Thanks again.
-Sam
It's really too bad that OOBase will only let you connect to one database on a MySQL server at a time. I have an application database that I want to make a front-end for but I was also hoping that it would be able to pull in some data from another database on our server. Anybody know if anything has changed since this article was written? Is there a way to have an OOBase form or report that pulls from more than one database on a MySQL server?
Hi Sam!
"However, I don't appear to be able to create any Forms..."
I'm on open office 3.1.0, and it seems to work fine on the sakila sample database. Do you have primary keys defined? Just a hunch that that might matter.
Hi!
"Is there a way to have an OOBase form or report that pulls from more than one database on a MySQL server?"
I imagine it's possible though manual programming, but that is probably not the way you want to go.
I do have primary keys defined, but I should try a sample DB like Sakila.
Roland, do you have experience with WaveMaker? It looks good. I'd probably be able to do a good bit of the build-out in it myself, but for our timeline and custom needs, I'd need help from an experienced developer. I can't figure out how to find developers with WaveMaker experience. I've posted in their dev forums, tried to contact WaveMaker, and twittered about trying to find WaveMaker experienced developers. I'll give all that more time, but if you know of a good way to find someone to talk with me about WaveMaker, that'd be great to know.
Hi Sam,
sorry - no - never tried it myself. Did donwnload it today. I figured, as I was suggesting it to you I'd better try it myself too.
Just want to say that I did find this helpful.
Had to use jdbc because the MySQL native driver for OpenOffice has been a disaster - it connects ok but then OpenOffice crashes on Windows. Maybe you need to go back to Sun and help them out!
Hi Stephanie,
thanks! Glad it was useful for you.
I never tried the mysql oo native driver - just never got round to it. I do know some of the developers involved, and I know that they're dying for your bug reports and feedback!
If you can find the time, please file bugs, it'll be greatly appreciated.
kind regards, and thanks in advance,
Roland
Hi Roland,
first of all: Thanks for your great article, Works like a charm! :)
Do you happen to know any good resources that help me understand if and how it is possible to load MySQL-Tables directly from Calc Spreadsheets? After trying for some time I feel like being too dumb for this ...
Thanks in advance!
Alex
I really liked this info. I am not a typical DBA so looking for a suitable way to deal with migrating information between spreadsheets and MySQL through Calc and forms as a looping mddle man proved to be the perfect solution, so far. The info has only been in my noggin for a few minutes now ;)
Hi
I've connected Base to mySQL, I can read the data from the DB with a query. But I cannot seem to be able to add a Record to the BB via a Form in Base, Is there sum thing I'm missing or is there a primition that I need to add.
A very great article on OpenOffice. Users of such are surely grateful. Thanks for sharing.
Thank you for posting this article about OpenOffice. It is very informative. Cheers!
Post a Comment