Doing Business With Transactions

Doing Business With Transactions

The AppleScript Language Guide contains a section entitled “With Transaction Statements,” which describes in some detail how to script applications that implement transactions. However, very few applications do implement transactions, and their proper use is often not clearly understood even by the developers of these applications.

Those applications that do implement transactions are database applications, in one way or another. I have verified that the following applications support transactions in the classic Mac OS: FileMaker Pro; Font Reserve; HyperCard; and Apple’s Network Setup Scripting. In Mac OS X, FileMaker Pro and Apple’s System Events purport to support transactions, the former in documentation and the latter in its dictionary, but I am unable to make them work.

Most of the following discussion relates to the classic Mac OS. Where I mean to include Mac OS X in the discussion, I say so explicitly.

Basically, the prescribed way to use transactions is to enclose a sequence of commands to a suitable application in a With Transaction block. This block must in turn be enclosed in a Tell Application block addressed to an application that supports transactions. The With Transaction statement causes the targeted application, if it supports transactions, to treat all of the commands enclosed within the block as a single operation that should not be committed to the database until the transaction is terminated by the block’s concluding End Transaction statement. In applications that support the Abort Transaction event, pending changes are discarded if the script sends an Abort Transaction event before the End Transaction event. The application, if it supports transactions, prevents scripts from altering the contents of the database while a transaction is pending, unless the commands are part of the transaction, in order to preserve the integrity of the database.

The use of transactions is optional even in applications that support them. They are useful mainly in environments where there is a potential for multiple users or multiple processes to attempt to modify a database simultaneously, typically on a network. You don’t, after all, want one user deleting a customer record while another user is changing the customer’s address.

Under the hood, a With Transaction statement sends a Begin Transaction Apple event to the application, which then returns a unique transaction ID, an integer, to the script. The transaction ID is used internally by AppleScript and by the application; you never need to know what it is. Every command your script sends to the application within the With Transaction block is accompanied by the transaction ID automatically. If the application determines that the transaction ID matches the currently open transaction, it performs the command. The application blocks any command from changing the database if it is not accompanied by the correct transaction ID and the transaction is not yet closed. An optional Session parameter to the With Transaction command can be implemented, as it is, for example, in Font Reserve in the classic Mac OS. Applications implementing transactions apparently should not expose the Begin Transaction and End Transaction commands in the application’s dictionary, because the With Transaction and End Transaction statements that are part of AppleScript send the correct events to the application.

An Abort Transaction command can be implemented by applications. It is used to prevent changes from being committed to the database if an error occurred during a transaction. It appears that this command should be included in the dictionary of any application that supports it, but I’m not really sure. In the classic Mac OS, Apple’s Network Setup Scripting application implements Abort Transaction; FileMaker Pro and HyperCard do not.

Unfortunately, the elegant simplicity of the transaction mechanism is obscured by applications that expose explicit Begin Transaction and End Transaction commands in their terminology dictionaries. HyperCard, available only in the classic Mac OS, does it right, supporting transactions but not exposing the events in the application dictionary. An example of an application that does expose Begin Transaction and End Transaction commands is Apple’s own Network Setup Scripting application, found in the Scripting Additions folder in the classic Mac OS; it is discussed in depth in the following paragraphs. FileMaker Pro’s dictionary does so as well, in the classic Mac OS. The Mac OS X version of FileMaker Pro omits the Begin/End Transaction terminology, but transactions do not appear to work in it. Apple’s System Events application, available only in Mac OS X, includes Begin Transaction, End Transaction and Abort Transaction in its dictionary, but they aren’t yet hooked up (as of Mac OS X 10.4.5 (Tiger)).

Apple’s old Network Setup Scripting documentation for the classic Mac OS compounded the problem by advising scripters to obtain the transaction ID explicitly using a Begin Transaction command, and to use it in error-trapping routines to determine whether a transaction is pending. This technique works, but it is more awkward than it needs to be. The preferred With Transaction form can be used in such a way, described below, that there is no need to use the transaction ID. The Begin Transaction form used in Apple’s examples can lead to confusion, for several reasons. For one thing, the Language Guide’s discussion of transactions doesn’t mention Begin Transaction at all. Secondly, Begin Transaction and End Transaction do not compile as a block but as separate commands; in a compiled script, the commands within the transaction are not indented, and compiling a script using Begin Transaction will not flag an omitted End Transaction as an error. Finally, the correct With Transaction form is the only syntax that will work with applications, such as HyperCard, that correctly omit the Begin/End Transaction commands from their dictionaries.

You can and, in my opinion, should use the correct With Transaction terminology even in an application that erroneously exposes the Begin Transaction and End Transaction Apple events in its dictionary. The classic Mac OS documentation for FileMaker Pro and HyperCard advises use of the With Transaction terminology.

Doing it the right way requires a scripter to put up with one annoyance. As the classic Mac OS FileMaker Pro Apple Events Reference database explains, you can’t compile a script using the With Transaction form in such an application unless you first delete the word “transaction” from the closing End Transaction command. This is true of Network Setup Scripting, as well. By deleting “transaction,” you allow AppleScript to see the End command at compile time, before the application intercepts it, and to compile it as part of a proper With Transaction block. If you forget to delete the word “transaction” from End Transaction every time you recompile a script using With Transaction, a compile error will remind you. This problem has been known since at least the Developer Notes released with AppleScript 1.0 in December 1993, which had this somewhat unsatisfactory advice for developers:

“begin transaction and end transaction events are recorded as normal events rather than as a “with transaction…end transaction” statement. In addition, the terminology for kAEBeginTransaction and kAEEndTransaction is not in the standard AppleScript dictionary. A workaround for applications is to define both these events in their AETE with names that do not conflict.”

“Another, although ugly, workaround is to suggest that users should edit the script by using a name like “with transaction (* remove this comment to make a transaction statement *)”, which will print out and include a comment as part of the event. This event will not re-parse, and the user will have to correct it.”

The example scripts in Apple’s old Network Setup Scripting documentation use the transaction ID returned by the Begin Transaction event, not to control access to the database, but instead only to determine in an error handler whether a transaction has begun and therefore needs to be aborted. The sample scripts included on the Mac OS 9 CD (in the OTExtras subfolder of the Network Extras folder) do not use the transaction ID at all, but they nevertheless use the Begin Transaction command rather than With Transaction. In the documentation scripts, a variable to hold the transaction ID is first initialized to a predetermined value, namely, an empty string; a large section of the script, including both the Open Database and the Begin Transaction commands, is then enclosed in a Try block; the On Error clause tests the variable to see whether its value is different from its initialized value, as a means to learn indirectly whether a transaction ID had been issued before the error occured; and the script then explicitly aborts the transaction. Because they need the transaction ID for this purpose, Apple’s scripts are forced to use the Begin Transaction form, because the correct With Transaction form does not return the transaction ID in the result available to scripters.

A test script verifies that Apple’s example scripts work as intended in the event of an error inside the Begin Transaction block. In Mac OS 9, compile and run the following script once, as is, to see that the script toggles your modem’s speaker on or off. Then uncomment the “1 div 0” command to generate a deliberate error, recompile, and run the revised script to verify that Network Setup Scripting aborts the transaction because of the error without committing the change in the modem speaker’s state to the database. This time, the speaker remains in its previous state, on or off, without change.


--Toggle modem speaker using Apple's technique
set the transaction_ID to "" --initialize variable to null string
tell application "Network Setup Scripting"
   
   try --catch all errors
      open database
      set the transaction_ID to begin transaction --set variable to transaction ID
      set the current_config to ¬
         (every Modem configuration whose active is true)
      set the current_config to item 1 of the current_config
      set theResult to modem speaker enabled of the current_config
      if theResult is true then
         tell me to display dialog "Modem speaker is enabled; try to disable it"
         set modem speaker enabled of the current_config to false
      else
         tell me to display dialog "Modem speaker is disabled; try to enable it"
         set modem speaker enabled of the current_config to true
      end if
      --uncomment next line to see what happens with a transaction error
      --1 div 0 --deliberately throw an error
      end transaction
      close database
      
   on error
      --test variable to determine whether transaction was pending at time of error
      if the transaction_ID is not "" then abort transaction
      close database --NOTE: this line may error if database was not opened successfully
   end try
   
end tell

--Check result of toggling modem speaker (error traps omitted)
tell application "Network Setup Scripting"
   open database
   begin transaction
   set the current_config to ¬
      (every Modem configuration whose active is true)
   set the current_config to item 1 of the current_config
   set theResult to modem speaker enabled of the current_config
   if theResult is true then
      tell me to display dialog "Modem speaker is enabled"
   else
      tell me to display dialog "Modem speaker is disabled"
   end if
   end transaction
   close database
end tell

There is a cleaner way to handle transaction errors in Network Setup Scripting. Devote an error handler solely to the With Transaction block, catching other errors, such as an error attempting to open the database, separately. In other words, enclose the With Transaction block in its own Try block. Then the script does not need the transaction ID, because its logic alone lets it know whether an error occurred during the transaction. You no longer need the transaction ID to determine whether the error occurred during the transaction.

Here, then, is the same script, reworked to use the With Transaction form instead of the Begin Transaction form. Remember: it will not compile unless you first delete the word “transaction” from both of the End Transaction commands.


--Toggle modem speaker using preferred technique

--NOTE: delete the word "transaction" from "end transaction" twice before compiling

tell application "Network Setup Scripting"
   
   try --catch error opening database
      open database
   on error
      return --or other error handling
   end try
   
   try --catch errors within transaction
      with transaction
         set the current_config to ¬
            (every Modem configuration whose active is true)
         set the current_config to item 1 of the current_config
         set theResult to modem speaker enabled of the current_config
         if theResult is true then
            tell me to display dialog "Modem speaker is enabled; try to disable it"
            set modem speaker enabled of the current_config to false
         else
            tell me to display dialog "Modem speaker is disabled; try to enable it"
            set modem speaker enabled of the current_config to true
         end if
         --uncomment next line to see what happens with a transaction error
         --1 div 0 --deliberately throw an error
      end transaction --DELETE 'transaction' BEFORE COMPILING
      
      close database
      
   on error
      abort transaction
      close database --NOTE: we never get here if database was not opened successfully
   end try
   
end tell

--Check result of toggling modem speaker (error traps omitted)
tell application "Network Setup Scripting"
   open database
   with transaction
      set the current_config to ¬
         (every Modem configuration whose active is true)
      set the current_config to item 1 of the current_config
      set theResult to modem speaker enabled of the current_config
      if theResult is true then
         tell me to display dialog "Modem speaker is enabled"
      else
         tell me to display dialog "Modem speaker is disabled"
      end if
   end transaction --DELETE 'transaction' BEFORE COMPILING
   close database
end tell

How do you catch the error that occurs when one script attempts to begin a transaction while another transaction is pending? There is a standard error number for this situation, defined in the Apple Event Registry as the constant errAEInTransaction, number -10011 (“Could not handle this Apple event because it is not part of the current transaction”), but it does not appear to be universally generated in all situations by applications supporting transactions. Therefore, in the absence of good application documentation, you must determine for yourself what error is returned by the application you are scripting. My testing under the classic Mac OS indicates that FileMaker Pro returns error number -10011 (“Data is being accessed by another user, script, or transaction”). However, Network Setup Scripting returns error number -3291 (“Location Not Found”) in my testing, although there is another, more appropriate error string in a resource, which I discovered is associated with error number -10011 (“In Transaction”). One user reports that Network Setup Scripting returns error number -3291 the first time a transaction is begun while a previous transaction is still pending, then error number -10011 the second time. Network Setup Scripting examples don’t address this issue, which is, after all, the whole point of using transactions. HyperCard can return error number -10011 (“Couldn’t handle this command because it wasn’t part of the current transaction”) or error number -10012 (“The transaction to which this command belonged isn’t a valid transaction”), depending on circumstances which I haven’t fully sorted out. It appears that HyperCard may not generate an error when the second Begin Transaction event is received, but only upon the first command after receiving that event. The Registry defines error no. -10012 as constant errAENoSuchTransaction (“The specified transaction is not a valid transaction; the transaction may never have begun, or it may have been terminated”). An earlier version of Font Reserve returned error number -3347 (“Another session already has a transaction in progress”); Cal Simone and I brought this issue to the developer’s attention, but I haven’t tested more recent versions.

I have written a script to accompany this tip, Transaction Ferret (available elsewhere on this Web site), which you can use to test applications to see whether they support transactions, and to determine what error is returned when a second script tries to begin a transaction while a previous transaction is pending. Version 1.0.2 of Transaction Ferret runs on Mac OS X as well as the classic Mac OS.

Making use of the information in this tip, here is a script skeleton that does normal transaction error checking and also checks specifically for the case where a previous transaction is already pending. It works with the classic Mac OS version of FileMaker Pro. It should work with any classic Mac OS application that supports transactions (subject to using the application’s name and the correct error number, as described above).


tell application "FileMaker Pro"
   --assumes a database is already open
   try
      with transaction
         get name of database 1
         --uncomment next line to see what happens when previous transaction is pending
         --begin transaction --deliberately throw an error
      end transaction --DELETE 'transaction' BEFORE COMPILING
      
   on error errText number errNum
      if errNum is -10011 then --transaction is pending
         display dialog errText & space & errNum buttons {"OK"} default button "OK"
      else
         --handle other errors here
      end if
   end try
end tell

A final word about errors. In some of the examples given here, such as the previous example when you uncomment the Begin Transaction command, it may seem that a transaction is being opened but is not being closed, because the On Error clause is apparently invoked before the End Transaction command is reached. In other words, it appears from reading the script that an error will cause script execution to jump over the End Transaction statement. The explanation of this conundrum is found in the Language Guide. An End Transaction event is sent automatically when execution leaves the With Transaction block, even if it leaves because of an error during the transaction. In other words, you don’t have to worry about putting another End Transaction command in the On Error clause, even when you are scripting applications that expose an explicit End Transaction command. However, in applications which support the Abort Transaction event, you can place an Abort Transaction command in the On Error clause in order to prevent any commands that have been successfully executed before the error occurred from being committed to the database.

I am not aware of any Mac OS X applications that support transactions, although two of them purport to do so.

FileMaker Pro 8 comes with documentation (apparently unchanged since FileMaker Pro 7) stating that it supports transactions. However, I can’t make transactions work in FileMaker Pro 8. It executes commands that are placed in a With Transaction block, but I believe it is simply ignoring the With Transaction statement as other applications do. When using a test script, an attempt to initiate a new transaction and change the database while a first transaction is still open is allowed, when it should be prevented.

Apple’s System Events dictionary includes explicit Begin Transaction, End Transaction and Abort Transaction commands. They are intended for use in the Property List Suite and the XML Suite, but they aren’t yet hooked up (as of Mac OS X 10.4.5 (Tiger)). All three of these events take a mandatory direct parameter, described in the dictionary as a “reference,” but no instructions are provided to explain how to create this required parameter.