Re: BLOBs - Mailing list pgsql-sql
QUOTE:
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>
I do not use Maven.
I use web.xml and standalone-ha.xml of JBoss AS 7.1.1 to configure the JDBC, such as
[web.xml]
<resource-ref>
<description>Resource reference to my database</description>
<res-ref-name>jdbc/web</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Application</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>
[standalone-ha.xml]
<datasource jta="false" jndi-name="java:/jdbc/web" pool-name="OracleDS" enabled="true" use-ccm="false">
<connection-url>jdbc:oracle:thin:@192.168.1.20:1521:deepy</connection-url>
<driver-class>oracle.jdbc.OracleDriver</driver-class>
<driver>OracleJDBCDriver</driver>
<security>
<security-domain>mysecuritydomain</security-domain>
</security>
<validation>
<validate-on-match>false</validate-on-match>
<background-validation>false</background-validation>
</validation>
<statement>
<share-prepared-statements>false</share-prepared-statements>
</statement>
</datasource>
What corresponding changes I need to make to use the Postgres?
Thanks
Eugene
On Monday, January 11, 2016 2:10 AM, Andreas Joseph Krogh <andreas@visena.com> wrote:
På mandag 11. januar 2016 kl. 01:37:52, skrev Eugene Yin <eugeneymail@ymail.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