We use Monkeybread’s SQL Plugin using ODBC to connect to MSSQL databases as this is the only solution that does everything we want. This is what we found with each of the options that we looked at:
MSSQLDatabase Xojo Plugin
This plugin does not deal with Large Data Objects. We cannot read and write successfully to these fields using this plugin.
ODBCDatabase Xojo Plugin
This plugin does not handle Lock Timeouts correctly. When a lock timeout occurs, no error is raised and the recordset contains whatever data has successfully been read up to the timeout. It does read and write BLOBs correctly.
Monkeybread SQL Plugin using DBLibrary
This plugin does not handle lock timeout errors from the recordsets correctly. To be clear, we think this is a problem with Microsoft’s SQL client, not Monkeybread’s plugin.
Monkeybread SQL Plugin using ODBC
Lock timeouts are raised correctly. It does handle BLOBs but you must set the dtb.Option(“ODBCAddLongTextBufferSpace”) = “false”
Both Monkeybread plugins require that all recordsets are closed before disconnecting from the database. It’s also important not to open multiple recordsets at the same time against one database connection. This causes “unpredictable” behaviour. Again this is a Microsoft issue, not a Monkeybread problem.
These findings are from about a year ago so may have been updated.
Hope this is useful.