ODBC with PostgreSQL

Version Relevance: V5

Issue: I want to use ODBC to externally get data from our database.

Background: ODBC (Open Database Connectivity) is a Microsoft technology that allows access to a range of data sources using middleware (drivers). In this example we are seeking to use data from our PostgreSQL Caliach Vision database on an Open Office spreadsheet, on a Windows 10 machine. Please Note: ODBC is an external program connectivity tool and as such does not come under Caliach subscription support. Warning: The Omnis ODBC Driver for pre-V5 Caliach Vision was READ-ONLY, so you were safe to use it without potentially damaging data. The PostgreSQL ODBC Driver supports the full range of SQL commands, including UPDATE, INSERT, CREATE/DROP TABLE/INDEX, etc, etc.. Bear that in mind!

Sep 13th, 2016

Feedback: psqlODBC, which is the PostgreSQL driver needed, can be dowloaded free from the internet. It is available from psqlODBC Installers. There is a 32bit (x86), 64bit (x64) and a combined installer.

Downloads

It is important to understand that you must instal the appropriate driver for your target program. In our case Open Office is a 32bit application and therefore we must use only the 32bit driver.

  1. Download to a suitable location and unzip the file. In this case we are installing the combined 32bit and 64bit.

  2. Installer
  3. Run the installer and follow any instructions.
  4. On completion you will have all the required components and system registrations completed.
  5. You now need to find the Windows 10 Control Panel and within it, Administrative Tools. You will find 2 ODBC contols, one for 32bit and one for 64bit.
    Administrative Tools
  6. Choose appropriatly and then typycally select the System DNS tab pane. In the following graphic, PostgreSQL for 32bit and 64 bit have both been added.
    ODBC Data Source Administrator
  7. You will need to click on Add, select PostgreSQL Unicode, and the PostgreSQL Unicode Driver Setup will open. You should enter all the details as illustrated below.
    ODBC Data Source
    Please Note: Unlike the Omnis ODBC driver used with pre-version 5 Caliach Vision, the User Name is the PostgreSQL server user name and will be the same as that used in the Caliach Vision Logon Settings (although it is often better to have a seperate PostgreSQL Role for use with ODBC, so that restrictions can be applied without disrupting operations in Caliach Vision). The Password is the PostgreSQL server password. These are nothing to do with the Caliach Vision Logon User ID and Password. Using ODBC to connect to the database by-passes ALL controls built in to the Caliach Vision programme to protect data integrity.
  8. You can use the Test button to confirm that the details are correct and a connection can be made. Note: The Server is typically the IP address of the server, e.g. 192.168.1.50.
  9. We now have the ODBC Data Source setup, so can switch over to the target application, in our case Open Office. Launch Open Office and in the opening window choose Database to open the Database Wizzard.
    Open Office Database Wizzard
  10. Choose the Connect to an existing database option and from the dropdown list ODBC, then click Next.
    Setup ODBC Connection
  11. Type in the name of the Data Source that you created in step 6 and 7 above. Click on Finish.
  12. You now have a direct connection to the database and can use a number of tools to select data.
    Open Office dynamic data
  13. In this example, we have used the Create Query in SQL View to write a SQL statement and named the query Parts. Using drag-and-drop we have dragged Parts onto our spreadsheet.

Chris Ross - Senior Consultant