#node-red-contrib-epsql
sorry by the redaction, but i think everything is in here, somdey in the future i will work in the document!!
the short version :
npm install -g node-red-contrib-epsql
or my favorite:
node-red-admin install node-red-contrib-epsql
#Long Version
las instrucciones completas para la instalacion
Tested in vanilla debian 8.6
Install esentials, some dependences y some tools
su root
nano /etc/apt/sources.list
apt-get update && apt-get upgrade
apt-get install -y sudo wajig build-essential curl
usermod -a -G sudo user
exit
reestart o logout, login
wajig install -y unixodbc unixodbc-dev
wajig install -y libgcrypt20 libgcrypt20-dev libgcrypt20-dbg
wajig install -y libssh2-1-dev libssl1.0.0 libssl-dev
O una por una:
wajig install -y libgcrypt20
wajig install -y libgcrypt20-dev
wajig install -y libgcrypt20-dbg
wajig install -y libssh2-1-dev
wajig install -y libssl1.0.0
wajig install -y libssl-dev
Verificar que queden instaladas todas, al instalar el driver de microsoft busca estas librerias, pero no adivierte si ahi alguna ausente, simplemente falla, en ocasiones se instalara pero no quedara correctamente instalado
Check all the libaries or you are goin in problems with the microsoft odbc driver
verificar los locales, debe estar instalado el siguiente: si no esta este idioma instalado no compila the driver need en_us.utf-8 as locals !!, is very importante verificar en_us.utf-8 utf-8
sudo dpkg-reconfigure locales
Bajar el driver correspondiente Cuidado por que son dos vinculos disintos para red hat y ubuntu, usar el apropiado o vendran los problemas con libcrypto.so.10 y libssl.so.10
there is a lot of confusion because there are three almost identical drivers, be carefully, dont waste your time like i did!!
Driver microsoft https://msdn.microsoft.com/library/mt703139.aspx
this links belows are not working anymore, you can find the new instructions in here:
https://www.microsoft.com/en-us/download/details.aspx?id=50420
i have the debian installer, but i dont know if i am allowed to redist! :(
redhat https://www.microsoft.com/en-us/download/details.aspx?id=36437
wget https://download.microsoft.com/download/B/C/D/BCDD264C-7517-4B7D-8159-C99FC5535680/msodbcsql-13.0.0.0.tar.gz
suse linux https://www.microsoft.com/en-us/download/details.aspx?id=34687
wget https://download.microsoft.com/download/7/3/1/7318E2AD-E7C5-4301-AEFA-A15EDEDACFB4/msodbcsql-13.0.0.0.tar.gz
ubuntu debian https://www.microsoft.com/en-us/download/details.aspx?id=50419
wget https://download.microsoft.com/download/2/E/5/2E58F097-805C-4AB8-9FC6-71288AB4409D/msodbcsql-13.0.0.0.tar.gz
whith the downloaded file:
tar -zxvf msodbcsql-13.0.0.0.tar.gz
cd msodbcsql-13.0.0.0
./install.sh verify
sudo ./install.sh install --force --accept-license
Verify your instalation Verificar la instalacion
sudo ldd /opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.0.0
debe marcar que encontro todas las librerias, si falta alguna ahi que instalar las depedencias
if there is a lib missing you have problems, google is your friend if it is missing some of this: libcrypto.so.10 or libssl.so.10 most likeli you have the wrong file check above.
ldd /opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.0.0
linux-vdso.so.1 (0x00007fff7fdde000)
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f53ffbb4000)
libodbcinst.so.2 => /usr/lib/x86_64-linux-gnu/libodbcinst.so.2 (0x00007f53ff9a2000)
libuuid.so.1 => /lib/x86_64-linux-gnu/libuuid.so.1 (0x00007f53ff79d000)
libgss.so.3 => /usr/lib/libgss.so.3 (0x00007f53ff591000)
libkrb5.so.3 => /usr/lib/x86_64-linux-gnu/libkrb5.so.3 (0x00007f53ff2bd000)
libcrypto.so.1.0.0 => /usr/lib/x86_64-linux-gnu/libcrypto.so.1.0.0 (0x00007f53feec1000)
libssl.so.1.0.0 => /usr/lib/x86_64-linux-gnu/libssl.so.1.0.0 (0x00007f53fec60000)
libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007f53fe955000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f53fe654000)
libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007f53fe43e000)
libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f53fe221000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f53fde76000)
/lib64/ld-linux-x86-64.so.2 (0x00007f54001a0000)
libltdl.so.7 => /usr/lib/x86_64-linux-gnu/libltdl.so.7 (0x00007f53fdc6c000)
libshishi.so.0 => /usr/lib/libshishi.so.0 (0x00007f53fda0a000)
libk5crypto.so.3 => /usr/lib/x86_64-linux-gnu/libk5crypto.so.3 (0x00007f53fd7d9000)
libcom_err.so.2 => /lib/x86_64-linux-gnu/libcom_err.so.2 (0x00007f53fd5d5000)
libkrb5support.so.0 => /usr/lib/x86_64-linux-gnu/libkrb5support.so.0 (0x00007f53fd3c9000)
libkeyutils.so.1 => /lib/x86_64-linux-gnu/libkeyutils.so.1 (0x00007f53fd1c5000)
libresolv.so.2 => /lib/x86_64-linux-gnu/libresolv.so.2 (0x00007f53fcfae000)
libtasn1.so.6 => /usr/lib/x86_64-linux-gnu/libtasn1.so.6 (0x00007f53fcd9a000)
libgnutls-deb0.so.28 => /usr/lib/x86_64-linux-gnu/libgnutls-deb0.so.28 (0x00007f53fca7b000)
libgcrypt.so.20 => /lib/x86_64-linux-gnu/libgcrypt.so.20 (0x00007f53fc799000)
libgpg-error.so.0 => /lib/x86_64-linux-gnu/libgpg-error.so.0 (0x00007f53fc587000)
libidn.so.11 => /usr/lib/x86_64-linux-gnu/libidn.so.11 (0x00007f53fc353000)
libz.so.1 => /lib/x86_64-linux-gnu/libz.so.1 (0x00007f53fc138000)
libp11-kit.so.0 => /usr/lib/x86_64-linux-gnu/libp11-kit.so.0 (0x00007f53fbef2000)
libnettle.so.4 => /usr/lib/x86_64-linux-gnu/libnettle.so.4 (0x00007f53fbcc0000)
libhogweed.so.2 => /usr/lib/x86_64-linux-gnu/libhogweed.so.2 (0x00007f53fba91000)
libgmp.so.10 => /usr/lib/x86_64-linux-gnu/libgmp.so.10 (0x00007f53fb80e000)
libffi.so.6 => /usr/lib/x86_64-linux-gnu/libffi.so.6 (0x00007f53fb606000)
in some cases we have newer libraries for openssl, but we can install some olders libs from here:
http://security.debian.org/pool/updates/main/o/openssl/
wget http://security.debian.org/pool/updates/main/o/openssl/libssl1.0.0_1.0.1t-1+deb8u5_amd64.deb
sudo dpkg -i libssl1.0.0_1.0.1e-2+deb7u20_ia64.deb
Verificar nuevamente
cd ..
odbcinst -q -d -n "ODBC Driver 13 for SQL Server"
Debe salir algo como esto
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.0.0
Threading=1
UsageCount=1
another test just to be sure
para verificar el driver
odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/exxerpro/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
of course you want to try with a real database a les you have mssqlserver for linux, you must have to configure slq server for remote conection, in sql express another big problem
ahi que configurar el acceso a MS SQL SERVER, algunos vinculos de utilidad y palabras claves Some useful links and keyword
https://msdn.microsoft.com/en-us/library/cc646023.aspx
som keyworks for google search
sql server configuration manager
netsh advfirewall firewall add rule name="MSSQL TCP" dir=in action=allow protocol=TCP localport=1434
netsh advfirewall firewall add rule name="MSSQL UDP" dir=in action=allow protocol=UDP localport=1434
netstat -ano | find /i "3564"
netstat -ano | find /i "3564"
NET STOP MSSQL$SQLEXPRESS
NET START MSSQL$SQLEXPRESS
#Ejemplos de configuracion Primero configuramos el archio odbc.ini
ejecutamos el siguiente comando:
sudo nano /etc/odbc.ini
vamos a editarlo con lo siguiente:
the driver name must match exactly,
Muy importante este es el servidor que usaremos en node-red, el de la configuracion actualmente no le hace caso!!!
Very important the server configured en node-red-controb-epsql is ignored, this is the real server to make the connection
[MSSQLTEST]
Driver = ODBC Driver 13 for SQL Server
Server = 192.168.1.23,1434
#
# EXAMPLE
# Server = tcp:servername,12345.
#
# Note:
# Port is not a valid keyword in the ~/.odbc.ini file
# for the Microsoft ODBC driver on Linux
#
#
#Encryption = Yes
El siguiente archivo ya debe estar configurado si se instalo bien el driver the next file, must to be like this:
sudo nano /etc/odbcinst.ini
[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.0.0
Threading=1
UsageCount=3
Para probar una base de datos now we can tray to connect
isql -v "odbc driver" "user" "pwd"
isql -v MSSQLTEST sa sa
be very carefull the remote conection in sql server must to be working, i recommended verify with a windows machine just to be sure
tiene que entrar a la consola de isql se requiere que exista el servidor en la direccion congigurada y con el usuario y password configurados en caso contrario marcara timeout o otro error si no esta bien instalada
en caso de encontrar el siguiente error
[unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.0.0' : file not found (0)
see the above comment about the 3 version of the driver (no counting windows version ) of course the file exist, is one of the librariis what is missing
para verificar la instalacion
ldd /opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.0.0
para instalar el nodeJS, si no esta instalado aun Installing node.js in case is not installed yet
curl -sL https://deb.nodesource.com/setup_6.x | sudo -E bash -
wajig install -y nodejs
Opcional para probar la conexion con la base de datos en node.js
nano s.js
var odbc = require("odbc");
var connectionString = "DSN=MSSQLTEST;UID=sa;PWD=sa;DATABASE=Produccion";
var db = new odbc.Database();
db.open(connectionString, function(err){
if(err){
throw err;
};
/*
Once the connection is open you can query it.
That means if you tried to query it outside or before this handler finished, it would say...
"Connection Not Open"
*/
db.query("SELECT * FROM contador", function(err, rows, moreResultSets){
if(err){
console.log(err.message);
};
console.log(rows);
});
});
process.on('SIGINT', function () {
db.close(function(){
console.log('Database Connection Closed');
process.exit();
});
});
node s.js
you must se the results in the console be free to write your own query
Para instalar node-red instalar como sudo por que requiere permisos de acceos a /usr/lib
sudo npm install -g --unsafe-perm node-red
sudo npm install -g --unsafe-perm pm2
sudo npm install -g --unsafe-perm node-red-admin
Now you can start node-red
pm2 start node-red
You can verify the running process with this:
pm2 info node-red
pm2 logs node-red
#Finally
para instalarlo se requiere que este corriendo el node-red recomiendo monitorear los logs, ya sea desde la pagina del node-red (localhost:1880) o desde otra consola (pm2 logs node-red) para ver que esta haciendo y ver los mensajes de error
npm install -g node-red-contrib-epsql
node-red-admin install node-red-contrib-epsql
Abel Briones
Exxerpro Solutions
www.exxerpro.com
Thanks to redant and redconnect-io, creator of https://github.com/redconnect-io/node-red-contrib-mssql - who's node this was based on.