Dataset access via ODBCC other applications

Created by Mat Ishac, Modified on Tue, 15 Mar, 2022 at 1:41 PM by Mat Ishac

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:



Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article