ODBC and LO questions....again. - Mailing list pgsql-general

Hello,

I've searched up and down for the answer to this, and I can't quite find
it. I've got an application that's being developed in Omnis Studio -
which is a RAD system from Raining Data Corp. We're a manufacturing
company which has used Omnis products to develop almost all our systems
for the past 15 years, hence lots of legacy data systems. The problem
I'm having has to do with inputting binary data and retrieving it via
ODBC at the client.

Omnis has a list type data, which is basically an array. Column data
types within the list can be of any type, including another list. We are
in the process of migrating a legacy system over, which uses Omnis
native data files, and need the ability to store these list type data
elements within our backend. We are still early in development and have
experimented with Oracle 8i and MySQL as our backends. Both allow us to
store these data types to the backend as a BLOB. What we are basically
doing, is mapping the client side data type of list to the backend type
of BLOB. I know this isn't optimal, as in many cases we *should* be
breaking out this list data as additional relational tables, since
that's in effect what they are. It's just not all that feasible at this
time.

I'd really rather use PostgreSQL  as opposed to MySQL due to it being
much more appropriate as an enterprise backend supporting procedures,
functions, triggers etc... I've tried using bytea and lo data types
without success so far. If I use the lo.so shared library from contrib
and setup the lo_in etc.. functions, it seems to work via the psql
native client. However, via ODBC, the data is never returned or entered.
In debugging it, If I try a simple insert of:

insert into test (lo_test) values ('this is a test');

I receive this error:
ERROR:  lo_in: error in parsing "this is a test"

I can use lo_import to import the contents of a file into the column,
however, but this doesn't help for client side storage of lists, binary
objects, pictures etc...

If I have previously lo_import(ed) a file into a column of type lo, I
can't get the contents via ODBC. It fails within the ODBC driver during
the PGAPI_GetData function. The error I receive is:

The buffer was too small for the GetData., SQLSTATE=01004

Interestingly, if I put the lo data type columns at the end of the row
definition, it will retrieve everything up to the first "lo" column.

What I really need, is a data type that acts similarly to the blob data
types of Oracle and MySQL. I need to be able to have the client to be
able to map its own data type (list, binary, picture etc...) to its
format regardless of how it's stored within the database. In this case,
it needs to seemlessly happen for the client to work correctly. I don't
have the luxury of any client-side processing of the data prior to it
existing within my client (bytea encoding/decoding etc...).

 From what I could see, it looks like the lo data type should do this. I
hope I'm just missing something as I'm not really sure how to work with
lo data types. For example, after importing a file into a lo column, a
subsequent select from within psql shows that column containing what
appears to be an OID. If I then select from pg_largeobject where loid=
the value of the lo, it is the object. But I can't see anyway to have my
first select transparently return *that* object from a select. Any help
would be greatly appreciated. Please CC me in any replies. Thanks,

-Walt


System Info:

PostgreSQL v. 7.3.2 on Linux
ODBC via unixODBC v. 2.2.2 and psqlodbc v. 7.2.5 on Linux

Also tried iodbc, which was less successful due to driver interaction in
Omnis.