Re: type unknown - how important is it? - Mailing list pgsql-hackers

Tom Lane wrote:

>Dave Cramer <pg@fastcrypt.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