Slashdot Mirror

← Back to Stories (view on slashdot.org)

Dealing with BLOBs in Postgres?

Posted by Cliff on from the binary-large-objects-with-silly-names dept.

Greyfox asks: "I've got a question and it's been bugging me for a while. I'm trying to do some database programming with Java and PostgresSQL. I've got an array of bytes I'd like to store in the database and I can't quite figure out how to do it. I've tried the method suggested in their old documentation (Which involves doing some JDBC thing with a FileInputStream) and some esoteric BLOB thing involving ResultSet.setBytes() and a more esoteric BLOB thing involving their LargeObjectManager and when I'm at my most successful I get error messages back griping about unexpected EOFs from the connection manager or somesuch. The documentation, example code and web searches have all been profoundly unhelpful. Am I going about this the right way and if I am, what am I missing?"

16 comments

  1. BLOB data in Postgres by mozkill · · Score: 1

    I am not an expert by any means. I am just poking a question and trying to get you to think about it.

    One question:

    How sure are you that your database tables are configured to be able to store BLOB data? Don't you need to specify some sort of separate tablespace to store the blob data and then assign a table column to use that space?

    --

    -- Betting on the survival of the media industry is a serious risk. I advise investing elsewhere.
    1. Re:BLOB data in Postgres by Greyfox · · Score: 2

      A BLOB is type oid in Postgres. I'm using type oid for the field I want to store the byte array in. At least, that's what all the documentation I've been able to scare up has said to do...

      --

      I'm trying to teach myself to set people on fire with my mind... Is it hot in here?

  2. Funny by JMZero · · Score: 2, Informative

    Large object documentation

    Or you can use bytea or TEXT like I do.

    -Dave

    --
    Let's not stir that bag of worms...
  3. What's going on with Slashdot? by m_ilya · · Score: 3, Interesting
    Probably I'll lose some karma but anyway.

    When I have a problem of such type and I cannot solve it myself I usually find relevant maillist and ask where. If problem is not very hard chances are that it will be answered in minutes.

    I just don't get why editors pass such submissions and people submit them. It just doesn't make any sense. If you post in PostgreSQL maillist chances that in the future another person with same problem will find solution in its archives. But who in good state of mind will search for solution of PostgreSQL very specific problem on Slashdot? On the other side I'm sure that submitter of this query will have to wait longer for reply which could solve his problems (probably forever).

    --

    --
    Ilya Martynov (http://martynov.org/)

    1. Re:What's going on with Slashdot? by platypus · · Score: 2, Informative

      Ok, I'm a bit late, but anyway:

      You are so right.

      Esp. when you go to google and type in:

      postgres 7.1 blob jdbc

      you get the following link on the first page of search results:

      http://www.postgresql.org/idocs/index.php?jdbc-l o. html

  4. Possible Solution by Slipped_Disk · · Score: 2, Informative

    One possible solution I came up with was to UUEncode the data, then store it in a text field.

    I never actually implemented this, but it should be fairly easy to do (either outside the DB or using a Postgres user-defined function.

    The existing BLOB support is documented briefly Here, but quite honestly it... well sucks.

    --
    /~mikeg
    1. Re:Possible Solution by Greyfox · · Score: 1, Troll

      Hmm. Perhaps I should look at some other database systems then. I seem to recall that us private users can get Oracle or DB/2 for free or relatively free. No point in trying to use a hammer to drive a screw...

      --

      I'm trying to teach myself to set people on fire with my mind... Is it hot in here?

    2. Re:Possible Solution by Anonymous Coward · · Score: 0

      You are thinking about switching db's simply
      because you get a handful of luke-warm responses
      from a forum that HAS NOTHING TO DO WITH POSTGRES?

      I don't get quality responses about the
      meaning of life from my dog either - but that
      doesn't mean I'm giving up on life altogether.

      Subscribe to pgsql-jdbc and ask your question.
      Instructions are here:
      http://www2.us.postgresql.org/users-lounge/index .h tml

    3. Re:Possible Solution by Anonymous Coward · · Score: 0

      large object support sucks in every database I've ever touched (mysql, postgresql, oracle, and (shudder) mssql). postgres' LOB support is not pretty, but not the ugliest out there by a long shot. oh, and running oracle on anything resembling a home workstation is an excercise in masochism, just an fyi. unless you have a 9gb scsi drive you can devote to it and 1gb+ ram... like the other replier said, ask the postgres-jdbc mailng list...

  5. Shameless plug by fm6 · · Score: 1, Offtopic

    Interbase has excellent blob support. Borland markets it as an "embedded" database, but in the real world it's used at all scales, including servers that do stock market transactions. The open source version has an active community, including a lot of people who helped create the original closed-source product.

  6. PostgreSQL JDBC docs by shrike · · Score: 2, Informative

    You should have a look at the PostgreSQL JDBC documentation, which can be found at http://jdbc.postgresql.org. While you're at it, also search for BYTEA in the PGSQL JDBC mailingslist.

  7. I'm going to be modded down for that but... by Betcour · · Score: 1

    This is one of the reasons I decided not to switch from MySQL to PostgreSQL : the BLOB support in PostgreSQL is very crappy (and the "large object interface" is a horrible hack and pain in the ass to use). (yes I know the latest version doesn't have any limit on row size anymore and could store BLOB without going thru the large object interface).