CODEWITHSUNDEEP
postgresqlpostgresql-9.1
Herr von Wurst
Herr von Wurst

Reputation: 2621

PostgreSQL export large object to client

I have a PostgreSQL 9.1 database in which pictures are stored as large objects. Is there a way to export the files to the clients filesystem through an SQL query?

select lo_export(data,'c:\img\test.jpg') from images where id=0;

I am looking for a way similar to the line above, but with the client as target. Thanks in advance!

Upvotes: 10

Views: 20940

Answers (5)

Torben
Torben

Reputation: 3913

This is borderline relevant, but since this StackOverflow question comes up every time someone searches for "lo_export" and I had to trial and error it due to lack of documentation, every object stored in raster column of table image can be exported to /tmp/exportdir/ with statement:

SELECT lo_export(image.raster, concat('/tmp/exportdir/', image.id))
FROM image

This assumes that the column named id identifies each row and contains a value that can be used as a file name.

Upvotes: 0

Andre Valdestilhas
Andre Valdestilhas

Reputation: 21

Source: http://www.postgresql.org/docs/8.4/static/lo-funcs.html

CREATE TABLE image (
    name            text,
    raster          oid
);

SELECT lo_creat(-1);       -- returns OID of new, empty large object

SELECT lo_create(43213);   -- attempts to create large object with OID 43213

SELECT lo_unlink(173454);  -- deletes large object with OID 173454

INSERT INTO image (name, raster)
    VALUES ('beautiful image', lo_import('/etc/motd'));

INSERT INTO image (name, raster)  -- same as above, but specify OID to use
    VALUES ('beautiful image', lo_import('/etc/motd', 68583));

SELECT lo_export(image.raster, '/tmp/motd') FROM image
    WHERE name = 'beautiful image';

Upvotes: 1

PresleyDias
PresleyDias

Reputation: 3713

this answer is very late but will be helpful so some one i am sure

To get the images from the server to the client system you can use this

"C:\Program Files\PostgreSQL\9.0\bin\psql.exe" -h 192.168.1.101 -p 5432 -d mDB -U mYadmin -c  "\lo_export 19135 'C://leeImage.jpeg' ";

Where

  1. h 192.168.1.101 : is the server system IP
  2. -d mDB : the database name
  3. -U mYadmin : user name
  4. \lo_export : the export function that will create the image at the client system location
  5. C://leeImage.jpeg : The location and the name of the target image from the OID of the image
  6. 19135 : this is the OID of the image in you table.

the documentation is here commandprompt.com

Upvotes: 14

Dave G
Dave G

Reputation: 9767

Georg,

According to the documentation for 9.1, lo_export is relative to the client executing the call. So if clientA is connected to databaseB, when clientA executes your SQL, lo_export should create the file on clientA where you've told it to.

In light of the fact that you've stated your using JDBC under MATLAB (I'm not familiar with what you can do under there nor am I familiar with the interface to perform the call), if you are calling this from a JDBC connection manually:

java.sql.Connection conn= ...
java.sql.Statement stmt= conn.createStmt();
java.sql.ResultSet rs= stmt.executeQuery("select data from images where id=0");
// Assume one result
rs.next();
// Gets the blob input stream
InputStream blobData= rs.getInputStream(1);

// At this point you will have to write it to a file. 
// See below

rs.close();
stmt.close();
conn.close();

I have played very loose and fast with the JDBC operations for brevity here. There should be more error checking as well as try/catch/finally statements to wrap and clean up the connections.

File copy example.

Upvotes: 2

Daniel Vérité
Daniel Vérité

Reputation: 61506

It's not possible, because all the PostgreSQL server can do is send back data to the client through the network connection that the client has established. In particular, it can't create a file on the client filesystem, only client code can do that.

Upvotes: 1

Related Questions