agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedRe: [SQL] Blobs
20+ messages / 12 participants
[nested] [flat]
* Fwd: Re: [SQL] Blobs
@ 2000-01-24 16:16 Compte utilisateur Sultan-advl <webmaster@advl.org>
0 siblings, 0 replies; 20+ messages in thread
From: Compte utilisateur Sultan-advl @ 2000-01-24 16:16 UTC (permalink / raw)
To: pgsql-sql
---------- Message réacheminé ----------
Subject: Re: [SQL] Blobs
Date: Mon, 24 Jan 2000 17:07:05 +0100
From: Compte utilisateur Sultan-advl <webmaster@advl.org>
You can find this in Postgresql documentation :
PostgreSQL
Prev
Chapter 44. Large Objects
Built in registered functions
There are two built-in registered functions, lo_import and lo_export which are convenient for use in SQL queries.
Here is an example of their use
CREATE TABLE image (
name text,
raster oid
);
INSERT INTO image (name, raster)
VALUES ('beautiful image', lo_import('/etc/motd'));
SELECT lo_export(image.raster, "/tmp/motd") from image
WHERE name = 'beautiful image';
Regards.
Emmanuel.
Le lun, 24 jan 2000, vous avez écrit :
> >%_Hi Folks
>
> I want to store Word docs etc into the mighty Postgres. I think this means using blobs.
>
> 1.Does anybody know the SQL to create say a table with a field called docs to store them in. I don't know what the data type is.
>
> ie something like CREATE TABLE documents docs (blob datatype ??)
>
> 2. Is there some special syntax to get the blob back. ie SELECT (special syntax)
>
> Thanks
>
> Neil@blueice
>
>
----------------------------------------
Content-Type: text/html; name="unnamed"
Content-Transfer-Encoding: quoted-printable
Content-Description:
---------------------------------------
^ permalink raw reply [nested|flat] 20+ messages in thread
* Blobs
@ 2000-01-24 22:33 Neil Cameron <n.cameron@blueice.co.uk>
2000-01-24 15:54 ` Re: [SQL] Blobs Mathijs Brands <mathijs@ilse.nl>
2000-01-24 23:49 ` Re: [SQL] Blobs Peter Eisentraut <peter_e@gmx.net>
0 siblings, 2 replies; 20+ messages in thread
From: Neil Cameron @ 2000-01-24 22:33 UTC (permalink / raw)
To: pgsql-sql
Hi Folks
I want to store Word docs etc into the mighty Postgres. I think this means using blobs.
1.Does anybody know the SQL to create say a table with a field called docs to store them in. I don't know what the data type is.
ie something like CREATE TABLE documents docs (blob datatype ??)
2. Is there some special syntax to get the blob back. ie SELECT (special syntax)
Thanks
Neil@blueice
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: [SQL] Blobs
2000-01-24 22:33 Blobs Neil Cameron <n.cameron@blueice.co.uk>
@ 2000-01-24 15:54 ` Mathijs Brands <mathijs@ilse.nl>
1 sibling, 0 replies; 20+ messages in thread
From: Mathijs Brands @ 2000-01-24 15:54 UTC (permalink / raw)
To: Neil Cameron <n.cameron@blueice.co.uk>; +Cc: pgsql-sql
On Mon, Jan 24, 2000 at 02:33:59PM -0800, Neil Cameron allegedly wrote:
> Hi Folks
>
> I want to store Word docs etc into the mighty Postgres. I think this means using blobs.
>
> 1.Does anybody know the SQL to create say a table with a field called docs to store them in. I don't know what the data type is.
>
> ie something like CREATE TABLE documents docs (blob datatype ??)
>
> 2. Is there some special syntax to get the blob back. ie SELECT (special syntax)
>
> Thanks
>
> Neil@blueice
You'll need to use large objects. The documentation explains how to (I'm
a bit lazy ;)
Mathijs
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: [SQL] Blobs
2000-01-24 22:33 Blobs Neil Cameron <n.cameron@blueice.co.uk>
@ 2000-01-24 23:49 ` Peter Eisentraut <peter_e@gmx.net>
1 sibling, 0 replies; 20+ messages in thread
From: Peter Eisentraut @ 2000-01-24 23:49 UTC (permalink / raw)
To: Neil Cameron <n.cameron@blueice.co.uk>; +Cc: pgsql-sql
You have to use "large objects". See the programmer's guide.
On 2000-01-24, Neil Cameron mentioned:
> Hi Folks
>
> I want to store Word docs etc into the mighty Postgres. I think this means using blobs.
>
> 1.Does anybody know the SQL to create say a table with a field called docs to store them in. I don't know what the data type is.
>
> ie something like CREATE TABLE documents docs (blob datatype ??)
>
> 2. Is there some special syntax to get the blob back. ie SELECT (special syntax)
>
> Thanks
>
> Neil@blueice
>
>
--
Peter Eisentraut Sernanders väg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
^ permalink raw reply [nested|flat] 20+ messages in thread
* BLOBs
@ 2000-07-27 22:19 Bernie Huang <bernie.huang@ec.gc.ca>
2000-07-28 09:40 ` Re: BLOBs =?iso-8859-15?B?QW5kcukgTuZzcw==?= <andre.nass@student.uib.no>
0 siblings, 1 reply; 20+ messages in thread
From: Bernie Huang @ 2000-07-27 22:19 UTC (permalink / raw)
To: pgsql-sql
Hi, everyone,
Browsing through the online manual, I didn't find anything related to
BLOBs, but I know there must be BLOBs since many people are asking about
it on the list and there is a pg_fetch_object() in PHP for Postgres.
Could anyone points me an URL where I can look it up? Thanks.
-Bernie
Attachments:
[text/x-vcard] bernie.huang.vcf (314B, ../../3980B577.4843C7E2@ec.gc.ca/2-bernie.huang.vcf)
download | inline:
begin:vcard
n:Huang;Bernie
tel;fax:(604)664-9195
tel;work:(604)664-9172
x-mozilla-html:TRUE
org:Environment Canada;Standards and Technology Services
adr:;;700-1200 West 73 Ave.;Vancouver;BC;V6P 6H9;Canada
version:2.1
email;internet:bernie.huang@ec.gc.ca
title:Programmer
x-mozilla-cpt:;0
fn:Bernie Huang
end:vcard
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: BLOBs
2000-07-27 22:19 BLOBs Bernie Huang <bernie.huang@ec.gc.ca>
@ 2000-07-28 09:40 ` =?iso-8859-15?B?QW5kcukgTuZzcw==?= <andre.nass@student.uib.no>
0 siblings, 0 replies; 20+ messages in thread
From: André Næss @ 2000-07-28 09:40 UTC (permalink / raw)
To: pgsql-sql
----- Original Message -----
From: "Bernie Huang" <bernie.huang@ec.gc.ca>
> Hi, everyone,
>
> Browsing through the online manual, I didn't find anything related to
> BLOBs, but I know there must be BLOBs since many people are asking about
> it on the list and there is a pg_fetch_object() in PHP for Postgres.
> Could anyone points me an URL where I can look it up? Thanks.
PHP's pg_fetch_object() actually just turns a normal row into an object
using the fieldnames as names for the instance variables. I couldn't find
anything about BLOBs in the PostgreSQL reference, but have a look at the
pg_lo****() functions in PHP, they are used for interacting with BLOBs
André Næss
^ permalink raw reply [nested|flat] 20+ messages in thread
* Blobs
@ 2003-07-10 11:10 sri devi <sridevi_racha@yahoo.co.in>
2003-07-13 06:28 ` Re: Blobs Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 1 reply; 20+ messages in thread
From: sri devi @ 2003-07-10 11:10 UTC (permalink / raw)
To: pgsql-sql
hi
we have to download url files in to oracle using BLOBs how to create oracle table stucture how to write the query,we are using oracle9i,and javaswings,reply me to this id. sridevi_racha@yahoo.co.in
thanking you
sridevi
SMS using the Yahoo! Messenger;Download latest version.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Blobs
2003-07-10 11:10 Blobs sri devi <sridevi_racha@yahoo.co.in>
@ 2003-07-13 06:28 ` Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 0 replies; 20+ messages in thread
From: Rajesh Kumar Mallah @ 2003-07-13 06:28 UTC (permalink / raw)
To: sri devi <sridevi_racha@yahoo.co.in>; pgsql-sql
Sir Devi,
This is PostgreSQL mailing List.
If you need help porting applications
from Oracle9i to PostgreSQL we might help.
Or if you have generic SQL question not specific
to any database we can also consider.
for help in Oracle specific problems there may
be more appropriate lists on the net.
Rajesh Mallah.
On Thursday 10 Jul 2003 4:40 pm, sri devi wrote:
> hi
>
> we have to download url files in to oracle using BLOBs how to create oracle
> table stucture how to write the query,we are using oracle9i,and
> javaswings,reply me to this id. sridevi_racha@yahoo.co.in thanking you
> sridevi
>
> SMS using the Yahoo! Messenger;Download latest version.
^ permalink raw reply [nested|flat] 20+ messages in thread
* BLOBs
@ 2016-01-10 06:20 Eugene Yin <eugeneymail@ymail.com>
2016-01-10 07:12 ` Re: BLOBs Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 20+ messages in thread
From: Eugene Yin @ 2016-01-10 06:20 UTC (permalink / raw)
To: pgsql-sql
The following statement is excerpted from the
"Oracle to Postgres Conversion"
https://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion#Grammar_Differences
on Postgres 8.4.
BLOBs
Binary large object support in Postgres is very poor and unsuitable for use in a 24/7 environment, because you can't dump them with pg_dump. Backing up a database that makes use of Postgres large objects requires one to knock down the RDBMS and dump the files in the database directory.Don Baccus put together a hack that extends AOLserver's postgres driver with BLOB-like support, by uuencoding/decoding binary files before stuffing them into or extracting them from the database. The resulting objects can be consistently dumped by "pg_dump" while the RDBMS is up and running. There is no need to interrupt service while making your backup.To get around the one-block limit on the size of a tuple imposed by Postgres, the driver segments the encoded data into 8K chunks.Postgres large objects are scheduled for a major overhaul in summer 2000. Because of this, only the BLOB functionality used by the ACS was implemented.
Now for Postgres 9.4.5, is backup of the BLOB still an issue as described above, OR, it now works equivalent to that of Oracle?
Thanks
Eugene
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: BLOBs
2016-01-10 06:20 BLOBs Eugene Yin <eugeneymail@ymail.com>
@ 2016-01-10 07:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-01-11 00:37 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
0 siblings, 1 reply; 20+ messages in thread
From: Tom Lane @ 2016-01-10 07:12 UTC (permalink / raw)
To: Eugene Yin <eugeneymail@ymail.com>; +Cc: pgsql-sql
Eugene Yin <eugeneymail@ymail.com> writes:
> The following statement is excerpted from the
> "Oracle to Postgres Conversion"
> https://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion#Grammar_Differences
My goodness, that's out of date (as you should have guessed from the
reference to "scheduled for an overhaul in summer 2000"). pg_dump
has been able to dump large objects just fine since 8.1 or so.
I don't know what else an Oracle user might be expecting that we don't
have, though, so I'm hesitant to change the text.
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: BLOBs
2016-01-10 06:20 BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-10 07:12 ` Re: BLOBs Tom Lane <tgl@sss.pgh.pa.us>
@ 2016-01-11 00:37 ` Eugene Yin <eugeneymail@ymail.com>
2016-01-11 10:09 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 20+ messages in thread
From: Eugene Yin @ 2016-01-11 00:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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?
Thanks
Eugene
On Saturday, January 9, 2016 11:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Eugene Yin <eugeneymail@ymail.com> writes:
> The following statement is excerpted from the
> "Oracle to Postgres Conversion"
> https://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion#Grammar_Differences
My goodness, that's out of date (as you should have guessed from the
reference to "scheduled for an overhaul in summer 2000"). pg_dump
has been able to dump large objects just fine since 8.1 or so.
I don't know what else an Oracle user might be expecting that we don't
have, though, so I'm hesitant to change the text.
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: BLOBs
2016-01-10 06:20 BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-10 07:12 ` Re: BLOBs Tom Lane <tgl@sss.pgh.pa.us>
2016-01-11 00:37 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
@ 2016-01-11 10:09 ` Andreas Joseph Krogh <andreas@visena.com>
2016-01-11 15:37 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
0 siblings, 1 reply; 20+ messages in thread
From: Andreas Joseph Krogh @ 2016-01-11 10:09 UTC (permalink / raw)
To: pgsql-sql
På mandag 11. januar 2016 kl. 01:37:52, skrev Eugene Yin <eugeneymail@ymail.com
<mailto: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?
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: BLOBs
2016-01-10 06:20 BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-10 07:12 ` Re: BLOBs Tom Lane <tgl@sss.pgh.pa.us>
2016-01-11 00:37 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-11 10:09 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
@ 2016-01-11 15:37 ` Eugene Yin <eugeneymail@ymail.com>
2016-01-11 17:48 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 20+ messages in thread
From: Eugene Yin @ 2016-01-11 15:37 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: 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>Resourcereference 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 KroghCTO / Partner - Visena ASMobile: +47 909 56 963andreas@visena.comwww.visena.com
=
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: BLOBs
2016-01-10 06:20 BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-10 07:12 ` Re: BLOBs Tom Lane <tgl@sss.pgh.pa.us>
2016-01-11 00:37 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-11 10:09 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
2016-01-11 15:37 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
@ 2016-01-11 17:48 ` Andreas Joseph Krogh <andreas@visena.com>
2016-01-12 01:32 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
0 siblings, 1 reply; 20+ messages in thread
From: Andreas Joseph Krogh @ 2016-01-11 17:48 UTC (permalink / raw)
To: pgsql-sql
På mandag 11. januar 2016 kl. 16:37:50, skrev Eugene Yin <eugeneymail@ymail.com
<mailto:eugeneymail@ymail.com>>:
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?
Using Maven is only for getting the right deps in place for your app, it has
nothing to do with configuration.
The URL should be on the form:
jdbc:pgsql://<host>:<port>/<database_name>?blob.type=oid
Other parameters are available here: http://impossibl.github.io/pgjdbc-ng/
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: BLOBs
2016-01-10 06:20 BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-10 07:12 ` Re: BLOBs Tom Lane <tgl@sss.pgh.pa.us>
2016-01-11 00:37 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-11 10:09 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
2016-01-11 15:37 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-11 17:48 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
@ 2016-01-12 01:32 ` Eugene Yin <eugeneymail@ymail.com>
2016-01-12 07:09 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 20+ messages in thread
From: Eugene Yin @ 2016-01-12 01:32 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: pgsql-sql
I did some search on the OID data type. Here is something I found regarding to the deletion of the OID data.
QUOTE:
"The Large Object method for storing binary data is better suited to storing very large values, but it has its own limitations. Specifically deleting a row that contains a Large Object reference does not delete the Large Object.
Deleting the Large Object is a separate operation that needs to be performed.
Large Objects also have some security issues since anyone connected to the database can view and/or modify any Large Object, even if they don't have permissions to view/update the row containing the Large Object reference."
From: https://jdbc.postgresql.org/documentation/84/binary-data.html
So I have two questions:
1) If it is true that "Deleting the Large Object is a separate operation that needs to be performed.", after the deletion, what operation I need to perform, in order to delete the OID data in the table? Possiblely put into an after trigger
2) "Large Objects also have some security issues since anyone connected to the database can view and/or modify any Large Object". Will this pose a real risk to the security? or just a forethought?
On Monday, January 11, 2016 9:49 AM, Andreas Joseph Krogh <andreas@visena.com> wrote:
På mandag 11. januar 2016 kl. 16:37:50, skrev Eugene Yin <eugeneymail@ymail.com>:
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?
Using Maven is only for getting the right deps in place for your app, it has nothing to do with configuration. The URL should be on the form:jdbc:pgsql://<host>:<port>/<database_name>?blob.type=oidOther parameters are available here: http://impossibl.github.io/pgjdbc-ng/ --Andreas Joseph KroghCTO / Partner - Visena ASMobile: +47 909 56 963andreas@visena.comwww.visena.com
=
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: BLOBs
2016-01-10 06:20 BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-10 07:12 ` Re: BLOBs Tom Lane <tgl@sss.pgh.pa.us>
2016-01-11 00:37 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-11 10:09 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
2016-01-11 15:37 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-11 17:48 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
2016-01-12 01:32 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
@ 2016-01-12 07:09 ` Andreas Joseph Krogh <andreas@visena.com>
2016-01-17 19:26 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
0 siblings, 1 reply; 20+ messages in thread
From: Andreas Joseph Krogh @ 2016-01-12 07:09 UTC (permalink / raw)
To: pgsql-sql
På tirsdag 12. januar 2016 kl. 02:32:46, skrev Eugene Yin <eugeneymail@ymail.com
<mailto:eugeneymail@ymail.com>>:
I did some search on the OID data type. Here is something I found regarding
to the deletion of the OID data.
QUOTE:
"The Large Object method for storing binary data is better suited to storing
very large values, but it has its own limitations. Specifically deleting a row
that contains a Large Object reference does not delete the Large Object.
Deleting the Large Object is a separate operation that needs to be performed.
Large Objects also have some security issues since anyone connected to the
database can view and/or modify any Large Object, even if they don't have
permissions to view/update the row containing the Large Object reference."
F <mailto:pgsql-sql@postgresql.org>rom:
https://jdbc.postgresql.org/documentation/84/binary-data.html
<https://jdbc.postgresql.org/documentation/84/binary-data.html;
So I have two questions:
1) If it is true that "Deleting the Large Object is a separate operation that
needs to be performed.", after the deletion, what operation I need to perform,
in order to delete the OID data in the table? Possiblely put into an after
trigger
2) "Large Objects also have some security issues since anyone connected to the
database can view and/or modify any Large Object". Will this pose a real risk
to the security? or just a forethought?
1) You don't need to perform any "after delete"-operation as a developer. But
the DBA (or someone else) has to execute vacuumlo (see "man vacuumlo" for more
info) using cron or some other periodic scheduling tool.
2) Your mileage may vary, but for our app this isn't an issue.
PS: 8.4 is EOL, use a more current version, preferably 9.5.
I also recommend the -ng driver as it's the only one with proper BLOB-support,
as mentioned earlier in this thread.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: BLOBs
2016-01-10 06:20 BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-10 07:12 ` Re: BLOBs Tom Lane <tgl@sss.pgh.pa.us>
2016-01-11 00:37 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-11 10:09 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
2016-01-11 15:37 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-11 17:48 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
2016-01-12 01:32 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-12 07:09 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
@ 2016-01-17 19:26 ` Eugene Yin <eugeneymail@ymail.com>
2016-01-17 22:08 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 20+ messages in thread
From: Eugene Yin @ 2016-01-17 19:26 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: pgsql-sql
BLOB binary large object see Large Object Support
- Minuses
- must use different interface from what is normally used to access BLOBs.
- Need to track OID. Normally a separate table with additional meta data is used to describe what each OID is.
- (8.4 and <8.4) No access controls in database.
- Sometimes advised against (basically you only need them if your entry is so large you need/want to seek and read bits and pieces of it at a time).
https://wiki.postgresql.org/wiki/BinaryFilesInDB
Do one really:
- Need to track OID. Normally a separate table with additional meta data is used to describe what each OID is.
Thanks
Eugene
On Monday, January 11, 2016 11:10 PM, Andreas Joseph Krogh <andreas@visena.com> wrote:
På tirsdag 12. januar 2016 kl. 02:32:46, skrev Eugene Yin <eugeneymail@ymail.com>:
I did some search on the OID data type. Here is something I found regarding to the deletion of the OID data. QUOTE: "The Large Object method for storing binary data is better suited to storing very large values, but it has its own limitations. Specifically deleting a row that contains a Large Object reference does not delete the Large Object. Deleting the Large Object is a separate operation that needs to be performed. Large Objects also have some security issues since anyone connected to the database can view and/or modify any Large Object, even if they don't have permissions to view/update the row containing the Large Object reference." From: https://jdbc.postgresql.org/documentation/84/binary-data.html So I have two questions: 1) If it is true that "Deleting the Large Object is a separate operation that needs to be performed.", after the deletion, what operation I need to perform, in order to delete the OID data in the table? Possiblely put into an after trigger 2) "Large Objects also have some security issues since anyone connected to the database can view and/or modify any Large Object". Will this pose a real risk to the security? or just a forethought?
1) You don't need to perform any "after delete"-operation as a developer. But the DBA (or someone else) has to execute vacuumlo (see "man vacuumlo" for more info) using cron or some other periodic scheduling tool. 2) Your mileage may vary, but for our app this isn't an issue. PS: 8.4 is EOL, use a more current version, preferably 9.5.I also recommend the -ng driver as it's the only one with proper BLOB-support, as mentioned earlier in this thread. --Andreas Joseph KroghCTO / Partner - Visena ASMobile: +47 909 56 963andreas@visena.comwww.visena.com
=
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: BLOBs
2016-01-10 06:20 BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-10 07:12 ` Re: BLOBs Tom Lane <tgl@sss.pgh.pa.us>
2016-01-11 00:37 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-11 10:09 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
2016-01-11 15:37 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-11 17:48 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
2016-01-12 01:32 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-12 07:09 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
2016-01-17 19:26 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
@ 2016-01-17 22:08 ` Andreas Joseph Krogh <andreas@visena.com>
2016-01-17 22:17 ` Re: BLOBs Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 20+ messages in thread
From: Andreas Joseph Krogh @ 2016-01-17 22:08 UTC (permalink / raw)
To: pgsql-sql
På søndag 17. januar 2016 kl. 20:26:34, skrev Eugene Yin <eugeneymail@ymail.com
<mailto:eugeneymail@ymail.com>>:
BLOB binary large object see Large Object Support
<http://www.postgresql.org/docs/current/static/largeobjects.html;
* Minuses
* must use different interface from what is normally used to access BLOBs.
* Need to track OID. Normally a separate table with additional meta data is
used to describe what each OID is.
* (8.4 and <8.4) No access controls in database.
* Sometimes advised against
<http://www.postgresql.org/message-id/20150129142450.b83ce5d0652c39791292d0d5@potentialtech.com;
(basically you only need them if your entry is so large you need/want to seek
and read bits and pieces of it at a time).
https://wiki.postgresql.org/wiki/BinaryFilesInDB
<https://wiki.postgresql.org/wiki/BinaryFilesInDB;
Do one really:
* Need to track OID. Normally a separate table with additional meta data is
used to describe what each OID is.
Using BLOBs and PG's OIDs is really simple if you use the pgjdbc-ng driver.
You column has to be defined as type=OID, like this
CREATE TABLE my_stuff(
id SERIAL PRIMARY KEY,
data OID
);
Then use the JDBC BLOB-interface as defined.
You don't need to do anything else except running the 'vacuumlo'
system-command (not SQL-command) once in a while, preferably using 'cron' or
some other scheduling-tool.
Note that "everything" you read about PG and JDBC and BLOB will describe the
official JDBC-driver, which doesn't even support BLOBs. So if you just get by
that and use the pgjdbc-ng driver your life will be much simpler.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: BLOBs
2016-01-10 06:20 BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-10 07:12 ` Re: BLOBs Tom Lane <tgl@sss.pgh.pa.us>
2016-01-11 00:37 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-11 10:09 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
2016-01-11 15:37 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-11 17:48 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
2016-01-12 01:32 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-12 07:09 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
2016-01-17 19:26 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-17 22:08 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
@ 2016-01-17 22:17 ` Thomas Kellerer <spam_eater@gmx.net>
2016-01-18 13:08 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 20+ messages in thread
From: Thomas Kellerer @ 2016-01-17 22:17 UTC (permalink / raw)
To: pgsql-sql
Andreas Joseph Krogh schrieb am 17.01.2016 um 23:08:
> will describe the official JDBC-driver which doesn't even support BLOBs.
This is not true.
using set/getBinaryStream() or set/getBytes() works just fine
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: BLOBs
2016-01-10 06:20 BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-10 07:12 ` Re: BLOBs Tom Lane <tgl@sss.pgh.pa.us>
2016-01-11 00:37 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-11 10:09 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
2016-01-11 15:37 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-11 17:48 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
2016-01-12 01:32 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-12 07:09 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
2016-01-17 19:26 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-17 22:08 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
2016-01-17 22:17 ` Re: BLOBs Thomas Kellerer <spam_eater@gmx.net>
@ 2016-01-18 13:08 ` Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 0 replies; 20+ messages in thread
From: Andreas Joseph Krogh @ 2016-01-18 13:08 UTC (permalink / raw)
To: pgsql-sql
På søndag 17. januar 2016 kl. 23:17:29, skrev Thomas Kellerer <
spam_eater@gmx.net <mailto:spam_eater@gmx.net>>:
Andreas Joseph Krogh schrieb am 17.01.2016 um 23:08:
> will describe the official JDBC-driver which doesn't even support BLOBs.
This is not true.
using set/getBinaryStream() or set/getBytes() works just fine
Just fine for you maybe, but it doesn't work for people working with large
data (think GB) and who want streams. Se my previous reply.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 20+ messages in thread
end of thread, other threads:[~2016-01-18 13:08 UTC | newest]
Thread overview: 20+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-01-24 16:16 Fwd: Re: [SQL] Blobs Compte utilisateur Sultan-advl <webmaster@advl.org>
2000-01-24 22:33 Blobs Neil Cameron <n.cameron@blueice.co.uk>
2000-01-24 15:54 ` Mathijs Brands <mathijs@ilse.nl>
2000-01-24 23:49 ` Peter Eisentraut <peter_e@gmx.net>
2000-07-27 22:19 BLOBs Bernie Huang <bernie.huang@ec.gc.ca>
2000-07-28 09:40 ` Re: BLOBs =?iso-8859-15?B?QW5kcukgTuZzcw==?= <andre.nass@student.uib.no>
2003-07-10 11:10 Blobs sri devi <sridevi_racha@yahoo.co.in>
2003-07-13 06:28 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2016-01-10 06:20 BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-10 07:12 ` Re: BLOBs Tom Lane <tgl@sss.pgh.pa.us>
2016-01-11 00:37 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-11 10:09 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
2016-01-11 15:37 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-11 17:48 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
2016-01-12 01:32 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-12 07:09 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
2016-01-17 19:26 ` Re: BLOBs Eugene Yin <eugeneymail@ymail.com>
2016-01-17 22:08 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
2016-01-17 22:17 ` Re: BLOBs Thomas Kellerer <spam_eater@gmx.net>
2016-01-18 13:08 ` Re: BLOBs Andreas Joseph Krogh <andreas@visena.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox