The Manifold Command Window allows writing queries that switch between the Manifold query engine and a query engine operated by some external database or data source. In this example, we use an .mdb file operated by Microsoft's Access Database Engine. We could just as easily use an SQLite database file, if we preferred, for this example.
Important: For simplicity, the following examples do not include a THREADS SystemCpuCount() command in the query. When writing queries manually using the Command Window we should make sure to add a THREADS SystemCpuCount() command to the query to automatically parallelize the query to use all CPU cores in our system. See the THREADS command for more info.
In this example, we use a 64-bit Windows
10 system that has had Microsoft's 64-bit Access Database Engine installed.
Manifold uses Microsoft facilities to work with Microsoft .mdb files. If Manifold
cannot import from, link to,
or export to an .mdb file,
that means the Windows system we are using is missing the necessary facilities.
Please see the Microsoft
Office Formats - MDB, XLS and Friends topic for a solution.
Microsoft's Access Database Engine uses .mdb files for its file databases. When a data source is created for a file database that uses an .mdb file, Manifold uses Access Database Engine to operate that data source. We will create a data source using an .mdb file and then open a Command window in that data source. We will then switch between Manifold SQL and native Access SQL using !manifold and !native commands to see what works and what does not.
We launch Manifold and then we choose File - Create - New Data Source. The dropdown menu provides a list of favorites to choose from as well as a More... option.
Choose More... to launch the New Data Source dialog.
We enter books.mdb for the Name, and we choose File: mdb as the Type. Use the [...] browse button to navigate to and to pick the books.mdb file as the Source.
We will use a file called books.mdb as a file database for our data source. The .mdb file is a Microsoft Access .mdb file database that contains tables, queries and examples for Chris Fehily's fine books on SQL, which are highly recommended by Manifold for anyone new to SQL.
To fit into this documentation, illustrations show a small Manifold desktop, with only a few panes, docked to the right side. In real life we use a much larger Manifold desktop, and more panes would be turned on, with panes docked to the left or to the right, or undocked, as we prefer. Right-click a pane's tab to change where it is docked. Manifold will remember that new arrangement for our next session.
Click Create Data Source.
If a new data source for the .mdb is not created as shown below, that means the Windows system we are using is missing the facilities necessary for a connection to .mdb. Please see the Microsoft Office Formats - MDB, XLS and Friends topic for a solution.
The data source appears in our project. We click on the + box to expand the data source. That also has the side effect of opening a live connection to the data source, which we can see in action when the database cylinder icon changes color from light gray to yellow.
Next, we right-click onto the data source to open a Command Window in the context of that data source.
In the context menu we choose New Command Window.
That will open up a new Command Window within the context of the books mdb data source. By right-clicking on the books mdb data source to open the Command window we have specified that data source will be the context for the Command window.
Tech tip: To open a Command Window in the context of the MDB data source we must follow the sequence above, where we first open the new data source and then we right-click on the data source for the context menu. If we fail to first open the data source, there will be no connection to the Access Database Engine, and no yellow cylinder, and then when we right-click on the gray data source the New Command Window menu option will not be enabled. If we fail to create the Command Window by right-clicking on the MDB data source, and instead we use the main menu command View - New Command Window - SQL to open a new Command Window, that Command Window will be opened in the context of the Manifold project, not in the context of the data source.
By opening the Command window within the context of a data source that is an .mdb file database, we have a context in play that uses the Microsoft Access Database Engine, within which we can launch examples that utilize commands and features unique to Access.
One such feature is the @@IDENTITY global variable which exists in Access but not in the Manifold query engine.
By default, the Command window opens in !native mode, that is executing commands and queries within whatever is the query engine for the data source within which it was opened. In this case we've opened a command window within a data source that uses Microsoft's Access Database Engine. Commands and queries issued in the command pane therefore by default will be executed within the Access Database Engine that operates the .mdb file database on which we created the data source.
Suppose we enter the SQL statement
SELECT @@IDENTITY;
In the command pane. We press the ! button to run the query.
The result reports the value of the Access global variable @@IDENTITY.
The @@IDENTITY global variable is a feature of the Access engine and is not something found in Manifold SQL. It reports what will be used as an Identity value in any new rows created that utilize an Identity column, zero in this case since Identity columns haven't been used in the data source. We use @@IDENTITY simply as an example of a characteristically Access feature that is harmless to use in an example no matter what .mdb file database may have been used.
The query runs OK because the Command window launches in !native mode by default, using whatever is the native SQL engine for the data source for which the Command window was opened. In this case that is a Access Database Engine data source, the .mdb file database, so the Access query engine is the native engine and Access SQL commands, like referring to a Access global variable, will work.
What about Manifold SQL commands?
Suppose now we run a command to look at a specifically Manifold item, as in
SELECT * FROM mfd_root;
What happens then?
When we press the ! button to run the query we get an error message in the Log tab. The illustration is too small to show the full error message, which is...
The Microsoft Access database engine cannot find the input table or query
'mfd_root'. Make sure it exists and that its name is spelled correctly.
The query fails because the mfd_root table does not exist in the .mdb.
When we open a non-Manifold data source Manifold will show a System Data folder in that data source that contains mfd_meta and mfd_root tables as if they existed within the data source; however, those are synthesized as virtual tables by Manifold for Manifold's use in operating that data source. They do not actually exist in the .mdb and the Access Database Engine does not see them.
Let's switch to Manifold mode and try again:
If we enter !manifold as a command and press the Run button the log reports we've switched to Manifold mode.
Running the same query...
SELECT * FROM mfd_root;
...now produces the expected result. The Manifold query engine understands that mfd_root in the query refers to the virtual mfd_root table within the books mfd data source.
We are in Manifold mode now so if we try to run a Access query such as...
SELECT @@IDENTITY;
That will fail as seen in the illustration above, because @@IDENTITY is a Access variable and not a Manifold variable.
We will switch back to native mode using a !native command.
If we enter !native as a command and press the Run button the log reports we've switched to native mode.
And now the
SELECT @@IDENTITY;
query will once again work, since in native mode we are running the Access query engine, which understands Access variables.
Use Manifold SQL to reference Manifold infrastructure - Manifold SQL understands Manifold infrastructure created on the fly, such as the System Data folder and system tables, or like drawings or images that visualize tables, but native SQLs will not. If we want to reference Manifold infrastructure like drawings and images, we must use Manifold SQL.
Command Window - Query Builder
Example: Create and Run a Query - See how the different parts of a command window operate when creating and running SQL queries. Includes use of the Log tab as well as the ?expression and !fullfetch commands.
Example: Refer to a Table within a Data Source - Within a query, how to refer to a table that is in a data source.
Example: Automatically Generating CREATE Queries - How to use the Command window to automatically generate SQL in the form of CREATE queries that create a desired component.
Example: Create and Use New Data Source using an MDB Database - This example Illustrates the step-by-step creation of a new data source using an .mdb file database, followed by use of SQL. Although now deprecated in favor of the more current Access Database Engine formats, .mdb files are ubiquitous in the Microsoft world, one of the more popular file formats in which file databases are encountered.
Microsoft Office Formats - MDB, XLS and Friends