Document information

Document ID:5453
Subject:Connecting to InterSystems IRIS SQL with ODBC
Creation date:4/2/21 9:04 AM
Last modified on:4/5/21 12:49 PM


Connecting to InterSystems IRIS SQL with ODBC

If you use InterSystems IRIS SQL server you may have experienced a lack of features with it's built in SQL Query tool. The good news is that IRIS provides ODBC connection functionality, meaning you can use an ODBC query tool to perform the same queries in addition to getting access to all of the features your chosen program provides. In this document you will see how to connect to IRIS SQL using an ODBC query tool, such as WinSQL.

Benefits

There are a plethora of benefits when using WinSQL with IRIS:

  • Seamlessly migrate data between IRIS SQL and any other Database.
  • Generate Executable scripts based on SQL queries.
  • Enhanced Data and Schema comparisons.
  • Full suite design tools for creating E/R Diagrams, scripts, and navigation tools.
  • And much more!

In addition to the benefits of additional features that WinSQL utilizes, you also get access to a much more seamless Interface for running queries. One of the major downsides to IRIS' web interface for running queries is that each one needs to be done in a separate window or tab. If you need to run multiple queries this can take more time than necessary. With WinSQL, you can run many queries from just a single window, as well as running pre-created queries and scripts you might have made in the past.

IRIS Query Tool

IRIS Query

WinSQL Query Tool

WinSQL Query

Step 1: Configure IRIS Permissions

The first step in connecting to your IRIS SQL database via ODBC is to give users permissions to interact with your SQL database. This is done within the IRIS Management Center web portal.

Go to your IRIS Management Center and navigate to SYSTEM > SECURITY MANAGEMENT > USERS.





The next step is to select a user and grant it SQL privileges. You can tailor each user's access privileges to your specific needs and security constraints.



Step 2: Configure User DSN on on your Query Tool

Once you have a user account configured, the next step is to configure your ODBC query tool to connect to your IRIS SQL database.

The first phase of this process is to setup a User DSN to use the IRIS Driver. In WinSQL, this is quite easy. You can obtain the IRIS ODBC driver when you install IRIS on the machine, however, if you are on a separate machine you will need to obtain this from your administrator.

Once the driver is installed on your machine you can simply click ADD and select the InterSystems IRIS ODBC driver that will appear in the list.



Step 3: Configure the Connection Parameters

Once you’ve selected the driver you need to specify your connection parameters within a System DSN. To do this, select the System DSN for IRIS that matches your System Architecture from the SYSTEM DSN tab.



  • Name: This is a friendly name to distinguish this database from other databases.
  • Description: Description of the Database.
  • Host: This is the IP address of the machine that IRIS is running on.
  • Port: This is the port that the IRIS SQL Database is listening on. By default it is 51773.
  • Namespace: This field is another optional descriptor for the Database you are connecting to.
  • Authentication Method: This is defined by your SQL Administrator, by default this will be PASSWORD, however, your administrator may require different connection parameters.
  • User Name and Password: This will also be obtained from the administrator, it is the same User Name and Password that was created in step 1.

Once all of the information is filled out, click OK.

Step 4: Connecting to the Database

Once you’ve filled out the connection parameters you are ready to connect.

In WinSQL you can do this by clicking FILE > NEW CONNECTION.

On this window, select the IRIS DSN you just created from the drop down and enter in the User Name and Password and click OK.

If there are no issues, you will successfully connect to the SQL Database running on IRIS and can begin to run queries.





Add a comment to this document

Do you have a helpful tip related to this document that you'd like to share with other users? Please add it below. Your name and tip will appear at the end of the document text.
Your name:
Your email:
Hide my email address
Verification code:
Enter the verification code you see above more submitting your tip
Tip:Please limit tips to 1000 characters

Navigation

Social Media

Powered by 10MinutesWeb.com