1. 100% Filemaker
a. using plug-in? if so which one(s)?
b. using web publishing? if so with what web app and is it using ODBC?
c. using Filemaker as ODBC source to ??
2. INSERT wouldn't use WHERE (unless with a select to get the values)
INSERT INTO tableName (
) VALUES (
INSERT INTO tableName (
) SELECT col4, col5, col6 FROM tableName2 WHERE ...
3. are you looking or the Filemaker/xDBC equivalent of SQL's (which would follow either above INSERT:
4. if it's just FileMaker do you have access to the key field?
5. why are you trying to use SQL when FileMaker functions and script might do better?
My issue is understanding which SQL functions that FileMaker supports and/or the syntax
I believe this is included in the FileMaker ODBC and JDBC Guide installed with the application.
Please read the post before replying. The post clearly stated that I have been using a SQL plug. Actually, I've used about six plug-ins because they have the same issues as the underlying issue is FileMaker's handling of SQL. I have read the odbc/jdbc guide but it says little about the optional SQL parameters which FileMakermay or may not support.
I an very interested in real feedback, but just quoting the manual about how to form a SQL is not the issue...
I've read your initial post a couple of times, and am still uncertain about your phrase "100% FileMaker...". Does that mean you want to use the new ExecuteSQL function instead of a plug-in?
If so, the new FM function only supports the SQL "SELECT" statements. Other interactivity via SQL still requires a plug-in. This limits the new function's use pretty much to outputting calculation results, not data creation or modification, except where that is done via traditional FM steps using the ExectureSQL calculated result.
I don't know if there's some actual FileMaker SQL answer to your "WITH RESULT", but you might try inserting a temporary key that you can use to SELECT the ID from the record after INSERTing it.
Set Variable [$temp = Get ( UUID )]
INSERT INTO table(temp) VALUES ($temp);
SELECT id FROM table WHERE temp = $temp;
UPDATE table SET temp = NULL WHERE temp = $temp
100% FileMaker, meaning that I am not using ESS and/or any other SQL source other than FileMaker. This method works great and does a wonderful job at reducing the cluster in my graph, but in order to determine the key, I use the MAX function which is very expensive. It seems stupid for FileMaker I run through every value in the ID field each time it creates a record.
I have considered using UUIDs, which would resolve the problem. On one solution in particular they track physical assets with the ID values which have been serilazed with an auto-enter value, but at least the UUID gives me the abilibity to poll the database for that number.
I wasn't found of using UUIDs but as a temp value, I think this is the best option. I will certain tweak my code and see what happens.
You said you “use the max function”. What about using an “order by” creation date desc clause. The first record should then be the last one created. Would this not be less “expensive”?
I realize that means a second statement, SQL query. But with the tools available, it's an alternative.
Just a thought,
Message was edited by: timwhisenant
If you are using the Max function, you are using a relationship, yes? Why not just use the Last () function? It's faster and only needs to look at one record.
Please read the post before replying. The post clearly stated that I have been using a SQL plug.
AFAIK, all the SQL plugins work within the SQL statements and constructs that Filemaker supports - and these are listed in Chapter 7 of the guide I mentioned. There's no need to be rude.
Please read the post before replying. I an very interested in real feedback, but just quoting the manual about how to form a SQL is not the issue...
Well here is some REAL FEEDBACK for you, Joe. As a professional developer, I recommend services to my clients all the time. You have just come OFF my list of recommendations. You are a real gem. NOT.
No relationship, using MAX via the SQL. The concept in this method is to be 'context free' and reduce the total number of TOs in my graph..
I'm not sure why you are offended. I asked a question and clearly stated that I was using a plug-in to access FileMaker via SQL, and then Beverly starts asking me if I'm using web publishing or ODBC and then she simply prints the Knowlegebase text about forming SQL statements. I have been using SQL in FileMaker for the past six years and the question had nothing to do with the formation of SQL statements. Beverly responds to every post I submit and has yet to ever answer the question which was asked.
The one time I mentioned my client's name, she called them directly and took work from them, so if you want to talk about a 'gem' I think your anger is misplaced. Most of the people on this board are interested in improving the quality of their code and we all know that there are many techniques which are not covered in the FileMaker documentation is an insult to those of us who do this for a living to say 'go read the manual'.
jbante clearly understood my question and made a good suggestion which was clearly not mentioned in the FileMaker manual. I have also had conversations with two other FileMaker developers which have struggled with this same issue. I value the input of those wh are truly interested in any topic, but those who just post without adding any input and/or asking realted questions devalue this service. When the same developer is calling my clients, I have even less patience.
Sorry to offend... the frustration was not aimed at you...
I tried that, but SQL is still having to 'evaluate' the entire table and on a 100,000 record table it takes about three seconds to get a result on a local Gigabit network. Since this script is used to create new records, three seconds is a lifetime.
Apparently, mySQL, msSQL, and Oracle all support additional SQL arguments which can return the result of any column, which is how many PHP solution handle this same issue of 'knowing' the value of the 'ID' field.
I realize that FileMaker has very limited support for SQL, but now that more users are using SQL (with the native SQL functionality), we can only hope that FileMaker will add support for INSERT, UPDATE, as well as additional arguements within these statements.