Mac OSX ODBC to MSSQL

Databases
1

Hi to all,
I would like to connect to a Microsoft sql server via Xojo ODBC driver (I don’t want to use paid plugins) from my Mac. This is my situation: Xojo web application, Mac os 12.4.7, Xojo 2024R1, Ms-SQL Server 2019. I installed the Microsoft odbc drivers (https://learn.microsoft.com/it-it/sql/connect/odbc/linux- mac/install-microsoft-odbc-driver-sql-server-macos?view=sql-server-ver16) everything is ok, if from the command line I try to connect to the sql server (sqlcmd -S 192.168.1.40 -U sa -P XXXXX -C) everything works (Exit:[SQL_SUCCESS]). Now I’m trying to connect via Xojo, the code and connection string I’m using is this:

Var myDatabase as new ODBCDatabase
myDatabase.DataSource = “Driver={ODBC Driver 18 for SQL Server};Server=192.168.1.40;Database=xojo_db;UID=sa;PWD=XXXXX;Encrypt=No;TrustServerCertificate=Yes;”
myDatabase.Connect

When I run the application the browser opens and shows the popup message “Connection problem. We are having trouble communicating with the server. Please wait a moment while we attempt to reconnect.”, the Xojo IDE closes the runtime execution and only the source mask remains without any error messages.

Does anyone have any suggestions on how to understand where the problem might be? Maybe in the connection string?

Thank you

2

I have a desktop application that connects to the ODBC server from macOS and Windows.
In macOS, I have been using the driver from ActualTech for years.
In Windows, I use the driver from Microsoft. I cannot get a working connection with the ODBC driver 18 either.
It looks like this for me:

#If TargetMacOS then
  odbcDB.DataSource = "Driver={Actual SQL Server};Server=192.168.1.2;Database=MyDatabase;UID=dba;Pwd=xyz"
#Else
  odbcDB.DataSource = "Driver={ODBC Driver 17 for SQL Server};Server=192.168.1.2;Database=;UID=dba;Pwd=xyz;"  
#EndIf

I can make the connection with

{SQL Server}

or with

{ODBC Driver 17 for SQL Server}

to establish the connection.
With

{ODBC Driver 18 for SQL Server}

I cannot establish a connection in Windows either. I don’t know why this is the case.

3

Hi Michael,
thanks for your feedback, the windows configuration works correctly for me too with the odbc drivers. The strange thing is that although the Microsoft drivers work on OSX (with sqlcmd command the connection and database management works well) but it is not possible to use these drivers from xojo, and all Xojo users must purchase other software/licenses. I hope some other user has the solution. I’ll try to open a ticket with Xojo and see if they can help us. Thanks again. Oscar

1 Like
4

The Microsoft-provided ODBC driver for Mac is compiled using the unixODBC library standard (used by most of the Linux world). Most Mac ODBC-enabled applications (including the Xojo plugin, MS Excel, FileMaker, etc) are compiled using the competing iODBC library standard. Because of this, nearly all ODBC drivers on the Mac are compiled using iODBC. An iODBC application cannot use a unixODBC driver.

An advantage of a commercially supported driver is there is an incentive for the vendor to make sure it works with your application.

Jonathan
Actual Tech

1 Like
5

I have had the driver from ActualTech for several years and am very satisfied with it.

Now I have a database on the MS SQL Server that contains BLOB fields. However, I can neither read nor write these from the Mac. This was the reason for me to try the ODBC drivers from Microsoft.

After clarifying the situation, it is now clear why it does not work with the MS ODBC drivers.

Apparently I am the only one with this problem (Issue#76243)

6

MBS Xojo SQL Plugin can connect from macOS to MS SQL Server using freetds library.

1 Like
7

Thanks for the issue link. I’ll take a look at it and let you know what I find out from the driver perspective.

Jonathan

1 Like
8

I’ve already thought about that. But just had a really BIG change in the app concerned. This is currently being rolled out. So I’m not touching anything there at the moment.

If I can’t get any further with the ODBCDatabase+ActualTech combination, then this is probably my only alternative.

9

Thank you for taking a look at the problem.

10

I can successfully read a BLOB from a varbinary / image field using the Actual SQL Server driver and the Xojo ODBC plugin. If the field is not already an image datatype, cast it to one and read it into a MemoryBlock (or Picture):

Dim mb As MemoryBlock
rs = db.SQLSelect(“select cast(Pic As Image) As Pic from QC_BLOB_TEST where id = 2”)

If Not IsNull(rs) Then
mb = rs.Field(“Pic”).NativeValue ’ the MemoryBlock will have all the data
End If

I can reproduce the problem with writing a BLOB. I’m still investigating and will let you know what I find out.

Jonathan

11

That is good news! Thank you!

I also have in a BLOB field a document that consists of an RTF text or an image or a combination of RTF text and image - depending on what the user enters. Is there something like
"select cast(Document as ????) as ???? from QC_BLOB_TEST where id = 2" possible?

12

Can you tell me the data type of the Document field in SQL database? Also, what Xojo type are you ultimately storing the document in (i.e. MemoryBlock or String)?

13

The data type of the document field in SQL database for MS SQL Server is “varbinary(max)”.

The document data type in my application is STRING because a STRING is retrieved from FormattedTextControl:

  • FormattedText.setRTF(MyDocumentString) to get a document in the control.
  • MyDocumentString = FTDocument.getRTF() to get a document from the control

If the document contains an image, it will be lost. The text comes across reasonably well (but is of course not usable for the customer).

In contrast to documents, pure images are displayed in the other controls
are always stored in a MemoryBlock: Read from the database into a MemoryBlock, write to the database from a MemoryBlock. The data type “varbinary(max)” is also used for images in MS SQL Server.
The handling works in the Windows version of the application.