Save/incorporate related files inside Base file?

[Horus]

Sometimes I have some other files related to database files: eg text files, some pics, some document files, some spreadsheet files, etc. Of course, I could just put them in the same folder as the database files.

If I'm using Access 2007/2010, in order to make sure I don't forget any of the related files or accidentally delete some of them, I would create a bogus table inside the Access file, and use an Attachment field to save the files. Sure, this is not the canonical way of using an Attachment field type but it is getting the job done and suits perfectly my needs.

In OO Base, I can see that there are Image field type and Binary field type, but both of them only accept image files (and as a matter of fact, I don't see the difference between both types -- but this is off-topic). So is there something similar which would allow me to save/incorporate any files inside database file?

[r4zoli]

Binary field for storing files.
If you stores files inside database it makes big performance drop.
Needs some macro to store file inside database.
Better solution store link to file, in text field.

[Horus]

Binary field for storing files.

I don't see how because Binary field only accepts image file! (This is how I did: I made a form for the Binary field. I opened the form and double-click on the corresponding field. A dialog opens but accepted file extensions are only image types.)

If you stores files inside database it makes big performance drop.

I know, but I don't care.

Needs some macro to store file inside database.

How exactly is this done?

Better solution store link to file, in text field.

This doesn't "do the job as I wanted", so sorry, I'm not interested.

[rudolfo]

I haven't seen any example for using BLOBs or CLOBs in Base programming. Probably because nobody is using it as it tends to be terribly difficult. Personally I would drop Base and Basic when I have to deal with BLOBs and use Python and maybe the OpenOffice Forms as graphical frontend. But that would actually be the worst implementation of the MVC pattern I could think of.
With "normal" column types your Basic code would be something like:

Code: Select all

stmt = Connection.PrepareStatement("update table_x set col_y = ? where ...")
stmt.setString(1, your_string_variable)
stmt.executeUpdate()

But stmt.setBlob() is more complicated as it will probably require you to open an input stream. See API documentation for XBlob interface and for setBlob(). Also the SimpleFileAccess service will be interesting for you.

That's only touching the surface as I haven't done anything in this area ... but you should have some starting points now.

[r4zoli]

BLOB, CLOB not supported by hsqldb 1.8.x which works in OOo until now.
May be some external databases support BLOBs, but OOo BLOB support will be in OOo versions when the hsqldb 2.x will be integrated into OOo, then will support BLOB fully.

[DACM]

Take a look at this workaround for the built-in HSQLDB 1.8.x which requires separate tables and a subquery for binary-file access. Otherwise, the "Embedded database" configuration performance drops very quickly as the binary-field data is squeezed through a soda-straw (tiny cache) even for simple queries -- although you can improve performance by adjusting the cache settings inside your .odb file using a zip utility (like 7-zip).

But the best solution involves an upgrade to HSQLDB 2.x as mentioned previously in this thread, and as outlined in my signature links below. HSQLDB 2.x eliminates the need for workarounds and the associated sub-queries through native BLOB/CLOB support. This support is similar to that of H2 and extensions for MySQL. When storing LOB (large object; files) data to a table, the data itself is automatically stored to a speacial LOB table, separate from the original table. A lightweight placeholder is automatically setup in the original table. The result is a table which only appears to contain the LOB data, so performance and SQL commands are unaffected, even by Gigabyte-size data files such as HD video.

[Gentlebeard]

Better solution store link to file, in text field.

That's thinking like the database. Thanks for the ray of insight!

[Gentlebeard]

Better solution store link to file, in text field.

That's thinking like the database. Thanks for the ray of insight!