I am also having connection issues with PSOS in general. It seems that it takes some time to make the initial connection to an ESS via ODBC. I have tried a number of pauses and commits in various places with no success. Any ideas? I know FMP sometimes needs to take a few seconds when first making a connection by ODBC. I assume the same is true for each PSOS run.
What is your ESS source? SQL Server ODBC driver for SQL Server lists different versions of the server. You might select a different version, does not have to match the actual server. I am connecting to 2012 with 2005 selected, if I try anything else, I am getting occasional problems.
nicolai has good questions about the OSs and versions you are using. Most Windows ODBC drivers are free or have a set price. So I'm thinking you are using the Actual Technologies ODBC driver that has an upgrade from a 10 seat to a Server version. If so, this means you are using a Mac with OS X.
I have found that when I did this, I replaced the driver with an updated one to get the most current and I erased and recreated the DSNs. When I created them with ODBC Manager, make sure to put the license in for EACH DSN you create. Make sure you are creating them in the System DSN. And when you get to the third screen, make sure to check the "Connect to SQL Server to obtained default settings for the additional configurations" because doing so will confirm you are getting a good connection to the database. And make sure on the next page you can do a pull down of the "Change the default data base to" has selected the correct database. If it doesn't list any databases, then you don't have a good connection.
Answer some of nicolai's questions too and we can probably get you further along. But I don't think you have to restart if you recreated your DSNs.
Thanks for clarification, Taylor. This is exactly what I assumed (Mac and Actual ODBC), as Native SQL driver is free. On top of this bigtom mentioned actualjon, who, I think, represents Actual Technologies, as far as I remember (hence the name).
Resetting DNSes after a driver update is a good tip, thanks Taylor.
Nicolai: Does anyone else have an issue with the price of Actual ODBC drivers? $400 for each type of Mac OS X server ODBC connection (MS SQL Server, Open Source & Oracle). Goodness, a single type is more than the annual cost of a FileMaker server license and I have several servers that have to have multiple types on them. Since when does a support driver cost more than the software? Granted the Actual ODBC driver is a great product and works well, but sheeesh. Its just that it doesn't cost anything for FileMaker server on Windows or for most other databases like MySQL, MS SQL Server, Oracle, Postgres, etc. I understand Actual is not FileMaker and they need to make money, but whoa. I guess it is better than having a cheap solution that is unreliable, but wish it wasn't quite so much because it results in clients avoiding using it when there are some great integrations when databases can talk directly.
It has been a busy Holiday season for me. Thanks for the replies. FMS is on OSX with Actual Tech driver for MySQL. The MySQL server is LAMP. So I did reset the DSNs and restarted the ODBC Manager. Not hitting the limit of connections now. I did a 200 record edit test and no issues.
The issue I have is I had to do this on FMP. It seems PSOS has issues with making connections to MySQL with ODBC. Even with script pauses it sometimes misses. The script I run goes to a layout and that has four fields to update. Two MySQL ESSs with two fields each. Sometime it makes a connection and up dates one but not the other, sometimes both, sometimes neither. I have not been able to figure out the cause or a random solution. When I run the script in FMP it is perfect and I can run multiple windows looping the script through records with out issues. The problem is with PSOS. I am guessing that the initial connection is not happening with each PSOS instance. I tried a 5 second pause after layout load. Maybe it needs more. I never wait more than 5 seconds for data to show up or refresh in FMP. Waiting 10-15 seconds for each update is not good for the workflow. Five is acceptable.
As for solutions to the problem I might be able to pack the edit parameters via script into a found set via PSOS with a loop for the edits and then there will only ODBC connection made. Maybe I did not need the Server version of the ODBC driver.
The other solution is to make all the ESS updates via PHP API, but ODBC was faster. There are advantages to the PHP API of course, but overall speed and PHP load on the ESS server were a concern for me. If this PSOS issue cannot get sorted out I will have to go the PHP API route and will have zero need for the Actual driver at all.
As for the price, it is a bit too much. I waited to get the server version as long as possible, due to costs. If it were $250-$300 I would happier and would have bought a license for each or my two FMSs. Do not get me wrong, I can afford it without issue. I just felt it was over priced. The other thing that I did not like as a consumer and feel unhappy about is that it costs $5 more to upgrade than just purchasing a server license. Yes, it is only $5 but it just does not set well. I asked about it once and was essentially to told just deal with it. I guess they really need the $5 and do not mind the negative effect it has on customers. With the ease and benefits these days of PHP APIs being able to manipulate and fetch ESS data I would think Actual would be aware of that in their pricing.
Any ideas on the PSOS problem?
There may be a latency issue and I see you even tried pausing (of course pausing won't work with PSoS though). Can I assume that the LAMP server is across the internet and not on the LAN? I ask because if it is on the same LAN, then you really should not be having these issues. Do you have FMP on the FMS machine? Do you have the same problem as PSoS when running it on the FMP on the FMS machine? If you run it on your local FMP connected to FMS, then it is the same ODBC connection between FMS and MySQL. So it should succeed or fail just as if run on the server. The FM solution uses the ODBC of the machine where the database is hosted, which when on a server usually means it is not on the same machine as FMP.
When you use FMP and go to an ESS based layout, do you see all of the records and fields? Often when I see these types of problems, I will see a bunch of question marks come up in all of the fields instead of real data. And this often happens on servers that are hosted on poor internet connections, especially the upload speed. Say on a DSL connection, etc.
Is there any possibility of hosting the MySQL database on the same LAN as FMS? That really is the ideal situation. Or alternatively move the FMS to the LAN where the MySQL database. Either way makes the communication much more robust and reliable.
You can also test by taking a backup copy to your local FMP machine and setting up an ODBC driver there and seeing if it works any better than the server does. But all that really will tell you is if your local FMP computer network is better at handling ODBC connections.
Also, when having problems like this, I often try other applications to see if they are having the same problem or not. I will use SequelPro to directly connect from the FMS machine to the MySQL database or maybe Excel or some other spreadsheet that is on the FMS machine and can use ODBC connections. Those are just ways to narrow down if it is a FM only issue or if it is a driver issue.
And you might try a system reboot, not that it really should make a difference.
The LAMP server is over WAN and this cannot change.
I always see all the fields in the ESS layouts on FMP and FMGo. A full ESS layout with 20 fields and a portal with 50-100 or so ESS related records show without a blink in FMP and FMGo after the couple seconds delay on the first view through that window.
With FMP the first layout that loads the ESS data every morning or after restart of the app takes about 2-3 seconds to see data. After that it loads quickly. I can only assume this is because there is some connection that is being held open. Also the local FMP script is run by button on the layout with the ESS fields. So it stands to reason that this is why I am not having problems. Even when doing this operation by FMGo it is fast enough after the first layout load. The only part that takes about a second time is a Refresh Window step to force an immediate update in the ESS and FM client. Maybe I need a refresh window before doing data entry in the fields. This may force the layout to wait for the ESS connection.
Why would Pause not work with PSOS? It looks like a valid script step that can run on Server.
Running FMP client on the FMS machine works exactly the same as any other remote FMP or FMGo connection. The client scripts will execute and I will be able to see the data change live in the ESS DB within less than 2 seconds usually and this is plenty fine for me.
I threw in a refresh window step after arriving on the layout and after going back to browse mode from the find in PSOS. Seems to be a little bit better, but still hit and miss.
The fields are actually related fields in a filtered portal as the ESS DB is a simple ID, Key, and Value setup. Maybe I should be refreshing the portals or objects and not the Window.
Can multiple portals be grouped and refreshed together on one step?
Why would Pause not work with PSOS? It looks like a valid script step that can run on Server.
The Pause script step used to be incompatible with Server side scripts. I thought it still was. I found as late as FMS 13 it was not compatible (FileMaker 13 Script Step Compatibility | FileMaker). But I am happy to see it finally is compatible in 14. Maybe that was one of the questions I missed on the FM certification exam <grin>.
Yeah, it is compatible in 14. Either way it is not helping.
As a test, can you ditch the filtered portal and put PSoS on the context of the portal's records (TO) and perform the find there to get the proper found set?
Since you were asking about multiple portals it sounds like you are taking the workflow to a busy layout that will take to render and update. I would cut down on that complexity and use multiple layouts for the different subtasks.
I will look into that idea day after tomorrow and let you know how it goes. I'm off tomorrow.
I want to say the relationship helps. If I do a find for a matching ID and Key field it takes a while to search all 200,000 or so records in the ESS table. With the filtered portals it was faster I think.
The layout has the field for the relationship to the ESS tables and 1 field in each of 4 portals wth one row only. The fields are changed form 0 to 1 or vice versa. Five fields total. I will get this figured out eventually.