Help with FMPro and AppleScript CGI (long)

[bcostlow]

I have inherited a project that's causing me a few problems.

Here's what we're trying to do in a nutshell.

1. Post a form that causes a specifc record in an FMPro 5 database to update (i.e. change the data in some cells)

2. Run an applescript that opens another application, opens a document in that application, and updates the document based on the record changes in the database.

Background

The original version of this was done with CDML and a FileMaker script was run when the post was done, the FileMaker Script ran the AppleScript.

This caused problems because when two Web users tried to run the Script simultaneously, everything ground to a halt ( a problem I saw discussed on the scripting forum on this site).

Later they moved to Lasso WDE, in an attempt to solve the problem. Lasso just used an LDML tag to call the AppleScript directly. This caused problems because the Finder wouldn't run multiple copies of the script correctly.

So the version I inherited now uses Lasso WDE to modify the DB, the return page from that update contructs a url to CGI version of the AppleScript and does a META refresh. The web pages now run under WebStar, which can run multiple copies of the script simultaneously as CGI code.

Here's where it still runs aground.

Problem Number 1. Preventing two users from modifying the same record simultaneously.

Using "with transaction, end transaction" appears to lock the whole DB, irregardless of what actually runs inside it. So when a second copy of the CGI starts up, it immediately dies because the db is locked. The only solution I can think of is to put every call to the database inside of a repeat with a try; if the error indicates the db is locked, cycle around and try again. This seems really ugly and inefficient. Anyone have a better idea? The other problem is that if there is an error before end transaction, everyone gets locked out of the database. I even tried this:

try

begin transaction

-- some working code

end transaction

on error

end transaction

-- some error handler code

end try

with the hope that any error would trip the end transaction process, but it doesn't seem to work. Anyone have any ideas?

Problem #2. Identifying the correct record to use.

The CGI code needs to read data from the record. The database has a field "RecordNumber" whose contents are auto-generated. The code assumes that the contents of "RecordNumber" can be used in "record i" calls.

The CGI is passed the name of the database, and the value of the cell "RecordNumber" which are gotten through an LDML call. These are stored in the AppleScript variables the_database_name and the_record_number.

This is then used in several places as the record number. e.g:

go to the_database_name

go to the_record_number

However, if any records are ever deleted, these numbers don't match up. (I also think, but have not tested, that if all records are not showing, these numbers don't match either).

So, given a cell value for a field whose value is unique for each record, how do you match it up with other cells from that record?

I tried

set foo to (every record of the_database_name whose cell "RecordNumber" = the_record_number)

get cell "someothercell" of foo

but that doesn't work, as the contents of foo are all the data in the record as a list, and not a pointer to the record object itself.

I also tried

set foo to (cell "someothercell" of the record of the_database_name whose cell "RecordNumber" = the_record_number)

Any help, comments, or even a completely different approach I might try would be appreciated.

[webdudes]

Perhaps if you define the project a little more specifically we can help out. Does the update to the other application's document have to be done while the browser is still active. Could you update these documents every ten minutes, every hour?

What about just checking to see if the script is running before running it again and telling the user to wait until it isn't busy.

How about a separate Mac for doing the script processing?

WebDudes