AUTHOR'S MAIN SITE   > > > > >   TABLE OF CONTENTS for Open Office database tutorials.
Delicious Bookmark this on Delicious   Recommend to StumbleUpon

Open Office Database Tutorials

Linking from a database to images not embedded in the database

(Images can be in same folder as .odb, or elsewhere.)

You 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 ®, 2/06-10/10.


Where we're going

You will learn how to make a small database with images. In the tutorial they are photos, but they could also be diagrams, drawing, etc. This isn't a very demanding tutorial. But it does show you the "tricks" of something that has many uses. It is a sequel to another tutorial I have put on the web for you which addresses the same question... but solves it a different way! It would be best to read that tutorial first, even if you only skim through it.

Specifically, we will create a database with pictures of some North American birds. (The photos are provided for you.) Each record will consist simply of an index number, the bird's name, and the name of the file holding the bird's photo.

The tutorial will show you how to get the image to display on your form, even though the image isn't held inside the database. All the database holds is information on where to find the image. This tutorial also gives you some help with details of creating and editing form designs, but the other tutorial gave more help.

And at the end, we will briefly visit generating reports with graphics, using the Oracle Report Builder, previously known as the Sun Report Builder, or SRB.

Before we delve into the tutorial, I would like again to thank the contributors to the ooBase forum at OOoForum. They showed me how to do images the "embedded" way, and the "by paths" way.

You can download a version of the first database described here. It comes in a .zip folder, complete with the .bmp files the database uses. (I haven't created records for every bird... sorry... but you can add them easily enough!) Download the 1.9 meg zip file, and un-zip it into a folder which you can create wherever you want it, and you can call it whatever you wish.

The downloadable version gives you the database set up with the image files separate from the database, but still in the same folder. You can also store the images elsewhere, even on a CD, for instance. To have the image files in a different folder, you need to change the entries in the PathToBirdsPicture field of the underlying table... but instructions are given, and you will have already downloaded the images you need. You'll just have to move them to a different folder to test the instructions for databases with "remote" images.

Caveat

(This caveat is also included at the start of fdb1imag. If you've read it there, feel free to skip down to the "Heart of the Story".)

Of all the tutorials I have posted about ooBase, I would say that in the year up to October 2010 perhaps the ones that give rise to the most correspondence are those about using images in databases.

ooBase has been stable and reliable in its core features for many years now, in my experience and opinion.

Having said that, I must concede... and celebrate!... that it is a continually evolving product. Unlike certain product line developers, which try to give everything to everyone, from time to time the Open Office product team take hard decisions and re-work elements of the design. When this happens, the way some things work sometimes changes. The alternative is software that gets more and more bloated, and more and more unreliable. (The changes tend to be on the fringes, but when it is a "fringe" you were using, it is tiresome.)

It would seem that the way images are managed with ooBase has been one area where changes have been deemed necessary. I hope you will find my tutorials, even the ones on images, useful... but I must admit that in October 2010, there seems to have been statements here that weren't quite right for the current ooBase! But they've been fixed!! I would encourage you to "go for" Open Office upgrades almost as soon as they become available. Don't do one just before needing your system for something important.... upgrades are always somewhat risky... but on the whole, I've found that Open Office upgrades go smoothly, and bring welcome enhancements.

One other little point about graphics and ooBase: I often get people writing to ask "How do I embed the graphics within the database?" It can be done, but, except for some special cases, I think it is a Bad Idea. (See my tutorial, if you think you want to do it.) Those special cases would be times when there are not very many graphics to embed, and when they can be small files.


Heart of the story

On 12 Oct 2010, using ooBase 3.2.1, I went through the following instructions. They worked! For the little bit at the end about reports with images, I used ORB 1.2.1 (ORB: "Oracle Report Builder", the replacement for the SRB.)

Set up the table....

Create a new database. Put it in it's own folder, call the folder whatever you like, and the database "BirdsWImagesByPath". (That's a bit long-winded. Oh well. Most of the names I've suggested could be different, if you feel strongly about it.) The name came from "BirdsW(ith)Images". It doesn't need to be registered, but registering it is probably a good idea, as it "costs" so little. (I did register the database when I was testing these instructions.)

Set up a table. ("Create in Design View" would probably be best, but the finished result doesn't carry any "signature" of which tool you used.)

Fields:

Index (Integer:INTEGER, Autovalue=yes, primary key)
PathToBirdsPicture (Text:VARCHAR, length 30)
Name (Text:VARCHAR, length 30)

Be sure to set Index as the primary key.

Yes... my name for the second field is a little long.

Save the table as BirdsWImagesBPT1. (The names don't have to be unique, but I like to fight ambiguity. The "BP" part is for By Paths.)

I'll give you sample data in a moment. Just create the table for now.


Now catch some rabbits... umm... some bird pictures

The techniques explained in this tutorial allow you to store photos of whatever you want. For the sake of the tutorial, I have made a collection of photos of some birds available. (You may make whatever use you wish of those photos, except a use that restricts anyone else from doing the same. I assert ownership of their copyright. Don't get too excited... they're not good photos!)(You can access a vast library of royalty-free images online at a sister project of Wikipedia, by the way.)

For the sake of the tutorial, I have made a collection of photos of some birds available. (You may make whatever use you wish of those photos, except a use that restricts anyone else from doing the same. I assert ownership of their copyright. Don't get too excited... they're not good photos!)(You can access a vast library of royalty-free images online at a sister project of Wikipedia, by the way.)

They are in a Windows "self-extracting archive" prepared with WinZip, for which the following instructions apply. They are also available as a mere .zip file, for which the following instructions partly apply.

Once you have downloaded the archive, put it in the folder you created earlier, BirdsWImagesByPath. Unzip the images into that folder. The archive is self extracting... you only need to double-click on it. The unzipper will start up, and ask you where (what folder) to store the images. It puts 10 small .bmp files in that folder. Each one is named for the bird shown in the file. Each is about 200k. (That's probably bigger than it needs to be.)

In the other tutorial, I told you that once the database was built, you would no longer need the .bmp files. And that was true. When the database was built as we built the other one, when we were done, the images were inside the database, an integral part of it.

Not So This Time.

This time, we only store in the database information about WHERE the .bmp image is! This approach has pros and cons, which are left as an exercise for the student.


And now for some data....

Set up a simple data entry form, using the Wizard....

Step 1: You only have one table, and no queries, so the only thing you need to know is that you select just PathToBirdsPicture and Name. The easy way to do this is to click the ">>" and then select the Index field and send it back to the "Available fields" list.

Step 2: There's no need for any subforms. Click Next, which will take us straight to step 5.

Step 5: Arrange the controls as a "Data Sheet". This will make the form look almost like the table it relates to. Click Next.

Step 6: Leave the tick in front of "The form is to display all data". Do not tick "The form is to be used for entering..." Do not click on any of the "Do not allow...." boxes. Click Next.

Step 7: Play with these settings if you wish... but it isn't necessary. Click Next.

Step 8: Name the form "BirdsWImagesBPF1" (You'll probably merely have to change the penultimate character), leave "Work with the form" selected, and click "Finish".

Enter the following data. You may want to adjust the widths of the columns first. Note that the file names, in the "PathTo..." field,Mallard may be case sensitive.

PATHTOBIRDSPICTURE  //  Name
Crow.bmp               Crow
Mallard.bmp            Mallard Duck
Chickadee.bmp          Chickadee

That's enough to test things with. You can leave the form open for further use later, or close it if you prefer a tidy "desk".


Form with image....

Now we'll make a form which displays the image of whatever bird you choose.

Use the Form Wizard to get things started....

Step 1: Put the PathToBirdsPicture and Name fields on the form.

Step 2: No sub-form. (You will skip steps 3 and 4)

Step 5: Data Sheet for layout.

Step 6: Defaults. ("Display all.." / allow all changes)

Step 7: Whatever.

Step 8: Call it WithImage

... and don't expect it to be working... yet.

Close the form, then re-open it in the editing mode.

(Re-size the data sheet, making it something more sensible.)

You need the "More Controls" palette. You get this from the "Form Controls" toolbar. (If you need help, see my "Naming of Parts" page about the two toolbars. It will open in a new tab or window.)

Use the "More Controls" palette add an "Image Control" (not "Image Button") to your form, in some area not already being used for the Table Control which is already on the form. Drag the Image Control out to roughly 4cm x 4cm.

Double click on it (still in edit, or "design" mode), and you should get the control's properties.

On the "data" tab, use the "Data Field" property to "connect" the control to the PathToBirdsPicture field of the .

On the "general" tab, be sure that "Scale" is set to "Keep Ratio". (This option was added in ooBase's version 3.1, and very welcome it is, too!)

Run the form, i.e. click the "Design Mode On/ Off" button to leave the "design" mode, move to the "run" mode. You should now see right graphic for any record in the data sheet.

Hmmm... I had all that working just fine, first time. I then "got clever", and tried changing "Mallard.bmp" to "mallard.bmp", while both forms were open. (It didn't seem to matter... to a Windows machine. A Linux machine might be less sloppy.) I also hit various "refresh" buttons along the way... and somehow "broke" it... but that only happened once, and I did plenty to "confuse" things. Bah!

It was quite simple to fix... I just had to go back to the image control's properties, and re-specify that the image's data should come from the PathToBirdsPicture field. Sigh.


Odds and ends....

== 1 ==

A minor tidy: You really don't need to see the text of the PathToBirdsPicture in the data sheet, do you? I included it on the first pass through this, just to keep you reassured.

You could have done everything as above, except in step 1 left out PathToBirdsPicture from the fields in the data sheet. The form would have worked because the form's MainForm (Confusing, that... two uses of the word "form". Beware.) is tied to the table, and thus all its fields are available).

If you want to fix that in our existing form, you can simply right click on the PathToBirdsPicture column of the data sheet and select "Delete column".

== 2 ==

In a moment, I'll show you how to create a slightly different display form. First a warning: If you "play" with the following before making the "Name" field (in our new form) "read only", you can "damage" the data in the underlying table. If you start getting strange results, use your BirdsWImagesF1 form to look at what is in the table. If it has been corrupted, save and close the display form you are working on, repair the data, then resume editing the display form.

Close whatever's open, except the main ooBase main project manager window. Launch the "new form" wizard again

Step 1: Just Name

Step 2: No subform

Step 5: "Columnar, Label on top"

Step 6: As before, defaults.

Step 8: Name this form "DisplayByBar".

Close the resulting form, re-open for edit, i.e. in design mode.

Add image control; connect it to PathToBirdsPicture.

Add navigation bar. (It is the last item on the "More Controls" palette.)

Use the form navigator to select JUST the txtName text box. (You can also get to it by right-clicking on it, and electing to ungroup it and its label.) (If you are not familiar with the form navigator, I have an introduction to form navigator for you.)

Change its Read Only property to "yes".

That should give you something that allows you to use the "Forward", "Backward", etc, buttons and fields of the navigation bar to move through the database. You will no longer be able top access the records by the bird's name, though, although you will be told the name still.

For the purposes of this display form, you probably would want to set the navigation bar's "acting on record" property to "hide". (And maybe others.)


Fancier paths...

In this tutorial, we have worked with images in the same folder as the database. Can you have the photos elsewhere? Yes.... I believe you can. For instance, if you had them on a CD, and you knew that any system the database was going to run on would refer to the CD drive as "D:", then you could, say, put something similar to "D:\crow.bmp" in the PathToBirdsPicture field and get the desired result.... Details are as follows....

I was using Windows XP and Open Office 3.2.1.

I started by creating a new folder in the root of my C: drive. I called it "birds", and put copies of all of the .bmp files we used before there.

I then did everything I did before, the same way... except: In the table "BirdsWImagesBPT1", in the field "PathToBirdsPicture", I put things like....

file:///C:/birds/Mallard.bmp

N.B....

Longwinded maybe... but it works!!!


By the way... ever since Windows introduced "My Documents", etc, I have found paths a nightmare for programming work. If anyone can tell me a reliable way to refer to the "My Documents", I'd love to hear from you.


Some good news... Reports

You can also build reports which draw images into the output, using just the path to the image from your database! I believe this needs OO 3.2 and perhaps ORB 1.2.1. ("ORB" is Oracle Report Builder, which replaced the Sun Report Builder.) I'll put details here soon.

Here's a quick rough idea. We're just going to print out all the records, showing the bird's name, and what it looks like.

Use the Wizard to get the first draft of the report.

Step 1: Field selection: You want the "Name" and "PathToBirdsPicture" fields.

Step 2: Defaults are fine.

Step 3: Don't invoke any grouping.

Step 4: Sort options: Might as well be tidy, and sort by Name.

Step 5: Layout: Columnar, two columns. Page orientation: Portrait.

Step 6: Change the penultimate letter in Title to an "R", making it "BirdsWImagesBPR1". Leave the kind as "Dynamic". (Static is for "one off" reports.) And elect "Modify Report Layout".

Click "Finish", and the report should open in the design view.

Drag the bottom edge of the "Detail" band down until the band is about 5cm high.

The Report Controls toolbar offers a "Graphic" control. Add one to the report's "Detail" band.

Double click on the control to access its properties. On the "Data" tab, elect...

On the control's "General" tab, leave most things in their default states... their default states after you have set the properties entered via the "Data" tab. Those entries will change which fields are enabled on the "General" tab. Set "Scale" to "Keep Ratio".

Arrange and re-size things so that they are mostly to the left of the panel, and pretty well fill the height of the "Detail" band.

Don't be alarmed when you click "Execute report" and get a report with jut the name of the required images showing in outlines indicating where the images will go.

Click "Page Preview", and you can see the page(s) with the images, instead of merely with image placeholders. You will also see the image if you print the page, or if you export it to a .pdf file, so all is well, is it not?

Hurrah!!

(If the text about the "PathToBirdsPicture" which is currently part of the report offends you, you can delete it from the report's design.)


Where We've Been

Well. There you have it. We've....

Best wishes to you in your database work. Please spread the word about ooBase. A visit to my freeware page (see below) would be appreciated, too!


A final bit of advice:

I would encourage you to keep upgrading your OpenOffice installation, including things like the Oracle Report Builder (Previously called the Sun Report Builder) (I was using version 1.2.1 in my tests)....

Another reason to do the upgrade to ooBase 3.1 or above is that it allows you to store macros in .odb files. A Good Thing. Trust me!



Editorial Philosophy

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.


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 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.




Valid HTML 4.01 Transitional Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org


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

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