Re: BLOBs

From: Andreas Joseph Krogh <andreas(at)visena(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: BLOBs
Date: 2016-01-11 10:09:49
Message-ID: VisenaEmail.a.18468a0d72ecb60d.15230235d69@tc7-visena
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-sql

På mandag 11. januar 2016 kl. 01:37:52, skrev Eugene Yin <eugeneymail(at)ymail(dot)com
<mailto:eugeneymail(at)ymail(dot)com>>:
I use the BLOB in an Oracle table to store IMG and document files. Now for
Postgres(9.4.5), I have two options, i.e., BYTEA or OID.
 
With consideration of passing the params (SAVING) from the Java side as
follows:
 
DiskFileItemDeepy file = myFile; InputStream is = null; long fileSize = 0; if
(file != null && file.getFileSize() > 0){      is = file.getInputStream();
    fileSize = file.getFileSize();     call.setBinaryStream(1, (InputStream)is,
(long)fileSize);
}
...
call.execute();
 
 
//When retrieve the data use:
 
 java.sql.Blob blob = (Blob) resultSet.getBlob(tableColumnName); 
 
 
For the purpose mentioned above, which Postgres data type is a better
candidate for replacement the BLOB,BYTEA or OID?

 
From my experience, always use OID for BLOBs, and use the pgjdbc-ng
JDBC-driver here: https://github.com/impossibl/pgjdbc-ng
 
Maven-config:
<properties> <version.pgjdbc-ng>0.6</version.pgjdbc-ng> </properties>
<dependency> <groupId>com.impossibl.pgjdbc-ng</groupId> <artifactId>pgjdbc-ng</
artifactId> <version>${version.pgjdbc-ng}</version> <classifier>complete</
classifier> </dependency>
 
In the connection-URL use blobtype=oid:
datasource.url=jdbc:pgsql://localhost:5432/andreak?blob.type=oid
 
This is the only (as I know of) combination which lets you work with true
streams all the way down to PG. This way you can work with very large
images/movies/documents without sacrificing memory.
The official JDBC-driver for PG doesn't support BLOBs proparly, no
getBlob/createBlob (among other things, like custom type mappings).
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>

 

In response to

  • Re: BLOBs at 2016-01-11 00:37:52 from Eugene Yin

Responses

  • Re: BLOBs at 2016-01-11 15:37:50 from Eugene Yin

Browse pgsql-sql by date

  From Date Subject
Next Message Eugene Yin 2016-01-11 15:37:50 Re: BLOBs
Previous Message Eugene Yin 2016-01-11 00:37:52 Re: BLOBs