Scripting in FM10 Server Advanced

[rizlaalzir]

Hi Everyone, I have just upgraded to FMS10ADV as the promise of being able to script imports is an ideal way of running batches overnight for me.

Unfortunately as hard as I try I cannot for the life of me get FMS to export OR import from the scheduler.

I have run the scripts from the same machine using a client and it works. What am I doing wrong? I have had two different errors with two different import and export scripts, 100 and 800

Any ideas?

[JesseSFR]

What os is running on your server and on the computer where the script works? Is the export going to a network resource? Can you upload a sample file for us to look at?

[rizlaalzir]

Hi there thanks for your response. I am trying to run the script from the FM10 Server Advanced (as per one of the new features in FMS10) The machine is a 2003 windows server. The script is a very basic one, but it seems as though the script running facility does not seem to be working properly, as it states OK when the script has run, but on checking the log file one of the two errors (100(cannot find file)) or 800 (file locked or disc full) has been shown in the log. I have tried the self same script running form a client on the same machine and it works just fine.

[mr_vodka]

Most likely your import script is pointing to a local directory path where advanced is running from. Use a network path instead.

[rizlaalzir]

Hi there, can you suggest an import and export script that ensures the path is dynamic. I cannot for the life of me activate the export and import script from scheduler.

Can anyone supply me with a foolproof, dynamic script suggestion which works please?

The server is FMS10 ADV on Windows 2003 and I am using both Mac and Windows clients.

[mr_vodka]

What is your path to the files that you are trying to import and export to?

[Wim Decorte]

The path can't be dynamic when using the import feature on the server. A server-side script can only import from 2 folders: the FMS documents folder and the FMS temp path. The temp path gets generated for each script session so it will vary from schedule to schedule. You only option here is to put the file in the FMS documents folder and import from there.

[Steven H. Blackwell]

[color:red]

Pay particular attention to what Wim said.

Steven

[LaRetta]

Hi Wim! Hi Steven!

So this means we can't use 10's server-side script to import SQL query (which resides on another server)?

L

[Steven H. Blackwell]

Pretty much no, you cannot do that from a server side run script to import data. You might be able to use a scripted Set Field to do this. But I have not tested that assumption.

Steven

[genevieve charbon]

WTF :

FMI Inc is just laughing at our faces !

That's totally stupid, taht feature has no sense.

Even better that's blattant false advertsising :

"Schedule import and export of records for automated reporting – no more robots required!"

Straight from their webpage !

[Wim Decorte]

Like Steven, my first reaction was "No". But looking at scriptMaker with the "server compatible" script steps showing it does not indicate that it wouldn't work. I still kinda doubt that it would work though and I can't test right now. You'd have to host the file on FMS10 first and then see if it shows the Server's DSNs (which I doubt).

If this is a fixed SQL query you could set that up on the SQL database end and use ESS I think.

[The Software Man]

I am running FMPS 10 Advanced on a Mac XServer running OSX Leopard.

I am hosting a daily details file that is populated with pertinent daily activity. At midnight I would like to import this data into a past details file that contains the accumulated daily details for reports etc.

Both of these files are hosted on the same server and reside in the same ../data/databases folder.

And a script with a two steps:

Show All Records

Import Records [no dialogs; Details]

However the script step fails with error 100.

I've tried to change the reference path to file:details; fmnet:/[serverip]/details; fmnet:/127.0.0.1/details; fmnet:/*/details; filemac:/[...]/data/databases/details [substituting the appropriate prefixes]

Still doesn't ever work. I have not been able to find any FileMaker Inc documentation on this new feature.

So, I figured I could push the data with a SetField script looping through all the records in the daily details file. I ran into another bug/anomaly.

find all records

goto record [first]

loop

setfield[pastdetails::uniqueid, dailydetails::uniqueid]

...

goto record [next, exit after last]

end loop

this script fails on the last "goto record [next, exit]" with and error 101

a work around (as mention in this forum) was to insert

exit loop if[Get(RecordNumber) = Get(FoundCount)]

These are serious problems. First, FileMaker made a big splash releasing version 10 with import features. Second, the goto record bug seems to have been around since version 9 as of spring 2008.

I've been a FileMaker developer since version 1.0 in 1986 and find all this very misleading.

I hope that someone reads this and corrects me because I am as disappointed in these limitations as finding out that version 6 only had a 2gb file limit!

Edited February 25, 2009 by Guest

[Wim Decorte]

I am running FMPS 10 Advanced on a Mac XServer running OSX Leopard.

I am hosting a daily details file that is populated with pertinent daily activity. At midnight I would like to import this data into a past details file that contains the accumulated daily details for reports etc.

Both of these files are hosted on the same server and reside in the same ../data/databases folder.

And a script with a two steps:

Show All Records

Import Records [no dialogs; Details]

However the script step fails with error 100.

That's a published limitation: you can not import on the server from an FP7 source. The easy way around it is to export to any other format and import again. Very fast too. And if you use the temporary path, FMS cleans up after itself even so you don't have to worry about the intermediate file.

Annoying limitation: yeah, slightly. But that's a personal opinion and YMMV.

So, I figured I could push the data with a SetField script looping through all the records in the daily details file. I ran into another bug/anomaly.

find all records

goto record [first]

loop

setfield[pastdetails::uniqueid, dailydetails::uniqueid]

...

goto record [next, exit after last]

end loop

this script fails on the last "goto record [next, exit]" with and error 101

a work around (as mention in this forum) was to insert

exit loop if[Get(RecordNumber) = Get(FoundCount)]

These are serious problems. First, FileMaker made a big splash releasing version 10 with import features. Second, the goto record bug seems to have been around since version 9 as of spring 2008.

I've been a FileMaker developer since version 1.0 in 1986 and find all this very misleading.

Not misleading and not bugs either, the information has been out there on both topics for a long time now. I can understand that it drives you nuts when you're in the middle of a coding crunch though and things don't work like you expect them too.

Why is the Exit Loop After Next biting you? Were you using "Set Error Capture On" in your script?

[LaRetta]

Hi Wim! Hi Steven!

So this means we can't use 10's server-side script to import SQL query (which resides on another server)?

L

Like Steven, my first reaction was "No". But looking at scriptMaker with the "server compatible" script steps showing it does not indicate that it wouldn't work. I still kinda doubt that it would work though and I can't test right now.

Wow! We CAN import this way! And it is fast too! ROCKIN!!

[Wim Decorte]

Thanks for testing this for us, L.

I've found that most scripts when run server-side are extremely fast...

Working on a solution to let users trigger server schedules :

[Steven H. Blackwell]

Import SQL appears to work. Using ESS may not work. There is some confusion still about this. I am getting conflicting guidance from different parts of FMI on this.

Steven

[mr_vodka]

My understanding to this confusion Steven is that while ODBC imports works, ESS imports will not because you have to reference a FileMaker file which uses the ESS. Since FileMaker files can not be used in the server side import, therefore, you can not access the ESS to import.

[LaRetta]

I agree, John. And I tested ESS referenced in the same file. It even indicates, when you create the import script step, that it is NOT COMPATIBLE as server script. If the entire script line isn't server compatible, it greys the line out and if only part of the script step isn't compatible, it lists NOT COMPATIBLE next to the line. Import/export can be ran server side but fp7 file can't be used.

I ran it anyway and I get script error 100 (file is missing). Of course the file is NOT missing ... it's referencing itself for the import, but I guess it can't see itself. :wink2:

[LaRetta]

I was really hoping they would eliminate the 101 error (which isn't a true server-side script error). It logs on every Set Field[] and every Go To Record/Request/page[]. It makes the log huge. I do not feel that we should have to add a test to 'fool' FM every time we use these commands.

FM was smart enough to know to split server steps from web compatible and client ... why can't their logs also tell the difference? Server-side scripting is great but having smaller (readable) logs is also important. And it produces 'script error' in Admin as well but the process works and writes just fine.

[LaRetta]

Working on a solution to let users trigger server schedules :

I almost missed this, Wim! Server-side scripts, as you've said, are a lot faster! If some of our heavier scripted processes can be moved to server side, it would be quite cool! Of course we'll need to remove non-server steps but, many times, that would still be a good trade off!!

I wonder if the speed would remain as fast if the process is normally ran client side? I envision a client-side process being ran from server and having to return to client to process it. I wish I understood the distribution of load and packet transfer a lot better! But I will run tests as soon as I get the chance. :smile2:

[LaRetta]

Well, FM isn't always so forgiving ... I just ran a script server side which had an import from an external fp7 file - but I imported from the table occurrence within this same file. FM may catch it if one is referencing an ESS file internally but it doesn't recognize a standard fp7 file!! I had searched the script for any grey'd out lines and any lines with NOT COMPATIBLE and there weren't any.

But this import will not work. Worse still ... admin console has hung up in the running state on this schedule. And even if I disable the script, it will not stop. How can I stop the script without unserving ALL my files? This happened in FMS9 as well and I couldn't figure out a way of ending the single hung script. I can't use 'User Abort' LOL!!

I'm okay - I'm working on separate FM server and separate data files (all with test data). But I'd like to know for future in case it happens again. And I'm saddened that FM doesn't recognize that I'm referencing same file to grab a different file and doesn't provide NOT COMPATIBLE to help me out. So in truth, I'm glad this happened but now what? :qwery:

BTW, even disconnecting server as a client won't stop the script from running (I know in FMS9 it didn't ever stop a running script) ... but wait, maybe it finally does. Admin console is now displaying 'aborted by user'. I had also added Allow User Abort [ on ] to the script and hit [esc] but I doubted that it did any good since I would think 'user abort' is client BUT ... it shows Allow User Abort [ ON ] is available in server script. How could THAT be possible?

I've had four posts in a row ... I feel like I'm over-talking (not something I normally do ROFLMAO!!).

Edited February 27, 2009 by Guest

[Steven H. Blackwell]

John:

I think that is essentially correct. Thanks for adding this clarification. I think a Tech Info may need to be coming.

Steven

[WestCoaster]

Hi there,

it's been a while since the last post to this thread so I hope you are still watching.

LaRetta, can you tell me how you set up the import from an ODBC source? I am stuck with this.

What I have/did: FM Server Adv 10 running on a Windows Server 2003. I set up an ODBC DSN to a mySQL database. The DSN is specified as 'user DSN'. I then set up a FM script that would import records from the mySQL table into a FM table. When I use this from within FM client (FM 10 Adv client is installed on the same machine where FM Server is running, I open the FM database in FM client through FM server) everything gets imported the way it's supposed to be. When I let this script run as a scheduled script from within FM Server nothing gets imported. The server log reports a scripting error 802.

What am I missing? Is this because I set up the DSN to be a user DSN? When I set it up as a system DSN though FM doesn't show the DSN in the import source dialog.

Any help greatly appreciated.

Regards,

Thomas

[Steven H. Blackwell]

There is some conflicting guidance still on this from FMI. But IIRC, the server must use a system DSN. BTW this does not work apparently with ESS. But it does work with a straight ODBC import.

I think I am going to make an inquiry of FMI on this.

Steven

[WestCoaster]

Hi,

thanks for your response. What is a ESS? I am from Germany so excuse me for not being aware of some of the abbreviations.

As for the system DSN: I set it up that way but only user DSNs seem to show up ODBC sources in the FileMaker import options, no system DSNs. What do I need to change?

Regards,

Thomas

[LaRetta]

Hi Thomas, as Steven indicates, it must be System DSN. If the import isn't scripted, you would select File > Import and specify ODBC source there, otherwise you would set up script. Within script, also be sure to specify ODBC source. The query will open and you would specify as SELECT * FROM "YOURDATABASE". It will then bring up the mapping dialog for you.

I'm unsure why using System DSN didn't work for you. I'd check 1) to be sure you specified to use ODBC and 2) If you use a FM calculation, you must handle it a bit differently (ie, if you wish to use a dynamic value which resides within FileMaker, such as restricting to specific record numbers and fields), then you can create a global calculation such as:

SELECT "Field1", "Field2" FROM "YOURDATABASE" WHERE "YOURDATABASE"."RECORD NUMBER" > 174331

Note: If by script using calculation, you must first map manually within the script and THEN re-point to your calculation instead of the SQL query. For some reason (and I disagree with FM on how they handle this), you cannot map if you have specified using a calculation. And if you ever wish to re-map, you must change back to manual query, map your import then change back to calculation again.

Did this answer your question? If not, please let us know.

[WestCoaster]

Hi LaRetta,

thanks for your answer. I just started this as a private topic because I was unsure whether you were stopping by in this forum and read my question. So please ignore the private topic.

I just checked again, the system DSN doesn't show up in the import via ODBC options in FileMaker. Can you tell me what ODBC driver you use? I have the MySQL ODBC 5.1 driver, version 5.01.05.00 (MYODBC5.dll). This is as far as I can see a driver specifically for 64-bit machines. Maybe I should downgrade to version 3.51?

Note: If by script using calculation, you must first map manually within the script and THEN re-point to your calculation instead of the SQL query. For some reason (and I disagree with FM on how they handle this), you cannot map if you have specified using a calculation. And if you ever wish to re-map, you must change back to manual query, map your import then change back to calculation again.

I guess this is due to the fact that for the mapping to be shown FileMaker first needs at least one specific record from the source to display so it can determine which fields are actually present in the source. With a calculated SQL query FileMaker can't get a specific record beforehand.

Regards,

Thomas

[mr_vodka]

We use the MySQL ODBC 5.1 driver, version 5.01.05.00 (MYODBC5.dll) and it works fine. As the previous posts stated, you must use a System DSN when setting up your driver in the ODBC sources. I am unsure of why you are having difficulty seeing the source within FileMaker when its a system DSN. Usually if one has setup the ODBC source as a "User DSN" they will not see it in FileMaker. It seems to be backwards in your case.

Perhaps you should try and reinstall the driver.

[genevieve charbon]

Working on a solution to let users trigger server schedules

Hi Wim, did you succeeed ? how ?