I hope someone can help me out here because I know Linux doesn’t get a lot of love. I am writing a web app to deploy as a cgi that will connect to a SQL Server 2012 database. My setup is:
Ubuntu 16.04 server (and desktop for coding/testing)
Xojo Pro 2017r2.1
MBS-Xojo-Plugins174
FreeTDS (unixodbc & tdsodbc packages installed)
I have verified I can connect on the command line:
isql -v FREETDS sa
I also verified I can connect in my app using the Xojo ODBCDatabase connection just fine, as well, but would prefer to use the SQLDatabaseMBS.
I have tried different iterations of connection statements but none have worked and mostly the application just closes with no errors which makes troubleshooting difficult. Appreciate any help!
Code that I have tried:
// preload libs, this helps on Linux to find them
Dim libodbcinst as Folderitem = GetFolderItem("/usr/lib/x86_64-linux-gnu/odbc/libtdsS.so")
Dim libtdsodbc As Folderitem = GetFolderItem("/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so")
Dim s As New SoftDeclareMBS
Try
Call s.LoadLibrary(libodbcinst.NativePath)
Call s.LoadLibrary(libtdsodbc.NativePath)
Catch n As NilObjectException
If n IsA NilObjectException Then
MsgBox(“Please be sure the FreeTDS SQL library is installed.”)
Else
End If
End Try
// connect
db = New SQLConnectionMBS
dim cs as string = “DRIVER={FREETDS};Server=”+App.sqlserver+";UId="+App.sqlusername+";PWD="+App.sqlpassword+";Database="+App.database+";TDS_VERSION=7.2;Port=1433"
db.SetFileOption db.kOptionLibraryODBC, libtdsodbc
db.Option(“UseAPI”) = “ODBC”
db.DatabaseName = “ODBC:” + cs
Oh, and here is an example of my working code using the ODBCDatabase method:
db = New ODBCDatabase
db.DataSource = “FREETDS”
db.UserName = “sa”
db.Password =
My odbc.ini looks like this:
[FREETDS]
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup=/usr/lib/x86_64-linx-gnu/odbc/libtdsS.so
Server = xx.xx.xx.xx (this is my sql server ip)
Port = 1433
I have been able to get FreeTDS working from macOS to MSSQL, but despite days of pain I was unable to get it working on Linux either. I could never find the ‘libtdsodbc.so’ file.
This is the connection string I used (same as yours):
Never tried it myself, but perhaps Microsoft’s own ODBC driver for Linux is an alternative at this point? Given recent support for Linuux and MSSQL Server on linux, it might be a good option.
I’ve used PHP, and had the Microsoft driver that Louis mentioned running on both Ubuntu and Red Hat. It’s buggy (doesn’t properly support NVarchar(max), for example) but it mostly works.
However, using Xojo, I’ve never been able to connect using the exact same ODBC connection parameters. I eventually gave up and stayed with PHP.
nvarchar(max) is buggy with ODBC and generally with the native client, period. It is not specific to this driver. There are several posts on this very topic elsewhere on the forum. I do everything to avoid using nvarchar(max).
Thank you, @David Cox , @Charles Weger and @Louis Desjardins for your replies.
I honestly wasn’t expecting those replies. It’s strange because Christian mentioned in some other posts I have read that they had the MBS plugins working on Linux for database connections. Granted that may have been awhile ago. I wonder if updates have broken things. I was assuming it was just my connection statement.
As I mentioned, I have it working with FREETDS using the Xojo odbc connection, though, just not with SQLDatabaseMBS connection. I try to stay away from nvarchar(max) as well so don’t believe that will be a problem for me. What are the pros and cons of using the Microsoft driver versus the FREETDS?
@Christian Schmitz I saw an old post that mentioned you were able to connect and use SQLDatabaseMBS on Linux (using FREETDS, I believe). Can you provide any help?
Just thinking outside of the box, is this a case where creating a native Windows Xojo REST application might help serve up your data?
Then use the native Linux application to request the data from that REST application. Look at the RESTy example application included with Xojo for ideas.
[quote=430697:@Kevin J Cully]Just thinking outside of the box, is this a case where creating a native Windows Xojo REST application might help serve up your data?
Then use the native Linux application to request the data from that REST application. Look at the RESTy example application included with Xojo for ideas.[/quote]
It would be awsome, but is a windows server 2003, and the xojo 2018 does not work on this version
I’m doing a wget on a php pdo to do this.
It’s a complete absurd what i’m doing because NO ONE help me what’s going on.
[quote=430816:@Louis Desjardins]I would not go to that extreme.
Here, you will find the Linux Connector and how to install it on a Linux computer.
You should then be able to connect via ODBC to a MSSQL datbase. (Here is a link to find out how to connect)[/quote]
I installed. It’s all ok.
I tested the DSN using isql. it worked
That’s why i’m asking WHAT’S THE MINIMUM information needed to put on ODBCDatabase to connect.
Of course, all other help like the “correct” syntax on DSN to satisfy XOJO’s odbc
Here is a resource that is very useful when you want to connect to SQL Server. You should find the required information to connect. The “standard security” connexion string is pretty much the bare minimum that is required.
I believe something broke between 2015 and 2019 and the built in XoJo ODBC linux drivers. My Linux application worked fine with TDS talking to Microsoft SQL server, but many things were different then… 32bit XoJo, Ubuntu 12 (32bit). Now trying with Ubuntu 18 (64bit), 64bit Xojo (I compiled in both 32bit and 64bit with no difference) and either TDS or Microsoft ODBC drivers. Both the TDS and Microsoft ODBC drivers are loaded and seem to be working - I can query databases from linux command line, but XoJo will not work. I have been able to connect, but queries are blank. I gave up and bought Christians MBS driver which worked perfectly. When I have more time, I’ll come back and try to create a feedback on things I’ve tried and document the bug.
Hi Alexandre,
Could you elaborate a bit?
My knowns:
Both command line tools tsql and sqlcmd can connect to either of my servers; so I think my client and server have the required protocols and security levels.
My 2008 server’s highest limit is SSL 2.0 and I believe my 2019 server is using TLS 1.2
I also believe Christian’s SQLDatabaseMBS is using the same underlying FreeTDS and Microsoft SQL 17 drivers and just like the command line tools; I can connect to either server.
