Connection walk through

Table of Contents Walk-through for (RHEL, Centos x64) To install Check the connection node.js

Walk-through for (RHEL, Centos x64)

So to get a connection from Linux to the world of MS SQL Server a rather convoluted approach is required.

The architectural components for this are:

 * FreeTDS
 * UnixODBC

To install

 yum install unixODBC unixODBC-devel
 yum --nogpg install freetds freetds-devel

Then configure the /etc/odbcinst.ini (note lib64)

 [FreeTDS]
 Description=ODBC for SQL Server
 Driver=/usr/lib64/libtdsodbc.so
 Setup=/usr/lib64/libtdsS.so
 FileUsage=1

Then the /etc/freetds/freetds.conf

 # Global settings are overridden by those in a database
 # server specific section
 [global]
         # TDS protocol version
 ;       tds version = 8.0
 
         # Whether to write a TDSDUMP file for diagnostic purposes
         # (setting this to /tmp is insecure on a multi-user system)
 ;       dump file = /tmp/freetds.log
 ;       debug flags = 0xffff
 
         # Command and connection timeouts
 ;       timeout = 10
 ;       connect timeout = 10
 
         # If you get out-of-memory errors, it may mean that your client
         # is trying to allocate a huge buffer for a TEXT field.
         # Try setting 'text size' to a more reasonable limit
         text size = 64512
 
 # A typical Sybase server
 [egServer50]
         host = symachine.domain.com
         port = 5000
         tds version = 5.0
 
 # A typical Microsoft server
 [egServer70]
         host = ntmachine.domain.com
         port = 1433
         tds version = 7.2 

Check the connection

To check and debug the connection use tsql, passing the credentials of the database server, user and optional password (it will prompt if not provided on the command line)

 tsql -S Server\\instancename -U LoginName -P PassWord
 > SELECT STATEMENTS....
 > GO

OR if you have a static port (default 1433)

 tsql -S Server -U LoginName -P PassWord
 > SELECT STATEMENTS....
 > GO

If you have a successful connection we can move on to configuring node.js to use this.

node.js

To allow node.js to get access to the newly created ODBC connection you will need to install some odbc components

 npm install odbc

If you get make errors ensure you have installed unixODBC-devel

Sample quick application to test the ODBC connection in node. Update the database, connection and query as appropriate.

 var connectionString = "DRIVER=  {FreeTDS};SERVER=host\\instance;UID=user;PWD=password;DATABASE=dbname;CHARSET=UTF8;TDS_Version=8.0";
 
 db.open(connectionString, function(err) { 
   if (err) {
     console.log(err);
     return;
   }
 
   db.query("select top 1 * from table", function(err, rows, moreResultSets) {
     console.log(sys.inspect(rows));
     db.close(function() {
     console.log("Database connection closed");
     });
   });
 });

NOTE, if you don't have a dynamic port allocation add the PORT=1433; into the connectionString

Example:

 var connectionString = "DRIVER={FreeTDS};SERVER=host;PORT=1433;UID=user;PWD=password;DATABASE=dbname;CHARSET=UTF8;TDS_Version=8.0";