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

  1. 3 months ago

    Jack B

    Aug 8 Pre-Release Testers, Xojo Pro

    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?

  2. Massimiliano C

    is not verified Aug 9 Europe (Italy, Bergamo)
    Edited 3 months ago

    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
  3. Christian S

    Aug 9 Pre-Release Testers, Xojo Pro Germany

    Or with a MBS SQL Plugin use freefds library.

  4. Massimiliano C

    is not verified Aug 9 Europe (Italy, Bergamo)

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

  5. Massimiliano C

    is not verified Aug 9 Europe (Italy, Bergamo)

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

  6. David C

    Aug 10 Pre-Release Testers, Xojo Pro Derby, ITM

    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.

  7. Jack B

    Aug 10 Pre-Release Testers, Xojo Pro

    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.

  8. Greg O

    Aug 10 Xojo Inc Somewhere near Raleigh, NC

    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).

  9. Oliver O

    Aug 10 Pre-Release Testers, Xojo Pro https://seminar.pro

    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.

  10. Sascha S

    Aug 11 Pre-Release Testers, Xojo Pro Germany/W'haven
    Edited 3 months ago

    @Oliver O 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:

    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
  11. Louis D

    Aug 11 Pre-Release Testers, Xojo Pro Montreal, QC, Canada

    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

  12. Jack B

    Aug 11 Pre-Release Testers, Xojo Pro

    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.

  13. Tanner L

    is not verified Aug 11 Pre-Release Testers, Xojo Pro Toronto, Canada

    @Jack B Release version is already available for 2016/2014/2012.

    Do you have a link for these? Thx.

  14. Louis D

    Aug 11 Pre-Release Testers, Xojo Pro Montreal, QC, Canada

    @Tanner L , 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.

  15. Tanner L

    is not verified Aug 11 Pre-Release Testers, Xojo Pro Toronto, Canada

    @LouisDesjardins @Tanner L , 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.

  16. Louis D

    Aug 11 Pre-Release Testers, Xojo Pro Montreal, QC, Canada

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

  17. Michael E

    Aug 11 Europe (Deutschland, Heggen)

    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.

  18. Jack B

    Aug 12 Pre-Release Testers, Xojo Pro

    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.

or Sign Up to reply!