Example: Switching between Manifold and Native Query Engines

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.

 

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.

 

Microsoft's JET 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 Microsoft JET 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 JET SQL using !manifold and !native commands to see what works and what does not.

tech_jason_sm.png

 

Tech tip:  JET is primarily a 32-bit technology.  Although Microsoft has introduced ways for 64-bit applications to utilize JET depending on what we have installed on our computer it may not be possible for 64-bit Manifold to utilize JET.  Therefore, whenever we want to connect to .mdb or .xls or other Microsoft file database types that use JET we must make sure to launch Manifold in 32-bit mode.

 

eg_datasrc01_00.png

 

We launch Manifold in 32-bit mode to ensure we can utilize the Microsoft JET database engine that is required to connect to Microsoft file databases such as .mdb.   

 

eg_mfdnatcmd01_01.png

 

 

We choose File - Create - New Data Source to launch the New Data Source dialog.   We will use a file called books.mdb as a file database for our data source.   This file contains the sample "books" database used in Chris Fehily's excellent book, SQL Visual Quick Start Guide.  Either this book or Chris Fehily's more recent SQL Short Course is a great way to learn the basics of SQL.   Both are highly recommended for anyone new to SQL who will be working with Manifold System, Radian Studio or other Radian-based Manifold products.   

 

We have given the new data source a more descriptive name than the default "Data Source."  We connect to the .mdb using the type File: mdb.  Click Create Data Source.

 

eg_mfdnatcmd01_02.png

 

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.

 

eg_mfdnatcmd01_02a.png

 

Next, we right-click onto the data source to open a Command Window in the context of that data source.

 

eg_mfdnatcmd01_03.png

 

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_jason_sm.png

 

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 JET data base 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 JET database engine, within which we can launch examples that utilize commands and features unique to JET.

 

One such feature is the @@IDENTITY global variable which exists in JET but not in the Manifold query engine.

 

eg_mfdnatcmd01_04.png

 

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 JET database engine.  Commands and queries issued in the command pane therefore by default will be executed within the JET 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.

 

eg_mfdnatcmd01_05.png

The result reports the value of the JET global variable @@IDENTITY.  

 

The @@IDENTITY global variable is a feature of the JET 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 JET 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 JET database engine data source, the .mdb file database, so the JET query engine is the native engine and JET SQL commands, like referring to a JET global variable, will work.

 

What about Manifold SQL commands?

 

eg_mfdnatcmd01_06.png

 

Suppose now we run a command to look at a specifically Manifold thing, as in

 

SELECT * FROM mfd_root;

 

What happens then?

 

eg_mfdnatcmd01_07.png

 

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 JET does not see them.

 

Let's switch to Manifold mode and try again:

 

eg_mfdnatcmd01_08.png

 

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;

 

 

eg_mfdnatcmd01_09.png

 

...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 JET query such as...

 

SELECT @@IDENTITY;

 

 

eg_mfdnatcmd01_10.png

 

That will fail as seen in the illustration above, because @@IDENTITY is a JET variable and not a Manifold variable.

 

eg_mfdnatcmd01_11.png

 

We will switch back to native mode using a !native command.   

 

eg_mfdnatcmd01_12.png

 

And now the

 

SELECT @@IDENTITY;

 

query will once again work, since in native mode we are running the JET query engine, which understands JET variables.

 

See Also

Tables

 

Queries

 

Command Window

 

Command Window - Query Builder

 

MDB Microsoft Access

 

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: Run JavaScript in the Command Window - How to run a simple V8 JavaScript script in the Command window.

 

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.  

 

MDB Files in 64-bit Windows

 

Launch in 32-bit Mode