EXECUTE

The EXECUTE statement allows queries to call other queries.  

 

EXECUTE <query>;

 

The <query> in the above may be:

 

 

EXECUTE <query>;

 

 

EXECUTE [[ <query> ]];

 

 

EXECUTE CALL <table-function>;

 

 

The EXECUTE WITH statement allows queries to specify parameter values for the query being called, including the passing of table parameters:

 

 

EXECUTE WITH (<parameter> <type> = <expression>, ...) <query>;

 

 

EXECUTE WITH (<parameter> <type> = <expression>, ...) [[ <query> ]];

 

 

EXECUTE WITH (<parameter> <type> = <expression>, ...) CALL <table-function>;

 

Example

 

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];

 

Parameter Query Examples

 

The following Manifold query takes a parameter named minid.   No special syntax such as a PARAMETERS option is necessary.  The Manifold query engine determines that minid is unbound during compilation and thus it knows that minid should be 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';

 

Another example: Consider a query named NamedStuff that contains the following text:

 

SELECT * FROM [mfd_root]

WHERE [name] LIKE namePattern;

 

We do not need to specify that namePattern in the above is a parameter, since the query engine will figure that out automatically.   We do not need to specify the parameter type either, since that will be passed by any caller.

 

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.

 

Table Parameters Example

 

When passing table parameters EXECUTE 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 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.

Inline Queries

The EXECUTE statement also supports 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 Manifold engine can decide to ignore a specified 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.

 

Inline Query Example

 

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.

Parameter Values and Inline Queries

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.

 

Examples

 

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];

 

Context for Inline Queries and ON Clause

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.

 

il_execute01_01.png

Consider a project that contains a drawing showing provinces in Mexico.  

 

il_execute01_02.png

 

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.

 

il_execute01_03.png

 

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.  

 

il_execute01_04.png

 

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.

 

il_execute01_05.png

 

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.

 

il_execute01_06.png

 

Likewise, if we want to use a query function such as:

 

FUNCTION f(T TABLE) TABLE AS

 (SELECT Max([Population]) FROM T) END

EXECUTE 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:

 

il_execute01_07.png

 

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:

 

il_execute01_08.png

 

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:

il_execute01_09.png

 

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:

 

il_execute01_10.png

 

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.

 

 

 

 

 

Notes

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.

See Also

Tables

 

Functions

 

Queries

 

Regular Expressions

 

Command Window

 

Command Window - Query Builder

 

SQL Functions

 

SQL Operators

 

Temporary Databases