Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.orgYou may find that the database being shipped with OpenOffice (ver.2 and higher) delights you as much as it has me. This page tries to help you use it.
Forget anything you may have heard about Adabas, which came with Star Office, the commercial version of Open Office 1. The current Open Office's database, "Base", aka "ooBase", is unrelated. And remember that Open Office, including ooBase, is free! But don't let that fool you. And it's not new. Big organizations, government and civilian, are adopting it as their standard office suite... and saving million$, but still Getting The Job Done.
There's more about ooBase in the main index to this material.
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 more fully explained, and there's another tip, at my Power Browsing page.)
Page contents © TK Boyd, Sheepdog Software ®, 7/09.
SQL is at the heart of ooBase and many other modern databases. Wikipedia has an article if you want an overview.
For many ooBase tasks, the GUI will take care of creating the necessary SQL, but eventually the time comes when you want to take more specific control, and use some SQL that you have either made from scratch, or adapted from something started for you by ooBase.
If you are using ooBase as a front end to access data held by something other than ooBase's native HSQL, MySQL, for instance, then what follows may be especially germane to your needs.
If you want to use something that is part of HSQL, but not yet fully catered for by ooBase, then you too will need what follows.
What was that?? Something not catered for by ooBase?
Besides the relatively simple matter of issuing SQL commands from within Open Office, there's the much larger topic of using an external MySQL database server, with Open Office's database "Base" as the client software, your "front end". That link will take you to a set of pages on the subject. SQL: A language. MySQL an opensource, free, multi-platform database server.
ooBase normally helps you. Remember when Mother used to help you cross the road? Nice at the time, but not something you need forever. Having Mother's help has its advantages; being independent has its advantages.
If you are a novice or casual user of ooBase, you may not notice the restrictions imposed by "Mother". Let's push the boundaries a little, discover some boundaries, and the way past them.
Make a small database as follows. I called mine fdb9parseSQL. It has one table called Scores, holding the number of points scored by a competitor in a contest....
Fill it with 12 records. There's no need to be fancy, simple names like "Fred, Mary, Joe..." (or even "A, B.C...."!) will do... but use the following scores, in this order....
22, 23, 24, 25, 15, 14, 13, 12, 5, 15, 15, 23
We'll start gently, with something that works without any "cleverness"... but don't fall asleep. Once we have that, we'll give it just a little tweak, and suddenly it won't work... but then it will again! I'm using OO 3.1 on a Win XP box, by the way.
Create a query to list all of the scores, in order, top to bottom. The easiest way is to use the query wizard....
Step 1: Table: Scores, move Competitor and Score to the "Fields in Query" panel.
Step 2: Sorting order: Sort by "Scores.Scores", descending.
Step 3: Don't set any search conditions, just click "Next".
Step 4: Don't set up any detail or summary, just click "Next". You should jump straight to step 7.
Step 7: Just click "Next".
Step 8: Leave the name unchanged ("Query_Scores"). Leave things so that you display the (results of the) query when you click "Finish". Which you do now. (Click "Finish.)
You should see the results of the query, a small transient table, with the competitors listed, person with highest score first. Note that the sorting is being done in the query, not at the table level.
Close that. In the ooBase main project manager window select "Query" in the left column, if you haven't done so already. Right click on the "Query_Scores" entry, and then click on "Edit in SQL view" in the menu that pops up.
If you don't have a "Query Design" toolbar, turn it on with View | Toolbars
You should be seeing....
SELECT "Scores"."Competitor" AS "Competitor", "Scores"."Score" AS "Score" FROM "Scores" "Scores" ORDER BY "Scores"."Score" DESC
... although it may not be set out as nicely as that.
In the window you are looking at now, the "Query Design" window, click on "Edit" in the menu. Third from bottom, you should see "Run SQL command directly". It should not (yet) be ticked. Leave it un-ticked.
Click the "Run Query" button on the Query Design toolbar, and the window should acquire a new pane at the top, displaying the same transient table as you obtained before.... the competitors and their scores, with the best scoring competitor listed first.
Change the start of the query. Merely insert "top 5" (with spaces on each side of it), immediately after "SELECT", making the query start....
SELECT top 5 "Scores"."Compet...
Now TRY to run the query again.
You should, at this stage, get "Syntax error in SQL instruction."
Click on the Query Design window's menu's Edit item. Click on "Run SQL command directly". The menu will close, nothing will appear to have happened. Try running the query again.
Hey! What's going on? We didn't change the query... but now it runs, returning the first ("top") six records from the larger result we got last time. What's going on?
That "Run SQL command directly" entry in the Edit button is a bit like a "Show Grid" or "Wordwrap" entry in other menus. It isn't a "do it" button, like, say, "Print". It is a "set the program to work like this" button. It tells ooBase whether (or not) to check SQL that it encounters.
Novices WANT their SQL checked.... for the simple life. "Mother" will keep you out of trouble. Once you are more advanced, though, you'll want to use advanced things like "top", which ooBase doesn't know about. For the purposes of this discussion, I am treating the underlying HSQL engine as not being part of ooBase, even though it comes with it, and for simple work we use it with ooBase. (OR is that "We use ooBase with HSQL?)
HSQL does know "top", and what to do with it. ooBase doesn't (yet) know that HSQL is this "powerful", and so, if you let ooBase check your SQL, it (ooBase) will say "No, you can't send "top" to HSQL". All you have to do to force ooBase to pass your SQL to the database engine is to put a tick in front of that "Run SQL command directly" entry in the Edit menu.
In our data, if you entered what I told you to, there are 5 competitors with scores between 26 and 21. The next best score is 15.. and three people have that score. Sadly, clever as it is, HSQL doesn't realize that if you ask for the top six competitor scores, you'd like to know that it is showing you one of 3 fifteens. (Try running the query again with the number after "top" changed to 6, and then 7 and then 8.
"Cures" for this are a discussion for another day... I just wanted to alert you. "top" does what is says it will... it just doesn't do what you might hope it would do.
Change just the word "top". Make it "tkb-gibberish", and try to run the query again. You should get a sensible error message... so... even though ooBase isn't directly "watching over us" any more, the whole system... ooBase+the supplied-with-ooBase HSQL still "works". (I lost my "Query Design" toolbar along the way of that little exercise, by the way. If it happens to you, just click on the "View" item in the menu, Toolbars, Query Design.)
Change the query back to what it should be, so that it will work again. (We're going to be using it later.)
To recap:
ooBase can work in two modes. In one, any SQL you try to use is passed through some vetting, some parsing built into ooBase to ensure that the SQL is something that ooBase finds acceptable. This is Good in that it catches some errors, gives you some help. It is Bad, though, if you want to use some things which are okay, but beyond the knowledge of ooBase's rule book. This can happen even when you are using HSQL, and can certainly happen if you are using ooBase as a front end to some other database engine.
Create a form to display the Scores table. Initially, base the form on the table, not the query we used before. Use the wizard to get things going....
Step 1: Set the base table or query to the table, "Scores". Elect to have the Competitor and Score fields in the form.
Step 2: (Subform)- no sub-form needed, just click "Next" which should skip you down to step 5.
Step 5: Elect "Data Sheet".
Step 6: Use defaults; just click "Next".
Step 7: (Styles) Just click "Next".
Step 8: Save as ScoresByForm (no spaces in name).
You should immediately get a sensible result, with the names will be in the order they were entered. Close the form. Re-open it from the ooBase main project manager window, for edit.
Open the form navigator. (There's a button for it, (perhaps on an edge of the window)... as long has you have View | Toolbars | Form Design ticked. Don't confuse the form navigator with the more general "Navigator". (Check my short introduction to the form navigator if need be.))
In the form navigator, right-click on MainForm, then on Properties. Click on the Data tab of the properties window which will have resulted. See the property "Analyze SQL command"? That is equivalent to the item we ticked earlier to tell ooBase to let queries through unchecked.
At the moment, our form is very crude, and involves little SQL.
Get yourself back to the Data tab for the properties of "Main form". (You will find you need to re-select things over and over as we work through what is to come. Sigh.)
Change the content type to "SQL command" and the content to....
select top 4 * from "Scores"
.. but don't change anything else for the moment. Try running the form now. You should get a complaint from the system, probably "Syntax error in SQL expression".... but ooBase is saying that, and it's wrong!
Change the "Analyze SQL command property to "No" and try running the form again. It should work! You should see the table's first four entries. Not the top four scorers... we haven't asked for sorting.
What you've just done is very like what we did before. We've told ooBase not to worry about the SQL we're sending.
First odd/ end:
If you want to create longer SQL entries to be processed by the form, you can. To enter multiple lines in the "edit box" next to "Content", then use shift-enter.
A better alternative would be to click the "..." button to the extreme right of "Content". That puts you in the Query Designer.... where you will again have to elect "Run SQL command directly"... but the fruits of your designing will be saved within the form.
All of the above, of course, is just a way to avoid using a query to "fill" the form... and there's no reason to do that. I only did it here so that I could talk about one thing at a time! Build a query, as a query, and drive the form with that. If you are driving the form with a query, I don't think you'll need to set the form's "Analyze SQL command to "No"... but it won't hurt to do so. (It's all to do with when and where ooBase (might) check the SQL against the limited ooBase concept of what is allowed.
Second odd/ end:
Having the "Run SQL command directly" menu item ticked has many things to recommend it, not least the fact that you can insert rems into your SQL code. Just type two hyphens to make a rem....
-- Here is the SQL for the query that we started with
SELECT "Scores"."Competitor" AS "Competitor",
"Scores"."Score" AS "Score"
FROM "Scores" "Scores"
ORDER BY "Scores"."Score" DESC -- "DESC" for DESCending
(There are two rems in what you see above.) What you see above also illustrates another joy of working this way: Things remain laid out as you want them laid out. Good layout is very helpful to developing robust code quickly. Let ooBase check things, and it will keep introducing "helpful" little changes, and messing up the layout you tried to accomplish.
Third odd/ end:
If you don't let ooBase check your SQL, you will have to be more careful about some details. For instance, the query above needs the quotes around "Scores" and "Scores" in the last line, if you don't let ooBase "help", but you can "get away with" leaving them out if you do let ooBase help... unless you thus confuse yourself. So: You will have to be more careful about details. Deal with it.
Fourth odd/ end:
I don't really understand the technique as well as I would like to yet, but I gather that when you use queries with parameters... a Cool and Good Thing... ooBase must be told to let your SQL pass un-molested. To whet your appetite:
Suppose you had a form with a box the user could fill in, and a button. Suppose that form was part of the database we've been using in this discussion. The box could be for a name, wildcards permitted, while we're dreaming. If we put Fr% in the box, when we pressed the button, we would get the scores for anyone who's name begins "Fr". If we put Mary in the box, when we pressed the button, we would get the scores for anyone named Mary. Those results would come from something like....
Select * from "Scores" where "Competitor" like 'Fr%'
.. and ...
Select * from "Scores" where "Competitor"='Mary'
(Either of those will work by the way). The tricky thing is setting up a form that passes either "like 'Fr%'" or "='Mary'" to "Select * from "Scores" where "Competitor".....". Tricky... but it Can Be Done... and the trick, as you can imagine, can be very useful!
So! Now you know how and when to cut the apron strings, tell ooBase not to bother checking your SQL code.
I hope the above was useful.
I dislike 'fancy' websites with more concern for a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Of course, an attractive site WITH content deserves praise... as long as that pretty face doesn't cost download time. In any case....
I am trying to present this material in a format which makes it easy for you to USE it. There are two aspects to that: The way it is split up, and the way it is posted. See the main index to this material for more information about the way it is split up, and the way it is posted.
PLEASE >>> Click here to visit editor's Sheepdog Software (tm) freeware, shareware pages <<< PLEASE
If you liked this ooBase tutorial, see the main index for information other help from the same author.
Editor's email address. Suggestions welcomed! - - - Want a site hosted, or email? I like 1&1's services.
Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org
. . . . . P a g e . . . E n d s . . . . .