Thread: Schema support

Schema support

From
"Dave Page"
Date:
There have been a couple of reports that psqlODBC does not support
schemas. This is not strictly true, it actually has full support for
schemas, however, SQLTables honours the search_path variable on the
server and hides objects in schemas that are not visible.

I'm not convinced this is the correct behaviour - SQLTables should show
everything afaict, unless a parameter is passed to it to filter the
output. The micro-patch below corrects this - however I'm not 100% sure
there won't be any side effects I haven't thought of. I'd appreciate it
if I could get some feedback.

Thanks Dave.

Index: info.c
===================================================================
RCS file: /usr/local/cvsroot/psqlodbc/psqlodbc/info.c,v
retrieving revision 1.114
diff -u -r1.114 info.c
--- info.c    26 Jul 2004 14:10:37 -0000    1.114
+++ info.c    4 Oct 2004 20:59:31 -0000
@@ -1284,7 +1284,6 @@
     if (conn->schema_support)
     {
         schema_strcat1(tables_query, " and nspname %s '%.*s'",
likeeq, szSchemaName, cbSchemaName, szTableName, cbTableName, conn);
-        strcat(tables_query, " and
pg_catalog.pg_table_is_visible(c.oid)");
     }
     else
         my_strcat1(tables_query, " and usename %s '%.*s'",
likeeq, szSchemaName, cbSchemaName);

Re: Schema support

From
anthony.caduto@micorp.com
Date:

Hi Dave,
I just started using the PG odbc driver with access and I noticed that the only schema that would show up was public, even though I had added several of my own that are visible.
The only way I could get it to work was with the searchpath, and that does not seem to me how it should work.
The search path is a marginal solution because if you add new schemas you have to update every single DSN with the new search path, this can be alleviated some by using a file DSN but it is still a huge PITA.
I was using the stable version from Nov 2003

Thanks,

Tony Caduto
Senior Programmer/Analyst
M&I Trust and Investment Management



"Dave Page" <dpage@vale-housing.co.uk>
Sent by: pgsql-odbc-owner@postgresql.org

10/04/2004 04:15 PM

To
<pgsql-odbc@postgresql.org>
cc
Subject
[ODBC] Schema support





There have been a couple of reports that psqlODBC does not support
schemas. This is not strictly true, it actually has full support for
schemas, however, SQLTables honours the search_path variable on the
server and hides objects in schemas that are not visible.

I'm not convinced this is the correct behaviour - SQLTables should show
everything afaict, unless a parameter is passed to it to filter the
output. The micro-patch below corrects this - however I'm not 100% sure
there won't be any side effects I haven't thought of. I'd appreciate it
if I could get some feedback.

Thanks Dave.

Index: info.c
===================================================================
RCS file: /usr/local/cvsroot/psqlodbc/psqlodbc/info.c,v
retrieving revision 1.114
diff -u -r1.114 info.c
--- info.c                 26 Jul 2004 14:10:37 -0000                 1.114
+++ info.c                 4 Oct 2004 20:59:31 -0000
@@ -1284,7 +1284,6 @@
                 if (conn->schema_support)
                 {
                                  schema_strcat1(tables_query, " and nspname %s '%.*s'",
likeeq, szSchemaName, cbSchemaName, szTableName, cbTableName, conn);
-                                  strcat(tables_query, " and
pg_catalog.pg_table_is_visible(c.oid)");
                 }
                 else
                                  my_strcat1(tables_query, " and usename %s '%.*s'",
likeeq, szSchemaName, cbSchemaName);

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

              http://archives.postgresql.org


Schema support problems with latest snapshot

From
anthony.caduto@micorp.com
Date:

Hi,
I just installed the latest snapshot on my PC where I have my Access test databases and now when I do a linked table I don't get anything back without a searchpath.
before I would at least get public.

Thanks,

Tony Caduto
Senior Programmer/Analyst
M&I Trust and Investment Management



anthony.caduto@micorp.com
Sent by: pgsql-odbc-owner@postgresql.org

10/04/2004 04:35 PM

To
pgsql-odbc@postgresql.org
cc
Subject
Re: [ODBC] Schema support






Hi Dave,

I just started using the PG odbc driver with access and I noticed that the only schema that would show up was public, even though I had added several of my own that are visible.

The only way I could get it to work was with the searchpath, and that does not seem to me how it should work.

The search path is a marginal solution because if you add new schemas you have to update every single DSN with the new search path, this can be alleviated some by using a file DSN but it is still a huge PITA.

I was using the stable version from Nov 2003


Thanks,


Tony Caduto
Senior Programmer/Analyst
M&I Trust and Investment Management


"Dave Page" <dpage@vale-housing.co.uk>
Sent by: pgsql-odbc-owner@postgresql.org

10/04/2004 04:15 PM


To
<pgsql-odbc@postgresql.org>
cc
Subject
[ODBC] Schema support







There have been a couple of reports that psqlODBC does not support
schemas. This is not strictly true, it actually has full support for
schemas, however, SQLTables honours the search_path variable on the
server and hides objects in schemas that are not visible.

I'm not convinced this is the correct behaviour - SQLTables should show
everything afaict, unless a parameter is passed to it to filter the
output. The micro-patch below corrects this - however I'm not 100% sure
there won't be any side effects I haven't thought of. I'd appreciate it
if I could get some feedback.

Thanks Dave.

Index: info.c
===================================================================
RCS file: /usr/local/cvsroot/psqlodbc/psqlodbc/info.c,v
retrieving revision 1.114
diff -u -r1.114 info.c
--- info.c                 26 Jul 2004 14:10:37 -0000                 1.114
+++ info.c                 4 Oct 2004 20:59:31 -0000
@@ -1284,7 +1284,6 @@
                if (conn->schema_support)
                {
                                 schema_strcat1(tables_query, " and nspname %s '%.*s'",
likeeq, szSchemaName, cbSchemaName, szTableName, cbTableName, conn);
-                                  strcat(tables_query, " and
pg_catalog.pg_table_is_visible(c.oid)");
                }
                else
                                 my_strcat1(tables_query, " and usename %s '%.*s'",
likeeq, szSchemaName, cbSchemaName);

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

             http://archives.postgresql.org


Re: Schema support

From
"Dave Page"
Date:
Patch applied.


> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Dave Page
> Sent: 04 October 2004 22:16
> To: pgsql-odbc@postgresql.org
> Subject: [ODBC] Schema support
>
> There have been a couple of reports that psqlODBC does not
> support schemas. This is not strictly true, it actually has
> full support for schemas, however, SQLTables honours the
> search_path variable on the server and hides objects in
> schemas that are not visible.
>
> I'm not convinced this is the correct behaviour - SQLTables
> should show everything afaict, unless a parameter is passed
> to it to filter the output. The micro-patch below corrects
> this - however I'm not 100% sure there won't be any side
> effects I haven't thought of. I'd appreciate it if I could
> get some feedback.
>
> Thanks Dave.
>
> Index: info.c
> ===================================================================
> RCS file: /usr/local/cvsroot/psqlodbc/psqlodbc/info.c,v
> retrieving revision 1.114
> diff -u -r1.114 info.c
> --- info.c    26 Jul 2004 14:10:37 -0000    1.114
> +++ info.c    4 Oct 2004 20:59:31 -0000
> @@ -1284,7 +1284,6 @@
>      if (conn->schema_support)
>      {
>          schema_strcat1(tables_query, " and nspname %s
> '%.*s'", likeeq, szSchemaName, cbSchemaName, szTableName,
> cbTableName, conn);
> -        strcat(tables_query, " and
> pg_catalog.pg_table_is_visible(c.oid)");
>      }
>      else
>          my_strcat1(tables_query, " and usename %s
> '%.*s'", likeeq, szSchemaName, cbSchemaName);
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>