Re: type unknown - how important is it?

Tom Lane wrote:

>Dave Cramer <pg(at)fastcrypt(dot)com> writes:
>
>
>>I just had a customer complain about this. My understanding is that
>>unkown is a constant which has not been typed yet. Is it possible for it
>>to be a binary type, if so how ?
>>I would think it should only ever be a string?
>>
>>
>
>You can read "unknown" as "string literal for which the query does not
>provide sufficient context to assign a definite type". I dunno what the
>OLE driver really needs to do with the datatype info, but I suppose that
>treating this as equivalent to "text" is not unreasonable. Ask the
>complainant what *he* thinks it should do.
>
> regards, tom lane
>
>
If JDBC can postpone the understanding what the type is until it's being
requested, all I can say is "lucky them". OLE DB isn't so lucky. We need
to actually report what type each column of a query is.

To make matters worse, there is almost not a single type that can simply
be passed along from PG to the caller. PG sends numbers in network byte
order, while OLE DB typically reports them in little endian. We do our
queries in UTF-8, while we need to return them in UTF-16, and so on and
so forth. Typically, just about any type you would care to mention would
need conversion of one type or another. I shudder to remember what I had
to do with dates.

So OLE DB performs the query in binary mode. Anything else would not
make much sense anyways. The "unknown" error may be a result of PG not
recognizing the type, as Tom mentioned, but it may also be a result of
OLE DB not recognizing the type. If the later is the case, OLE DB of
sufficiently late versions should report what OID the missing type has,
if not through the usual OLE error mechanisms, then through a log file
you can set through the registry. Read the docs for more info.

Most new types are actually fairly easy to add. With some types,
however, one would need to understand what is the expected behavior.
This is not as simple as one would expect, as most people use ADO rather
than OLE DB directly. Still, a question (to the oledb-dev list,
preferably) saying "PG type #696 should be returned as DBTYPE_UINT4"
would almost guarantee quick response, as it's fairly easy to handle (in
most cases). If this is not a PG built in type then things are a little
more complicated. Non-built in types have non-constant OIDs, and are
identified by name on session startup. This means that OLE DB cannot
handle a type that is only added to the database after the session
started (not normally a problem). Also, I cannot possibly report
non-standard types unless I know how to parse them. I'll probably add
code to handle all unknown types as BLOBs or something, but I cannot
give a time frame for that. I'm also not certain how helpful that would
be for most cases.

On the good news front, Version 1.0.0.17 is about ready to be released
(initial schema support). I am resuming development after about half a
year of doing other stuff.

Shachar

--
Shachar Shemesh
Lingnu Open Source Consulting ltd.
Have you backed up today's work? http://www.lingnu.com/backup.html