Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2009
    Posts
    9

    Unanswered: Problems adding comments no Large Objects with JDBC

    Hi!

    Im using PostgreSQL v8.3 with JDBC to store binary data (read from local files) as Large Objects in the database.

    At the creation of a new Large Object I want to add the filename as a comment (or 'description' as the table header says of the large object table) but I cant get it to work. It only seems to work if I add the comment from inside psql-manager like:

    Code:
    COMMENT ON LARGE OBJECT 1234 AS 'somefile.pdf';
    But my JDBC code do nothing at all (even if this is done AFTER the full creation of the Large Object):

    Code:
    // Add original filename as comment on LARGE OBJECT
    PreparedStatement psX = null;
    try {
        psX = db.prepareStatement("COMMENT ON LARGE OBJECT ? IS ?");
        psX.setLong(1, oid);
        psX.setString(2, filename);
        psX.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        close(null, psX); // dBforums readers, this is a custom method so dont copy this!
    }
    ...I cant find any error output either. Anyone who knows how to do this?


    have fun
    /codehunter

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Why would you choose this approach? It would be better to put the metadata in table where it will be easy to search, join, whatever.

  3. #3
    Join Date
    Nov 2009
    Posts
    9
    I have meta data in another table also (like file name and file size etc.), the thing is that I want an extra kind of fail safe function if something happens to the metadata table.

    I mean, why not add the original file name as a comment, its a fast operation and if I for some reason need to manually dump data from the large objects then I can use the original file name stored in the comment.

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Well, this is a duplication of data. Which means that if you were to update the data in the table, you would also have to update the data in the comments, else, your data would be inconsistent.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Nov 2009
    Posts
    9
    My idea was to never update the comment/description in the large object table, just store the original file name once (at creation). The user will only see the filename in the metadata table nor will the large object comment ever get updated if the filename in the metadata table changes. I just want the comment to give me a hint of what this large object is (where the filetype is the most interesting part really) if I ever need to fiddle directly with the large object table. Is this really a bad idea you think?

    I have been thinking a little...instead of doing this in JDBC, maybe i should assign a trigger to do it. Ill post my code if I get it to work well (need to read up about triggers first ).


    have fun
    /codehunter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •