Psql DBC -08.02.0400 problem, only public schema can be viewed.

Greetings,

Current environment setup:

OS - Solaris 9 Sparc 64 bit

Application - SAS 9

Odbc Driver - UNIX ODBC 2.2

Postgres driver - Psqlodbc-08.02.0400

Problem situation :

Requirements - Access postgres 8.1.3 database from sas ( CRM application
) using unixODBC and psqlodbc compiled as 64 bit because sas is
installed as 64 bit

Current situation - the above is in place , I can access all data/tables
in the public schema only, all other schema's cannot be accessed from
sas.

Kindly advise as to a possible workaround or fix for this issue, please
see Comments below from SAS support regarding this issue,

"Development confirmed that the ODBC driver is the reason for the
behavior.

SAS query the odbc driver regarding Schema usage and the PostgreSQL
driver comes back basically saying "no schema usage". Therefore tables
are not prefix'ed with the schema.

ODBC: ENTER SQLGetInfo

0x000000000033e530

91 <SQL_SCHEMA_USAGE>

0x0000000078024528

4

0x000000007307c0fe

ODBC: EXIT SQLGetInfo with return code 0 (SQL_SUCCESS)

0x000000000033e530

91 <SQL_SCHEMA_USAGE>

0x000000007307bf4c (0) <> <---- This is the
information returned from the PostgreSQL driver - no schema usage

4

0x000000007307c0fe (4)

This is what the driver should return (exmple with oracle's odbc
driver):

ODBC: EXIT SQLGetInfo with return code 0 (SQL_SUCCESS)

0x049a1348

91 <SQL_SCHEMA_USAGE>

0x0495f028 (31) <SQL_SU_DML_STATEMENTS |
SQL_SU_PROCEDURE_INVOCATION | SQL_SU_TABLE_DEFINITION |
SQL_SU_INDEX_DEFINITION

| SQL_SU_PRIVILEGE_DEFINITION>

4

0x0495f2be (4)

I suggest that they contact the ODBC driver vendor with the issue. Maybe
there is an option to turn on the schema usage within the driver
configuration."

Kind Regards

Maya Govender

Tel: 011 263 5009

Fax: 011 263 5131

Cell: 082 550 9180

email: maya(dot)govender(at)ebucks(dot)com

This email is subject to a disclaimer, which is available at: https://www.ebucks.com/email/disclaimer.html

If you are unable to access the Disclaimer, send a blank e-mail to disclaimer(at)ebucks(dot)com and we will send you a copy of the Disclaimer.

Hi.

> From: Govender, Maya
> To: pgsql-odbc(at)postgresql(dot)org
> Sent: Wednesday, August 15, 2007 7:50 PM
> Subject: [ODBC] Psql DBC -08.02.0400 problem, only public schema can be viewed.
>
>
> Greetings,
>
> Current environment setup:
>
> OS - Solaris 9 Sparc 64 bit
> Application - SAS 9
> Odbc Driver - UNIX ODBC 2.2
> Postgres driver - Psqlodbc-08.02.0400
>
> Problem situation :
>
> Requirements - Access postgres 8.1.3 database from sas ( CRM application ) using unixODBC
> and psqlodbc compiled as 64 bit because sas is installed as 64 bit

Cool:-)

> Current situation - the above is in place , I can access all data/tables in the public
> schema only, all other schema's cannot be accessed from sas.

Does on the ShowSystemTables of odbc.ini help you?

Regards,
Hiroshi Saito