cidaris has asked for the wisdom of the Perl Monks concerning the following question:

I've been working on getting images into a MySQL database, and I think I'm doing things correctly, but wanted verification. I've seen the same thing done several different ways, so thought someone might have input.

I've been using the CGI.pm documentation, the DBD/DBI documentation, and a number of PM threads.

my $img = $query->upload('pc_img');    $img = <$img>;
my $id = $query->param('pc_id');

my $sql = "INSERT INTO data_table (id, image) VALUES (?, ?);          
+  ## id is BIGINT type, image is LONGBLOB type
my $sth = $dbh->prepare($sql) or die "Couldn't prepare: $DBI::errstr\n
+";
    $sth->execute($id, $img) or die "Unable to execute query: $DBI::er
+rstr\n";
    $sth->finish;
    $dbh->disconnect;

[download]


I'm using MySQL AB's MySQLCC to view the contents of the database, and it shows there is indeed data in the 'image' field, and if I upload a GIF, it's GIF89a and then garbled text, and if it's a JPG, usually y0ya-garbage-garbage so I think binary data is being put into these BLOB type fields.

However, the problem is as follows: MySQLCC doesn't display GIFs, only supporting 'free' formats ( damn Unisys ) and when I try to open a JPG from the viewer, it says "An error occurred while opening this image."

So my dilemna is 'Am I doing it right?' and suffering from a limitation of the program? Am I doing it wrong? Is there another application out there that will allow me to connect to a database and show me information w/o me having to install something server-side like MySQLMan?

I'd really appreciate your input.
cidaris

Replies are listed 'Best First'.
Re: Images into MySQL database
by blokhead (Monsignor) on Jan 23, 2003 at 01:33 UTC
    Considering you are getting some data into the blob column, you are on the right track. Your problem is most likely here: 
        $img = <$img>;

    [download]
    Unless you have changed $/ from its default in some unshown code, this line will only read the first line of the file (i.e., until the first \n character). The data in the database is probably the correct bytes, but only the first fraction of them. To avoid that, "slurp" the entire file in one go: 
        {
      local $/;
      $img = <$img>;
    }

    [download]
    If that doesn't work, or you're sure that the data in the db is the correct byte length, you may be facing a problem with the software. I've never used the programs you've mentioned, so I can't say.

    The rest of the code looks good. While some might cringe at your use of $img for a filehandle and then a scalar, I don't mind -- I do it too. ;)

    blokhead

[reply]
[d/l]
[select]
Re: Images into MySQL database
by gmax (Abbot) on Jan 23, 2003 at 09:14 UTC
    As merlyn would say, I have a column on that. ;)

    For an alternative way of reading a binary file and storing its data into a database, have a look at Handling huge BLOB fields with DBI and MySQL
    You may use the read function to get the data into your variable, with some more control on how much you are reading. See the "upload" sub in the above node for a suitable example.
     _  _ _  _  
(_|| | |(_|><
 _|
[reply]
      I don't like to use the word "Great American Hero" very often, but I think I'm required in this case to call gmax my new "Great American Hero".

      This kind of write-up is exactly what I was looking for, as the DBI docs and the like tend to be sparse.

      I appreciate this article more than you know.

      ++ !
      cidaris
[reply]
Re: Images into MySQL database
by Gilimanjaro (Hermit) on Jan 23, 2003 at 01:43 UTC
    This is me without docs, just my first impression...

    You're *almost* there... You've either got a knack for coding, or for copying examples, which is actually pretty much the same knack... :)

    On your very first line, you assign the value of <$img> to a scalar. This means the <> brackets are used in a scalar context, which in turn means they read the handle up to (and including) the first 'input record separator', which by default on a unix system should be a line-feed (\n or ascii 10).

    GIF (or JPG, or PNG) images can contain an ascii 10. In fact, the odds of any character being an ascii 10 are 1 in 256. Which gives you pretty good odds of not getting 'the big picture' if it's anything over 1kb....

    What would probably be the easiest way to handle this, is using the <> operator in a list context. But you still want the entir file in one string:

    $img = join '', <$img>;

    [download]

    That should work. The other way around it is to set the input record separator to 'undef' before using the <> operator.

    The drawback of that one is that it could mess up other code. The drawback of the join, is that for a brief moment, the content will have to exist as one big (anonymous) array, because of the list context of the <> operator. Immediately afterwards it will be joined, so there will be 2 copies of the file present in memory (one as a string, and one as an array of lines). The array will get garbage-collected as soon as the statement ends (I think; I'm no perl-internals kind-a-guy), so it shouldn't be a problem for any but the hugest of files.

    Having to convert the array to a big string will also have a slight performance hit, but again, not much if the file isn't huge.

    In my opinion Perl's power lies in efficient programming. And the efficiency that matters IMHO is the results / time spent equation, not the memory used / memory available one...

    Happy coding!

    Update:
    blokhead beat me to it! He got both the input records separator right, and the way to keep it local to the context at hand... And he's a faster typist!

[reply]
[d/l]
OT: Images into MySQL database
by Ryszard (Priest) on Jan 23, 2003 at 07:00 UTC
    Keep in mind while its pretty cool to keep images and binary data in a database these solutions may be prone to scalability issues (at the very least, its inefficient).

    To store binary data in a database you've got two hits:

    1. Reading the data from the database
    2. The database reading the data from the disk

    The general solution employed is to have a pointer in the database pointing to your file in the filesystem.

    Now what you have is a considerable reduction of data in the database which may easy be cached in memory, resulting in a performance increase.

    Of course I'm generalising here, there are always exceptions.. :-)

[reply]

      Keep in mind while its pretty cool to keep images and binary data in a database these solutions may be prone to scalability issues

      lol, this statement made me laugh quite a bit for one fact: does perlmonks not keep user images in the database? Each user image appears to have it's own node_id, so is this the case? Or does the node_id in this case just point to a file that the perlmonks code slurps in and outputs?

[reply]

      From a general security point of view, I think it is inadvisable to have the database point to a file in the filesystem and then allowing the user access to your filesystem to retrieve the file. It could lead to all kinds of nasty problems.

      Also as database-engines are (should?) be optimised to retrieve data from their storage, I fail to see if it would be more efficient to first retrieve from the database the pointer to the file in your filesystem and then by a totally different process get the file itself.

      Or am I missing something obvious here?

      CountZero

      "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

[reply]
        I think it is inadvisable to have the database point to a file in the filesystem

        I understand what you're saying here (someone can change a file to be something you dont want it to be) regardless of the optimisations in a modern RDBMS it is generally considered a more scalable solution to put the images on the disk and a pointer in the db.

        Of course if you've a low volume site that is lite on graphics, it may well be better to keep your graphics in a DB.

        Unfort, i dont have any metrics handy to backup my claims here..:-(

[reply]

Back to Seekers of Perl Wisdom