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

Open Office Database Tutorials

Putting images inside a database

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 fields which hold 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.

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 bird's photo.

Besides discussing the specific tricks to putting an image field into a database, this tutorial also gives you some help with details of creating and editing form designs.

I do go off on one little diversion, talking of extra fields you might have, but not a lot of time is spent on that.

Before we delve into the tutorial, I would like to thank the contributors to the ooBase forum at OOoForum. That is a great place to "hang out" if you want to expand your ooBase skills. Please "lurk" for a bit (read posts without writing them) if you are new to using forums. There are "rules" of etiquette to be learned. In the meantime, and always, make use of the search service. Whatever you want to know has probably already been discussed... probably several times. Get your answer now, instead of having to wait!

You can download a version of the database, but you may need at least OO 3.1 (and...maybe... SRB 1.1 (now called ORB, and in versions 1.2.1)) to open it fully. It is 3.9 meg big, because of the embedded images.

Caveat

(This caveat is also included at the start of fdb1imagbp. 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?" I think it can be done, but, except for some special cases, I think it is a Bad Idea. Those special cases would be times when there are not very many graphics to embed, and when they can be small files. We're going to do just that (embed images in the database) in this tutorial, just to get started with images in a database. The more advanced "answer" isn't so very much harder. It is explained in my tutorial about databases accessing external images.


Heart of the story

Set up the table....

Create a new database. Put it in it's own folder, call it BirdsWImages. (Most of the names I've suggested could be different, if you felt strongly about it.)(Name from "BirdsW(ith)Images".)

Set up a table. (Create in Design View would probably be fine.)

Fields:

Index (Integer:INTEGER, Autovalue=yes, primary key)
BirdsPicture (Image:LONGVARBINARY)
Name (Text:VARCHAR)

Be sure to set Index as primary key.

Save the table as BirdsWImagesT1. (The names don't have to be unique, but I like to fight ambiguity.)

A biologist doing this might say that in place of a "boring" number called "Index", using the "Latin" name for the primary key would make sense. It would be using a natural unique key, something that you always want to look for ways to do. However, in this case we might have more than one record per species, so the "Latin" name would only be part of the answer, anyway. (We might, for instance, not have just one picture for the mallard duck... The male and female look different, as do the juveniles.)


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

They are in a Windows "self-extracting archive" prepared with WinZip. Before you download the archive, make a folder for it. You'll use the same folder for the images that will come from the archive. The folder can be dispensed with before you finish this tutorial, so don't agonize over "the right place" for it. Once you have the folder, all you have to do is download the archive, and then 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.) (I don't know the complete list of image formats that ooBase can handle. I believe it is quite extensive.) (I tried putting an animated gif into the database. It was possible to have it in the database, but only the first frame of the animation was displayed, i.e. you could see the gif... but "paused". If anyone knows how to get it to work more fully, please get in touch?)

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

Once you have run the self-extracting archive once, you won't need it anymore, other than for backup. Once the database is complete, you won't need the .bmp files, either. (Copies of the images will exist within the database. There is another approach for managing image data. It is beyond the scope of this tutorial, but, in a nutshell, the database is set up to store the path to the image file rather than the image itself. that approach is covered in another tutorial, but I would encourage you to finish the one you are reading now, as the other tutorial assumes you've read what follows here.)

The photos in the set you were given are all about the same size. I'll say more on the subject of the images' sizes later.


And now for the data entry form....

Get a form started by using the Wizard....

At "Step 1", I hit the first "little mystery": Only the Index and Name fields appeared to be available for inclusion in the form, despite an explicit note once present in the wizard window which said (and may still be saying!) "Binary fields are always listed and selectable from the left list. If possible, they are interpreted as images." That seems to be wrong, but fear not! We're using the wizard simply to create a first draft of the form we need. We want the "Name" field, but don't need the "Index" field on our form. Click Next.

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

Step 5: Arrange the controls "Columnar- labels on top." 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 "BirdsWImagesF1", and select "Modify the form", and click "Finish".

Don't be alarmed if a few things are odd... like the labels above the edit boxes.

You may need to widen the controls to see all of the text of the label.

(Most readers will be able to just go on to the next steps. If, however, you have closed the form before you start (or re-visit) the following steps, be advised that you must not re-open the form simply by clicking on its name in the ooBase project management window. You need to right-click and select "Edit". This is not a quirk of the database with images; it is an always present safeguard.)

Use View | Toolbars to be sure that the Form Controls and Form Design toolbars are showing. (Details at my "Naming of Parts" page for the Form Controls and More Controls toolbars.)

Be sure you are in design mode. (If you click on the already present Name-field control while you are in design mode, you will get 8 drag handles. There's a "Design Mode On/ Off" button on the Form Design toolbar to switch in and out of design mode. (Details at my "Naming of Parts" page for the Form Navigator and that. Is there no end to the help available?!)

If, in the wizard, you, like me, were only able to add the Index and Name fields to the form, you will add the BirdsPicture field to the form "by hand", in Form Design mode. You might think it would be easy, and it is, but there are a few little twists. What follows is probably at the heart of most people's problems with doing databases with images... the rest of the job is pretty straightforward. I found this part of "my" tutorial in a post by the helpful Drew Jensen in the excellentOOoForum. I have lightly modified version of what he said. If you are not familiar with the Form Navigator (as distinct from the ordinary, F5, navigator, you may want to look at my just mentioned short introduction to the Form Navigator.

BEGIN QUOTE...

Click the 'Form Navigator' button on the form design toolbar.

This opens a window with a tree view showing all your form's dataform controls.

In the case of a form newly created with the wizard, the dataform you want is named MainForm. Using your mouse select this dataform.

Back on the Form Design toolbar is a button for Add Field; it is the button just to the right of the Form Navigator button. Click on it.

This opens a window listing all of the fields in the datasource used by the dataform control. You should find "BirdsPicture" in the list.

Use your mouse to drag the image field from the Add Field window onto the form.

What you've just done should add two controls to your form, a label field and an image control.

....END QUOTE

The two controls are, at the moment, "grouped". If, still in design mode, you click on what you added you should get drag handles, and then you can move it around to just the right spot. The new label field and the new image control will move together, because they are grouped. This is good if you want to position them. It is not so helpful if you want to change something specific to one of them, for instance (as you may have to) the size of the label field.

Before you can access the specifics of either, you need to "ungroup" them. Happily this is easy.

Right-click on the group. You should see "group" on the menu, and an "ungroup" option once you click on that. Just before you ungroup the label field and the image control, click on "Control...". You'll see a few properties, e.g. enabled (yes/ no) and background color. Now ungroup the members of the group. Click outside the group's boundaries, to de-select the members of the group. (Previously, you had the group selected. It is possible to select multiple controls, in which case what you see is similar to what you see if you group them and select the group.) Now right-click on the image control, and select "Control..." again. Now you see rather more properties, and data and events tags have also appeared.

So: to work with a "broad brush", you can group things, and operate on the group as an entity. For detailed work, you need to ungroup things, and work on each control individually.

A quick word about selecting multiple objects and how to group them, and then we'll get back to the database with images.

I couldn't find a way to select multiple objects by shift-click or control-click... but there is a simple way. All you have to do is drag a box out around the objects you wish to select. The "select" tool has to be the one currently selected, but it probably is, anyway. It is the mouse pointer arrow at the left end of both the form design and the form controls toolbar.

There is one little trick to selecting multiple objects: The box you drag out must completely enclose all of every object you want in the multi-selection.

As you have probably guessed: To group (or re-group) objects: Select all the objects you want in the group. Right-click inside the selection. Click "Group..." and then "Group" again from the sub-menu. Easy when you know how!

Add a navigation bar control to the form, too. It is on the More Controls toolbar. (See my "Naming of Parts" note, if you need help getting the navigation bar control.)

Re-size the form and its elements to suitable sizes.

Leave the design mode. The form's now ready. Next we're going to put some data in the table's records.... words in the "Name" field (the bird names) and pictures in the "BirdPicture" field. Again, it is easy when you know how.

Entering the data...

Now... to enter our first record. Obviously, you're not going to "type" the photo into the record... but how DO you enter a picture?

If you right-click on the image field on the form, and keep the button down (unlike a normal "right click"), you'll get "insert graphic from..." Move the mouse pointer onto the "insert graphic from..." text and then release it. A "load file" dialog should come up. Pick an graphic. It should appear in the field on your form! Fill in the bird's name. That's it!

Use the navigation bar to go to the next record. Add another image.

Beware: At this stage it would probably be a good idea to completely shut ooBase down. Close the form. Close the table if it is still open. Close ooBase.

You don't have to do this every time you enter two records! But as this is a new database, and as you are (presumably) new to adding images to a database, this double-check that all is well seems warranted. There are lots of things in computers that work once... but then mysteriously are "broken" the next time you try them from a standing start. Happily, usually if things work on the second trial, they will work on the third, fourth, fifth, etc.

Restart ooBase. Reopen the form. If all has gone well, you'll see your data!

We have a working database, with images!

Some experiments... (You can skip this section.)

After putting two .bmps into my database, one of 287k, the other of 248k, the database file (BirdsWImages.odb) was 759k.

I then went back to the database and entered three more records, involving 670k more in image data. I left the last record I entered, to "ensure" that it had been written to the database.

Here's the "odd" thing: I looked at BirdsWImages.odb again... and it was still only 759k. Here's a nasty little secret: ooBase caches things to memory. It doesn't automatically flush things to disk as soon as you might think it would. It didn't write the new data to the disk when I closed the table. It only wrote to the disk when I closed ooBase. (The file then "grew" to 1,783k.) Now... this is great for performance... but! What happens if you have a crash at the wrong moment? There is a macro you can add to your database which will allow you to force a flush- to- disk, but that's a story for another time. If you BUY a piece of my software, I will get the tutorial written up before I cash your check! (But remind me of those terms when you send it.)

A word about the images... and an important property of the image object

There are many ways to store images on a computer, and many choices associated with each way. As you work with images, you will learn more about...

I'll go into these in a moment, but first a word about why we care.

The different choices allow us to achieve a compromise appropriate to our needs. We have to get a balance between having a "good" image, and having an image that doesn't take acres of disk space, hours to manipulate, hours to fetch over the internet, can't be used on many people's systems, etc, etc. (That last "wished for" item might be the opposite of what we want in some circumstances. Presumably the CIA has images which it would prefer to be readable only on their computers? It can be done... with the storage format.)

One other thing, before discussing the image properties just listed: Tools. Any decent photo manipulation software will have a way to export images to a variety of storage formats, at resolutions, etc, of the user's choosing. (I like Serif's PhotoPlus, not least because Serif will give you a free copy of PhotoPlus, with no unreasonable restrictions, of a very useable, if not completely up to date, version. And if you like that, the cost of their latest/ greatest version is reasonable. (That page is sometimes unresponsive. Try it at ten minute intervals if it is not responding.) See also my page about free tools for Windows.)

If you want to get a little way into manipulating images, without "doing" very much to your computer, or taking on another learning chore, the excellent Irfan Viewer will do a lot with image re-saving, size changing, etc. While I said you might want to avoid a learning chore, and you can use Irfan for many things without any study, if you do take time in it's help files, you will find it amazingly capable. For instance: My camera puts ExIF data into my jpegs. With Irfan I can change the names "IMG0234", "IMG0235", IMG0236", etc, to "2007-06-12 12:00:00", "2007-06-12 12:01:06", "2007-06-12 12:01:58", assuming the photos were taken on June 12th, around noon... all automatically. I don't have to type in all that "stuff"... Irfan will pull it from the image's internal data.

So! Back to the mini tutorial on images...

Storage formats: You've probably heard of "bmp", "jpeg", etc? These are storage formats. They each have pros and cons... or else one would have replaced all the others, and life would be easier. If less full of choices. Don't worry... as long as you let your government officials let Microsoft do what it likes, it won't be long before the spurs to Microsoft's creativity are driven out of business, and then Microsoft can stop bothering with giving us choices. Miao.

Resolution: This is the "pixels per inch" property of an image, also called "dots per inch", or dpi. It is only meaningless if you have defined the size of the image you are looking at. The same image, printed once at 8" x 12" and a second time at 4" x 6" will "have" different resolutions... the big one's resolution... pixels per inch... will be lower. Note that if you were to re-do the image so that it, at a given physical size, went from 100 dpi to 200 dpi, you wouldn't double the amount of disc space needed to store it... you would quadruple the storage space need.

Image size: This can mean several things. It may refer to the physical size of the image on the page or screen, i.e. something like 8" x 10", or it may refer to how much space is needed on a disk to store the image, or it may refer to the dimensions of the image in pixels. "Image size" in that sense and resolution interact the way speed, time and distance-travelled interact.

Color depth: Some images have only black, shades of gray, and white in them. They have a very low color depth, although even here you are speaking of color depth when you start talking about how many shades of gray are present. A fancier image may have some colors, but not a large number of different colors. If your image has more different colors, and shades/ tints of colors, then it has a greater "color depth".

Image properties and our database with images

By now, you are probably wondering "Why is he talking about image properties?"

The reason is that you need to master them to make the right choices, and to prepare your images properly for inclusion in your database. Err one way, and the quality of the images will be inadequate. Err the other, and the images will take up so much space in the database, and hence on your disk, that the database runs slowly.

An important property of the ooBase Image object....

If you reopen the form we made for viewing the birds database, ungroup the image control if necessary, and look at its properties, you'll see one called "Scale". Before I say what that will do, one thing that it won't: It won't cause the size of the image object on your form to change, regardless of how you set it.

If this is set to "Yes", then any image that you view with the form will be stretched shrunk as necessary to make it exactly fill the image object on the form. As long as all of your images have the same aspect ratio as each other and as the image object, all will be well. (Aspect ratio: The answer you get if you divide the height of the image by its width.) Even if all of your images have the same aspect ratio, you may still regret having "Scale" set to "Yes" because it will hide problems. If your images are bigger than they need be to fill the box on the form, you are wasting disc space. If they are smaller, you are not getting a "better" picture just because you've blown it up. In extreme cases, you'll see pixellation... the little squares, one per enlarged pixel.

Prior to ooBase 3.1, if your images did not all have the same aspect ratio as the image object on the form, then if "Scale" was set to "Yes", the images were displayed with distortion. (The absence of this feature is a flaw in some slide show/ screensaver applications) A panorama will fit in a square... as long as you don't mind it being "squished" in its horizontal dimension. Happily, from ooBase 3.1 this is no longer a problem. With 3.1, we were given an additional choice: "Keep Ratio", which is what you will usually want.

So! What are the disadvantages of setting "Scale" to "No"?

You may prefer distortion to not being able to see all of an image, which is what will happen if the image is too big for the image object. If all of your pictures have similar aspect ratios, even if they are not all exactly the same, you may never notice the distortion. Setting "Scale" to "Yes" also produces a "tidy" result with less effort.


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!


Where You Can Go

Once you have upgraded to at least ooBase 3.1... and I would encourage you to do so.... and (only perhaps necessary... but a good idea anyway) upgraded to the current version of the Oracle Report Builder (Once called the Sun Report Builder, and usually referred to as "the SRB". I was using version 1.1 in my tests)....

You can generate reports with images in them. There's an example in the demo database that you can download. (That's just another link to the same one you were invited to download at the start of this.)

Another reason to do the upgrade to ooBase 3.1 is that it seems to allow 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 . . . . .