Question about ODBC connection under linux

[greenb...@gmail.com]

Hello,

I want to ask you for little help. I'm struggling with ODBC connection under linux.

I have cache database on ubuntu server 14.04. I want to read MsSQL database situated on another (windows) server from my cache server.

So what I have done by far. First of all I installed 'Microsoft ODBC Driver 11 for SQL Server'.

I modified .odbc.ini file in administration's root directory.

I used 'isql' command to see if everything is alright. The result is successful connection to MsSQL.

But It's difficult for me to read that database from my cache server 2012.

As secound step I add the description of my odbc in cacheodbc.ini file situated in 'mgr' folder.

I did the following: 

In [ODBC Data Sources] section I add:

MSSQL = MSSQL (which is the name of my ODBC connection with external database)

and below:

[MSSQL]

Driver=/path-to-my-driver/..

Server= IP address were is my external database

Port=default MsSQL port

Database=the name of my external database

That didn't work then I tried:

ODBCINI=/home/administrator/.odbc.ini

export ODBCINI

LD_LIBRARY_PATH=/usr/cachesys/bin

export LD_LIBRARY_PATH

But that didn't work either.

When I try to create new gateway connection I get the following error.

ERROR #6022: Gateway failed: DSN/User Connect.

And at the end that is the way I am trying to read the external MsSQL database. Unfortunately without any luck

by now.

S dsn="MSSQL", usr="username", pwd="password"

S gc = ##class(%Library.SQLGatewayConnection).%New()

S sc = gc.Connect(dsn,usr,pwd,0)

I hope you can give me some advice and point me my mistakes. Any help will be deeply appreciated.

P.S. Excuse my bad English. If something is not explained well enough please let me know.

Thank you again,

Pavel

[Mike Baker]

I do this several times a day so I'll tell you what we do.

First thing is I use the microsoft ODBC driver for sql server.  the linux driver is a couple of years old and works very well for SQL Server 2008 and 2012.  I don't know about earlier versions of SQL Server but if you use one of the newer SQL Server versions it works well.

https://msdn.microsoft.com/en-us/library/hh568451(v=sql.110).aspx

We use 64 bit RedHat Linux as our cache server but what I do to get things working is this:

1. Install ODBC Driver on LInux.

2. Set up the /etc/odbc.ini file with parameters needed to connect to sql server.

3. next I check to see if there are missing libraries to the sql server driver.

doing something like this:

   ldd /opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0

and on my system I usually get a couple of missing pieces.

one is libcrypto.so.6 and the other is libssl.so.6

so I make links from newer versions to the version that the driver needs...

 like this:

ln -s /usr/lib64/libcrypto.so.10 /usr/lib64/libcrypto.so.6

ln -s /usr/lib64/libssl.so.10 /usr/lib64/libssl.so.6

4. Now I test connection with a utility like isql and run a quick query to make sure it returns some rows.

5. after that I install cache.

6. then i Link cacheodbc.ini to /etc/odbc.ini

ln -s /etc/odbc.ini /cachesys/mgr/cacheodbc.ini

7.  Then in /cachesys/bin I make a couple of links, I think it is mainly for using 64 bit versions of these files and if you are using 32 bit you may not need to do this:

cd /cachesys/bin

mv cgate.so cgate.so.old

ln -s cgateur64.so cgate.so

mv libodbc.so libodbc.so.old

ln -s /usr/lib64/libodbc.so.1 libodbc.so

8. Change permissions so that cache can read and write /etc/odbc.ini

chmod 666 /etc/odbc.ini

9. Create an ODBC SQL Gateway connection using the ODBC DSN you created in step 2 above.  Shutdown and start cache and test connection.

After that I can create classes that link to SQL Server and can read and write from the SQL Server tables using those classes.

--
--
Caché, Ensemble, DeepSee

---
You received this message because you are subscribed to the Google Groups "Caché, Ensemble, DeepSee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to intersystems-publi...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[As As]

Hi Mike,

I finally make it work. The problem was that I used cgate.so 32-bit version instead of 64-bit version. I changed it and everything woks fine now.
I wish I could speak english better to express how grateful I am to you for your help.

I  wish you success and also lots of fun in the pursuit of your endeavors.

Regards

Pavel

To unsubscribe from this group and stop receiving emails from it, send an email to intersystems-public-cache+unsub...@googlegroups.com.