SQL Server - SQLConnectionMBS - ODBC Invalid cursor state

Databases
1

Hi,

I have an app using SQLconnectionMBS for SQL Server 2017 (VM), running on macOS (Catalina).
The SQL Server database was migrated from SQLite.
Xojo 2019r3.1.

I’m testing the app after migrating to SQL Server and have hundreds of SQL statements/methods, same config, many are running fine.

But the following is raising an exception: 24000 [FreeTDS][SQL Server]Invalid cursor state

All SQL methods are similar:

' Prepare SQL Command
sqc = New SQLCommandMBS
Try
  sqc.Connection = sqmDB
  sqc.setCommandText(Str) ‘ see SELECT statement below
 
  ' Set parameters
  sqc.Param("doc_ID").setAsLong(docID)
   ' Execute command
  sqc.Execute
   ' Get Record Set
  rs = sqc.AsRecordSet
Catch r As SQLErrorExceptionMBS
  db_Exception(sqc, r, CurrentMethodName)
End Try

The sqc.AsRecordSet gives an SQLErrroExceptionMBS: 24000 [FreeTDS][SQL Server]Invalid cursor state

Executing the same SELECT statement in SQL Server Management Studio (Windows 10) runs fine, returning 140 records.
The same statement running against the SQLite DB runs fine, returning 140 records.

I tried the workaround found here adding “SET NOCOUNT ON;” before the SELECT Statement, it solves the error but returns a NIL RecordSet.

This SELECT statement is complex and build by multiple routines, it contains JOINS, Aggregates functions, Case When clauses, etc.
Here is a small part of it.

SELECT dh.doc_ID, ... sum(dep.parcel_Weight) AS parcel_Weight ...
(SELECT sum(dpp.Qty) from sDocProd As dpp ... WHERE dpp.doc_ID = :doc_ID) as prod_Qty, ...
FROM sDocHdr As dh, ...., sDocElement As de LEFT OUTER JOIN sDocElemParcel dep ON dep.docElem_ID = de.docElem_ID  
WHERE ... AND ...
GROUP BY  ...
ORDER BY  ...

The SQLConnectionMBS is created with the following parameters:

Dim libtdsodbc As FolderItem = FindFile("libtdsodbc.dylib") Dim s As New SoftDeclareMBS Call s.LoadLibrary(libtdsodbc.NativePath) sqmDB = New SQLConnectionMBS sqmDB.SetFileOption sqmDB.kOptionLibraryODBC, libtdsodbc sqmDB.Option("UseAPI") = "ODBC" sqmDB.ConnectMT("Driver={FREETDS};Server=192.168.1.7;UId=xx;PWD=xxxx;Database=xxxDB;APP=XXXX;TDS_VERSION=7.2;Port=1433", "", "", SQLConnectionMBS.kODBCClient) sqmDB.Option("ODBCAddLongTextBufferSpace") = "false" sqmDB.Option("AutoCache") = "false" ' !!!! IMPORTANT: if true the INSERT methods are failing sqmDB.Option("Scrollable") = "true" ' !!!! IMPORTANT: if false ALL SQL commands are returning « Invalid cursor state » end if

Any solution or workaround are welcome !

2

What plugin version?

3

MBS SQL Plugins: last one (March 2020) but I have the same issue with Xojo 2019r2.1 and SQL plugin from Nov 2019.
MBS Complete v201

4

Let me write you an email…

5

There is one thing I noticed. We have two ways to load libtdsodbc, one is directly as you do and other is with putting the dylib path in the connection string, which may be better:

[code]// connect via iODBC pointing to libtdsodbc
Dim libtdsodbc As Folderitem = FindFile(“libtdsodbc.dylib”)
Dim cs As String = “DRIVER=”
cs = cs + libtdsodbc.NativePath
cs = cs + “;Server=”+iServer.Text+";UId="+iUser.Text+";PWD="+iPass.Text+";Database="+iDatabaseName.Text+";TDS_VERSION=7.2;Port="+iPort.Text

con.Option(“UseAPI”) = “ODBC”
con.Connect(cs,"","",SQLConnectionMBS.kODBCClient)[/code]

Can you try that way, too?

6

I wrote a blog post about the two ways to connect to Microsoft SQL Server from Mac:

https://mbs-plugins.de/archive/2020-04-23/Connect_to_Microsoft_SQL_Serve/monkeybreadsoftware_blog_xojo