ODBC Database Insert Issue

  1. last week

    Bob K

    Apr 12 Pre-Release Testers, Xojo Pro Kansas City

    It's not been a good day. First, I found out that Commits and Rollsbacks don't like to work in MS SQL Server because of a bug Feedback Case #32121.

    So we switched to ODBC to the same MS SQL Server database and a simple Insert in a transaction doesn't like to insert. DB shows no errors either. Anybody seen this? This provides no error but no insert either:

    db.SQLExecute("Begin transaction")
    if db.error then
      break
    end if
    
    db.SQLExecute("insert into addresses (Name, Active) values('GEOFFRY',0)")
    if db.Error then
      break
    end if
    
    db.Commit
    if db.error then
      break
    end if

    All done with 2017 R3.

  2. Jared F

    Apr 12 Pre-Release Testers, Xojo Pro Milwaukee, WI
    Edited last week

    Does db.SQLExecute("COMMIT TRANSACTION") work?

  3. Bob K

    Apr 12 Pre-Release Testers, Xojo Pro Kansas City

    @Jared F Does db.SQLExecute("COMMIT TRANSACTION") work?

    yes! That solves part of the problem at least.

  4. Bob K

    Apr 12 Pre-Release Testers, Xojo Pro Kansas City

    I don't see any Feedback issues related to this problem. Or perhaps my search is faulty. Is this a known issue?

  5. Jared F

    Apr 12 Pre-Release Testers, Xojo Pro Milwaukee, WI

    I'm not sure if it's a known issue.
    I've always done my database work with the raw SQL commands rather than the built in functions since they are a bit of a black box.

  6. Jared F

    Apr 12 Pre-Release Testers, Xojo Pro Milwaukee, WI

    The LR does specifically say that you should be able to use db.Commit after (and only after) starting a transaction with the appropriate SQL command db.SQLExecute("BEGIN TRANSACTION")

    So i'd say it's a bug.

  7. 7 days ago

    Wayne G

    Apr 12 Pre-Release Testers, Xojo Pro New Zealand axisdirect.nz

    @Jared F So i'd say it's a bug.

    Or another limitation of the MSSQL ODBC driver.

  8. Nedi F

    Apr 13 Europe (Italy, Bologna)

    That's strange....I have an application in Windows 10 environment. My app uses MSSQLServer plugin, and Commit/Rollback works fine...

  9. 6 days ago

    Jared F

    Apr 13 Pre-Release Testers, Xojo Pro Milwaukee, WI

    @Wayne G Or another limitation of the MSSQL ODBC driver.

    I guess the question is, if db.SQLExecute("COMMIT TRANSACTION") works in Bob's case, but db.Commit doesn't, then what is db.Commit actually doing other than just sending the COMMIT TRANSACTION command to the database?

  10. Joseph E

    Apr 13 Pre-Release Testers CA

    Hi Bob & Co.,

    In your record insert example above, what is the advantage of using a transaction when using the MSSQL Server plug in? I only ask because it raises a question as to my understanding of transactions, at least how they relate to MSSQL.

    In your case above, if the insert fails is there really anything to "RollBack"? My understanding, based somewhat on the linked article is the default mode for MSSQL is AutoCommit, unless of course you use the "begin transaction", "end transaction" and "commit", which turns it off for that transaction. Now I understand that if you are executing several dependent transactions then wrapping those in begin and end makes perfect sense, in case a portion of them fail. But for most simple inserts, deletes, updates to a single record in the table, what actually gets rolled back if the operation fails and is actually never written?

    MSSQL AutoCommit

    I want to understand this better.

  11. Bob K

    Apr 13 Pre-Release Testers, Xojo Pro Kansas City

    Well, it's really a simple, nonsense example. It was proving what we were seeing in ActiveRecord without all the overhead.

    In ActiveRecord we fire events Before/After Save/Create/Delete/Update and if the user does anything in those events they're automagically part of the same transaction. But you're right, *most* of the time it's not necessary to be in a transaction but we provide it just in case. And in AR we provide the ability to create your own transaction block which overrides the one we do in the Save/Delete methods.

  12. Joseph E

    Apr 13 Pre-Release Testers CA

    Ah, that makes perfect sense now.

    BTW: "Automagically"? I Love it... Have to find a way to use that one today :-)

  13. Bob K

    Apr 13 Pre-Release Testers, Xojo Pro Kansas City

    @Joseph E Automagically

    I use it enough that I added it to my dictionary. :)

or Sign Up to reply!