Note: This function is only available in FileMaker Pro 11 and above.

This “extended” version of CNSQuery_ExecuteSQL allows you to optionally specify a separate, open Database File from the current Database File. The third parameter allows you to specify how to return the result to you, either in plain text, JSON format, or to store the results internally, which you can then retrieve the value from individual cells using the CNSQuery_GetRecordData function. Retrieving the data in this manner allows you to retrieve not just textual values, but any of the FileMaker Pro Data Types including Container data. On the other hand, if you want the results returned as text, you can optionally define the Separators that should be placed between the Fields and Records using the last two parameters. By default, CNS Query will use a comma between multiple Fields and a return between multiple Records. The Field and Record Separators you specify are not limited to a single character. Finally, this function allows you to use “SQL Parameters” (question marks in your query) and then specify those parameters starting with the sixth parameter position. This allows you to create queries that can copy Stored Container data into other fields, among other things.

ReturnAs

Specify “Text”, “JSON”, or “RecordData” to determine how you want the result returned to you. If you choose “RecordData”, this function will return a blank response when successful and you can use the CNSQuery_GetRecordData function to retrieve any data. The default is “Text”.

FieldSeparator

The Separator to use between multiple Fields when doing an SQL Select. By default, Fields will be separated with a comma.

RecordSeparator

The Separator to use between multiple Records when doing an SQL Select. By default, Records will be separated with a return.

...

Any number of extra parameters corresponding to any ?’s in your SQL query.

Example 1

Returns an empty response (meaning success), and populates the internal Record Data with every field and every record from the "MyTable" Table Occurrence in the current Database File.

CNSQuery_ExecuteSQLEx( "" ; "select * from MyTable" ; "RecordData" )

---

Example 2

Returns something like "<tr><td>Mary</td><td>Jones</td></tr>" for every record in the "People" Table Occurrence in the "Contacts" Database File.

CNSQuery_ExecuteSQLEx( "Contacts" ; "select '<tr><td>' + \"First\", \"Last\" + '</td></tr>' from People" ; "Text" ; "</td><td>" )

---

Example 3

Sets the "Photo" field in the "People" TO with the value from the "SomeContainerField" field.

CNSQuery_ExecuteSQLEx( "" ; "update People set \"Photo\"=?" ; "Text" ; "," ; "¶" ; SomeContainerField )