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