FM9 Server can't execute SQL?

[kliao]

I have an FM9 solution hosted with FileMaker Server 9 Advanced on a Windows Server 2003 platform. As such, I set up an ODBC connection on both my dev box and server box to an internal Oracle database. The Oracle table I wish to connect to contains over 700K records, so ESS is very slow. I'd like to execute SQL queries instead.

The SQL queries I wrote on my dev box run correctly. When I connect to the server version of the FM database, the server's ODBC connection is not available, so the queries don't run at all.

Can FM9 Server not use the server box's ODBC setup? I'd rather not set up individual ODBC connections for the hundred or so users.

TIA

ken

[corn]

ExecuteSQL is a client-based script step. Predating ESS by seven versions (it was introduced in FMP 4.1) it does not leverage the ODBC connection configured for ESS or the tables included in your database via ESS.

[kliao]

Corn, thanks for the reply.

However, I want to use ExecuteSQL, leveraging the server's ODBC connection, instead of using ESS. Unless I missed something completely, the current state of FM9Server can't let me do this?

[corn]

I'm sorry if I wasn't clear in my response.

The ODBC configuration available to FileMaker Server is a function of ESS. It did not exist before FMS 9.0's support of ESS whereas the ExecuteSQL function predates ESS by several releases. FMI did not change ExecuteSQL to leverage the ODBC connection on server when they created the ESS feature.

To be clear, the FMS ODBC connection is strictly a part of ESS and functions strictly at the datasource level (i.e. tables and fields). It does not provide the capability to perform ad hoc SQL queries.

The ExecuteSQL script step, in contrast, executes on the client side (as do all scripts initiated by the client ) and therefore can only leverage the local ODBC connection.

[kliao]

I was afraid of that. Thanks, Corn.