Re: large objects and pg 7.3.x with OpenOffice.org - Mailing list pgsql-odbc

Hello, Hiroshi
Many thanks for your message.
first, assure that you downloaded the most recent 1.1RC3 OpenOffice.org
Then, read some docs, tips and tricks, and external docs at
http://dba.openoffice.org
You will need to setup a new data source. Use the how-tos for mysql and odbc.
They are very similar to pg.
Also, you will need java 1.4.0_2 or newer installed and recognized by
OpenOffice. Autopilots and forms need java to run.

I will summarize without details and hints found at those docs:
1- using psql with a test user and pwd, create a database for testing for not
messing your other db. Let's say, odbctests.
2- setup a new DSN for odbc for this new db odbctests.
3 - Start openoffice for a new text document.
4 - Menu Tools > Data sources.
5- New data source (it is a not so obvious button at upper left).
6- Give it a name, select a db type (odbc). At this moment, the line below
changes to odbc protocol and if you click at button '...' you will able to
choose among the DSN.
7- At the odbc tab, write the username and mark if pwd is needed. OK.
8 - Press F4 or Menu > view > datasources
9 - a top frame opens, showing datasources tree.
10 - open the odbctests data source. Expand to see "tables".
11 - Right click on tables. New table design.
12 - Create an ID , int4. Click on left row to select entire line. Rigth
click. Primary key.
13 - New row for "photo", data type "lo".
14 -save table as "imagetest1"

The easiest way to create a form is using its autopilot.
15 - Menu File > autopilot > forms
 16- Choose datasource, and table, then choose to exhibit binary fields as
images and to not ignore binary fields (radio buttons at below of wizard).
 17- Click "=>" to add all fields to form.
 18- At next, choose a columnar layout, as default spreadsheet layout is not
so good for this use.
19 - create form. And choose a name to save file.

20 - The ID will appear as an input field.
21 - The image field will appear as square, transparent to allow background.
22 - click on the ID field and type an int number.
23 - double click on the image square and it will pop a file open dialog.
Choose an image file (jpeg, gif, png).
24 - Save the record using the small diskette icon at database bar at the
bottom.

Voila. Error issued.

IF, before creating the form, you manually inserted a record with an image,
then the new form will start showing that image.

I guess that openoffice escapes the binary file, in order to to pass it by
value into a SQL statement, as it is recommended for bytea and for mysql
BLOBs. But pg odbc driver expects raw binary file, not escaped one, for
longvarbinary. And postgresql/contrib/lo also does expect a file name, not a
parsed by value.

If this theory is right, mapping "lo" to bytea, and allowing it to grow up to
1GB (pg limit), instead of fixed 9 bytes, should work as a hack.
the pg lo implementation does not have such size limit and uses pg resources
more efficiently. The pg lo implementation is file oriented instead of value
oriented (as from pg documentation). But such modifications to bytea (with
the new TOAST feature?) could work well enough for daily usage (reasonable
sized jpeg, gif and png) until a real pg lo / blob become native.
bytea has internal management for deletes and updates.


At a brazilian postgresql discussion group, one told about delphi apps
complaining about images ("lo") into pg db through psqlodbc. I guess delphi
apps assume blobs as parsed by value too, like any other variable.
I imagine that odbc apps always assume "lo" as a parsed by value variable.
How can OpenOffice display previously inserted images?

Regards.
Andre Felipe



>
> I may be able to test OpenOffice but am not familiar with OpenOffice.
> How do you insert a new image using OpenOffice ?
>
> regards,
> Hiroshi Inoue