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

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

From
"Govender, Maya"
Date:

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.govender@ebucks.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@ebucks.com and we will send you a copy of the Disclaimer.

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

From
"Hiroshi Saito"
Date:
Hi.

> From: Govender, Maya
> To: pgsql-odbc@postgresql.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