Problem with server side script and external data source

[Baloo]

With the implementation of server side scripting, I decided to fully normalize my tables and run nightly scripts to update local "index" columns. Users have the choice of a faster search against last night's index vs. a (frequently painfully) slower search against live data.

I've run into a problem when the data is located in an external source. When the script is run by FMS the result for data from an external source is a blank field, but when I run the script via either IWP or the PHP API it will grab and set the appropriate data as long as the external source is on the same Filemaker server (even if the external source is an ODBC link to a MySQL table). If the data is located in a file on another Filemaker server (We have 2), I’m completely hosed. FMS doesn’t work, and neither the PHP API nor IWP seems to be able to grab data from another server (I’ll save that question for another post).

The script is simple:

Go to layout

Show all records

Go to first record

Loop

Set field …

Next record, exit after next

End loop

Go to layout //to prevent 101 error from showing in admin console

I used the same login/password that I set in the FMS Admin console “Schedule Assistant” to test the script in IWP and the PHP API so I don’t see how it could be a permissions issue. The only thing I see in the log is the 101 “error” from trying to grab the record after last.

Before I spend the rest of my morning converting server side scripts to cron jobs on our LAMP server I figured I’d ask if anyone sees any glaring errors or can confirm that this is in fact a limitation of server side scripting.

Thanks

[xochi]

Your script is not multi-user safe, e.g. if another user has a record open for editing it will fail and kill your script. However, you'd probably see an error message in the log.

You might want to code it more defensively by using

Set Error Capture On

Set field

if Get(LastError) = xxx then ' record was locked, either skip it and return to it later or throw an error.

Set Error Capture Off

Another tip: Create a "log" table and a utility script "Log(msg)" which goes to the log table and creates / adds a new record and returns to the original layout. You can then use this logging function for debugging. I often put messages such as

Perform Script ("Log", "About to start server-side script XXX")

Perform Script ("Log", "Processing record # " & Get(RecordNumber))

In the script to make sure it's working normally. Then after the script is debugged I'll disable the ones I don't want.

[Baloo]

Thanks, but the script will run nightly at 3:00 AM (3 hours after the nightly backups start), and with all accounts set to disconnect from server when idle I'm not too worried about multi-user issues.

[tgilders]

Are all the username/passwords the same across all the FileMaker databases across all the servers? Are they FileMaker accounts or are you using External Authentication?

[Baloo]

All accounts are Filemaker based with the same user/password. It's set to the stock "[Data Entry Only]" permissions set which has access to the full list of extended privileges in all databases in all servers.

Also, if it was a permissions problem wouldn't the script also fail when run with the same login via IWP or the PHP API?

[tgilders]

I thought you indicated that the IWP/PHP only worked on data that was on the same server, and that all ways were failing on the different (external) FileMaker Servers.

Edited August 7, 2008 by Guest

[Baloo]

I did; sorry for the confusion. I've currently got my head solely around the problem of why IWP and the PHP API can see data on the same server and the FMS scheduled script can't.

I've sort of resigned myself to the idea that the wpe can't see relationships across servers.

I wrote a PHP test page grabbing the data I needed from a table on one server stuffing it into an associative array with the PK as the array key. Then scrolling through all the records in the table on the second server and using the pulled FK to grab the data from the array and edit the field. I passed the same $user/ $pw vars to the 2 Filemaker constructors and while it took awhile it did work.

Edited August 7, 2008 by Guest

[tgilders]

I think a previous issue I posted here confirms your suspicion. I had forgotten about this until now. Take a look at this previous post.

Edited August 12, 2008 by Guest

[Baloo]

Thanks.

In general I'm loving Filemaker 9, but it does look like they still have a few more things to iron out.