[quote=437831:@Jürg Otter]Bingo…! I guess you’re on the right track with this.
I’ve just found my old Feedback Case #32121.
@Bob Keeney : Is it possible that you’re running into this issue?[/quote]
Yes, this looks like the problem. First reported in 2014.
How can a major issue like this still sit in the queue for 5 freaking years?
what if you do a recordset close before the commit ?
not sure if thats feasible every where
Jay’s post and notes on the case seem to imply that works around the issue
I’m using a recordset to load the record and that’s it. For updates I use SQL and Prepared Statements. I start the transaction before I create the SQL and by the time I attempt the Execute it fails with transaction not started error. But let me play with it some more and see if I can wrangle it into submission.
Right but after loading do you close the recordset ?
Trying that now…
Thanks @Jay Madren for the Information / Workaround.
Xojo’s MSSQLServer-Plugin still remains an odd duck. Regarding this behavior, it acts different to all other DatabasePlugins provided by Xojo. And it acts differently than MBS SQL Plugin. That makes “x - Database/System” code, and switching to/from Xojo’s Plugins not quite easy. Or moving an existing project from DB-System-X to MS-SQL (using Xojo’s Plugin).
So in my opinion, Xojo should make sure to use RetainSameConnection by default.
Otherwise, even experienced developers such as @Bob Keeney are running into issues every now and then. Bob has created two Forum posts because of that, and added Info to Feedback. One should be aware of it - but still one is running into this again and again. It’s currently not “Xojo like”, as usually it’s very good at x-Platform (or in this case: different DB Servers).
'cause with all other Databases one is usually fine to just let the RecordSet go out of scope (and let it close itself). See above → existing code currently doesn’t play well with Xojo’s MSSQLServer Plugin.
That’s why the info about rs.Close is quite interesting - but it still can be considered just as a workaround. If this “don’t RetainSameConnection” is giving us a lot of additional benefits, then Xojo should make this an “opt-in” Property. But the default should be as “x-DB-Server/System” compatible as possible.
I’m wondering if there is a way to force the connection to set the RetainSameConnection?
I haven’t found one in 2014… It seems to me that this is some Property of the API being used, and has to be set in the Plugin’s Code for that reason (or can we use Declares to the DB-object?). I haven’t found a way to set the behavior using “SQL Commands”. I’d be happy if either of that is possible 
So my problem might be a little different - not sure. I’m using this code to Begin the Transaction and to immediately check the TranCount and it’s always returning 0 which is ultimately the problem since later we try to get to commit any changes.
[code]DataFile.DB.SQLExecuteRaiseOnError( “BEGIN TRANSACTION” )
Dim rs As recordset = DataFile.DB.SQLSelect(“Select @@TRANCOUNT”)
Dim icnt As Integer = rs.IdxField(1).IntegerValue
If icnt = 0 Then
Break
End[/code]
The fact that icnt is always zero is troubling (although to be fair yesterday I had a variation where it did get a count so I might just need to start over.)
DataFile.DB.SQLExecuteRaiseOnError is one of our methods that simply does the SQLExecute and checks the error and if has one it raises an exception so nothing fancy there. By this point in the code we’ve gone through a half dozen or so Recordset queries before attempting this.
Thoughts?
Yes, and in my testing this works correctly on SQL Server. But in your example project, the RecordSet doesn’t go out of scope, you’re just assigning new data to it. It’s in these situations that you need to close the RercordSet before issuing further Selects or Executes.
I would say that you are encountering this issue by not following proper coding techniques, which is to properly close the RecordSet before reusing it. Just because that’s not been an issue with other databases doesn’t excuse doing due diligence and properly closing the RecordSet. Why would there be a Close method if it was never needed?
In my case I am never reusing a recordset. But they do go out of scope since that works everywhere else. But I’m not sure that helps me - see above.
DB.SQLSelect creates a new Instance of a RecordSet, so the instance of the old one is no longer referenced and goes out of scope (sooner or later).
So that’s not “reusing a RecordSet”. Right or wrong?
Are you sure none of those previous queries are not still in scope? Especially since this is a method on your own custom object, which I assume is staying in scope?
Since performing the operations directly with the Xojo methods works, I think you need to step through your code to find where things may be different. You can also add @@SPID to your @@TRANCOUNT select, which will tell you if it’s starting another session. I think @@SPID is always 52 for the first session.
[quote=437868:@Jürg Otter]DB.SQLSelect creates a new Instance of a RecordSet, so the instance of the old one is no longer referenced and goes out of scope (sooner or later).
So that’s not “reusing a RecordSet”. Right or wrong?[/quote]
I don’t know how the mechanics work - someone from Xojo would have to answer this. But the fact is that I can replicate the issue by “reusing” a RecordSet within the same method without closing it, whereas if I use separate methods which also don’t close the recordset, it works correctly. So truly “going out of scope” seems to be different than “assigning a new recordset” via SQLSelect.
I never keep a recordset around so they are definitely going out of scope. The custom objects are just maps of the db fields to class properties so there is zero need/desire to keep the recordset around once the object is loaded.
[quote=437868:@Jürg Otter]DB.SQLSelect creates a new Instance of a RecordSet, so the instance of the old one is no longer referenced and goes out of scope (sooner or later).
So that’s not “reusing a RecordSet”. Right or wrong?[/quote]
I would say it’s a new recordset instance, not reusing an existing recordset. This is unless Xojo is doing something different with SQL Server recordset’s compared to everything else.
[quote=437868:@Jürg Otter]DB.SQLSelect creates a new Instance of a RecordSet, so the instance of the old one is no longer referenced and goes out of scope (sooner or later).
So that’s not “reusing a RecordSet”. Right or wrong?[/quote]
In theory that should make the existing ones reference count go to 0 and so it should get destroyed
Does that happen immediately when no references remain or is it sometimes deferred? Calling RecordSet.Close explicitly should always have immediate effect.
I’ve guessed so.
But the difference is: rsExisting = DB.SQLSelect "new query" will cause the DB object to fetch a new RecordSet while the rsExisting still is in Scope and not yet .Close’d.
That certainly would account for the difference compared to:
rsExisting.Close
rsExisting = nil
rsExisting = DB.SQLSelect("new query")
So I keep my my opinion described in this answer.
The current behavior is not quite friendly when it comes to use different DB Servers/Systems with Xojo, since their Plugin is behaving unexpected (even though it can be explained). Existing code working perfectly fine on other DB Systems won’t “just work” with Xojo’s MSSQLServerPlugin. Well, kind of works - but giving unexpected behavior 
Still: I really appreciate what you’ve figured out. That will be very useful in some situations.
That makes perfect sense… While I don’t use MS SQL server I do always close close on a recordset even if I am going to reuse the variable or it is going to go out of scope… I’m glad I got in the habit of doing that!
Maybe Docs should say always to close and Not rely on going out of scope?
Well, my @@SPID immediately before the BeginTransaction call is the same value and I’m still getting a @@TranCount of zero so something is clearly very wrong in the plugin.
It might be that not closing any recordset will cause this issue. Very frustrating that a 5 year old bug still hasn’t even been verified by Xojo. Essentially it means anyone looking at Xojo for Windows apps and using SQL Server will quickly discover this bug and simply move on…Like my client.