Dataset access via ODBCC other applications

SPSS
Go to File->Open database->New query

In the database wizard window, select “SeRP-PostgreSQL” as the ODBC data source and click Next. This should bring up a login prompt for you to enter your credentials.

In the next window, select the tables and fields to import

SAS

1) Adding ODBC as an SAS library object(need to know the schema name you want)

Adding the ODBC source as a library needs information regarding the desired schema name. Leaving out the schema name results in a blank library object.

Click on the “New Library” icon. In the New Library window, first select ODBC from the “Engine” drop down menu, then enter a name for the library and fill in the following Library Information Fields:

Data Source: SeRP-PostgreSQL

User ID: <your_username>

Password: <your_password>

Options: SCHEMA=”<desired_schema>” PRESERVE_TAB_NAMES=yes

This should add the ODBC object in the explorer window as a library object that can be queried for the tables:

The library can also be added using editor by typing and submitting the following command (note the quotes around user, password and schema values):

libname test odbc datasrc=’SeRP-PostgreSQL’ user=’<your_username>’ password=’<your_password>’ schema=’<desired_schema>’ preserve_tab_names=yes;

2) Adding ODBC as a connection:

It is also possible to add the ODBC as a connection in SAS (for example if the schema names are not known).

A simple sql proc that establishes the odbc connection and queries the available schema is shown below:

The listing will be given in the output window as shown above.

NOTE: Use double-quotes around the schema and table names in SAS queries

MATLAB

1) Using the command window

Enter the command below.

conn = database('PostgreSQL','<your_username>','<your_password>');

This creates a conn object that can be used to query tables and import data, such as the schemas present:

2) Using the GUI

Open the Database explorer app

Click on New Query and enter your credentials

Under the catalog and schema window, select the Tables that you would like to view

PYTHON

Import the pyodbc package and create a pyodbc connect object using the following options:

DSN=SeRP-PostgreSQL

UID=your_username

PWD=your_password

Run the SQL statements using the cursor.execute() function from the connection object, for example to return the first row of information from a table:

To print a list of the available schemas, query the information_schema.schemata table: