Marc's classes require that large binary data BLOBs be escaped into
ASCII; for me this takes a lot of time because I pass large iamge
bitmaps back and forth from the postgreSQL DB.
Does the new RB PostgreSQL plugin not send BLOBs in binary without
first escaping them each time?
The simple PostgreSQL frontend-backend protocol (the regular stuff
you'd use) doesn't allow to move binary data into bytea (i.e. binary)
columns without escaping them.
This can only be achieved with the extended query protocol--which
pgSQL4RB does implement as well. (pgSQL4RB does all 3 of the
PostgreSQL protocols: simple query protocol, copy sub-protocol, and
extended query protocol.)
So if the RB PostgreSQL plugin can move binary data without escaping
it then it's most likely to the Large Object Table on which there's
no security. (Obviously you can use that table as well from pgSQL4RB
but personally I wouldn't recommend it because of the many
limitations.)
Here's the first part of Appendix C of the pgSQL4RB manual FYI:
Storing binary data (Bytea and Large Objects)
Source: snippets of the PostgreSQL documentation. See "PostgreSQL License and
Copyright" in this manual's appendix for reproduction authorization.
PostgreSQL provides two distinct ways to store binary data. Binary data can be
stored in a table using PostgreSQL's binary data type Bytea , or by using the
Large Object feature which stores the binary data in a separate table in a
special format, and refers to that table by storing a value of type OID in your
table.
In order to determine which method is appropriate you need to understand the
limitations of each method. The Bytea data type is not well suited for storing
very large amounts of binary data. While a column of type Bytea can hold up
to 1 GB of binary data, it would require a huge amount of memory (RAM) to
process such a large value. 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 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.
The Bytea data type has been covered in the tutorial in this manual.
Below is an
example on how to use large objects:
There are two built-in registered functions, lo_import and lo_export which are
convenient for use in SQL queries.
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';
We do not recommend the use of the large object system. Instead, simulate it by
creating your own using the bytea method and by chopping your binary data into
chunks that you'll store over several related records.
Cheers,
Marc
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>
