topic Re: LIBNAME connection to PostgreSQL - Table Listing in SAS Programming

LIBNAME connection to PostgreSQL - Table Listing

JohnT — Mon, 18 Nov 2013 23:00:56 GMT

Hi,

I'm trying to connect to a PostgreSQL database using the LIBNAME statement.

This is what I'm using:

libname core odbc datasrc = 'PostGreSQL30' schema = Public;

This connects successfully according to the log, but I have no idea what the tables are.

When I connect to a Microsoft SQL database I use schema = dbo and in Enterprise Guide, the library is expandable and I can see the tables and I know what to query/interogate.

The obvious answer is to ask the DBA as to what tables are available, but he's not here. Is there a way that I can setup the library such that I can see the contents? I would want this feature even if the DBA could give me a listing of all the tables available.

We are on Enterprise Guide 5.1 (5.100.0.12269) Hot fix 7 (32-bit), and SAS 9.3 (TS1M2), Rev. 930_12w41, on Windows Server 2008 64 bit R2 SP1.

Thanks.

Re: LIBNAME connection to PostgreSQL - Table Listing

jakarman — Sat, 23 Nov 2013 11:52:23 GMT

It are not the tables in a DBMS you are refering to but to containers, the schemas, that contain those tables.

Within SAS I do not knon an option to see the schema-s.
One excpeption was the ODBC interface missing the schema name as using SAS/Share. As SAS/SHare is SAS is not useful info.

There are a lot of developers tools like SQL developer that will show the Schema-names when being autorized

Re: LIBNAME connection to PostgreSQL - Table Listing

Patrick — Sat, 23 Nov 2013 13:16:14 GMT

I might be missing something but if you say that the libname as such executes successfully then the following code will show you all tables available via schema "Public" (which could be none).

proc contents data=core._all_;

run;

Re: LIBNAME connection to PostgreSQL - Table Listing

JohnT — Sun, 24 Nov 2013 21:29:24 GMT

Here's my log:

15        libname core odbc datasrc = 'PostGreSQL30' schema = report_target;
NOTE: Libref CORE was successfully assigned as follows:
      Engine:        ODBC
      Physical Name: PostGreSQL30
16
17        proc contents data=core._all_;
18        run;

NOTE: Due to the PRESERVE_TAB_NAMES=NO libname option setting, 9 table(s) have not been displayed/returned.
WARNING: No matching members in directory.
NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.14 seconds
cpu time 0.00 seconds

I get nothing in the results tab when I run this.

Having said that, this is the first time I've paid attention to the log.

I've successfully done it !

libname core odbc datasrc = 'PostGreSQL30' schema = report_target PRESERVE_TAB_NAMES=yes;

proc contents data=core._all_ ;
run;

And now I can see the datasets in the CORE library. I wonder why I never noticed the hint in the log when I was originally trying to do this.

Thanks.