ODBC Linking Linux OS to MS SQL Databases
The information below provides detailed steps on how to set up an ODBC connection to extract information from an MS SQL database while running on a Linux (Red Hat/CentOS/Oracle Linux) platform.
The first step is to install the required drivers. The install instructions and drivers Microsoft® SQL Server® ODBC Driver 1.0 for Linux can be found on the Microsoft download website at the following link: https://www.microsoft.com/en-us/download/details.aspx?id=28160.
When the install is complete, the odbcinst.ini file should resemble the following:
[SQL Server Native Client 11.0]
Description=Microsoft SQL Server ODBC Driver V1.0 for Linux
The newly installed driver will require a connection link to the ODBC through the odbc.ini file. The Driver assignment should correspond to the entry in the odbcinst.ini file above. The Server assignment must contain the network information required to find the MS SQL database.
Driver = SQL Server Native Client 11.0
Server = [protocol:]server[,port] i.e. Server = tcp:192.168.0.1,1433 (default port)
At this point, it is a good idea to ensure that the connection is operational. To test the connection, try the following command:
isql -v MSSQLTest Username Password
If the connection to the server is correct, the following should display:
Test the connection to the database with the following:
select * from table_name;
| KEY | DESCRIPTION |
| PXP | PVX Plus Technologies |
SQLRowCount returns 0
1 row fetched
To exit the test, enter quit:
To program the connection to the MS SQL Database, some easy steps will need to be followed.
Set a variable to contain the record layout along with the KEY assignment:
The next statement will be the OPEN statement that will supply the connection information and return the data:
Without the cursor_type=dynamic parameter, the program will only read data from the table and would require a CLOSE and OPEN to allow writes.
if custno$="0059" then total=total*-1; write (1)
[ODB] Open Database
PREFIX FILE Directive