Example

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 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 LIMIT
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 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.

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

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:

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.

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.