Scenario:
Xojo 2025r2.1 web app
MS SqlServer 2022
MBS SQL plugin 25.3 (SQLConnectionMBS and SQLCommandMBS)
I have a similar problem, but with seemingly opposite behavior.
In several projects, even with many users connected, I have always used a single SQLConnectionMBS saved in the session, without any problems.
Now, in a new web project, if I use a single SQLConnectionMBS saved in the session, the error “24000 [FreeTDS][SQL Server]Invalid cursor state” does NOT occur ONLY if I set with.Option(‘AutoCache’) = “true”.
I don’t understand the reason for this error, given my past experience with a single SQLConnectionMBS.
On the other hand, if I create a new SQLConnectionMBS for each SQL statement, no error occurs regardless of the presence of AutoCache and its value.
What is the best solution, in your opinion?
Use AutoCache = true (even with a single SQLConnectionMBS) or create a SQLConnectionMBS for each SQL statement (not using AutoCache, if not necessary)?
After a SQL Execute, you have a cursor in SQLCommandMBS, so you need to call FetchNext to go to the first row. If you do something else, the library my complain.
Dim con As SqlConnectionMBS = Session.db // option 1
(or, if I create a new connection)
Dim con As SqlConnectionMBS = GetDatabaseConnection() // option 2
dim sql As String = “SELECT …”
Dim cmd As New SqlCommandMBS(con, sql)
cmd.Param(“name”).setAsString(pName)
cmd.Execute // here is the “24000 [FreeTDS][SQL Server]Invalid cursor state” error if I use
// “option1”
On Web specifically, each Session needs it’s own database connection. You cannot have one single connection across the whole app. I wonder if the cursor state is a symptom of cross contaminated queries.
Thank you @Tim_Parnell, your message allows me to clarify:
In my previous projects, I always created one connection for each session.
What happens now (and what I don’t understand) is that I have to create a connection before executing each SQL statement and close the same connection at the end of the execution.
Or create a single connection per session, but with Option(‘AutoCache’) = “true.”
I would like to update this discussion because my problem has not yet been resolved.
To continue the development in a Mac Intel environment with FreeTDS, I have chosen to open and close an SqlConnectionMBS for each SQL statement executed.
even though it seems absurd at first glance.
Today I was able to run the application in a Windows environment with a single SqlConnectionMBS created when opening the web session.
So, as I have always done.
Unfortunately, at this time I cannot test the same application on Linux, but I have never had similar problems with other applications using FreeTDS on Linux.
My question: if the problem only affects FreeTDS on MacOS, what is the solution?
Thanks to anyone who can help.
Hi all,
For me, it’s a FreeTDS error on macOS, since it works on Linux.
Can anyone confirm or deny this opinion?
If it is an error, can it be corrected? The MBS plugin documentation still states the access to SqlServer from Mac/Linux is via FreeTDS.
So it is assumed it should work or, if there is a bug, I think it should be fixed.
Alternatively, if the MBS plugin can also work with Microsoft’s ODBC driver for macOS, are there any examples or positive/negative experiences?
Thank you.
It is the same application, and the execution order is the same whether the application is running on Windows (native ODBC)/macOS (FreeTDS for macOS)/Linux (FreeTDS for Linux).
The cause of the problem is not the execution order.
As I specified, the error probably only occurs when the application is running on macOS.
This is especially true when more than one SQL command is executed in a method.
The same application works fine on Windows and, I believe, also on Linux (based on past experience; I am currently unable to run it on Linux).
Furthermore, I was asking if the SQL MBS plugin can use the MacOS/Linux version of Microsoft’s ODBC driver.
And if it cannot use it, why not?
Can you reproduce this in a sample project.
FreeTDS may react a bit different then ODBC, so there may still be an order issue, which is okay for one, but not the other.
Could you do this in a console project?
And then add these lines to the top of the open event:
this will tell the plugin to log a ton of things.
It should show up in the console app in the window and we’d love to know what you do before you get the error.