Skip site navigation (1) Skip section navigation (2)

Selecting large objects stored as bytea

From: "Ludger Zachewitz" <ludger(dot)zachewitz(at)gmx(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: Selecting large objects stored as bytea
Date: 2008-02-22 10:52:35
Message-ID: 20080222105235.228230@gmx.net (view raw or flat)
Thread:
Lists: pgsql-general
Hello,

I have the following problem accessing postgres (version: 8.2.X) from java 1.6_03 using driver (currently version: postgresql-8.2-508.jdbc4.jar) and hope someone can help me. I have created an table 'files' with the columns 'file_id', 'filename' and 'file_content'. 'file_content'is of type bytea and should store large binary files. A method for storing files in that table also exists and work fine.

Now I am trying to select a stored 'file_content' from the table and store it directly on the filesystem. Because the files could be very large, i don't want to hold them in the main memory -- so I am using Input- and Outputstreams (see source code below).

When i am now selecting a file_content (of only 50 MByte -- according to the pgsql documentation files of 1 GByte are supported) the running of the source code below results in a 'Out of Memory Exception' at the command line

'ResultSet rs = statement.executeQuery(selectClause);'

After increase of HEAP in java it works, but also the java needs much memory, as I don't expect it. I have also tried to substitute this command line by prepared-statement like
'PreparedStatement ps = this.dbConnection.prepareStatement(selectClause);'

Do have anyone a solution for that problem?

Thanks for any help.

Regards,
Ludger Zachewitz


SOURCE-CODE:


    public void selectLargeObject(String selectClause, File file)
           throws SQLException, IOException {
       InputStream in = null;

       this.dbConnection.setAutoCommit(false);
       Statement statement = this.dbConnection.createStatement(
               ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

       ResultSet rs = statement.executeQuery(selectClause);

       if (rs != null) {
           while (rs.next()) {
               in = rs.getBinaryStream('file_content');
           }

           FileOutputStream fos = new FileOutputStream(file);

           int readBytes = 0;
           byte[] value = new byte[1024];

           while ((readBytes = in.read(value)) > 0) {
               fos.write(value, 0, readBytes);
           }
           fos.flush();
           fos.close();
           rs.close();
       }
       statement.close();
   }


-- 
GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
Jetzt dabei sein: http://www.shortview.de/?mc=sv_ext_mf(at)gmx

Responses

pgsql-general by date

Next:From: Richard HuxtonDate: 2008-02-22 11:26:05
Subject: Re: Selecting large objects stored as bytea
Previous:From: Tino WildenhainDate: 2008-02-22 10:45:01
Subject: Re: selective backup and restore

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group