display it in show custom dialog or store the results into a table in Filemaker?
In Show Custom Dialog, you (must) specify the title and the message as a calculation; in Set Field, you (must) specify the result to set as a calculation;
and ExecuteSQL() is a function that you use in a calculation …
Is that what you wanted to know? (Also, there is the Auto-Enter option of "Calculated value” … )
Oliver (erolst) is correct. The ExecuteSQL function returns essentially a block of text, delimited as you choose when you specify the calculation. One common way to delimit the result is to use carriage returns to separate rows. Should you choose to do so, you can use the various functions dealing with return delimited lists (GetValue, LeftValues, etc.) to extract one or more values and insert them into rows in a table.
Another way is to use the Virtual List technique, which will create table records out of a return-delimited variable, from which you can import the results into a table.
Does that help?
So if I understand you correctly, I should make a calculated field with executeSQL and then refer to the field when i show custom dialog?
So I could "GetAsText" the "ExecuteSQL" statement within a calculated field on a table? Then, I could set that field equal to a variable, and then, I could parse that field with "Position", "Left", and "Right"?
You were asking two different questions, and got two different answers.
If you're asking specifically about Show Custom Dialog (SCD), be aware that when you need the result to be displayed only during the runtime of your script, there is no need to store it anywhere (or, more precisely, create a dedicated field for it; that would usually be an unstored calculation).
You can do
Set Variable [ $mySQLResult ; sqlQuery ]
Show Custom Dialog [ title: someTitle; message: $mySQLResult ]
Show Custom Dialog [ title: someTitle; message: sqlQuery ]
The actual form would depend on whether you will need the result of the query elsewhere, or if you change context after calculating, but before performing SCD, or if you want to use the Data Viewer to inspect the script while debugging etc.
No, you're making it too complicated. Just use a Set Variable script step and set the variable equal to the result of the ExecuteSQL. Then you can parse it any way you want, including using Set Field script steps to shove bits of it into fields or new records, or having it separated out automatically using the Virtual List.
erolst I tried to show custom dialog in both of those fashions. Both results returned a "?" for me. Here's my executeSQL statement: ("Select * From Bill";",";"¶"). I get no error message when running Get(LastODBCError), and 0 for (Get(LastError)), and I know my connection is good because I've tested it, and the connection is currently working in QODBC's VB Demo 32
Set Variable can also be used. Result is text.
There is a good example of ExecuteSQL and placing the resulting values into a virtual list table. This can be found in the FileMaker Training Series.
-- sent from myPhone --
Right – so you were asking about the script step Execute SQL (note the blank in the name), about which I declare myself entirely ignorant – except that, again AFAIK, this step is used to set values in a data source, not query them …?!
I'm sure Mike and Beverly can provide you with all the advice you need.
I was under the impression that up until FM 12, you could only "select" with "executeSQL"
If you mean the ExecuteSQL() function – that was only introduced with FM12, and supported (and as of v14 still supports) only the SELECT statement.
As for the Execute SQL script step: as mentioned, I couldn't tell.
The Execute SQL script step to an external SQL DB is used for the INSERT, UPDATE or DELETE actions on those external records/rows. Use the IMPORT script step to bring in the records for you query. Note you need to specify a table (create one in the step if needed). These two script steps are not "live connect" to the SQL db. Any actions are independant of what is shown in FMP and in the SQL tables.
If you wish to have "live connect" so that what you see is what you get in both, then use ESS (External SQL Sources). You place a TO (table occurence) to the SQL table on the Relationship Graph (RG). Then it appears and works with most of the FM functions and methods (find, create, delete, edit) as if a native FMP table.
I suggest you research the HELP for all of these terms.
Yes, IF the records are "IN" FMP (via import or ESS), then you can use the ExecuteSQL() FUNCTION on those records. Note: the result of this query is still a TEXT result and can be placed in a field or variable. It is NOT put into a table as with the IMPORT. You must still post-process.
Again you need to research: Execute SQL (script step), Import (script step), ESS (External SQL Sources), ExecuteSQL() - the function. Many of these have been covered well in this forum (and others) as well.