Example: Refer to a Table within a Data Source

ico_nb_arrow_blue.png  Important:  This example connects to a Microsoft .mdb file.  We must launch Manifold in 32-bit mode since Microsoft's drivers for .mdb do not work with 64-bit software.

 

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.

 

ico_nb_arrow_blue.png  We launch 32-bit Manifold to ensure we can use the built-in copy of Microsoft's "Jet" DBMS engine to connect to file databases in Microsoft "Jet" formats such as .mdb.  

 

In a new project we choose File - Create - New Data Source and then we create a new data source called GeoNames MDB File.  

 

eg_table_datasource01_00a.png

 

We will connect to an .mdb file database that contains tables of populated places around the world.   After creating the new data source in the Project pane we click on the + icon by the new data source to expand it.

 

We can take a look at the U2000 table by double-clicking it to see the contents.

 

eg_table_datasource01_00.png

 

We will now write a query using the U2000 table.   From the main toolbar we launch a Command window by choosing View - New Command Window - SQL.   

 

Using the built-in capabilities of the query builder, we drag and drop the U2000 table into the tables pane of the Command window.

 

We can then build a SELECT query:

 

SELECT * FROM [GeoNames MDB File]::[U2000]

WHERE StringContains([Value], 'grad');

 

In the command pane.

 

eg_table_datasource01_01.png

 

Note the naming syntax for referring to the name of a table within a data source.   Two colon : characters are used in the form [data source]::[name].  

 

That same syntax extends into nested data sources.  Suppose, for example, we had a table called dbo.orders that existed within a data source called postgresql that in turn existed within a data source called sqlserver in our project.   We could refer to that table as [sqlserver]::[postgresql]::[dbo.orders] and it will be found by the query.

 

In this example we refer to the U2000 table within the GeoNames MDB File data source using the syntax [GeoNames MDB File]::[U2000].   The query uses the StringContains function to find all records where the [Value] field includes the substring "grad."

 

eg_table_datasource01_02.png

 

Press the ! button in the main Manifold toolbar to run the query.   Running the query produces the results seen above.  

 

To refer to data sources nested within other data sources we simply add more :: separators.   

 

il_nestedsources01_01.pngil_nestedsources01_01a.png

 

Consider the Customers table shown above, opened in a project from a data source nested several levels within other nested data sources.   We could refer to it in an SQL query as in:

 

SELECT *

FROM [Europe]::[France]::[Centre]::[Eure-et-Loir]::[Customers];

 

It is easy to see the nomenclature for such tables: simply drag and drop the table into the Command window's Tables pane to see how it is named.  

 

il_nestedsources01_03.png

 

That also provides easy access to it in the Command window query builder for double-clicking on its elements for use in queries.

 

See Also

Tables

 

Queries

 

Command Window

 

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: Switching between Manifold and Native Query Engines - How to use the !manifold and !native commands to switch a query in the Command window from use the Manifold query engine to whatever query engine is provided by 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.