Passing table parameters
to queries run on query engines other than Manifold will not work.
The EXECUTE and the EXECUTE WITH statements allows queries to call other queries.
The EXECUTE statement allows queries to call other queries without passing any parameters. The query called by the EXECUTE statement may be:
A named query component.
EXECUTE <query>;
An inline query consisting of SQL within [[ ]] brackets.
EXECUTE [[ <query> ]];
An inline query executed on the named data source.
EXECUTE [[ <query> ]] ON <data-source>;
The EXECUTE WITH statement allows queries to specify parameter values for the query being called, including the passing of table parameters. Query parameters use @ as a prefix.
A named query component.
EXECUTE WITH (@<parameter> <type> = <expression>, ...) <query>;
An inline query consisting of SQL within [[ ]] brackets.
EXECUTE WITH (@<parameter> <type> = <expression>, ...) [[ <query> ]];
An inline query executed on the named data source.
EXECUTE WITH (@<parameter> <type> = <expression>, ...) [[ <query> ]] ON <data-source>;
A query function, that is a CALL to a function that returns a table.
EXECUTE WITH (@<parameter> <type> = <expression>, ...) CALL <table-function>;
Suppose we have a query called p as follows:
SELECT * FROM [mfd_root] WHERE [mfd_id] >= 1;
We could run it from a command window with:
EXECUTE [p];
If the query p were located in a data source we could run it from a command window using the usual syntax to refer to components within data sources:
EXECUTE [datasource]::[p];
Suppose in the Project pane we create a query named q that contains the following SQL text:
SELECT [city], [state]
FROM [authors]
WHERE [state] = @statename;
The query takes a parameter named statename, which is prefixed in the query with a @ character to indicate it is a parameter.
We can open a Command Window and run the following EXECUTE WITH query:
EXECUTE WITH (@statename NVARCHAR = 'CA') [q];
The EXECUTE WITH query calls the q query and passes the text value CA to that query as the value to use for the @statename parameter. The results table will list the city and state fields for all records in the authors table for which the state is CA.
Note the declaration as NVARCHAR for the data type of the @statename parameter in the EXECUTE WITH query. The data type declared has to match the data type used within the query that is called. For example, if the state field in the authors table was an INT16 number, and not NVARCHAR text, then the declaration of the @statename parameter being passed as NVARCHAR would be an error. We do not need to specify the parameter type within the q query, since the parameter data type will be passed by any caller.
Consider the following Manifold query, which takes a parameter named minid. The minid parameter is prefixed with a @ character to indicate it is a parameter:
SELECT * FROM [mfd_root] WHERE [mfd_id] >= @minid;
Suppose the query above is called q. We can use it within the following query, which returns all tables with IDs greater than or equal to 2:
SELECT * FROM (EXECUTE WITH (@minid INT32 = 2) [q])
WHERE [type] = 'table';
Note the declaration of the minid type as INT32, matching how the parameter is used within the q query.
Consider a query named NamedStuff that contains the following text:
SELECT * FROM [mfd_root]
WHERE [name] LIKE @namePattern;
We use a @ prefix before namePattern to indicate it is a parameter.
We can invoke the NamedStuff query from either the Command Window or from another query with:
EXECUTE WITH (@namePattern NVARCHAR = 'mfd%') [NamedStuff];
Note the declaration of the parameter type in the above as NVARCHAR.
When passing table parameters EXECUTE WITH parameters are declared before the body. The parameter declaration includes parameter types.
Suppose we have a query component called q that contains the following query:
SELECT * FROM @p WHERE mfd_id=@x;
To display a line from the mfd_root table where the value of the mfd_id field is 2 we could use the following command:
EXECUTE WITH (@p TABLE=mfd_root, @x INT32=2) q;
Or, we could use an inline text version of EXECUTE WITH that does the same:
EXECUTE WITH (@p TABLE=mfd_root, @x INT32=2)
[[ SELECT * FROM @p WHERE mfd_id=@x ]];
Passing table parameters
to queries run on query engines other than Manifold will not work.
The following query works OK because we are passing a parameter @n from the EXECUTE WITH to the TABLE clauses:
FUNCTION f(@T TABLE) TABLE AS
(SELECT Max([Population]) FROM @T) END;
EXECUTE WITH (@n TABLE = CALL f([States Table])) [[ TABLE @n; ]] ;
This query does not work because what is inside the [[ ]] brackets is in a separate execution context that cannot see the function definition that comes before the EXECUTE WITH statement:
FUNCTION f(@T TABLE) TABLE AS
(SELECT Max([Population]) FROM @T) END;
EXECUTE WITH (@n TABLE = [States Table]) [[ TABLE CALL f(@n); ]] ;
If we wanted to use the function, we could define it within the [[ ]] brackets. This works OK:
EXECUTE WITH (@n TABLE = [States Table]) [[
FUNCTION f(@T TABLE) TABLE AS
(SELECT Max([Population]) FROM @T) END;
TABLE CALL f(@n);
]] ;
It can be tempting to use short query parameters or global values (using the VALUE statement) such as @t to avoid keyboarding long table names or in big queries with multiple statements on multiple tables to avoid tedious keyboarding or copying/pasting of table names. However, using query parameters or global values for table names works only in statements like SELECT, INSERT, and DELETE (SQL's "data manipulation language", DML) but not in statements that create tables or change table schemas such as CREATE, ALTER, and DROP (SQL's "data definition language" or DDL).
One of the reasons for that limitation is that the Manifold query engine compiles the whole query before it begins execution, but it cannot do that in cases where tables either do not exist at the beginning of the query or change within the query. So, for example, the following SQL will not work:
VALUE @t TABLE = [real_table];
DROP TABLE @t;
The best way to write a very large query with multiple statements (that might include many CREATE, ALTER, or DROP statements) on multiple tables without resorting to copying and pasting table names is to generate the query using a script. If there is only one table involved in the query, the best way is to write the query using the literal name of the table. That is easy to do by writing the query using a short abbreviation and then searching and replacing that short abbreviation with the full table name.
The EXECUTE statement allows use of inline queries. The text of the inline query must be enclosed in double square brackets [[ ]]. The use of double square brackets removes the need to escape single quotes within the inline query.
Inline query options:
The optional ON clause specifies the data source to run the query on. The inline query will run on the native query engine for that data source.
The optional FETCH clause is an optimization hint for the query engine that will run the inline query and allows it to limit the number of returned records to the specified value.
The Manifold engine can decide to ignore a specified FETCH limit. For example, Manifold will usually ignore the limit if the actual number of returned records was small enough to be retrieved in a single fetch from the data source.
Nesting of EXECUTE statements within [[ ... ]] inline queries is not allowed. If we have one or more EXECUTE statements each should be in its own query component.
The following query will run on [datasource] using the native query engine for that data source. Because the query uses Manifold syntax, it will run perfectly if [datasource] is a Manifold .map file. But if [datasource] is not a Manifold .map file but is something else, such as a PostgreSQL database, the query will likely fail or return incomplete data or data from the wrong table.
EXECUTE [[ SELECT * FROM [mfd_root]; ]] ON [datasource];
When executing inline queries on non-Manifold data sources, we should take care to write SQL that is legal for the native query engine that will be used. Another possibility is to use the $manifold$ directive to force use of the Manifold query engine. For example, the following query will always use the Manifold query engine:
EXECUTE
[[
-- $manifold$
SELECT * FROM [mfd_root];
]]
ON [datasource];
In the above the Manifold query engine will have to bring data in from the data source for the query engine to use. If we want to leverage the data source's ability to run queries to distribute processing into the data source, it may make more sense to write the query using native syntax so it can run in the data source.
EXECUTE
[[
-- $manifold$
SELECT * FROM [mfd_root];
]]
ON [datasource]
FETCH 5;
The query above includes the FETCH clause, to bring in only the first 5 returned records.
To specify parameter values in inline queries we use WITH.
If the inline query is going to be run on the Manifold query engine, no special quoting of parameter names inside the query is required. If the inline query is going to be run on a non-Manifold query engine, we must enclose parameter names inside the query in @...@ brackets.
Passing parameters to a Manifold query:
EXECUTE WITH (@x INT32 = 1)
[[ SELECT * FROM [mfd_root] WHERE [mfd_id]=@x; ]];
Passing parameters to a SQL Server query:
EXECUTE WITH (@x INT32 = 2, @y INT32 = 20)
[[ INSERT INTO dbo.t (a, b) VALUES (@x@, @y@) ]]
ON [sql];
Queries in the Command Window execute in the context that the Command Window was launched, by default in the root of the project, that is, locally. We can launch the Command Window in the context of a data source if desired. See the discussion of Command Window Context in the Command Window topic.
An ON clause also modifies the context in which query text is run, but the ON clause only applies to the inline text of the query. When an ON clause is used with EXECUTE WITH the context specified by ON will not apply to parameters that are passed. When an ON clause is used with EXECUTE and a query function, the ON context will not apply to arguments for the function.
Consider a project that contains a drawing showing provinces in Mexico.
The drawing's table is called Mexico Table. It contains a few fields such as a Population field in addition to the Geom field used by the drawing.
The project also contains a simple query called MexQ:
SELECT * FROM [Mexico Table];
We save the project in a Manifold .map file and then we close the project. We use File - New to create a new, blank project.
In that new project we create a data source called Mexico using the .map file that was saved. We will use this project for the following examples. In all cases we launch the Command Window using View - New Command Window - SQL so it is launched in the default context of the root of the project. We first consider two examples of queries involving the data source that do not use ON.
We can execute the MexQ query within the data source with:
EXECUTE [Mexico]::[MexQ];
There is no need to use ON to set the context since the query is already within the data source.
Likewise, if we want to use a query function such as:
FUNCTION f(@T TABLE) TABLE AS
(SELECT Max([Population]) FROM @T) END
TABLE CALL f([Mexico]::[Mexico Table]);
...we pass the name of the target table within the data source, [Mexico]::[Mexico Table] , as an argument to the function. That tells the system to go into that data source to find the table and allows it to find the [Population] field used in the function.
We now consider two cases of using ON, first a correct use:
The query:
EXECUTE [[
SELECT Max([Population]) FROM [Mexico Table];
]]
ON [Mexico];
... takes an inline query:
SELECT Max([Population]) FROM [Mexico Table];
... and runs it on the Mexico data source using the native query engine of that data source. In this example since the data source is a Manifold .map file the native query engine of the data source is also Manifold. Running the query "on [Mexico]" means that when the SELECT statement searches for a table named [Mexico Table], it does that search within the [Mexico] data source. Within that data source the query engine finds the table named [Mexico Table], and it finds the field named [Population] and so the query works.
We now consider an incorrect use of the ON clause:
The query:
EXECUTE WITH (n TABLE = [Mexico Table]) [[
SELECT Max([Population]) FROM n;
]]
ON [Mexico];
... is incorrect because the context of the ON clause is not applied to parameters passed to an inline query. ON only applies to the inline query text. In this case when the query engine prepares parameters, it sees only one parameter, n, which is set to [Mexico Table]. Since the ON clause does not apply to parameters, the query engine looks for a table called [Mexico Table] in the context of the main query, which is the root of the project. Since there is no such table there the query cannot find a [Population] field and the query fails.
As an alternative, we could simply use:
The query:
SELECT Max([Population])
FROM [Mexico]::[Mexico Table];
... refers to [Mexico Table] within the [Mexico] data source so there is no problem finding the [Population] field.
An example using ON when passing parameters via EXECUTE WITH:
In the query:
EXECUTE WITH (@n int32 = 2000000) [[
SELECT [Name], [Population] FROM [Mexico Table]
WHERE [Population] > @n;
]]
ON [Mexico];
... we are simply passing a number as a parameter, and not the name of a table that depends upon the execution context to be found.
Brackets and Parentheses - [ ] square brackets or ` ` reverse quotes are used for names. ( ) parentheses are used for grouping language constructs. { } curly brackets are not used in Manifold SQL but often appear in various other usages such as JSON. [[ ]] double square brackets act as fences to isolate what is within. For example, when inline queries are enclosed within double square brackets [[ ]] the use of double square brackets removes the need to escape single quotes within the inline query. @ @ brackets are used to enclose parameter names inside inline queries that are going to be run on a non-Manifold query engine.
Command Window - Query Builder