Script works on client, so the DSN should be defined on client. But user DSN also should be in the selection.
ESS need system DSN.
I did find this small note in an old ESS guide from FM:
One important note about the ODBC Import and Execute SQL features is that they are not able to take advantage of server side DSNs, as is the ESS feature set. DSNs defined on a remote host are only available to ESS operations. Using ODBC Import and Execute SQL still necessitates configuring a DSN on each client desktop.
I guess this is a no go. Time for a plugin.
User DSN? Explain. FM documentation only covers System DSN.
so many things needing clarity!
1. Execute SQL (space between words) is a Script Step (so no parens or parameters)
- it allows you (with an ODBC setup) to INSERT, UPDATE or DELETE data in an external source
- available in FM 6.0 and later
2. ExecuteSQL() is a Function with the parens and parameters
- using the SELECT statement can access any data that is connected on a Relationship Graph (external or internal source)
- available in FM 12.0 and later
3. PSoS or Perform Script On Server is a Script Step
- available in FM 13.0 and later
so, you are correct. Execute SQL must have DSN (data source name) set up in ODBC manager/administrator/control panel with valid ODBC driver on the client (FMPro) machine, not on FMServer.
why is this a limitation? Can the external source be used as ESS?
As the note says, the 2 steps do nothing on FMS, so client has to have DSN defined on local.
OTOH, when using ESS client does nothing on accessing ODBC server, so DSN should be defined on FMS.
Both cases DSN is defined on "local" that who accesses to ODBC server.
DSN has 3 types, user, system, file. user DSN is defined for only the user now logged on.
My error on adding the parenthesis. I am working with the script step.
I have not tried yet but I would assume if the DSN is on FMS and Execute SQL is run via PSOS it should work. This particular solution is using FM12 so that is not an option, but I will have to give it a try just to see.
Currently editing ESS records by going to layouts and setting fields. I thought it would be a good idea to skip the layouts and use Execute SQL to do the record edits, but not possible with the DSN only on FMS.