#1
  1. No Profile Picture
    elvis
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    28
    Rep Power
    0

    How to save bitmap image into postgreSQL


    hi all,
    Is there anyone of you know how to save bitmap file into PostgreSQL database and load bitmap file from PostgreSQL database? Please help.

    regards,
    elvis
  2. #2
  3. No Profile Picture
    Clueless llama
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Feb 2001
    Location
    Lincoln, NE. USA
    Posts
    2,353
    Rep Power
    117
    I am moving this to the PostgreSQL forum
    ~Nemi

    Before posting did you try:
    [ Javadocs | Google ]
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    save a URL/URI in the database, and embed the source of the link in your application. It's not good practice to store images in BLOBs.

    From the documentation:
    The Large Object method for storing binary data is better suited to storing very large values, but it has its own limitations. Specifically deleting a row that contains a Large Object does not delete the Large Object. Deleting the Large Object is a separate operation that needs to be performed. Large Objects also have some security issues since anyone connected to the database case view and/or modify any Large Object, even if they don't have permissions to view/update the row containing the Large Object.


    here's a linkie

    here's some code, lookie lookie!
    From the documenatation:
    Code:
     For example, suppose you have a table containing the file name of an image and you also want to store the image in a bytea column:
    
    CREATE TABLE images (imgname text, img bytea);
    
    To insert an image, you would use:
    
    File file = new File("myimage.gif");
     FileInputStream fis = new FileInputStream(file);
     PreparedStatement ps = conn.prepareStatement("INSERT INTO images VALUES (?, ?)");
     ps.setString(1, file.getName());
     ps.setBinaryStream(2, fis, file.length());
     ps.executeUpdate();
     ps.close();
     fis.close();
    
    Here, setBinaryStream() transfers a set number of bytes from a stream into the column of type bytea. This also could have been done using the setBytes() method if the contents of the image was already in a byte[].
    
    Retrieving an image is even easier. (We use PreparedStatement here, but the Statement class can equally be used.)
    
    PreparedStatement ps = con.prepareStatement("SELECT img FROM images WHERE imgname=?");
     ps.setString(1, "myimage.gif");
     ResultSet rs = ps.executeQuery();
     if (rs != null) {
         while(rs.next()) {
             byte[] imgBytes = rs.getBytes(1);
             // use the stream in some way here
         }
         rs.close();
     }
     ps.close();
    
    Here the binary data was retrieved as an byte[]. You could have used a InputStream object instead.
    
    Alternatively you could be storing a very large file and want to use the LargeObject API to store the file:
    
    CREATE TABLE imagesLO (imgname text, imgOID OID);
    
    To insert an image, you would use:
    
    // All LargeObject API calls must be within a transaction
     conn.setAutoCommit(false);
     
     // Get the Large Object Manager to perform operations with
     LargeObjectManager lobj = ((org.postgresql.PGConnection)conn).getLargeObjectAPI();
     
     //create a new large object
     int oid = lobj.create(LargeObjectManager.READ | LargeObjectManager.WRITE);
     
     //open the large object for write
     LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);
     
     // Now open the file
     File file = new File("myimage.gif");
     FileInputStream fis = new FileInputStream(file);
     
     // copy the data from the file to the large object
     byte buf[] = new byte[2048];
     int s, tl = 0;
     while ((s = fis.read(buf, 0, 2048)) > 0)
     {
     obj.write(buf, 0, s);
     tl += s;
     }
     
     // Close the large object
     obj.close();
     
     //Now insert the row into imagesLO
     PreparedStatement ps = conn.prepareStatement("INSERT INTO imagesLO VALUES (?, ?)");
     ps.setString(1, file.getName());
     ps.setInt(2, oid);
     ps.executeUpdate();
     ps.close();
     fis.close();
    
    Retrieving the image from the Large Object:
    
    // All LargeObject API calls must be within a transaction
     conn.setAutoCommit(false);
     
     // Get the Large Object Manager to perform operations with
     LargeObjectManager lobj = ((org.postgresql.PGConnection)conn).getLargeObjectAPI();
     
     PreparedStatement ps = con.prepareStatement("SELECT imgOID FROM imagesLO WHERE imgname=?");
     ps.setString(1, "myimage.gif");
     ResultSet rs = ps.executeQuery();
     if (rs != null) {
         while(rs.next()) {
     //open the large object for reading
     int oid = rs.getInt(1);
     LargeObject obj = lobj.open(oid, LargeObjectManager.READ);
     
     //read the data
     byte buf[] = new byte[obj.size()];
     obj.read(buf, 0, obj.size());
     //do something with the data read here
     
     // Close the object
     obj.close();
         }
         rs.close();
     }
     ps.close();
  6. #4
  7. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    MetaBarf,

    Don't be so quick to judge someone's ... er judgement, on storing images in the database. Depending on the needs and business logic involved, this might be a perfectly valid thing to do.

    Unfortunately, PostgreSQL's implementation of Large Objects is limited, in the sense that it doesn't apply full database constraints, as you can see from the documentation you posted. Also, large objects a somewhat of a pain to work with.

    But the BYTEA datatype is a much more "standard" datatype--very easy to work with, and it enforces all built-in database constraints and permissions. (essentially, you just prepare a binary stream of data, and store directly in the column, as you would store text). BYTEA columns can store images or any binary data up to 1 GB per column row. I'm sure database performance would not be the greatest with files that size, but If the images you are dealing with are generally small (a few MB or less), I would say BYTEA is a great choice, and have used it myself in certain situations. It certainly gives you the kind of control needed for such things as document management solutions.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    The bytea datatype does seem to be a good jab at solving this fairly common issue; I wish that the original post had contained some more info about the project, so we could give a better response.

    Of important note,

    7.2 is the first release that contains the bytea type!!! My references and link were from the 7.3 docs.
  10. #6
  11. No Profile Picture
    elvis
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    28
    Rep Power
    0
    hi all,
    Thanks to ~nemi direct me to the correct forum.

    Well guys, my intention is to store BMP image into PostgreSQL via Java And retrive it at later time. I'm new to PostGreSQL. I'm sure someone out there could give me better guideline on how to store BMP image via Java. So, this is a part of the project I involving.

    Hence, I saw some postgreSQL document state the same as what metaBarf had mentioned. Of coz, while other gave also their technical opinion. Well, my BMP file could up to 2 MB at this moment and don't see it could go up to 1GB coming a year time.

    As refer to rycamor spoken, I will implement Binary Stream as the BMP file relatively small.

    Thanks to metaBarf guide line and rycamor's opinion.

    Goodday
    warmest regards,
    elvis

IMN logo majestic logo threadwatch logo seochat tools logo