Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aIeaT-0007AJ-Sn for pgsql-sql@arkaria.postgresql.org; Mon, 11 Jan 2016 15:40:54 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aIeaT-0002SZ-0V for pgsql-sql@arkaria.postgresql.org; Mon, 11 Jan 2016 15:40:53 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aIeaS-0002R7-0n for pgsql-sql@postgresql.org; Mon, 11 Jan 2016 15:40:52 +0000 Received: from nm42-vm8.bullet.mail.ne1.yahoo.com ([98.138.120.214]) by magus.postgresql.org with esmtps (TLS1.0:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1aIeaM-0003yT-UC for pgsql-sql@postgresql.org; Mon, 11 Jan 2016 15:40:51 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ymail.com; s=s2048; t=1452526843; bh=lMRo4oxMey+UaOqTHtVaM+McKkenw9aEh6kNAVu7ZN8=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=ARdOOIg9QiaTOgiVv7KdsE5U1BO3eEwEqD2xAvZkIQcWvHb48H782dFKelGBpZxCuVyQ590dsbOVmjcXIdbO8niLPqMCPxm+CCH8WtLCgUnadE3ZqRu/VqXISirCFZ73KhiknYLzpfoK8pES577fSaS4hMXUzThm0pQtrECxUII7Z5CXPAmXhSA4IgRzTx+wJmnbHNWepofGZtKg15EM21gfPo6vU/z8o+jbGyG4fwk3WBXTY/+xHNIrUh2K1P6BggcbRWqaPlER5XvtvjnOLhC2qLHAdIhaBmZaLoFNKCDTsd0rP4x/dYgcvMH98BIGSypwZKnPkfiN02DdWzcOMw== Received: from [127.0.0.1] by nm42.bullet.mail.ne1.yahoo.com with NNFMP; 11 Jan 2016 15:40:43 -0000 Received: from [98.138.226.178] by nm42.bullet.mail.ne1.yahoo.com with NNFMP; 11 Jan 2016 15:37:51 -0000 Received: from [98.139.215.142] by tm13.bullet.mail.ne1.yahoo.com with NNFMP; 11 Jan 2016 15:37:51 -0000 Received: from [98.139.212.243] by tm13.bullet.mail.bf1.yahoo.com with NNFMP; 11 Jan 2016 15:37:51 -0000 Received: from [127.0.0.1] by omp1052.mail.bf1.yahoo.com with NNFMP; 11 Jan 2016 15:37:51 -0000 X-Yahoo-Newman-Property: ymail-4 X-Yahoo-Newman-Id: 195774.9034.bm@omp1052.mail.bf1.yahoo.com X-YMail-OSG: dqptPLEVM1m6p7qwkLRgc2CRVkLRze8J8sQVzNdqS1t7VhdomoRo2K1w_E6NJlo z4ldY1Yiz.mgVmabnEPxYv_mCUUpLeRwXxYMh4tv73xpunTissTinj8MGlZ68iPuAR55lWuadNfa w53VPbizdRdrbxSu6GB_LP7M42kFYKGwZkzBzQYdv2tfxxPPkL62PQDemLiKZ9pwYddWGSIuPy.u ksScs2BtMU.JsrzJmBPOVFK.GDbpdZJRkJsS9y4S613peAjH8Du7MbiWbE9fYmCk4xOJEyyZf817 uq_wbZ3ktxbVoteEFdzt_WNlK_vbxtFVyYeusvs_Yg89HGxphAY9.s3gmFQQiTZXAs3obVEGcFxU lIkcW2j8vIzH5aLuyZOedTCIqdcsoI3xRecGjGyRQfbDs38BtN8MvxUwo3GNbBvj9JGoBWIK0jFn g_af2WmhHshxXTRUy5oA_BX0QpaN1Bs111xab9OFXTBqcJaOnCyxPYjxQIor6YrSOpu.nl3B5tpI SJna0iNwYvw_7hDQTr6DRp1O4kcL0LlEbQn3l Received: by 66.196.80.114; Mon, 11 Jan 2016 15:37:50 +0000 Date: Mon, 11 Jan 2016 15:37:50 +0000 (UTC) From: Eugene Yin Reply-To: Eugene Yin To: Andreas Joseph Krogh Cc: "pgsql-sql@postgresql.org" Message-ID: <2023667230.3407381.1452526670272.JavaMail.yahoo@mail.yahoo.com> In-Reply-To: References: Subject: Re: BLOBs MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_Part_3407380_1669771497.1452526670271" Content-Length: 46152 X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org ------=_Part_3407380_1669771497.1452526670271 Content-Type: multipart/alternative; boundary="----=_Part_3407378_53174731.1452526670240" ------=_Part_3407378_53174731.1452526670240 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable QUOTE: Maven-config: 0.6 com.impossibl.pgjdbc-ng pgjdbc-ng ${version.pgjdbc-ng} complete I do not use Maven. =C2=A0 I use web.xml and standalone-ha.xml of JBoss AS 7.1.1 to configure the JDBC= , such as [web.xml] =C2=A0=C2=A0=C2=A0=C2=A0 Resourcereference to my= database=C2=A0 =C2=A0=C2=A0jdbc/web=C2=A0 =C2=A0=C2=A0javax.sql.DataSource=C2=A0 =C2= =A0=C2=A0Application =C2=A0 =C2=A0=C2=A0Shareable=C2=A0 =C2=A0=C2=A0 =C2=A0 [standalone-ha.xml] =C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 jdbc:oracle:thin:@19= 2.168.1.20:1521:deepy=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 oracle.jdbc.OracleDriver=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 OracleJDBCDriver=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 my= securitydomain=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = false=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 false=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 false=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 What corresponding changes I need to make to use the Postgres? Thanks Eugene =C2=A0=20 On Monday, January 11, 2016 2:10 AM, Andreas Joseph Krogh wrote: =20 P=C3=A5 mandag 11. januar 2016 kl. 01:37:52, skrev Eugene Yin : 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.=C2=A0With consider= ation of passing the params=C2=A0(SAVING) from the Java side as follows:=C2= =A0DiskFileItemDeepy file =3D myFile; InputStream is =3D null; long fileSiz= e =3D 0; if (file !=3D null && file.getFileSize() > 0){ =C2=A0=C2=A0=C2=A0= =C2=A0 is =3D file.getInputStream(); =C2=A0=C2=A0=C2=A0=C2=A0fileSize =3D f= ile.getFileSize(); =C2=A0=C2=A0=C2=A0=C2=A0call.setBinaryStream(1, (InputSt= ream)is, (long)fileSize);}...call.execute(); =C2=A0=C2=A0//When retrieve th= e data use:=C2=A0=C2=A0java.sql.Blob blob =3D (Blob) resultSet.getBlob(tabl= eColumnName);=C2=A0 =C2=A0=C2=A0For the purpose mentioned above, which Post= gres data type is a better candidate for replacement the BLOB, BYTEA or OID? =C2=A0From my experience, always use OID for BLOBs, and use the pgjdbc-ng J= DBC-driver here: https://github.com/impossibl/pgjdbc-ng=C2=A0Maven-config:<= properties> 0.6 com.impossibl.pgjdbc-ng pgjdbc-ng ${version.pgjdbc-ng} complete =C2=A0In the connection-URL use blobtype=3Doid:datasource.url=3Djdbc:pgsql:= //localhost:5432/andreak?blob.type=3Doid =C2=A0This 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).=C2=A0--Andreas Joseph KroghCTO / Partne= r - Visena ASMobile: +47 909 56 963andreas@visena.comwww.visena.com=C2=A0 =20=20= ------=_Part_3407378_53174731.1452526670240 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

QUOTE:

Maven-config= :
&=
lt;properties>
    <version.pgjdbc-ng>0.6&=
lt;/version.pgjdbc-ng>
</properties>

<=
span id=3D"yui_3_16_0_1_1452227859253_176224" style=3D"font-size: 12px;" cl=
ass=3D""><dependency>
    <groupId>com.impossibl.=
pgjdbc-ng</groupId>
    <artifactId>pgjdbc-ng&l=
t;/artifactId>
    <version>${version.pgjd=
bc-ng}</version>
    <classifier>complete<=
;/classifier>
</dependency>


I do not use= Maven.  

I us= e web.xml and standalone= -ha.xml of JBoss AS 7.1.1 to configure the JDBC, such as


=09 =09 =09
[web.xml]<= /span>

<resource-ref>
     <description>Resource reference to my database</descript= ion>
    = <= ;res-ref-name>= jdbc<= /font>/web</res-ref-name>
    <res-type>javax.sql.DataSourc= e</res-type<= font face=3D"Monospace" id=3D"yui_3_16_0_1_1452227859253_177909" class=3D""= >><= /font>
    <res-auth>Application<= /font></res-auth>
    <res-sharing-scope>Shareable= </res-sharing-scope>
 </resource-ref<= font size=3D"2" id=3D"yui_3_16_0_1_1452227859253_178041" class=3D"">>

  =  
[standalone-ha.xml]

 <datasource jta=3D"false" jndi-name=3D"java:/jdbc/web" pool-name=3D"Or= acleDS" enabled=3D"true" use-ccm=3D"false">
        &= nbsp;           <connection-url>jdbc:oracle:= thin:@192.168.1.20:1521:deepy</connection-url>
      &n= bsp;             <driver-class>oracle.j= dbc.OracleDriver</driver-class>
          &nb= sp;         <driver>OracleJDBCDriver</driver&g= t;
                    <= ;security>
                 =       <security-domain>mysecuritydomain</security-= domain>
                  &n= bsp; </security>
              &nbs= p;     <validation>
           = ;             <validate-on-match>false&= lt;/validate-on-match>
              &= nbsp;         <background-validation>false</ba= ckground-validation>
              &nb= sp;     </validation>
          &nb= sp;         <statement>
      &nbs= p;                 <share-prepar= ed-statements>false</share-prepared-statements>
     = ;               </statement>
=
 =               </datasource>
=



What corresp= onding changes I need to make to use the Postgres?



=

Th= anks

Eugene



<= /div>
<= span style=3D"font-size: 12px;">

 


=
On Monday, January 1= 1, 2016 2:10 AM, Andreas Joseph Krogh <andreas@visena.com> wrote:
=


P=C3=A5 mandag 11. januar 2016 kl. 01:37:52, skrev Eugene Y= in <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.<= /div>
&nbs= p;
With consideration of passing the param= s (SAVING) from the Java side as follows:
&nbs= p;
= DiskFileItemDeepy file =3D myFile; InputStream is =3D null; long fileSize = =3D 0; if (file !=3D null && file.getFileSize() > 0){  &nbs= p;   is =3D file.getInputStream();     fileSi= ze =3D file.getFileSize();     call.setBinaryStream(1, = (InputStream)is, (long)fileSize);
}
...
= call.execute();
&nbs= p;
&nbs= p;
= //When retrieve the data use:
&nbs= p;
=  java.sql.Blob blob =3D (Blob) resultSet.getBlob(tableColumnName); 
&nbs= p;
&nbs= p;
For the purpose mentioned above, which Postgres data type is a better c= andidate for replacement the BLOB, BYTEA or OID?
 
From my experience, always use OID for BLOBs, and use the pgjdbc-ng JD= BC-driver here: https://github.com/impossibl/pgjdbc-ng
 
Maven-config:
<properties>
    <version.pgjd=
bc-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</dependency>
 
In the connection-URL use blobtype=3Doid:
datasource.url=3Djdbc:pgsql://localhost:5432/and=
reak?blob.type=3Doid
 
This is the only (as I know of) combination which lets you work with t= rue streams all the way down to PG. This way you can work with very large i= mages/movies/documents without sacrificing memory.
The official JDBC-driver for PG doesn't support BLOBs proparly, no get= Blob/createBlob (among other things, like custom type mappings).
 
--
Andr= eas Joseph Krogh
CTO / Partne= r - Visena AS
Mobile: +47 = 909 56 963
3D""
 


= ------=_Part_3407378_53174731.1452526670240-- ------=_Part_3407380_1669771497.1452526670271 Content-Type: image/png Content-Transfer-Encoding: base64 Content-Disposition: inline Content-ID: iVBORw0KGgoAAAANSUhEUgAAAIUAAAAYCAYAAADUIj6hAAAABHNCSVQICAgI fAhkiAAABzBJREFUaEPtmNFxHDcMhmVP3i1VECpvnjzkVIHWFfhcgVcVRKrA UgWRK/C6Al8H3lTgy0PGbzFdQc4VJP/HADs43q6kROeJNbOYgQACIAgCWJKn g4MZ5gxUGXh0U0b+SIuF9G+EWXj2Q15vbrE/NPtk9uub7Gfdt5mBx1NhqSEo 8HshjbEUtlO2yIM9tsx5dZP9rPt2MzDZFAqZE4LGcFjdsg3saQaHt7fY70X9 49OnaS+OZidDBkavD33157L4xaw2os+Mp/DAC10l2XhOCeStj0W5ajrJL8Wf Ci80Xgf9vVlrhg9yRONe/f7xI2vNsIcM7JwU9o6oGyJrLT8JOA1aX9sKP4wl 94bAniukEbo/n7YPmuTETzIab4Y9ZWCrKexd8M58lxPCvvD6aihfvexbEQoP YO8NQRO0JnddGO6FJYaVsBde7cXj7KRk4LsqDxQzCYeGsJNgaXZe+JXkyGgW INq3Gp+bHNILz+wEYs5qH1eJrouNrpDXtk4O6x1IvtD40GWyJYYdkF0jIbYZ lN16xygIgl9smbMDsmFdfAKb23xGB8H/mv3tOJfArs1kukk79DEPdQ5u0g1v CvvqKXIW8mZYW+F3Tg4r8HvZkQCCLydK8EFMQCe5N8RgL9mRG0SqQFuNvdE6 beSs0qPDBuCdg0+gvClso8SbTO4ki7mQzQqBrcMHQPwReg2wW8umEe/+L8T/ LEzBGF9nXjzZ46s+ITHvhegW8LL39xm6App7KYL/GE+vcYnFbJiP/0aYhdiC nRC7jaj7eiK2ETInC5PRF6LYkSN0+HabF75WuT6syCyI0YkVGGMvUC33AmfZ TDUEj8u6IVhuEhRUJ2U2g6UlugyNb03Hl9obHwlxJbcRzcYjO4QPjVfGFTQa v4vrmp7cpMp2qbHnBxWJbisbho2QXI6C1sIHDUFhH4Hij4UbIev66eANeiwb kA+LBsO36zAHzoXU7AhbqLAXEiOYTXcSdO8VS9L4wN8UBIYhBd6oSUgYMijO kecRuTdQa/YiBXhbXFcnCnI2SrfeBG9NydrLYMhGHa5qB9pQIxlzAE6Okjzx JO5afGe6kmhBicWKQNKuTZ5EW+MjQY8v/9rQ0bhJSJyNGUe/JH1t8h1iMbdS PAvxHYin6YmN9YBXQmTYZZNh14vHhhhal5vtcIrJjmvszPRJdExH3MWHvyko BEc9CoCGWJisOLOGoCORs1FvIMbYA8z3kwM59oemy6LlWrLxFLmWgiQAfEGd 8S+NssbK+EhyGLxUkhhiy717waBqHOJYSEacwBejkFNhjJNjv/gANOdQxPfc iP/JVBCK2cOIcg3RRJ+CPrLPNVhhN6F38VKMF3XLlIJrjU5C8gMFxvKDvOcP c4rVNjCn7KM0BV+161V8viSCuJZ8SITGJGEh7IUUlxOFMYUH1kJOCN4Wh+I5 pqCuK01k40kSNtnKiKIlUfxAgW5sU5JlS05rtt5YFJF1SWoqHv6BRgQcA4/b db9WRjmMk3jyUMAbIoyJq9e4cVmgzKt9j5gNJ/aYDhk+hhjExwaPcz5PObA5 Zd9bvz5UzCTZuZDidu5AchpiKSwPR+TV1bCWqBQ9nCjJ5neivC82Nr4LeS2j 1gw5LUqwBuhGQQU5UwHeSslXkwIynz2U2A2yKDgG7OffwLA3TpGRpk0Tzpj3 ZEJXi/GRa6GN0e0NtppChePdcAz1FTS+FN8Kpxoiykk+J8fC5tenjbu9kSqp HLu9jBrhUuhNwVGbpyZrzrl0HPVD8SXj5EOOD/eDC64VjvYBZLuUbIVAfBN1 t/C/SU+cAGtdGo+fVnzycUX5wmn6eCKPmWYJG2E/ppTsuRCbvUD9fwquksG5 GqLVKhzDQ3GrEyLKSXhsiK3T5j9EyxffCFOYi2wUlPyFFDQAhehESDjQGoX0 ho0oj8RPou6TxHJdcT0NTcWkO0AnG7+uXsnHqcas/72wvWF+mSf7N/Watp9k TXoluzeS0fB99CcZ/hvhSZTfh99pispZOXL9KrGrARkNUMu9ITbScZWs7xOY Nt9pwxSZtYDsX/GE3xTkrXgwAsXmfud08FiTeC+m2/o7ppo+PTS/NBK5ARpD G5YHr++DpmVfreYdiX8mnp+DzPEG9WbqJON0JBenZofssxBA1gj5NXGvfJu/ Qh7HwQh/VDUEyUxCit5hH94QfKkEdu+GwK8BX0hvCF+D67xhjmXQCXMwJCZ+ olK0A1EKRCE4snsh42w8Mv/Zhxw9iD7Cjo7CyQC/KyF6oBfShL4PYgG+CHsY KyZxvxZSZBAgjhIzYDz+AbfD37GtbaoSKzgGt+lKfI/GZtay6vG4VXTpPsh+ IcQhOk9I7WYeP5AM3HZ9+DaSGIp9HIuuhuC4pCGGx+YD2fcc5tfIAA0h/Et4 /jX8zz4fWAasIf4UbR9Y6HO4d8jAnd4U0Y8ageuCB+c+H5R3CHU2mTMwZ+B/ y8DfSMBLLOYXVuEAAAAASUVORK5CYII= ------=_Part_3407380_1669771497.1452526670271 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql ------=_Part_3407380_1669771497.1452526670271--