Example: Refer to a Table within a Data Source

In this example, we will connect to an .mdb file database that contains tables of populated places around the world.   


 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.


In a new project 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.



For the Name, we enter GeoNames MDB File.  Choose File: mdb as the Type.  In the Source box we enter the path to the file name (or use the browse button to navigate to and to pick the file).  Press the Create Data Source button.


A new data source appears in the Project pane.  We click on the + icon by the new data source to expand it, to see that it contains three tables.


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


To fit into this documentation, illustrations show an artificially small Manifold desktop, with only a few panes, docked to the right side.   In real life we use a much larger Manifold desktop, and all panes would be turned on, with some panes docked to the left and others docked to the right.



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.   


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.


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.



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



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.   



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:



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.  



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





Edit - Join


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.