Обсуждение: 'select count...' returns SQL_VARCHAR?

Hello everyone,

I am using the PostgreSQL and its ODBC driver but there seems to be
some problem with the "select count(...)..." queries. Here is my
configuration:
  - FreeBSD 4.6
  - PostgreSQL 7.2.1
  - PostgreSQL odbc driver 7.2.1
  - Driver Manager: iodbc 3.0.5 (and also tested with unixODBC 2.2.1
  and their pgsql odbc driver)
  - OTL <http://members.fortunecity.com/skuchin/home.htm>

In my test case I just want to execute a simple "SELECT COUNT(*) FROM
test_tab" but the result is an OTL exception whenever I try to get the
count as an int. The error message is that the result of the query is
a VARCHAR and so I should use a char[] instead. (Indeed, extracting it
in a C string works and gives the correct result.)

I tried to track the error and I saw that inside the OTL the
SQLDescribeCol function is called for the specific query that returns
12 (SQL_VARCHAR) as the data type of the (single) 'count' column.

So is this a bug, a feature, or something else?
Do I miss something?

Best regards
Stelios Sfakianakis

--
"Right now, CORBA *is* the future of middleware. I'm not aware of any
other middleware that would come even close in portability, platform
support, features, performance, or multi-vendor support. It will be a
minimum of six or seven years before any other middleware platform
will be able to match that."
       --- Michi Henning in news:comp.object.corba, 28/06/2002

Stelios Sfakianakis <ssfak@ics.forth.gr> writes:
> I am using the PostgreSQL and its ODBC driver but there seems to be
> some problem with the "select count(...)..." queries. Here is my
> configuration:
>   - FreeBSD 4.6
>   - PostgreSQL 7.2.1

count() returns bigint (int8) in 7.2.  I seem to recall that ODBC has
some problem classifying bigint as numeric.  I don't recall if that's
a simple bug or a standards compatibility issue.  Anyway, the easiest
workaround may be to cast the result of count to integer (int4) so that
ODBC knows what to do with it.

            regards, tom lane

Tom Lane wrote:
> Stelios Sfakianakis <ssfak@ics.forth.gr> writes:
>
>>I am using the PostgreSQL and its ODBC driver but there seems to be
>>some problem with the "select count(...)..." queries. Here is my
>>configuration:
>>  - FreeBSD 4.6
>>  - PostgreSQL 7.2.1
>
>
> count() returns bigint (int8) in 7.2.  I seem to recall that ODBC has
> some problem classifying bigint as numeric.  I don't recall if that's
> a simple bug or a standards compatibility issue.  Anyway, the easiest
> workaround may be to cast the result of count to integer (int4) so that
> ODBC knows what to do with it.
>
>             regards, tom lane

It works as expected, of course!
Thank you very much!

Best regards
Stelios Sfakianakis

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly



--
"Right now, CORBA *is* the future of middleware. I'm not aware of any
other middleware that would come even close in portability, platform
support, features, performance, or multi-vendor support. It will be a
minimum of six or seven years before any other middleware platform
will be able to match that."
       --- Michi Henning in news:comp.object.corba, 28/06/2002

> -----Original Message-----
> From: Tom Lane
>
> Stelios Sfakianakis <ssfak@ics.forth.gr> writes:
> > I am using the PostgreSQL and its ODBC driver but there seems to be
> > some problem with the "select count(...)..." queries. Here is my
> > configuration:
> >   - FreeBSD 4.6
> >   - PostgreSQL 7.2.1
>
> count() returns bigint (int8) in 7.2.  I seem to recall that ODBC has
> some problem classifying bigint as numeric.  I don't recall if that's
> a simple bug or a standards compatibility issue.

It isn't a problem of the ODBC driver itself. The problem is if your
app (or the software you are using) can handle bigint properly.
Unfortunately there are some(many ?) softwares that can't
handle bigint properly and so the ODBC driver has returned
VARCHAR for int8 type. Recently I added the INT8 As (bigint,
numeric, double, ...) option to cope with this *sum(int4) returns
int8* problem.

regards,
Hiroshi Inoue