Re: type unknown - how important is it?

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/

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
>

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/

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

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

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

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

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

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(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
> >>
> >>
[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.

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

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

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

Dave Cramer <pg(at)fastcrypt(dot)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

Tom Lane wrote:

>Dave Cramer <pg(at)fastcrypt(dot)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

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