Set Up an ODBC Driver (Windows Systems)

Description

You can integrate LogicNets applications with external enterprise-level databases such as MySQL, MS SQL, and/or Oracle using an ODBC driver connected to a logicnet. This topic walks you through the steps required to do that.

Use

You use an ODBC connection by calling the execute_query part, which is located in process nodes. However, you must first install the ODBC driver, then create a system DSN (data source name), and then configure the LogicNets part. 

Install the ODBC Drivers

To use an ODBC connection you must first install the Oracle ODBC driver, using the same bit version as the executable you will use it with; for example, if you use a 32-bit logicnets.exe (for LogicNets versions up to Release 8.0) you need to install the 32-bit ODBC driver. For LogicNets versions of Release 8.1 or higher, you need to use the 64-bit version.

Download the 32-bit ODBC Drivers

To download the driver, go to the following link: http://www.oracle.com/technetwork/topics/winsoft-085727.html

Download the following two files:

• instantclient-basic-nt-12.1.0.2.0.zip
• instantclient-odbc-nt-12.1.0.2.0.zip

Download the 64-bit ODBC Drivers (LogicNets v8.1+)

To download the drivers, go to the following link: https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html and download the files for your database type.

Extract the ODBC Driver

Once you have downloaded these files, extract them to the same location. Both zip files contain a folder with the same name. When you extract them to the same location, the folders and files will merge.

Install the ODBC Driver

On your machine, open an elevated command prompt (open it as an administrator) and navigate to the location where you extracted the files. Run odbc_install.exe, and the system will install the ODBC driver and show a message saying it was installed successfully. If you do not see the message verify that you opened the command prompt as an administrator.

The driver is now linked to the location where you unzipped the files. Do not delete the files if you want to keep the ODBC driver.

Create a System DSN

For LogicNets to use an ODBC connection, you must add the connection as a system DSN in Windows. If you installed the 32-bit version of the driver, you must open the 32-bit ODBC manager in Windows. This file can is generally located at C:\Windows\SysWOW64\odbcad32.exe.

1. Open the driver and click on the System DSN tab. 
2. Click Add.
3. Locate the driver you just installed. It should be named something like Oracle in XE.
4. Click Finish.

Note: If you experience any error when installing 32-bit version of the driver on a 64-bit system – for example, error code 126: cannot load module sqoras32.dll – install the Microsoft Visual C++ 2010 Redistributable Package (x86) from the following link: https://www.microsoft.com/en-gb/download/details.aspx?id=5555.

Repeat the previous step. If everything is installed correctly, you will see the following window:

1. Enter your connection name in the Data Source Name field.
2. In the TNS Service Name field enter the TNS Service Name, which is a combination of the hostname and the service name; for example, www.logicnets.com/XE.
3. Enter the user ID in the User ID field. 
4. Click Test Connection and fill in the password to test if the connection is successful.
5. Click OK to complete the process. 

You have now added a system DSN with the connection to your database; however, the password is not stored in the DNS and you will have to provide the password in the connection string. Because this is not safe, store the password in the registry using the following steps:

1. Click on the Windows Start menu and navigate to regedit.exe. 
2. Go to the following key: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI.
3. Expand ODBC.INI to see a folder with the name of your newly created DSN. Click on that folder and locate the key named Password. 
4. Double-click on the password key and enter the password of your database connection. 
5. Click OK and close all windows.

Configure the LogicNets Part

With the ODBC driver installed and configured you can connect it to LogicNets using the execute_query part.

1. Add a process node in a logicnet.
2. In the Data tables dropdown menu, select Execute query.
3. In the part editor, enter odbc:DSN= and the name of your system DNS in the Source field; for example, odbc:DSN=MyDB. This tells LogicNets to make the connection using an ODBC driver. 
4. In the Query field, enter the query you want to execute. Note, in the example image we have used a query that has been stored as a parameter: $(sql.query).
5. Using the Mode dropdown, select either Fetch or Execute.
   • Use Fetch when results need to be returned (SELECT statement). 
   • Use Execute when no results have to be returned (INSERT, DELETE, UPDATE statements).
6. Using the Multi record dropdown menu, select Yes or No.
   • When you chose No, the system will only return 1 record.
   • When you chose Yes, the system can return multiple records.
7. In the Result path field, enter the variable in which the system should store the results.

For more information about the execute_query part, see Execute Query.