AUTHOR'S MAIN SITE  »  OVERVIEW: client/ server work  »  Intro: MySQL  »  Setting up MySQL
Delicious.Com Bookmark this on Delicious    StumbleUpon.Com Recommend to StumbleUpon

Open Office's "Base" as front end for MySQL

This page is "browser friendly". Make your browser window as wide as you want it. The text will flow nicely for you. It is easier to read in a narrow window. With most browsers, pressing plus, minus or zero while the control key (ctrl) is held down will change the texts size. (Enlarge, reduce, restore to default, respectively.) (This is fully explained, and there are more tips, at my Power Browsing page.)

Page contents © TK Boyd, Sheepdog Software, 3/13-2/15.



Using Open Office's database ("Base") as front end for MySQL database

Getting "Base" (the database supplied as part of the free, multi-platform Open Office suite) to work as a front end for MySQL is not hard. You don't have to give up continuing the use Base with other databases the "simple" (embedded engine) way.

But first catch your rabbit.

Before you can use Base as your front end for interacting with data held on a MySQL server, you need.... data held on a MySQL server!

Getting that set up may give you a few headaches... But it is a sophisticated system, with lots to recommend it. (Also free and multi-platform, I'm glad to say.

I've written elsewhere about MySQL, and how to set up a server. You can do it! But get it set up and working before you try to access it with a "Base" front end.

I've also written a separate webpage with duplicates the first part of this page. I suspect the separate page does it better! So... you can either...

Jump to that separate page, which has a jump at the end of it to where the following finishes....

... or....

You can keep reading this!

... or....

If your server is already set up, and has a database on the server (MySQL Command Line: "create database [name];") and has a user created to use the database on the server... you can skip over the "first part of getting started" stuff, jump to creating Open Office Base connection to the MySQL database.




First steps...

Get yourself a working MySQL server, running on the same PC as you are going, at least to begin with, use for the Open Office part of the exercise.

Make sure that the MySQL server works. Use the MySQL Command Line Client to create a database, and a table within that database, and add some records to the table. Use the MySQL Command Line Client to display the data which (you think) you have put in the table.

Once you've got that much working, the remaining steps to get you to where you can use Open Office to interact with the database's tables are almost trivial.




Dinosaurs be warned...

(March 2013) I only recently turned off a machine that did a job for me running MS-DOS. Is till use XP machines for most of my work, and have only one Win7 machine in routine use... and I don't use that for much that is "important" or "tricky"... although my MySQL server is on it, because it was "experimental" for a time. I am now beginning to trust that, so I guess I have to admit to one "important" job being done on my Win7 machine.

All of which to say I am firmly in the "if it ain't broke, don't fix it" camp.

However, if you want to use MySQL and Open Office together, it really would be wise to update your Open Office to whatever is the current version, and ditto your MySQL, if you have scraps of earlier attempts. It wasn't so very long ago that the MySQL/ Open Office marriage was more a messy living together. Avail yourself of all the (fairly, but not too!) recent work to make the union felicitous.




Thing you do once...

You will need to do the following just once on each PC which is going to be used to access your server.

You will need to add the "official", "native", Open Office MySQL connector to your Open Office installation.

I am a bit reluctant to add extensions, so, if you share my skeptical attitude, rest assured that this recommendation comes from someone who feels as you do. The only other extension I routinely install is the one for the Report Builder.

You start up an Open Office application, Base, if you like but that entails opening a database, which you might not feel like doing at the moment.

-- Click on the "Tools" menu entry.

-- Click on "Extension Manager..."

-- Click on "Get More Extensions Online" (Blue text, lower left)

That should open a browser page. (http://extensions.services.openoffice.org/getmore?cid=920794)

-- Search for "MySQL Connector"

When I did that just now, 13 Mar 13, I got....

MySQL Connector for OpenOffice.org
by Oracle
Sun Connector for MySQL

The MySQL Connector for OpenOffice.org installs a
native MySQL database driver. It is faster, seamless
integrated, easier to administrate and there is no
need to install and setup a JDBC or ODBC driver
separately. It was never easier to use MySQL
databases in OpenOffice.org Base.

Version: 1.0.1
Date: 2011-Jan-05
License: Open source
Downloads: Week: 501 | Month: 2,334 | Year: 57,781

-- Click on the title line of that entry ("MySQL Connector for...")

.... which will take you through to a page with more detail, and buttons to download whichever version you need. (There are different versions for different operating systems.

Click the one you need. You should then see a "You have chosen to open... What do you want to do with it..?" dialog. Elect to save the file. Go to containing folder. Double-click on it. (An Open Office app can be open at the time.)

"Nothing happens"? Look to see if you need to switch to a hidden window to confirm license terms, proceed. All should soon be well.

How do you know you've succeeded in the download and install?

Go back to your Open Office app's Tools/ Extension Manager page, and you should see a listing for: "MySQL Connector"

That's it. That's the "do it once" stuff you need to do. You need to do it once on each PC that is going to be used to connect to the MySQL server you have running somewhere. In due course, we will talk about connecting to it across a LAN, or even the internet (WAN). For now, walk before you run. Put an instance of Open Office on the same PC as you have the MySQL server on. Use the one PC to, for now, run the server (MySQL) and the client (Open Office's "Base" RDBMS. You don't even need to restart your Open Office, let alone your operating system.




First of three "Once for each database" chores...

I'd be amazed if you really have to do this chore the way I'm doing it now. But I can understand the way I do it now.

The first chore is to create "the database"! I.e. the "container" on the MySQL server which will hold the table(s) with the data you want to work with.

I've explained doing this elsewhere. If you are following through the example I've written up across these many pages, you already have the demo database "tkb13mardem". If you don't have it: In a nutshell: Use the MySQL Command Line Client, and issue the following command:

create database tkb13mardemo;

(If that nutshell is insufficient, you can consult the more extended tutorial I did on getting a MySQL database started.




Second of three "Once for each database" chores...

(I am using the term "database" here to mean one of those collections of tables, etc, that a MySQL server can serve. One server may have several databases on it, e.g. one for your Christmas card list, one for books you have read, etc. An individual database may have just one table... but it could have several.)

If you are willing to be Very Bad at this point, you can skip down a bit, by just usiong the server's root user... who can do everything... and with whom a hacker can also do everything, if the hacker "gets in". Probably best to...

Using the server admin part of MySQLWorkbench, or an alternative, once for each database... and you may have already done sufficient for our needs for user Simple-First and database ("schema") tkb13mardemo... you need to create a user (or add to an existing user's allowed activities) who can access the database over localhost, aka, I think '127.0.0.1' (try '127.0.0.1' as "different", if 'localhost' doesn't work!), and has most "rights" to do things to and with the database.

Optional (at this point) extra... if you want to go on, later, to accessing the database across the internet, you will need a suitably empowered user. That user, I think (to be tested!) needs to be set up to allow access via your router's LAN IP address... quite possibly 192.168.0.1. In the next few paragraphs, I am trying to give Simple-First both capabilities. If your interest in connecting over the WAN is not strong at this point, leave the connecting via 192.168.0.1 for later... it will be a weakening of your defenses against the Bad People... if you get things wrong. And you can add the capability later.

It is easy to miss a part of the "set up and empower a user" process. I have discussed it in detail elsewhere.

During the process...

For now, I would suggest filling in the New Schema Privilege Definition along these lines:

Host: Be specific. When you know what you are doing you can, within limits, say "anyone" with wildcards. Until you know more, be careful. So... "Hosts matching pattern or name": localhost (or '127.0.0.1'). You can do two entries in the table to allow access to one database over two different channels.

Schema: If you are just following along the example I have developed, use the "Selected schema" option, and select "tkb13mardemo".

Be sure: To tick some of the rights that the user can be given in respect of the selected database. It is probably best, until you know more, to use the 'Select "all"' button, and to respect its "choice" of leaving "Other rights: Grant Option" un-ticked. And best, until you know more, not to put sensitive data into any of your "learning" databases, if you don't have good physical control of the PC it is on.

Beware: MySQLWorkbench seems to let you say "User X" should be allowed access to database Y via 127.0.0.1 and 192.0.0.1".... even though, earlier, on the "Server Access Management" page you only gave user X connectivity via 127.0.0.1!

The "fix" for that is to set up "two users", as far as the "Server Access Management" tab is concerned: Both with the same name, but with different connectivity limits. (I couldn't find a way to put "can connect via 127.0.0.1 OR 192.168.0.1" in the box by "Limit Connectivity to...")

As you wrestle with the system, it may take you a little while to see the logic of how the "Schema Privileges" And "Server Access Management" settings map to users. There is a method in the madness.

Thankfully, you only have to wrestle with this... and the next thing... once for each new user, each new database. And if you are still just at the "one PC, hosting server and client" stage, getting enough right isn't... too... hard.




Last of three "Once for each database" chores...

Now... you need to think quite carefully about what I am going to say here.

If you have used a database previously, without using an external server, in other words, everything to do with your database was in one place, then that was nice and simple, wasn't it?

Your database was probably in a particular folder. If you were using Open Office's Base "the easy way", i.e. with the embedded database engine, then all your work ended up in the single .odb file. (If you don't know what I'm talking about with my "embedded database engine" stuff, then you probably were using the database that way!

You know, I hope, that databases involve tables and queries and forms and reports.

In your "simple days", all those things were in one place. Neat and tidy. Under Open Office's Base, they were not only in one "place", they were even all in one file! (And if it became corrupt... rare, but not unknown... you were in a world of hurt.)

But now you have put away childish things.

(Sorry... just trying to be "witty"(?)... I don't mean to disparage using Open Office with the embedded database engine, or other RDBMS packages in a similar way.)

When you move to using a server, you have to remember: Some of "your stuff" will be on the server. (I thought I understood this bit.. but as I come to write it up for you, I begin to wonder just how accurate my grasp is!)

I think that your tables, in particular, will be on the server. Maybe some of the other "stuff" of the database, too, e.g. queries and forms... though maybe one or both of those will be on the client machine. Once I am a little further along in my MySQL investigations, things will become more clear. (When I can access the server from two different instances of client, on two different PCs, we'll see what things "stay with" the server, presumably on the same PC as the server, and what things "stay with" the client. (Mostly it is the definition of user(s),their passwords, their priviledges.)

One thing at least stays on the client machine, in the client software's files: The details of how to connect the client. How to connect... but only as long as what is needed isn't changed, which can only be done on the server. If your client is Open Office Base, then that information is saved in the database's .odb file.... after you make the initial connection "by hand".

To try to say that again, another way: The client "knows" where the data was, at least the last time it accessed the data. And it knows the user name, etc, that were right the last time it connected to the data. (The password can be stored in the .odb, too, but I wouldn't recommend that. Having to put it in each time makes sure you still have the password, even if the data isn't terribly important.)

Making that initial connection is the third of the three "do once per database" tasks, and I am about to take you through connecting to the database tkb13mardemo.

Remember: We are still "walking"... Here I will just show you how to connect to tkb13mardemo from a client running in the same PC as the MySQL server hosting "tkb13mardemo".




Make the connection: Doing the last of three chores...

Launch Open Office's Base. Use the relevant button on the start menu. In future, you will be able to launch it by double-clicking on the .odb file of the database you want to access... but you don't have an .odb file for this database yet, do you?!

You should get the "Database Wizard" dialog, asking what you want to do, and your answer should be "Connect to an existing database"... Not "Open an existing database file". (You are just starting to create a "database file". The term ("database file") is supposed to imply "one of the files with details of a connection", not "a file with data")

After you have ticked the "Connect to existing database" radio button, scroll though the connectors on offer (the original offer was probably for "JDBC") until you find "MySQL", near the bottom. You may need to use the scroll bar on the side of the list box. ("MySQL" won't be there unless you've installed the extension... see earlier info on this page.)

Click "Next", and you should find yourself in Step 2: Set up MySQL connection. You want: "Connect Directly". Click "Next". ("Connect Directly" wasn't available, at first, one time I was first getting going on a new machine with MySQL. A full Windows restart fixed that.)

Step 3: Set up connection to a MySQL database...

--Database Name: tkb13mardemo (if you are working though my example material)

--Server/Port:

-----Server: localhost (or maybe 127.0.0.1)
     You DON'T enter the name of the SERVICE

-----Port: Unless you've did something "clever" elsewhere which messes this up: 3306

By the way, curiously, one of the many, many times I went through the "Database Wizard" in the course of writing this guide, when I was at this step, a "test class" button was present. It should not have been. Maybe I didn't tick the "connect directly" option in step 2? I don't know. If you find that "Test Connection" is offered, cancel out of the Database Wizard, start it again.

That's it, for step 3. Click "Next".

Step 4: User....

Enter the name of the user which you set up with MySQLWorkbench, set up to have access to our database. I suggested "Simple-First" as a name for the user.

Tick the "password required" box if, as I recommended, you set the user up as a user with a password.

Click the "Text Connection" button. A dialog titled "Authentication Required" should pop up. Put Simple-First's password in, and click "OK".

If you get "Access denied", it may just be due to a typo, or confusion, in respect of the user's password.

(An aside: I had given Simple-First the wrong password, on my test bed system. Rather remarkably... well, I find it remarkable... I was able to change the password for Simple-First without significantly interrupting my work on writing this tutorial. I'd tested the connection, as described in the previous paragraph, entering the actual password then on Simple-First, and getting a very agreeable "Connection Established Successfully". Then, leaving the Database Wizard dialog open at step 4, I went off, started up MySQLWorkbench, changed Simple-First's password, went back to the still-open Database Wizard dialog and found, to my delight, that the new password was already active. If in doubt... shut down and restart things like servers. But in this case, it seemed to work without that hassle being necessary. end of aside!)

Step Six: Save and proceed....

"Do you want the wizard to register the database in OpenOffice.org": Fear not: Your data isn't (as far as I can tell, but how certain can we be?) being sent off to Big Brother. This "registering" allows other programs in the Open Office suite to access data in the database. An advanced topic. You probably don't need to "register" the database... but it probably won't hurt, if you do. I tend to register most things, even little temporary exercises. (There is a way to "weed" the table of registered databases, i.e. if you later want to "de-register" it.)

"After the database file has been saved, what do you want to do?" Probably open the database for editing. Go that route for now, anyway. You can create tables later, if you want to.

When you click "Finish", rather annoyingly, you are presented with a further major Thing To Do. I suppose I'm just being petulant. ("Finish" should take you out of a finished chore, don't you think? Not lead you to one more step?)

You will be presented with a Save As dialog.

This is when you save all of that "stuff" you've spent the last 5(?) minutes entering, so that the next time you want to connect to tkb13mardemo on your MySQL server, you don't have to go through all that again. Fear not... you will have to enter the user's password each time, if you ticked the "Require Password" box.

This "Save As" will look very much like the "Save As" you encountered when using Open Office's Base with the embedded database engine. However now, you are saving both less and more. You are not... when using Open Office as client software, as a front end to the MySQL server be saving the data of your tables. You are, as I said a moment ago, saving the "how do I connect to this database on the MySQL server?" information... information which just didn't pertain before you were in the client/ server environment.

After the save is completed, you should find yourself looking at the usual Open Office Base main project management window, probably with "Forms" selected in the left hand, "Database" column.

Click on "Tables" in the left hand column. (You may get a pop-up, requiring you to enter a user name and password... but this won't happen annoyingly often) After you click on "Tables", the lower right hand pane should show some thing or things.

This may not be quite what you are used to. One or more databases... some of which you may not be able to access... may appear. Among them tkb13mardemo, if all is well. Double click on that, and any tables you have set up for that should appear. (Even at this stage there may be tables there... if you created some earlier from within one of the otherM ways to work with a MySQL database.) If any exist, double click on one of them, and you should be into the table. You won't (yet) be able to edit the data in that table unless, as you should have done, you established a primary key. (Open Office won't let you edit data in a table with no primary key. You can assign one from within OpenOffice, if there isn't one already established. (There must not be any duplicates in existing records in the field chosen.))

That's it! We've finished the "do once" chores... for the PC, for the database. Let's get on with Using the database, as we would in day to day operations!




Using the database...

Don't shut down or restart! (Neither will do any harm... but there's no need for either.)

If you have a "basictablebyclc" window open, shut it.

At the main project management window, right-click on the entry for "basictablebyclc", if you have one, and opt for "edit". If you don't, you'll have to set one up, or just set off on creating a new table, and playing with that.

If, from previous work... maybe the stuff at the top of this page, which you may have skipped over... you have a table called "basictablebyclc", you can carry on as follows. If you haven't got it, skip down to the concluding remarks.

Make "RecNum" the primary key in the usual way. (right-click in left hand column, just before the name "RecNum".)

Save your changes to the table's definition. Close the definition editing dialog. Re-open the table by the simple double-click. Your change may have taken, you may now be able to edit the records in the table. That's what "should" happen.

For reasons I can't fully explain, such changes don't always seem to "take". When they will, when they won't: I can't fully explain.

But, hey... come on: Introducing a primary key is a pretty major change.

Now: In this case, I might have used the database access tools in MySQLWorkbench to make that first field a primary key. (I haven't, at least at 13 Mar 13) explained those tools to you yet.

Instead, I used Open Office Base's "Tools / SQL" to execute...

ALTER TABLE `tkb13mardemo`.`basictablebyclc`
ADD PRIMARY KEY (`RecNum`);

(A bit of a cheat, I must admit. How are you supposed to know such advanced SQL at this stage?)

And I had to close the client down, re-open it, to see the fruits of my labors.

But! This isn't going to be a problem for you! (Well, not very often.)

Get a client session going.

Use the normal Open Office Base table creation tools to make a new table, being sure that it has a primary key field from the start. (Remember: We made that "basictablebyclc" by a kludge technique. Yes: We will continue to create databases with the MySQL Command Line Client.... but once we have the database, we'll do the rest from our nice, Open Office Base GUI client.

A little Gotcha, by the way: When you come to save your newly created table (or query, or form), there's an extra box to deal with... extra beyond what the Base- by- Embedded- Engine user sees....

Make Table Save dialog

Note that first box. Be sure to set it to the right schema before you save your new table.

Having created a new table, play with it. You should find the experience virtually the same as what you experienced when working with Base via its embedded database engine, back in the "good (simple) old days". I'm afraid that there may be other things, like the making of the first field a primary key, that the Open Office client doesn't do well. But not many! And none (I hope) that get in the way of routine data entry and processing. Just think of all the nice SQL you can learn! (And there is the MySQLWorkbench, which will, I'm pretty sure, always succeed, even where Base fails. (There are reasons for that faith beyond mere optimism. I've used computers too long to have much optimism left.))

You can, by the way, establish relationships between tables by the ordinary Base tools. Do be sure, though, that things you need have "taken".

So! Open Office used as a client of a MySQL server is "a bit" of work. And not perfect. Were you happier in the "good old, merely use the embedded database engine" days?..... but...




Ah, but.... could you...

a) Sleep nights? I believe there is less chance of losing data, when you use a MySQL server for your data. Don't worry... I don't think the chance is LARGE for most situations, using the embedded database engine. But I wanted an even more robust system, and I was putting a strain on it by a "special feature" of something I was doing.

b) Can you... access your data from any PC on your LAN? Or even from PCs outside the LAN, out there on the internet? I don't think you can, if you use the embedded engine, and I think you can, if you are using a MySQL server. (These are areas I've "played with".... I will try to write up what I know when I can. I DO BELIEVE that the old situation of there being a bug in the MySQL connector has been fixed, or if not fixed, then it is only if you try to connect with a raw IP address that you can't get in. I think you CAN get in, if you have a more usual URL, using DNS. E.g. http://TomsMySQLServer.dyndns.com)

c) Can you... have multiple users accessing the data concurrently. Again, I don't think you can, if you use the embedded engine, and I think you can, if you are using a MySQL server... but I haven't even played with this one yet!

But I hope that what I have played with, and written up, gives you a head-start in your own Open Office/ MySQL adventures??




Preview....

As Billy's dad says: "I wouldn't call myself an expert or anything." Not in the "simple" things above, and certainly not in the next stages of this adventure.

But! I have managed to make the following happen...
-- multi- user access to the MySQL server, using Open Office for the client (front end) software, and,
-- remote access.. i.e. across the internet, the "WAN" to the MySQL server. Again, using Open Office for the front end.

Those links take you to some details of my progress to date. That "progress" may not be rigorously tested, but might help you to do similar things?

.



Have you heard of Flattr? Great new idea to make it easy for you to send small thank you$ to people who provide Good Stuff on the web. If you want to send $$erious thank yous, there are better ways, but for a small "tip" here and there, Flattr ticks a lot of boxes which no one else has found a way to do yet. Please at least check out my introduction to Flattr, if you haven't heard of it? "No obligation", as they say!



Editorial Philosophy

I dislike 'fancy' websites more concerned with a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Not everyone has fast broadband.

I present this material in a format aimed at to helping you USE it. There are two aspects to that: The way it is split up, and the way it is posted..

Please remember the material is copyright. (TK Boyd, 2006 and later) The procedures in the page just cited are suggested only for convenient personal use of the material, however, also....

Feel free to use this information in computer courses, etc, but a credit of the source, quoting the URL, would be appreciated. If you simply copy the pages to other web pages you will do your readers a disservice: Your copies won't stay current. Far better to link to the original pages, and then your readers will see up-to-date versions. For those who care- thank you. I have posted a page with more information on what copyright waivers I extend, and suggestions for those who wish to put this material on CDs, etc. (There is at least one prison using Sheepdog Software/ Sheepdog Guides material for inmate education. Situations do exist where good internet connections are not possible!)

Translations are welcomed. Tell me about yours, so I can post links to it. (More information at the page about copyright waivers.)




Ad from page's editor: Yes.. I do enjoy compiling these things for you... I hope they are helpful. However.. this doesn't pay my bills!!! If you find this stuff useful, (and you run an MS-DOS or Windows PC) please visit my freeware and shareware page, download something, and circulate it for me? Links on your page to this page would also be appreciated!

PLEASE >>> Click here to visit editor's Sheepdog Software (tm) freeware, shareware pages <<< PLEASE


If you liked this tutorial, see my main webpage for more things from the same author.

Editor's email address. Suggestions welcomed!     - - -    Want a site hosted, or email? I like 1&1's services.




Valid HTML 4.01 Transitional Page WILL BE tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org. Mostly passes. There were two "unknown attributes" in Google+ button code. Sigh.


One last bit of advice: Be sure you know all you need to about spyware.

. . . . . P a g e . . . E n d s . . . . .