I have a complaint from an OLE DB user that when he does "select 'a'", 
he gets an "unhanded type" error. Since OLE DB uses a binary interface, 
it has to know about all variable types that pass through it.

The debug information for the problem show that the returned type is 
705, which is "unknown". My question is - what is "unknown" used for? Is 
it important to support binary send and receives with this type? Does 
postgresql know how to convert it to other types?

I was under the impression that 'a' would be "text". Why is "unknown" 
used instead?

             Shachar

-- 
Shachar Shemesh
Lingnu Open Source Consulting ltd.
http://www.lingnu.com/


Tue, 28 Sep 2004 20:27:21 +0200
Shachar Shemesh <psql@shemesh.biz>
type unknown - how important is it?
Shachar Shemesh wrote:
> I have a complaint from an OLE DB user that when he does "select 'a'", 
> he gets an "unhanded type" error. Since OLE DB uses a binary interface, 
> it has to know about all variable types that pass through it.
> 
> The debug information for the problem show that the returned type is 
> 705, which is "unknown". My question is - what is "unknown" used for? Is 
> it important to support binary send and receives with this type? Does 
> postgresql know how to convert it to other types?
> 
> I was under the impression that 'a' would be "text". Why is "unknown" 
> used instead?

Try casting a to varchar. Which is binary compatible but understood by 
ODBC/OLE DB.

Sincerely,

Joshua D. Drake



> 
>             Shachar
>
Tue, 28 Sep 2004 12:57:46 -0700
"Joshua D. Drake" <jd@commandprompt.com>
Re: type unknown - how important is it?
Joshua D. Drake wrote:

> Shachar Shemesh wrote:
>
>> I have a complaint from an OLE DB user that when he does "select 
>> 'a'", he gets an "unhanded type" error. Since OLE DB uses a binary 
>> interface, it has to know about all variable types that pass through it.
>>
>> The debug information for the problem show that the returned type is 
>> 705, which is "unknown". My question is - what is "unknown" used for? 
>> Is it important to support binary send and receives with this type? 
>> Does postgresql know how to convert it to other types?
>>
>> I was under the impression that 'a' would be "text". Why is "unknown" 
>> used instead?
>
>
> Try casting a to varchar. Which is binary compatible but understood by 
> ODBC/OLE DB.

Hmm, I did not explain myself clearly enough.

I maintain the OLE DB provider. My question was not that. My question 
was whether I should add support to unknown? If so, what type should it 
be represented to outside applications?

If unknown is binary compatible to text, I can just pass it on as text 
and have it done with. The question is whether that makes sense, whether 
it makes more sense to pull and error if that happens, or whether there 
is something else which I should do.

Thanks,

       Shachar

-- 
Shachar Shemesh
Lingnu Open Source Consulting ltd.
http://www.lingnu.com/


Tue, 28 Sep 2004 22:17:01 +0200
Shachar Shemesh <psql@shemesh.biz>
Re: type unknown - how important is it?
Shachar,

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?

his particular case is select 'picture.jpg' as icon1, 'picture2.jpg' as 
icon3 ...

The jdbc driver handles this by parsing the data at the time of the 
getXXX call where getXXX could be getString, getInt, getDate, etc.

I have zero knowledge if this is possible in the oledb environment.

Dave

Shachar Shemesh wrote:

> Joshua D. Drake wrote:
>
>> Shachar Shemesh wrote:
>>
>>> I have a complaint from an OLE DB user that when he does "select 
>>> 'a'", he gets an "unhanded type" error. Since OLE DB uses a binary 
>>> interface, it has to know about all variable types that pass through 
>>> it.
>>>
>>> The debug information for the problem show that the returned type is 
>>> 705, which is "unknown". My question is - what is "unknown" used 
>>> for? Is it important to support binary send and receives with this 
>>> type? Does postgresql know how to convert it to other types?
>>>
>>> I was under the impression that 'a' would be "text". Why is 
>>> "unknown" used instead?
>>
>>
>>
>> Try casting a to varchar. Which is binary compatible but understood 
>> by ODBC/OLE DB.
>
>
> Hmm, I did not explain myself clearly enough.
>
> I maintain the OLE DB provider. My question was not that. My question 
> was whether I should add support to unknown? If so, what type should 
> it be represented to outside applications?
>
> If unknown is binary compatible to text, I can just pass it on as text 
> and have it done with. The question is whether that makes sense, 
> whether it makes more sense to pull and error if that happens, or 
> whether there is something else which I should do.
>
> Thanks,
>
>       Shachar
>

-- 
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561


Tue, 15 Mar 2005 07:34:18 -0500
Dave Cramer <pg@fastcrypt.com>
Re: type unknown - how important is it?
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

Tue, 15 Mar 2005 10:01:33 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: type unknown - how important is it? 
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


Tue, 15 Mar 2005 17:40:33 +0200
Shachar Shemesh <psql@shemesh.biz>
Re: type unknown - how important is it?
Shachar,

I think with type oid 705 (unknown) it's safe to treat it as text. 
Certainly better than punting.

On another note are you aware of any issues with transactions? 
Specifically with using the dated autocommit mode ?

Dave

Shachar Shemesh wrote:

> 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
>

-- 
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561


Tue, 15 Mar 2005 13:17:19 -0500
Dave Cramer <pg@fastcrypt.com>
Re: type unknown - how important is it?
On Wed, 16 Mar 2005 05:17 am, Dave Cramer wrote:
> Shachar,
> 
> I think with type oid 705 (unknown) it's safe to treat it as text. 
> Certainly better than punting.
> 
> On another note are you aware of any issues with transactions? 
> Specifically with using the dated autocommit mode ?
> 
> Dave
> 
> Shachar Shemesh wrote:
> 
> > 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
> >>  
> >>
[snip]
> >
> > 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
> >
> 
I have complained about this in the past, and would also suggest that it be treated as a
string value.

CREATE table b AS SELECT 'unknown', col2 from a;

Will even create a table with a column type as unknown, which doesn't have any operators
to convert to anything, including text.

Regards

Russell Smith.

Wed, 16 Mar 2005 08:02:39 +1100
Russell Smith <mr-russ@pws.com.au>
Re: type unknown - how important is it?
Dave Cramer wrote:

> Shachar,
>
> I think with type oid 705 (unknown) it's safe to treat it as text. 
> Certainly better than punting.

Question is what DBTYPE to report it as. Options are DBTYPE_WSTR (UTF-16 
string, which means the input string must be a valid UTF-8 string), 
DBTYPE_STR (just dump it as I get it, and hope that client doesn't barf 
on the UTF-8 encoding), DBTYPE_BYTES (it's an array of bytes, just let 
the client figure out what to do with it. No promises on my part).

I don't know type 705 well enough to decide which would work best. If 
it's guaranteed to be a validly encoded text string, then I'll just put 
it in as DBTYPE_WSTR, and get it done with.

> On another note are you aware of any issues with transactions? 
> Specifically with using the dated autocommit mode ?

I'm not sure what dated autocommit is. What are the issues you are seeing?

> Dave

       Shachar

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


Thu, 17 Mar 2005 09:01:17 +0200
Shachar Shemesh <psql@shemesh.biz>
Re: type unknown - how important is it?
Shachar Shemesh wrote:

> Dave Cramer wrote:
>
>> Shachar,
>>
>> I think with type oid 705 (unknown) it's safe to treat it as text. 
>> Certainly better than punting.
>
>
> Question is what DBTYPE to report it as. Options are DBTYPE_WSTR 
> (UTF-16 string, which means the input string must be a valid UTF-8 
> string), DBTYPE_STR (just dump it as I get it, and hope that client 
> doesn't barf on the UTF-8 encoding), DBTYPE_BYTES (it's an array of 
> bytes, just let the client figure out what to do with it. No promises 
> on my part).
>
> I don't know type 705 well enough to decide which would work best. If 
> it's guaranteed to be a validly encoded text string, then I'll just 
> put it in as DBTYPE_WSTR, and get it done with.

Oh, if you want to look it up yourself:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledb/htm/oledbdata_types.asp

       Shachar

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


Thu, 17 Mar 2005 09:04:05 +0200
Shachar Shemesh <psql@shemesh.biz>
Re: type unknown - how important is it?

Shachar Shemesh wrote:

> Dave Cramer wrote:
>
>> Shachar,
>>
>> I think with type oid 705 (unknown) it's safe to treat it as text. 
>> Certainly better than punting.
>
>
> Question is what DBTYPE to report it as. Options are DBTYPE_WSTR 
> (UTF-16 string, which means the input string must be a valid UTF-8 
> string), DBTYPE_STR (just dump it as I get it, and hope that client 
> doesn't barf on the UTF-8 encoding), DBTYPE_BYTES (it's an array of 
> bytes, just let the client figure out what to do with it. No promises 
> on my part).
>
> I don't know type 705 well enough to decide which would work best. If 
> it's guaranteed to be a validly encoded text string, then I'll just 
> put it in as DBTYPE_WSTR, and get it done with.

I think it's safe to assume it will be encoded properly. Attempting to 
convert it to a DBTYPE_WSTR makes more sense to me. Of course I reserve 
the right to be wrong.

>
>> On another note are you aware of any issues with transactions? 
>> Specifically with using the dated autocommit mode ?
>
>
> I'm not sure what dated autocommit is. What are the issues you are 
> seeing?

Pre-7.4 servers used set autocommit on/off and that was the error they 
referred to, however after asking them to get me a test case I haven't 
heard back
9 times out of 10 this means that while creating their test case they 
found the problem.

>
>> Dave
>
>
>       Shachar
>

-- 
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561


Thu, 17 Mar 2005 10:15:19 -0500
Dave Cramer <pg@fastcrypt.com>
Re: type unknown - how important is it?
Dave Cramer <pg@fastcrypt.com> writes:
> Shachar Shemesh wrote:
>> I don't know type 705 well enough to decide which would work best. If 
>> it's guaranteed to be a validly encoded text string, then I'll just 
>> put it in as DBTYPE_WSTR, and get it done with.

> I think it's safe to assume it will be encoded properly.

AFAICS it will work exactly like 'text'.

			regards, tom lane

Thu, 17 Mar 2005 10:20:03 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: type unknown - how important is it? 
Tom Lane wrote:

>Dave Cramer <pg@fastcrypt.com> writes:
>  
>
>>Shachar Shemesh wrote:
>>    
>>
>>>I don't know type 705 well enough to decide which would work best. If 
>>>it's guaranteed to be a validly encoded text string, then I'll just 
>>>put it in as DBTYPE_WSTR, and get it done with.
>>>      
>>>
>
>  
>
>>I think it's safe to assume it will be encoded properly.
>>    
>>
>
>AFAICS it will work exactly like 'text'.
>
>			regards, tom lane
>  
>
I have some doubts. From "textin":

>     /* verify encoding */
>     len = strlen(inputText);
>     pg_verifymbstr(inputText, len, false);

"unknownin" has no such thing. I'm worried. I realize that unknownout 
and textout are precisely the same, but I can't say I understand completely.

          Shachar

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


Thu, 17 Mar 2005 17:29:18 +0200
Shachar Shemesh <psql@shemesh.biz>
Re: type unknown - how important is it?
Dave Cramer wrote:

> Pre-7.4 servers used set autocommit on/off and that was the error they 
> referred to, however after asking them to get me a test case I haven't 
> heard back
> 9 times out of 10 this means that while creating their test case they 
> found the problem.

You do realize that OLE DB uses pqlib's "exec with params" to pass 
parameters around, and also to make sure that data is returned in binary 
format. As a result, it requires PG version 7.4 and above. It may well 
be that the answer is "It's irrelevant for our supported backends".

          Shachar

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


Thu, 17 Mar 2005 17:31:36 +0200
Shachar Shemesh <psql@shemesh.biz>
Re: type unknown - how important is it?