DbMysql - Blob / Timestamp

If I had any idea how to setup blob, I would have tried this myself but I have absolutely no idea how to impliment this in your dbmysql mod.

So..=P Any plans on adding functionality to blob and timestamp field datatypes? Or suggestions?

I need to store large image files (up to 500kb) and longtext is not an option, and blob isn't functional so as far as I know, i'm out of alternatives to storing them in the database.

I'll have a look into it when I get a chance :-)

Could you base64 them and prepared insert them as Text fields?

base64 makes them bigger, and it's probably not as efficient as storing the data in its raw form.

I expect a blob will just be doing a memcopy or something similar.

Yes, unfortunately the amount of images to load will exceed my comfort and encoding will really slow down loading,processing and data transfer for what i'm trying to achieve.
I normally would just have the server save the files into a package or something, but it becomes a million times easier to have everything in a single database for backing up.

Thanks for the idea though =)

I have come across the occasional argument on the net over whether its a good thing to store large image files in a database and not via the file system. Those against claim it bloats and slows the database; and it's not like you can search against the image data anyway...

But I guess it all depends on your particular needs.

Mind you - blobs would be nice Brucey ;-)

It all depends on the database. I don't use MySQL because it forces me to either buy one of their not-so-cheap subscriptions or to GPL my own applications, so I don't know how it behaves when you heavily use BLOBs.

I know that the FoxPro database engine, for example, did not slow down when you used BLOBs - it stored its indexes, data and BLOBs in separate files, so one would not have a direct impact on the others. Only when you deleted BLOBs, the files bloated because it only marked a blob as deleted, but did not shrink the file - that you had to do manually. Anyway, using BLOBs in the FOXDBE was quick and effective.

Storing image files on the file system has a ton of implications and in my opinion only unnecessarily complicates things; not when you only have --one-- file and --one-- database server, when you start using clusters or only have very restrictive file server access, you might no longer want to use that approach.

As for not being able to search through a BLOB... Well, maybe MySQL cannot do that. But I am not so sure about Oracle, and I know for certain that back at Alaska Software we even had third party tools that allowed for extremely fast searching through the BLOBs of our database. I used BLOBs to store large texts of variable length with attachments --- or to store persistent objects.

From what I've heard already ten years ago, there -are- some very specialized applications for Oracle that actually can search through image data and that are able to process queries like "show me all the houses with a red roof" based upon the actual image data stored in the database. But, well, there must be some reasons why people are still paying those high license fees for Oracle DBs.

I agree.

For a forum, or heavily visited websites? Hell no.

For my use of hardly occasional loading of small images, and to ensure that everything, including files, are located within the same database? I'm not even going to be doing odd queries. A simple select @ ID, bank it, load it. I think blob is perfect for my needs, otherwise I agree with those people.

Is there any news on this? I really would like to be able to store pdf files in a sqlite database.

It's currently being investigated.

Unfortunately, work has eaten into some "me" time of late, so things have been moving a bit more slowly than usual.

thanks alot. but i shouldnt hold my breath i guess :)

Okay, I've implemented blob support for DBMySQL. (also requires latest bah.database)

There's a new "test", called blob_test, which loads some jpegs into a table, and later retrieves them into a pixmap, displaying the width and height.
Nothing very exciting I know...

By default when you create a TDBBlob, it will create a copy of the data. You can override this by passing False as the third parameter :

Function Set:TDBBlob(value:Byte Ptr, size:Int, copy:Int = True)

or

Method setBlob(v:Byte Ptr, s:Int, copy:Int = True)

If you use False, you'll need to ensure your data is valid (ie. not GC'd) for the duration of the SQL execution (for your Insert or Update).


I haven't tested it like a maniac yet, so there may well be issues, but on first glance it appears to work as intended.

The other DB modules will get done at some point too :-p

<EDIT> 8 months... that's a good turn-around time, huh? :-/

Awwwesome. Thanks brucey, i've been itching for this for a while now.