1 of 1 people found this helpful
I'm running the ExecuteSQL calculation on an external ODBC data source. I'm not using the script step, perhaps I should be.
Correct. The new ExecuteSQL function is for internal FM querying. The "Execute SQL" script step is the one to use to query other, external databases running on MySQL, MSSQL, etc., usin an ODBC connection.
Ok, I do have table occurances of the external tables in the database, that was why I was supprised they did not work as I thought this had worked in the past. Thanks for your help Doug.
FileMaker Inc. should look into the hanging issue though. If the calc function can't reference external tables, there should be more documentation regarding this.
I've been told by at least two FMI service engineers that the new ExecuteSQL() should be able to work on data sources other than an FMPro database. They said that as long as you have a TO on your relationship graph for the external file (which implies an item under File>Manage>External Data Sources, which further implies an entry in your ODBC manager setup to create the DSN for the data object), then you should be able to perform queries with this step. I'm think they even demonstrated it to a combined MIT/Harvard/Boston Area Developers group a couple of weeks ago.
Try a couple of simpler tests (even though your calculation looks very, very simple). Maybe substitute a hard-coded student ID for a field value. Also check that your DSN has a relatively short timeout. Maybe specify your cell & row delimiters. (I recall hearing that results from external data sources across platforms may not play well inside FileMaker.) Maybe contact FMI support?
I haven't tested this myself, but my local engineers certainly have.
-- Drew Tenenholz
Thanks very much for that Drew. I've tried many different options, I started off with the file served from FMSA, moved it to local, tried hard coding things (as I was doing much of the testing in the data viewer, though I originally noticed it when a script hung), separate files, only thing I haven't tried yet is reinstalling FMA. That might have to be my next step.
I've made the mistake of rushing off to get FMIs help before, I'll do some more testing before anything else, maybe I'll notice something along the way.
Thanks again for looking into this, in the end I may have to send the file off to FMI.
James, I wonder if your query is correct?
It appears that the EXTERNAL SQL SOURCE (table) is "External_STUDENTS" (the FROM clause). If so, why are you using the same table to supply the "?". Can you give more information on the tables (local and/or external) that you are using and where are the fields you are trying to match?
The FileMaker 12 ODBC/JDBC Guide on ExecuteSQL (the function) does, indeed, say it works on FileMaker and on ESS sources.
And the Help says that the function: "Executes an SQL query statement for the specified table occurrence within a FileMaker Pro database."
The 'table occurrence' (the name of the "table alias", if you will) on the Relationship Graph is the name that should be used in the function.
WHERE STU_ID = ?";
""; ""; External_STUDENTS::STU_ID)
External_STUDENTS is the fictional name I gave the actual table occurance for this. It was a simple statement I was testing with to ensure there weren't issues with my SQL that were crashing the program. I was supplying the same table because I was using a layout with that table backend to select a student from and have it return the whole row. It was a test as the argument in the original attempt was a variable as part of a set field script step.
The SQL tables hold student and subject data with about 200,000 rows at least together, 10,000 or so in one, many more in students. About 20 columns each. The field I'm matching in this case is a primary key for the student table. The local tables for the most part mimic the SQL and going to be used to create a copy of the SQL with additional calculations.
Below is an exact copy of something I just tested with. This statement runs with no problems in MSSQL where the table names are changed to those in the SQL DB. In FM though the table occurances exist as written and FM hangs indefinately.
FROM ISIS_SPack SP
JOIN ISIS_SPackAvails SPA on SP.SPK_NO = SPA.SPK_NO
WHERE SPA.Avail_YR = '2012';
For both of these examples though, I'm only interested in the tables specified, there's nothing else in play. Much of the testing now I'm doing through the Data Viewer.
How many columns in the tables? Have you tested with specific columns instead of "*"? There may be a limit.
-- sent from my iPhone4 --
I have tried testing with specific columns yes. Unfortunately it had the same effect. Below is one of the specific column tests I have performed. This should return a single name, 'Taylor'. Unfortunately, it still hangs the program.
WHERE STU_ID = '295591'";
I've been in contact with FMI, I'm awaiting to hear back from some of their Systems Engineers regarding the issue. Happy to keep exploring though.
Thanks for your help Beverly, I understand you're one of the most experienced in FM and ESS.
Sorry for the erroneous information in my earlier reply to you about the ExecuteSQL function vs. the Execute SQL script step. I learned something as well. That's one of the cool things about this forum, incorrect information will usually get corrected by someone pretty quick.
Out of curiosity, have you tried your queries using a 3rd party SQL plugin? I know that this is not the answer to your issue, but it would be interesting to know the result. If you don't already have a plugin, there are a couple of them that are free.
The SQL Runner plugin is SQL only and is available at:
The BaseElements plugin has a SQL function (among many others), and is available at:
The new SQL engine is picky about data types. Could it be that the datatype of STU_ID is not a string?
Yes! Peter has a gold-plated Ni...
WHERE STU_ID = '295591'
does imply that it's a STRING rather than INTEGER.
What about testing without the single quotes? But you've probably testing directly in MS SQL, right James?
"I've been in contact with FMI, I'm awaiting to hear back from some of their Systems Engineers regarding the issue. Happy to keep exploring though."
Good to hear they are on the case!
"Thanks for your help Beverly, I understand you're one of the most experienced in FM and ESS."
LOL. I just get to play in FileMaker, MS SQL & MySQL dbs nearly everyday. ;D
Yes I've tried both. In MSSQL Server Management Studio, it doesn't matter. In FM is I remove the quotes it doesn't crash FM but is doesn't work either, ie. shows '?'. I should say, the SQL server is feed built from the production environment. I'm saying this because I believe every field is typed as a string for the feed.
I haven't tried with a plugin, I will look into these today and let you know. For our environment, unless there is no other option it would be preferable not to use one. But I will try, thanks Doug.