Re: database readers and images

[Miles Elam]

Ste'phane Delort wrote:

This is a desc of my postgresql table:
pfe=> \d document
             Table "public.document"
    Colonne      |      Type       | Modifications 
------------------+-----------------+---------------
doc_id           | integer         | not null
doc_title        | character(1024) | 
content          | oid             | 
comments         | character(2048) | 
provider         | integer         | 
documentation_id | integer         | 
doc_type         | character(128)  |

So the request I want to do in my sitemap is :

SELECT content FROM document WHERE {1}=doc_id ;

But when i do: http://localhost:8888/path_to_my_app/images/5.jpg (for
example) 

I've got :
The image "http://localhost:8888/path_to_my_app/images/5.jpg"; cannot be
displayed, because it contains errors.
(if I try with 18.jpg which does not exist I've got a sitemap error.)
 

This is related to how PostgreSQL handles BLOBs. ...or more precisely 
the many ways PostgreSQL handles BLOBs. The "content" field contains an 
object id, an integer value, the refers to BLOB data -- similar to a 
pointer. I like to think of the large object functions in PostgreSQL 
(lo_* functions) as dereferencing operators. From what I gather, the 
DatabaseReader isn't thinking of "content" as a pointer but rather the 
data itself. So the DatabaseReader is getting this oid value and sending 
it out the pipe. Not surprisingly, your browser complains that this 
isn't a valid image. ;-)

For this to work without writing some Java code, you would have to use a 
bytea (byte array) type in your document table. This puts the actual 
image data in the table row instead of just a pointer/reference.

             Table "public.document"
    Colonne      |      Type       | Modifications 
------------------+-----------------+---------------
doc_id           | integer         | not null
doc_title        | character(1024) | 
content          | bytea           | 
comments         | character(2048) | 
provider         | integer         | 
documentation_id | integer         | 
doc_type         | character(128)  |

The size of your database would remain the same, but your table rows 
would get bigger. If your images are normal web images, I wouldn't worry 
too much about the performance differences. I'd check the PostgreSQL 
mailing list archives for more info on that instead of taking my word 
for it though. I am by no means a PostgreSQL guru.

As far as using oids is concerned, I can't help you much. The JDBC code 
for getting large objects from a PostgreSQL table using oids is specific 
to the PostgreSQL JDBC driver and the behavior has changed from 7.1 to 
7.2 (or somewhere thereabouts. Read more about it here:

http://www.postgresql.org/docs/7.4/static/jdbc-binary-data.html

Specifically, the "compatible" property might help you, but I haven't 
tried it.

If this works, be sure to let the list know so that there's a record for 
the solution. And/or you could write a page on it in the Wiki 
(http://wiki.cocoondev.org/).

Best Wishes,

Miles Elam

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]