MS SQL Server Transactions

  1. ‹ Older
  2. 3 months ago

    Karen A

    May 23 Pre-Release Testers

    @Norman P 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.

    - karen

  3. Jürg O

    May 23 Pre-Release Testers, Xojo Pro

    @Norman P In theory that _should_ make the existing ones reference count go to 0 and so it should get destroyed

    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.

  4. Karen A

    May 23 Pre-Release Testers
    Edited 3 months ago

    @Jürg O 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

    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?

  5. Bob K

    May 23 Pre-Release Testers, Xojo Pro Kansas City

    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.

  6. Alberto D

    May 23 Pre-Release Testers
    Edited 3 months ago

    @Bob K so the @@ SPID value before the BeginTransaction is the same as after BeginTransaction and after Select @@TranCount ?

    Sorry I'm commenting on something I have no idea about, just trying to help.

  7. Bob K

    May 23 Pre-Release Testers, Xojo Pro Kansas City

    I retrieved the SPID and TRANCOUNT in the same query.

  8. scott b

    May 23 Pre-Release Testers, Xojo Pro local coffee shop

    @Bob K if you don’t find the issue or want help bearing on the ActiveRecord code next week, let me know. I’m currently out of town at the Indy500 race. When I get home next week I can spin up a MS SQL Server and help test/beat on AR. Not very helpful for you today.

  9. Bob K

    May 23 Pre-Release Testers, Xojo Pro Kansas City

    @scott b @Bob K if you don’t find the issue or want help bearing on the ActiveRecord code next week, let me know. I’m currently out of town at the Indy500 race. When I get home next week I can spin up a MS SQL Server and help test/beat on AR. Not very helpful for you today.

    Sure, having another set of eyes on it can't hurt.

  10. Norman P

    May 23 Pre-Release Testers, Xojo Pro great-white-software.com/blog

    @Karen A Does that happen immediately when no references remain or is it sometimes deferred? Calling RecordSet.Close explicitly should always have immediate effect.

    - karen

    Immediate - this has always been the case
    As soon as an objects reference count is 0 it is destroyed and disposed of

  11. Norman P

    May 23 Pre-Release Testers, Xojo Pro great-white-software.com/blog

    @Jürg O 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.

    This truly seems to be a bug in the ms sql server recordset implementation then

  12. Norman P

    May 23 Pre-Release Testers, Xojo Pro great-white-software.com/blog

    @Bob K 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.

    which sucks for you and Xojo

  13. Jürg O

    May 23 Pre-Release Testers, Xojo Pro
    Edited 3 months ago

    @Bob K 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.)

    I think what we all see is that Xojo's MSSQLPlugin is using "multiple Connections". And that this can cause issues with Transactions. Especially since we don't know when/why a "new Connection" is made by the Plugin (or the API it's using). And we don't seem to get control over that behavior (otherwise it could even be quite useful). But like this, it seems like a "fuzzy logic" to us (even if there might be some "well-thought logic" behind the scenes - but it doesn't play well with how we're using it in Xojo). If the Plugin would use it's API with RetainSameConnection by default, that most likely would be better, for all ways we're currently using it (rs.Close explicitly or not).

  14. Alberto D

    May 23 Pre-Release Testers

    Travis posted a plugin to Feedback Case #32121 if you want to test it to see if that solves/reduces this issue.

  15. Russ L

    May 23 Pre-Release Testers, Xojo Pro

    @Bob K 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.

    Two things I learned the hard way.

    1. The Xojo database code (for MSSQL) is limited.
    2. Always use MBS! ( I know you said no plugins!)
  16. Bob K

    May 23 Pre-Release Testers, Xojo Pro Kansas City

    Well, the SPID is the same through now and I'm not getting any errors when trying to commit. However, my preparedStatements aren't actually saving any data and I'm not getting any errors either.

    Will add more when I do some additional testing.

  17. Bob K

    May 23 Pre-Release Testers, Xojo Pro Kansas City

    Very simple prepared statements appear to work , or at least my simple one did, but as soon as I create a more complex one (updating a bunch of different types of fields) it silently fails. The silent failure is awful as you don't know it failed.

  18. Tim H

    May 23 Pre-Release Testers Portland, OR USA

    SQL Server (at least circa 2010) is itself an odd beast in that you can only have one query open at a time on a given connection. It looks like some time after 2010, Xojo "fixed" that problem by automatically opening a new connection. It used to be SQL Server would return a database error if you tried to do an sqlselect when you had an active recordset.

  19. Tim H

    May 23 Pre-Release Testers Portland, OR USA
    Edited 3 months ago

    @Jürg O 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?

    The problem is that the existing result set isn't replaced (and therefore doesn't go out of scope) until after the new query is processed by the database. So at the time the new sqlselect is executed, the existing recordset is still alive.

    So while you are technically not reusing the actual recordset, you are reusing the variable without first closing the existing recordset. So for all intents and purposes, yes, you are "reusing the recordset".

  20. Russ L

    May 23 Pre-Release Testers, Xojo Pro

    @Tim H SQL Server (at least circa 2010) is itself an odd beast in that you can only have one query open at a time on a given connection. It looks like some time after 2010, Xojo "fixed" that problem by automatically opening a new connection. It used to be SQL Server would return a database error if you tried to do an sqlselect when you had an active recordset.

    This Sounds like ‘mars’ multiple active record sets. It can be turned on or off via a connectionstring option

    MultipleActiveResultSets=True

    I believe it’s off by default

    Although it’s on the same connection not a new one

  21. Norman P

    May 23 Pre-Release Testers, Xojo Pro great-white-software.com/blog

    @Bob K The silent failure is awful as you don't know it failed.

    :(

or Sign Up to reply!