SM SQL statements in FM11 vs. FM12 built-in SQL statements

[Mr. Scott]

I'd like to know how to properly construct SQL statements using ScriptMaster 4.x, because I cannot seem to get even the simplest SQL statements written in 12 to work using the plugin.

For example, I'd like to implement the MagicValueList featured on filemakerhacks.com, of which a sample file was provided. The first example in that file is where a user chooses a state from a drop-down list made possible by a custom function, and a script trigger for an empty script that uses the custom function wrapped around a FM12 SQL statement:

MVL_Dropdown ( ExecuteSQL ( "SELECT state FROM zipcode" ; "" ; "" ) )

The next drop-down of cities is made possible with this statement that includes an argument based on the "state" value:

MVL_Dropdown ( ExecuteSQL ( "SELECT city FROM zipcode WHERE state = ?" ; "" ; "" ; settings::gInput ) )

I get this error in either, highlighting the first semicolon after the quoted SELECT statement:

"There are too many parameters in this function."

How would I have to write my statements for the ExecuteSQL from ScriptMaster to work in FM11? I'd like to know that the SQL I write using the SM plug-in would be compatible with FM12's built-in SQL, as well as any other SQL plug-in in FM or FM12. Any syntax tips would be appreciated.

- - Scott

[dansmith65]

SM's SQL doesn't take additional arguments, which substitute ? with the specified value, so you'd have to write it like this:

MVL_Dropdown ( ExecuteSQL ( "SELECT city FROM zipcode WHERE state = '" & settings::gInput & "'" ; "" ; "" ) )

[Mr. Scott]

Hi, Dan:

I still had an error, but I was inspired by your response and found that this worked:

MVL_Dropdown ( ExecuteSQL ( "SELECT city FROM zipcode WHERE state = '" & settings::gInput & "'" ) )

The FMP12 file had a programmatic error in listing the zip codes (did not combine query of city and state to display zip, which led to zip codes for cities like "Portland" being shown for both Maine and Oregon. I set up this calc for that:

MVL_Dropdown ( ExecuteSQL ( "SELECT zip FROM zipcode WHERE state = '" & settings::gInput & "' AND city = '" & settings::gInput[2] & "'" ) )

When I get the file done, I will ask the original author(s) for permission to post it. Thanks for taking the time to read and respond to my post, Dan.

SM's SQL doesn't take additional arguments, which substitute ? with the specified value, so you'd have to write it like this:

MVL_Dropdown ( ExecuteSQL ( "SELECT city FROM zipcode WHERE state = '" & settings::gInput & "'" ; "" ; "" ) )

[Mr. Scott]

What about the LIKE operator in SM's ExecuteSQL?

Here is the FileMaker 12 version:

MVL_Dropdown ( ExecuteSQL ( "SELECT dictvalues from dictionary WHERE dictvalues LIKE ?" ; "" ; "" ; "%" & settings::gInput[4] & "%" ) )

Here is what I've put into FM 11 with SM 4.132, but it doesn't work:

MVL_Dropdown ( ExecuteSQL ( "SELECT dictvalues FROM dictionary WHERE dictvalues LIKE '% & gInput[4] & %'" ) )

[wbasham]

I've tested the LIKE keyword and it works correctly. It may be a syntax error where there may be a couple missing double quotes in the example you gave.

[Mr. Scott]

Thanks, Brent!

Just knowing I am doing it wrong helps. Here's what I did to make it work:

MVL_Dropdown ( ExecuteSQL ( "SELECT dictvalues FROM dictionary WHERE dictvalues LIKE '%" & settings::gInput[4] & "%'" ) )

I've tested the LIKE keyword and it works correctly. It may be a syntax error where there may be a couple missing double quotes in the example you gave.

[Mr. Scott]

OK, Dan and Brent (and anyone else that can jump in)…

Here's a SELECT statement with a "NOT IN" operator that works with FM12 built-in SQL, but as usual — the statement does not work by default in FM11 with SM SQL:

. I have tried both of the following, which produce an "ERROR":

MVL_Dropdown ( ExecuteSQL ( "

SELECT firstName from people WHERE firstName NOT IN ( SELECT person FROM assignedtasks WHERE task = ?)" ; "" ; "" ; settings::gInput[7] ) )

I have discovered that arguments and the pair of separators does not work in SM SQL (= ?)" ; "" ; "" 

MVL_Dropdown ( ExecuteSQL ( "

SELECT firstName from people WHERE firstName NOT IN ( SELECT person FROM assignedtasks WHERE task = '"& settings::gInput[7] & "'" ) )

MVL_Dropdown ( ExecuteSQL ( "

SELECT firstName from people WHERE firstName NOT IN ( SELECT person FROM assignedtasks WHERE task = ' & settings::gInput[7] &'" ) )

[David Wikström]

I think you're not closing the parenthesis around the second SELECT properly. You need an end parenthesis within the literal string (after the apostrophe).

For debugging purposes, it is often practical to define the SQL statement to a variable and reference that, rather than just run the ExecuteSQL directly on the formula.

[Mr. Scott]

Hi, David:

Thanks for taking the time to read and respond to my post. As a "visual" person, I'm having a hard time grasping your debugging process using a variable. In the statement above, I am asking it to show only those records that do not have a task assigned using the task value in another GLOBAL repeating field (repetition 7). Should I set a variable based on the selected value in gInput[7]? It would be great to see what you describe in the context of this statement.

I think you're not closing the parenthesis around the second SELECT properly. You need an end parenthesis within the literal string (after the apostrophe).

For debugging purposes, it is often practical to define the SQL statement to a variable and reference that, rather than just run the ExecuteSQL directly on the formula.

[Mr. Scott]

I have almost replicated the FileMaker 12 version of MagicValueLists example file produced by Andries Heylen of BH&A (http://www.filemakerhacks.com/?p=5412) in FileMaker 11 with the ScriptMaster ExecuteSQL function.

The checkbox section is setup using conditional formatting. The original statement that works using FM 12's built-in ExecuteSQL is:

MVL_CheckBox_2 ( ExecuteSQL ( "SELECT FieldName + ' [' + FieldType + ']' FROM FileMaker_Fields WHERE TableName = ?" ; "" ; "" ; settings::gInput[11] ) )

The statement I have "massaged" (that does not work like the FM 12 version) is here:

MVL_CheckBox_2 ( ExecuteSQL ( "SELECT FieldName + ' [' + FieldType + ']' FROM FileMaker_Fields WHERE TableName = ' & gInput[11] &'" ) )

Any suggestions?

[Mr. Scott]

I have successfully replicated most of the MagicValueList file created by Andries Heylen from BH&A using a plug-in created by ScriptMaster 4.132 in FileMaker Pro Advanced 11.v04.

The exception ONLY is:

1). Create value list for missing records, second field (gInput[8])

Here is the working statement from FM 12's built-in ExecuteSQL:

MVL_Dropdown ( ExecuteSQL ( "

SELECT firstName from people WHERE firstName NOT IN ( SELECT person FROM assignedtasks WHERE task = ?)" ; "" ; "" ;  settings::gInput[7] ) )

I got this to display data rather than an error, but it is showing me all the person first names, not the ones that don't have records in the assigned tasks table:

MVL_Dropdown ( ExecuteSQL ( "

SELECT firstName from people WHERE firstName NOT IN ( SELECT person FROM assignedtasks WHERE task = 'settings::gInput[7]' ) " ) )

[Mr. Scott]

I got it, i got it! Using a GLOBAL $$variable:

MVL_Dropdown ( ExecuteSQL ( "

SELECT firstName from people WHERE firstName NOT IN ( SELECT person FROM assignedtasks WHERE task = '" & $$gFilter & "' ) " ) )

I'll see if I can make it work with a local $variable. Thanks, for the tip, David!

I think you're not closing the parenthesis around the second SELECT properly. You need an end parenthesis within the literal string (after the apostrophe).

For debugging purposes, it is often practical to define the SQL statement to a variable and reference that, rather than just run the ExecuteSQL directly on the formula.

[john renfrew]

Mr Scott

What about using this?

MVL_Dropdown ( ExecuteSQL ( "

SELECT firstName from people WHERE firstName NOT IN ( SELECT person FROM assignedtasks WHERE task = '" & fmpro.merge('<<settings::gInput[7]>>') & "' ) " ) )

John

[Mr. Scott]

Hi, John:

I will check that out for sure and let you know. What is "fmpro.merge"? Thanks for the response!

- - Scott

[dansmith65]

Mr. Scott, I think you miss-interpreted what David was referring to. Try setting your SQL statement to a variable, then use that variable in the ExecuteSQL() function...

Set Variable [ $sql ; "SELECT FieldName + ' [' + FieldType + ']' FROM FileMaker_Fields WHERE TableName = ' & gInput[11] &'" ]

Show Custom Dialog [ $sql ]

Set Variable [ $result ; MVL_CheckBox_2 ( ExecuteSQL ( $sql ) ) ]

Doing this will allow you to see the SQL statement you have constructed - which will make it easier to debug.

[Mr. Scott]

Cool, Dan! I will definitely check that out.

Thanks for posting a reply,

- - Scott

[john renfrew]

If you look in the Acessing the Calculation Engine demo in the scriptmaster file you will see that fmpro. is the Filemaker object and the merge method will grab any field from FileMaker

so fmpro.merge('<<table::field>>') will grab that data into the current script as if it were a merge field on a layout

there is also fmpro.evaluate('Get(CurrentDate)') for example will also use FM functions inside your Groovy code...

The documentation, while not totally brilliant does cover these things.

[Mr. Scott]

Very helpful, John. Thanks for replying.

- - Scott

[Mr. Scott]

SQL performance in FM11:

Sounds like a great idea to not have to worry about TO's for special data and display uses, but if you work with large data sets and multiple related tables, it's still easier to use the method of finding records.

MVL was a 2-minute DOG over the WAN, where using find and a new dedicated table occurrence was almost instantaneous.

I am sticking with my "old ways" for now…and will retry in FM12. I will still be "testing" SQL for where it can apply.

- - Scott

[dansmith65]

I don't think SQL's speed has improved in FM12. I have also found that "old ways" are faster: http://fmforums.com/forum/topic/85844-only-show-5-highest-values/#entry394956

[Claus Lavendt]

SQL speed HAS improved in FM12, when you have a client/server setup, as the sql request will be sent to the server, which process the query, and return only the result.

In FM11, the server sent the data to the client for processing the sql query on the client.

So there has been a big speed improvement in FM12. This is also true for many other things. The server will process much more and only deliver the result to the client.

The sql (or odbc engine) has not, as such, had speed improvements, so local queries to a local datafile is not significantly faster.