MS SQL Server ODBC on Mac - anyone have it working?

I’m trying to use the MS SQL Server ODBC driver for macOS with Xojo. I think I have it configured OK - seems to work with the unixODBC command line utility just fine. But I can’t get it to work with Xojo. I have compiled a 64-bit app (as the driver is 64-bit only) but when I try to connect I get an empty error response (no message) and error number 1. I have confirmed the user, password, database name and server are correct, and have tried using a DSN in iODBC and building the whole connect string in Xojo. I know I’m getting the right driver, because when I compiled as 32-bit it failed on the architecture to load the driver.

Anyone have this working?

Jack, MS SQL not work on mac os ( for now )
Use ODBC with third part software eg actualsoft odbc

or install brew
ad use this :

brew update
brew install unixodbc
brew install freetds --with-unixodbc

Or with a MBS SQL Plugin use freefds library.

Christian sure ? whit MBS SQL Plugin is possible use MSSQL ?

OOOK, i try all in SQL examble of MBS complete plugin

I use the MBS SQL plugin to connect to MS SQL via the FreeTDS plugin. It works. No need for ActualSoft or anything else, but you need to set up MS SQL to allow connections via TCP/IP and choose a port.

My project is personal, so no hurry. Maybe I’ll just put in a support request for Xojo to work with the MS ODBC driver. Seems to me something they’d want to have working. Not keen on buying plug-ins to use a free driver. Thanks for the responses.

the solution I used before working here was the odbc drivers from actualtech.com. They work very well with the xojo odbc driver (or at least they did).

If one uses the MBS SQL plugin, you simply include his libtdsodbc.dylib with a Xojo project, when targeting macOS. You don’t have to install any odbc driver on the clients computer then.

I once made an example for my presentation at the MBS Xojo conference in Koblenz, and you still can download it from here:

https://www.seminar.pro:9321

Just click on the button “Download Xojo sample project : database interface”

In my example, look into the Connect method of the imDB_MSSQL class to see the MSSQL connection details.

You may want to check with Christian if a newer version of the .dylib is available.

On his site is an archive with all needed Libs: https://www.monkeybreadsoftware.de/xojo/download/plugin/Libs/
I’ve successfully used MSSQL with Xojo and MBS, using the following code:

[code]
If WaWi_DB_MBS = Nil Then
WaWi_DB_MBS = New SQLDatabaseMBS
End If
If WaWiConnection = Nil Then
WaWiConnection = New SQLConnectionMBS
End If
If WaWiCommando = Nil Then
WaWiCommando = New SQLCommandMBS
End If

WaWiConnection.Option(“OLEDBProvider”) = “SQLNCLI10”

try

#If TargetWin32 Then

WaWi_DB_MBS.Option("UseAPI") = "OLEDB"
WaWi_DB_MBS.Option("SQLNCLI10.LIBS") = "sqlsrv32.dll" // Library included in Windows Vista and newer
WaWi_DB_MBS.UserName = WaWi_Login
WaWi_DB_MBS.Password = WaWi_Password
WaWiConnection.ConnectMT(ServerAddress + "@DB_NAME", Login, Password, SQLConnectionMBS.kSQLServerClient) // PcName\\SqlServerInstanceName@DatabaseName

#ElseIf TargetMacOS

dim libtdsodbc as FolderItem
libtdsodbc = App.ExecutableFile.Parent.Parent.Child("Resources").Child("libtdsodbc.dylib")

Dim s As New SoftDeclareMBS
Call s.LoadLibrary(libtdsodbc.NativePath)

Dim cs As String = "Driver={FREETDS};Server=" + ServerAddress + ";UId=" + Login + ";PWD=" + Password + ";Database=DB_NAME;TDS_VERSION=7.2;Port=1433"

WaWiConnection.SetFileOption WaWiConnection.kOptionLibraryODBC, libtdsodbc
WaWiConnection.Option("UseAPI") = "ODBC"
WaWiConnection.ConnectMT(cs, "", "", SQLConnectionMBS.kODBCClient)
WaWiConnection.Option("ODBCAddLongTextBufferSpace") = "false" 

#EndIf

WaWiCommando.Connection = WaWiConnection // Connection to use

WarenwirtschaftDatabaseIsOnline = WaWiConnection.isConnected

If WarenwirtschaftDatabaseIsOnline Then
// WaWiConnection.RaiseExceptions = False
End If

catch r as SQLErrorExceptionMBS
//Error Handling here
EndOfLine)
// Debug Logging
end try[/code]

I looked briefly at the MS SQL Server 2017 (beta) page. SQL Server 2017 will run natively on Windows and Linux and also on MacOS with Docker. There is also a MacOS ODBC driver for MACOS that runs with Homebrew. There are several code examples in PHP, Ruby, Python etc. on MacOS. MS has released FC 2, so RTM is coming soon

Release version is already available for 2016/2014/2012. That’s what I’m using. And guys, I know I can buy plugins and drivers. I have the Actual drivers. But for a hobby web project I am not going to pay for a server license from Actual(and I’m not going to cheat just because it’s a hobby project), and I’m not buying plugins. Especially since the DB vendor has native Mac drivers available for free. So I guess it’s time to put in a feature request.

Do you have a link for these? Thx.

@Tanner Lee , This link shows how to install version 13.1 of the MS ODBC driver on macOS. I believe that it is backwards compatible with versions prior to SQL Server 2017, but I have not tried it myself.

Thanks Louis, but I thought Jack was referring to docker containers for SQL Server versions other than 2017. My mistake.

Personally I use Christian’s rock-solid MBS plugin to access MSSQL db’s.

The solution that works for you is the best solution for you. Nothing wrong with MBS plugins, on the contrary!

I can’t see what kind of MSSQL-Server you use.
If you use a MS SQL Express Server you must first set up the connection to your LAN.
With the “SQL Server Configuration Manager” you must activate the TCP/IP protocol to the port 1433, because the free of charge server is intended to be used on a single machine.

Guys, really, thanks for the help, but I’m good - I don’t need to know how to set up SQL Server, I have been a DBA for many years. Just wanted to see if anyone had the MS ODBC drivers working with Xojo on a Mac, and apparently they don’t work. Not interested in spending any money for other drivers or solutions when the vendor offers a free one.

Tanner, I was not referring to Docker, but the driver Louis linked to.