ODBC linking Linux OS to MS SQL databases

From PxPlus

Jump to: navigation, search

These instructions will provide a detail set of steps of how you can setup an ODBC connection to extract information from an MS SQL database while running on a Linux (Red Hat/CentOS/Oracle Linux) platform.

Contents

Installation

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 at this Microsoft download site.[1]

When the install is complete, the odbcinst.ini file should resemble the following:

/etc/odbcinst.ini

[SQL Server Native Client 11.0]
Description=Microsoft SQL Server ODBC Driver V1.0 for Linux
Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0
Threading=1
UsageCount=1

The newly installed driver will require a connection link to the ODBC thru 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.

/etc/odbc.ini

[MSSQLTest]
Driver = SQL Server Native Client 11.0
Server = [protocol:]server[,port]     ie: Server = tcp:192.168.0.1,1433(default port)

It would be a good idea at this point to ensure that the connection is operational. To test the connection try the following command:

isql -v MSSQLTest Username Password

The following should display if your connection to the server is correct.

+--------------------------------------+
| Connected!                           |
|                                      |
| sql-statement                        |
| help [tablename]                     |
| quit                                 |
|                                      |
+--------------------------------------+
SQL>

To test the connection to the database can be done with the following:

use database_name
select * from table_name;
+-----+--------------------------------+
| KEY | DESCRIPTION                    |
+-----+--------------------------------+
| PXP | PVX Plus Technologies          |
+-----+--------------------------------+
SQLRowCount returns 0
1 row fetched
SQL>

To exit the test enter quit.

SQL> quit

Programming PxPlus

In order to program the connection to the MS SQL Database you will need to follow some easy steps.

Set a variable to contain the record layout along with the KEY assignment.

rec$="REC=CUSTNO:5,NAME:35,TOTAL:10.2,ADDRESS:35;KEY=CUSTNO"

The next statement will be the open statement that will supply the connection information and return the data.

OPEN(chn,iol=*,opt=rec$)"[ODB]MSSQLTest;mycust;db=master;user=UserId;pswd=Password;cursor_type=dynamic"

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.

Example program

rec$="REC=CUSTNO:5,NAME:35,TOTAL:10.2,ADDRESS:35;KEY=CUSTNO"
OPEN(chn,iol=*,opt=rec$)"[ODB]MSSQLTest;mycust;db=master;user=UserId;pswd=Password;cursor_type=dynamic" 
rd_lp:
read (chn,end=eof)
if custno$="0059" then total=total*-1; write (1)
goto rd_lp
!
eof:
close(chn)
exit
Personal tools