Postgres Pro
Downloads
Home   >   mailing lists

Thread: SQLColumns does not allow schema name

SQLColumns does not allow schema name

From
Date:
I came across this problem while trying to debug another application
that uses unix ODBC to access a Postgresql database. The application
uses the SQLColumns function to describe tables. When the table is in
the public schema and the table name is given just as "tname" then the
columns are returned. When the table is given as "public.tname" then the
request returns empty.

You can reproduce this in isql. If you connect to the database and do
"help tname" you get the columns listed. If you try "help public.tname"
no rows are returned. Selects return the same results either way, so it
is just the description functions that appears broken.

I am using unixODBC-2.2.12-9, Postgresql-odbc-08.03.0200-1 and
Postgresql-server-8.3.8-1.

Steve Hindmarch
BT Design

Re: SQLColumns does not allow schema name

From
Hiroshi Inoue
Date:
stephen.hindmarch@bt.com wrote:
> I came across this problem while trying to debug another application
> that uses unix ODBC to access a Postgresql database. The application
> uses the SQLColumns function to describe tables. When the table is in
> the public schema and the table name is given just as "tname" then the
> columns are returned. When the table is given as "public.tname" then the
> request returns empty.

The declaration of SQLColumns() is the following.

SQLRETURN SQL_API
SQLColumns(HSTMT StatementHandle,
    SQLCHAR *szCatalogName, SQLSMALLINT cbCatalogName,
    SQLCHAR *szSchemaName, SQLSMALLINT cbSchemaName,
    SQLCHAR *szTableName, SQLSMALLINT cbTableName,
    SQLCHAR *szColumnName, SQLSMALLINT cbColumnName);

We specify the schema name as the 4th parameter together with
the 5th length parameter.

> You can reproduce this in isql. If you connect to the database and do
> "help tname" you get the columns listed. If you try "help public.tname"
> no rows are returned. Selects return the same results either way, so it
> is just the description functions that appears broken.
>
> I am using unixODBC-2.2.12-9, Postgresql-odbc-08.03.0200-1 and
> Postgresql-server-8.3.8-1.
>
> Steve Hindmarch
> BT Design
>
>

Re: SQLColumns does not allow schema name

From
Date:
> -----Original Message-----
> From: Hiroshi Inoue [mailto:inoue@tpf.co.jp]
> Sent: 25 September 2009 13:08
> To: Hindmarch,SJ,Stephen,DMM R
> Cc: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] SQLColumns does not allow schema name
>
> stephen.hindmarch@bt.com wrote:
> > I came across this problem while trying to debug another application
> > that uses unix ODBC to access a Postgresql database. The application
> > uses the SQLColumns function to describe tables. When the table is
in
> > the public schema and the table name is given just as "tname" then
> the
> > columns are returned. When the table is given as "public.tname" then
> the
> > request returns empty.
>
> The declaration of SQLColumns() is the following.
>
> SQLRETURN SQL_API
> SQLColumns(HSTMT StatementHandle,
>     SQLCHAR *szCatalogName, SQLSMALLINT cbCatalogName,
>     SQLCHAR *szSchemaName, SQLSMALLINT cbSchemaName,
>     SQLCHAR *szTableName, SQLSMALLINT cbTableName,
>     SQLCHAR *szColumnName, SQLSMALLINT cbColumnName);
>
> We specify the schema name as the 4th parameter together with
> the 5th length parameter.
>

Thank you. I understand this now. The developer of the original
application has acknowledged that it his bug and he had not used the API
correctly.

> > You can reproduce this in isql. If you connect to the database and
do
> > "help tname" you get the columns listed. If you try "help
> public.tname"
> > no rows are returned. Selects return the same results either way, so
> it
> > is just the description functions that appears broken.
> >
> > I am using unixODBC-2.2.12-9, Postgresql-odbc-08.03.0200-1 and
> > Postgresql-server-8.3.8-1.
> >

So how can I describe a table in isql if it is part of a schema?

Steve Hindmarch
BT Design