Thread: large objects and pg 7.3.x
Hello, I am trying to use OpenOffice 1.1RC2 and its forms to store images (large objects) into a postgresql 7.3.2 connected through psqlodbc when at windows and unixodbc when at linux. Since pg 7.3.0, the prescribed workaround to create a new type "lo" does not work anymore. I already tested the semi-official postgresql-contrib package that contains a "lo" implementation in c and sql. It works nicely. I can store and extract images (jpeg, gif) from database and the trigger launches db cleanup when deleting. Reading the code, with my limited skill, I found the following files containing large objects code in the psqlodbc: connection.c convert.c execute.c pgtypes.c statements.c And their companion h files. Please, is this possible to modify psqlodbc to use the semi-official "lo" implementation? Regards. Andre Felipe
Andre Felipe Machado <andremachado@techforce.com.br> writes:
> Since pg 7.3.0, the prescribed workaround to create a new type "lo" does not
> work anymore.
I think this is just a matter of obsolete documentation --- if you can
point to where it still says to use a CREATE TYPE hack, we can fix it.
The easy way to define "lo" now is just as a domain over oid:
CREATE DOMAIN lo AS oid;
> I already tested the semi-official postgresql-contrib package that contains a
> "lo" implementation in c and sql.
However, if you want the cleanup semantics that the contrib package
offers, by all means use it instead. I don't believe ODBC should care
which way you've defined "lo".
regards, tom lane
Tom Lane wrote:
>
> Andre Felipe Machado <andremachado@techforce.com.br> writes:
> > Since pg 7.3.0, the prescribed workaround to create a new type "lo" does not
> > work anymore.
>
> I think this is just a matter of obsolete documentation ---
Maybe pointing to contrib/lo is preferable. However, few people
take care of contrib/lo and it seems easily broken.
PostgreSQL has never supported LO properly and I can't recommend
anything for continuous LO use.
> if you can
> point to where it still says to use a CREATE TYPE hack, we can fix it.
> The easy way to define "lo" now is just as a domain over oid:
> CREATE DOMAIN lo AS oid;
I'm suspicious if it still works even in 7.4.
Can we find the oid of the domain "lo" in the row description
message from the backend ?
regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> Tom Lane wrote:
>> The easy way to define "lo" now is just as a domain over oid:
>> CREATE DOMAIN lo AS oid;
> I'm suspicious if it still works even in 7.4.
> Can we find the oid of the domain "lo" in the row description
> message from the backend ?
Hm, I think the column type will be reported as "oid", because the
JDBC crowd argued that they didn't want to know about domains.
Is that a problem for ODBC?
regards, tom lane
Tom Lane wrote:
>
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > Tom Lane wrote:
> >> The easy way to define "lo" now is just as a domain over oid:
> >> CREATE DOMAIN lo AS oid;
>
> > I'm suspicious if it still works even in 7.4.
> > Can we find the oid of the domain "lo" in the row description
> > message from the backend ?
>
> Hm, I think the column type will be reported as "oid", because the
> JDBC crowd argued that they didn't want to know about domains.
> Is that a problem for ODBC?
Probably no except the "lo" domain case.
Psqlodbc would be able to convert PG types to corresponding
ODBC types using known base types.
regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/
Hello, Many thanks for your answer. > > I think this is just a matter of obsolete documentation --- if you can > point to where it still says to use a CREATE TYPE hack, we can fix it. http://gborg.postgresql.org/project/psqlodbc/faq/faq.php?faq_id=52 > The easy way to define "lo" now is just as a domain over oid: > CREATE DOMAIN lo AS oid; I already tested this idea before contacting the list. Unfortunately it does not work also. At least from OpenOffice 1.1rc2 connected by psqlodbc or unixodbc. Also without success, tested the idea from Hiroshi Saito (http://archives.postgresql.org/pgadmin-hackers/2003-06/msg00359.php ) Both suggestions did not work. Using postgresql-contrib "lo", I was able to insert and extract images using sql commands from inside psql. When inserted this way, psqlodbc can DISPLAY images at OpenOffice.org forms. But can not insert new ones. > > However, if you want the cleanup semantics that the contrib package > offers, by all means use it instead. I don't believe ODBC should care > which way you've defined "lo". > > regards, tom lane Please, could you give some guidance, or point to some psqlodbc devel documentation in order to modify the code? I have very limited desktop programming coding skill , but will try to get it working, without disrupting whole code. The contrib lo cleanup code seems to be context sensitive, adapting to the variable details. The contrib lo definition does not use "passedbyvalue", but use a pointer instead. It uses variable length output too. Regards. Andre Felipe
> -----Original Message----- > From: Andre Felipe Machado > Using postgresql-contrib "lo", I was able to insert and extract images using > sql commands from inside psql. > When inserted this way, psqlodbc can DISPLAY images at OpenOffice.org forms. > But can not insert new ones. Could you give us more details about "can not insert new ones", i.e. error messages ? regards, Hiroshi Inoue
Hello, Thanks for your message. I am not sure if this mail list server allow attachments. attached to this message is a compressed sql log session from unixodbc 2.2.6. A very elementary table was created. Only an int ID and an image column. The postgresql psql tool can insert an image (can also extracts it) using direct sql commands. And OpenOffice can display it at the form. When trying to save a new record, containing a new image, the program complains. The error message is reproduced at near the end of the attached sql log file. I hope this help. Regards. Andre Felipe > > > But can not insert new ones. > > Could you give us more details about "can not insert new ones", i.e. > error messages ? > > regards, > Hiroshi Inoue
Attachment
I am able to insert an image into postgresql using the psqlodbc driver. I created the lo type using the files in the contrib directory. I was never able to make the lo_manage() function work. Instead I created my own functions and used triggers to execute them. I only needed the functions for deleting and updating records. I haven't had any problems inserting. When the lo type was created it should have created the following functions : lo(oid) lo_in(cstring) lo_manage() lo_oid(public.lo) lo_out(public.lo) oid(public.lo) I hope that is somewhat helpfull. Julie <snip> ----- Original Message ----- From: "Andre Felipe Machado" <andremachado@techforce.com.br> > The postgresql psql tool can insert an image (can also extracts it) using > direct sql commands. And OpenOffice can display it at the form. > When trying to save a new record, containing a new image, the program > complains. > The error message is reproduced at near the end of the attached sql log file. > I hope this help. > Regards. > Andre Felipe </snip>
Hello, Julie Many thanks for your message. Any hint will be helpfull. I verified again and the functions are there. Also, I created a trigger for the lo_manage() function, following exactly the instructions of the contrib/lo readme. Could you tell how do you successfully insert images using the psqlodbc? I guess the problem is that openoffice creates a prepared sql and parses the lo "by value", as it expects lo to be as a "normal" blob type. The domain approach should solve this issue. But this theory should be confirmed by another experiment: I created a clean db, then declared lo as a domain (following mr. Lane suggestion) and tried to insert an image. OpenOffice issued the following error: Error inserting the new record SQL Status 01000 Error code -1 [unixODBC]Error while executing the query (non-fatal); ERROR: oidin: error in "GIF87a\216\000\076\000\367\000\000\000\000\000\000\000\100\000\000\200\000\000ÿ\000 \000\000 \100\000 \200\000 ÿ\000\100\000\000\100\100\000\100\200\000\100ÿ\000\140\000\000\140\100\000\140\200\000\140ÿ\000\200\000\000\200\100\000\200\200\000\200ÿ\000\240\000\000\240\100\000\240\200\000\240ÿ\000À\000\000À\100\000À\200\000Àÿ\000ÿ\000\000ÿ\100\000ÿ\200\000ÿÿ \000\000 \000\100 \000\200 \000ÿ \000 \100 \200 ÿ \100\000 \100\100 The error is very similar to the one issued when using another db with the postgresql/contrib/lo functions. It seems that the image is being escaped to octal before parsing to the driver, as it should be for use with bytea pg type. The previously sent sql log session file was of any help? Regards. Andre Felipe Em Terça 19 Agosto 2003 20:25, você escreveu: > I am able to insert an image into postgresql using the psqlodbc driver. I > created the lo type using the files in the contrib directory. I was never > able to make the lo_manage() function work. Instead I created my own > functions and used triggers to execute them. I only needed the functions > for deleting and updating records. I haven't had any problems inserting. > When the lo type was created it should have created the following functions > : lo(oid) > lo_in(cstring) > lo_manage() > lo_oid(public.lo) > lo_out(public.lo) > oid(public.lo) > > I hope that is somewhat helpfull. > > Julie > > <snip> > ----- Original Message ----- > From: "Andre Felipe Machado" <andremachado@techforce.com.br> > > > The postgresql psql tool can insert an image (can also extracts it) using > > direct sql commands. And OpenOffice can display it at the form. > > When trying to save a new record, containing a new image, the program > > complains. > > The error message is reproduced at near the end of the attached sql log > > file. > > > I hope this help. > > Regards. > > Andre Felipe > > </snip>