Tools - Database Console

The Database Console allows browsing ADO.NET, OLE DB, ODBC or file-based (like .mdb or .xls files) data sources to see what tables are available and what fields and type of fields they contain. Connections may also be made to Oracle databases using the Oracle Call Interface (OCI) for a native Oracle connection, or to spatial DB2 or PostgreSQL databases using a native DB2 or PostgreSQL connection. Toolbar buttons allow importing or linking tables from data sources.

 

The Database Console is resizable, so we can increase the size of the dialog if we have many objects to display or a lengthy query to write.

 

The database console understands a variety of spatial DBMS storage methods:

 

·      Native spatial DBMS - When using the DBMS's native connection technology to connect to a DBMS providing "built in" spatial capabilities such as Oracle Spatial, IBM DB2 with spatial extender, SQL Server 2008 or PostgreSQL with spatial extensions, database console will automatically utilize the built in spatial features of that DBMS. Images stored as Oracle GeoRasters will also be automatically identified as images.

·      Manifold spatial DBMS - When connecting to a DBMS used to store spatial information using non-native geometry types supported by Manifold, database console will automatically use Manifold spatial indices and interpret tables containing geometry as drawings. Images and surfaces stored using Manifold facilities will also be automatically identified as images and surfaces. Spatial indices in SQL Server managed by the Manifold Spatial Extender for SQL Server will automatically be recognized and used.

·      ESRI SDE or Personal Geodatabase storages - If SDE or Personal geodatabase storages are present in the database, database console will recognize those and will configure itself to work with them.

 

The above are referred to as spatial databases for the purposes of this topic. A spatial database is one of the above three classes of database and is distinguished by having tables that contain geometry for drawings using generic geometry types and generic spatial indices. Although any Manifold edition can read generic spatial DBMS storage ennabled with Manifold facilities, only Manifold System Enterprise Edition or greater edition can create such storage.

 

To work with spatial DBMS using native spatial DBMS capabilities (such as drawings and images stored within Oracle Spatial or Locator facilities within standard Oracle databases), we must have Manifold System Enterprise Edition or greater installed. If we do not have at least Enterprise Edition installed, we will not be able to connect to and use native spatial DBMS capabilities within spatial DBMS products such as IBM DB2 with Spatial Externder, Microsoft SQL Server 2008 (Katmai), Oracle Spatial or PostgreSQL with spatial extensions.

 

The database console dialog also allows interactive execution from within Manifold of command language statements to manipulate the external database. This allows one to completely control a large data source (such as an Oracle or SQL Server database) from within a Manifold dialog.

 

Note: the example screenshots in this topic use a connection to the Northwind Traders sample Access database that has been set up in the Data Sources dialog. See the examples in the Data Sources Dialog topic.

 

images\dlg_database_console.gif

 

The database console dialog consists of two panes. The upper, contents pane allows browsing of all tables, fields and queries in the database, including tables in addition to the table that is opened. The bottom pane provides a workspace for writing statements in the command language for that data source (normally SQL/DDL). Database objects in the upper pane are sorted by type (tables are shown together, queries are shown together, etc.) and then by name. Table columns are sorted by name.

 

Highlighting an item by clicking on it will show what it is. For example, the highlighted table in the illustration above is identified as a TABLE in the small status row at the bottom of the dialog. If the provider supports comments or descriptions for fields, these will appear in tool tips when the mouse hovers over a field. In the illustration above (showing the Nwind.mdb sample database) the description entered into the table design for the Customer ID field is shown as a tool tip when the mouse cursor hovers over that field.

 

images\dlg_database_console_01.gif

 

Field types will be shown for any fields highlighted in the contents pane. Field types will be reported using Manifold equivalents so we can see what the column type will be when the table is imported or linked into a Manifold project.

 

When connecting to a spatial database any tables that contain one or more geometry columns will be shown as drawings in the contents pane. Such drawings may be imported or linked into the project. For example, when connecting to an Oracle database using OCI, any tables that contain one or more SDO_GEOMETRY columns will be shown as drawings in the contents pane. To take another example, when connecting to any database using OLE DB which has a spatial index created by Manifold, any tables that contain one or more geometry columns of any supported Manifold geometry type (such as OGC Geometry(WKB) or Manifold Geometry) for which a spatial index has been declared will be shown as drawings in the contents pane.

 

Likewise, images stored either using native DBMS technology or generic Manifold image storage technology will appear with image icons and may be imported or linked into the project. For example, images stored in Oracle databases using GeoRaster technology will appear with image icons. The status readout for an image stored in an Oracle database will show the dimensions of the image, the number of channels and the data type used for image pixels. Surfaces will also appear as images: surfaces may be imported by importing the Height channel of the image.

 

 

Data Source

The name of the last used data source. Manifold will not actually connect to the last used data source until the Refresh button is pressed. Press the browse button to launch the Data Sources dialog to choose a data source.

(contents pane)

Displays the contents of the data source.

images\xbtn_refresh.gif

Refresh - Update the contents pane.

images\xbtn_import_component.gif

Import - Imports a copy of the highlighted table into the project. The table will be stored within the Manifold project with no connection to the originating data source. Also used to import drawings from geometry tables in databases or to import images or surfaces from GeoRaster storage in Oracle databases.

images\xbtn_link_component.gif

Link - Links the highlighted table into the project. The table remains stored in the data source. Also used to link drawings from geometry tables in databases or to link images from GeoRaster storage in Oracle databases.

images\xbtn_dbconsole_component_view.gif

Component View - Enabled if the Administrator Console has been used to add friendly, component names to this database for database objects. Press in (the default if friendly names are available) to show the database using component names as in a Manifold project. Database objects without friendly names will be hidden.

images\xbtn_global_filter.gif

Global Filter - Launches a dialog enabling filtering the names of database objects using regular expressions. Changes to the global filter are saved between different sessions of Manifold. By default, the global filter includes several masks suppressing display of system tables commonly found in Oracle databases.

Filter

Filter by character sequence - Show only those database objects in the contents pane the name of which contain the given sequence of characters. This is particularly helpful when working with data sources with lots of objects, such as SQL Server or Oracle data sources.

(command pane)

A text pane in which command lines can be entered to be executed by the data source.

images\xbtn_runquery.gif

Run - Press to execute the command line text in the command pane.

images\xbtn_import_component.gif

Import Query - Fetches a copy of the results of the query into the project as a table. The table will be stored within the Manifold project with no connection to the originating data source.

images\xbtn_link_component.gif

Link Query - Links the results of the query into the project as a table. The data remains stored in the data source.

 

Note: By default, system tables and views do not appear in the data source contents pane for Access .mdb databases. To cause them to appear, open the Tools - Options - Import and Export dialog and check the option box for Show system tables and views in external data sources.

 

When connecting via ADO .NET if the database provider supports schemas (introduced in ADO .NET 2.0) then the database console will list tables and columns returned by the database provider.

 

Friendly Names

 

Enterprise-class databases used for storing drawings and other data from Manifold will often employ fearsome naming conventions for database objects that, to put it mildly, ordinary users will find threatening. Friendly names, also known as component names, make it possible to use simple, non-threatening, names within the database just like in our Manifold project.

 

To add friendly names to a database, an administrator must use the Administrator Console to add them. Normally, this is done at the same time a drawing or other Manifold component is first uploaded into a database. If the Administrator Console has not been used to specify friendly names for use in a database, then friendly names will not be available.

 

When Database Console connects to a data source that has had at least one friendly name assigned with the Administrator Console, Database Console will automatically launch with component view turned on, that is, to display friendly names by default. If for some reason component view has been turned off, we can turn it back on by using the Component View toolbar button. If the data source does not contain a metadata table set up with Administrator Console, the component view button will be disabled.

 

When using component view, the Database Console dialog will only show components that have been assigned friendly names. The Filter box will work with friendly names as well.

 

For example, if a drawing is exported into an Oracle Spatial database it might be known within the database as SYSTEM.US_MAINDRAWING.GEOMETRY. In normal usage, of course, everyone would prefer to call that drawing by a simpler name such as US Main.

 

images\eg_friendly_names_01.gif

 

For example, without friendly names enabled we have to wade through many database objects that are not of interest even if global filtering is set.

 

images\eg_friendly_names_02.gif

 

With friendly names, when Component View is turned on, we see only those database objects usable as Manifold components as we might in the project view, without any unnecessary clutter.

 

Note: The Administrator Console is available only in Database Administrator Edition licenses of Manifold System. The ability to enable use of friendly names by the entire organization is one of the many reasons why it makes sense for organizations that use Manifold System to acquire at least one Database Administrator Edition license. Once a Manifold Database Administrator Edition license has been used to create friendly names in a database, then clients using other Manifold licenses, such as Enterprise Edition licenses, can use those friendly names. Therefore, organizations with many Manifold licenses will normally have a few Database Administrator Edition licenses for their DBMS or IT administrators to use with most of their Manifold licenses being Enterprise Edition licenses.

 

Resolving Identical Names

 

If a database object has been assigned the same friendly name then regardless of the Component View setting the Database Console will display the object

 

Import / Link Options Dialog

 

Clicking the Import or the Link button will import the highlighted component. If the Administrator Console has been used by a DBMS administrator to pre-configure the import and link options for the component, it will import or link immediately from the database.

 

Any Manifold edition can connect to a spatial DBMS managed by Manifold using either generic spatial indices or SQL Server running the Manifold Spatial Extender for SQL Server. Enterprise Edition or higher edition is required to export components into such databases, but once the components are uploaded into the database and a spatial index created (for drawings), then any Manifold System edition can import or link that data.

 

Enterprise Edition is always required to connect to a native spatial DBMS such as DB2 with IBM Spatial Extender, Oracle Locator or Oracle Spatial, PostgreSQL or SQL Server 2008 spatial.

 

If Administrator Console has not been used to pre-configure import and link options, clicking the Import or the Link button will launch the Import / Link Options dialog to allow specification of options during the import or link process. Most options require spatial DBMS capability, either using a native spatial DBMS or Manifold-managed spatial indices.

 

Name

Name to use for the component that is imported or created.

Use all objects

Spatial DBMS only. Import or Link all objects.

Use all objects in the following area

Spatial DBMS only. Import or link only a subset of objects found within the given X (longitude) and Y (latitude) extents. The read-only X and Y coordinate boxes that surround the editable X and Y coordinate boxes report the minimum and maximum X and Y extents of the data as reported by the data source.

Require objects to be completely within area

Spatial DBMS only. Enabled if a subset area of interest has been specified. Not checked by default: will import or link any object that has at least one coordinate within the given area of interest. If checked, will import or link only those objects that have all coordinates entirely within the given area of interest.

Version

Enterprise Edition only. Column to use as the version column for tracking and resolving concurrent multi-user editing conflicts.

Rely on server to increment version automatically

Spatial DBMS and Enterprise Edition only. Enabled when a Version column is specified. If checked (the default) causes the database (such as Oracle) to increment the version value whenever an object is edited by any user.

 

Note that some options require Enterprise Edition and others require both use of a spatial DBMS and also Enterprise Edition.

 

For example, importing or linking a drawing via Database Console will automatically retrieve and use the value for location precision if available. Drawings uploaded as tables into a spatial DBMS from Manifold will store location precision.

 

Filtering

 

Some data sources contain very large numbers of objects such as tables and queries. It is very convenient in such cases to restrict the items shown in the contents pane to only those of interest. We can do so by entering characters matching the desired names into the text box in the toolbar.

 

images\dlg_database_console_06.gif

 

For example, if we look at the sample Northwind Traders database without any characters in the text box we see listed in the contents pane everything in the database.

 

images\dlg_database_console_07.gif

 

If we enter the character c into the text box we see listed only those objects that have a c in their name. For example, the Suppliers table no longer appears because there is no c in "Suppliers."

 

images\dlg_database_console_08.gif

 

If we enter the character sequence ca into the text box we see only those objects that have a c followed by an a in their names.

 

Global Filter

 

The Global Filter provides a default way of filtering out system level and other DBMS objects we don't need to see.

 

Pressing the Global Filter button opens the Filter dialog. The lower pane of the dialog contains a list of masks, which are simply regular expressions using standard regular expression syntax. For example, the * asterisk character matches zero or more characters of any type.

 

Object names that match any of the regular expression masks within the Filter dialog will not be displayed. If we have a mask like OLAP.* then any database object that has the letters OLAP followed by zero or more characters will match. Such a regular expression matches names like OLAP1 or OLAP or OLAP.234. So DBMS names like OLAP.INDEX or OLAPSYS.XML_LOAD_LOG or similar will not be displayed. See the Regular Expressions topic for details on regular expressions.

 

To add a new mask, enter the regular expression in the uppermost box in the Filter dialog and press the Add button. To delete a filter, highlight it in the list of filters and press the Delete button. The Add button is enabled whenever text is entered into the upper box, and both the Add and Delete buttons are enabled if any existing mask is highlighted.

 

Executing Commands

 

The lower pane of the database console allows direct control of databases using the command language (normally SQL or associated languages such as DDL) supported by the provider. For example, when connecting to a SQL Server Express Edition server or to an Oracle Express Edition server, database console is a handy way of creating new tables.

 

The lower pane of the database console may also be used to execute queries and to import or link the resulting tables into the project. This is an alternate, interactive way of creating tables from queries in addition to creating a Query component.

 

After creating a query in the lower pane we press Run to execute the query.

 

Editing Shortcuts

 

The command pane in the lower part of the dialog provides an edit pane into which command statements may be written. For convenience, double clicking a database object in the upper pane will add it to the text in the lower pane at the current cursor position.

 

images\dlg_database_console_02.gif

 

For example, suppose we open the database console with the nwind sample database and begin writing a query that will use the Customers table. We would like to select company names. To do so we double click the Company Name field object in the upper pane.

 

images\dlg_database_console_03.gif

 

It appears in the lower pane as part of the query. When automatically adding names to a query in this way, Manifold will insert the name of the object into the query using the correct quote characters for the type of database connection being used.

 

images\dlg_database_console_04.gif

 

We can continue writing the query by entering FROM into the lower pane and then double clicking the Customers table object. in the upper pane.

 

images\dlg_database_console_05.gif

 

This adds 'Customers' to the query text in the lower pane. The ability to add text to the query in the lower pane by double clicking objects in the upper pane is a great way to avoid entering long names manually and also helps avoid typographic errors.

 

Manifold is reasonably adaptive when loading database object names into the lower pane. For example, double-clicking an image in a spatial DBMS will insert the name of the database table containing the image data.

 

Query Results

 

Using Run makes sense only if the SQL command is an action query used to alter table structures, etc., since there is no way for a SELECT query to report its results within the database console. The SELECT query in the above sequence of editing shortcuts, for example, would have no effect when using Run.

 

To run a SELECT query like the one above and collect the results in a useful way, press either the Import Query button or the Link Query button so that the results of the query appear as a new table in the project.

 

images\dlg_database_console_09.gif

 

For example, if we press the Link Query button with the command text above we will create a linked table in the Manifold project that lists the city and country for each record in the Customers table, as seen below.

 

images\dlg_database_console_10.gif

 

After running an action query, press Refresh to update the upper pane in the dialog (necessary if the SQL statement adds or deletes tables or otherwise alters database structure). If a parameterized command is issued in the edit pane the dialog will prompt for values of required input parameters.

 

The language available within the database console will be whatever language is supported by the underlying OLE DB driver. The execution capability of the database console is therefore driver-specific.

 

When running SQL within Manifold queries, one is using the Manifold SQL engine. When executing SQL within the database console, one is using whatever SQL is the native SQL of the external database system. One should be aware that SQL implementations in various database systems may contain bugs. If an SQL bug occurs within the database console, the bug should be tracked down with the vendor of the external database system being used.

 

Examples

 

Connecting to the Nwind.mdb sample database we could create a new table, alter it and then delete it using the following sequence of commands in the lower pane of the database console, pressing Execute after each command.

 

CREATE TABLE People (Name TEXT, Age NUMBER)

 

ALTER TABLE People ADD COLUMN Weight NUMBER

 

DROP TABLE People

 

We can work with other databases through the database console, for example, Microsoft's SQL Server 2000. For examples showing configuration of Microsoft's SQL Server 2000 desktop engine see the SQL Server 2000 desktop engine topic.

 

Open Data Source Command

 

Right clicking a component that is linked from an external data source in the Project pane and then choosing Open Data Source will open the Database Console and connect to the component data source.

 

Opening a component linked from an external data source in its own window, and then choosing the Open Data Source command in the component menu (for example, choosing Drawing - Open Data Source when a linked drawing window is open) does the same thing.

 

Losing a Connection

 

Losing a connection to a data source (such as might happen if a network connection to a remote database is interrupted) will disable the object tree in the upper pane as well as the query text lower pane.

 

Troubleshooting

 

Sophisticated DBMS installations can very easily be configured with all sorts of security and access permissions that can be difficult to understand. Therefore, if attempting to connect to a database and the connection is not successful, examine very carefully the administrative settings used for the database and connections thereto.

 

As a confidence-builder in complex situations, it may be helpful to set up a database and connect to it using Manifold to prove to yourself that all is correct with the Manifold installation and with your understanding of Manifold.

 

For example, install SQL Server Express and connect to it using a data source as given in the Data Sources Dialog topic example.

 

Note that when specifying a connection in the Data Sources dialog Manifold uses Microsoft configuration dialogs. Manifold uses standard Microsoft technologies, so the key to debugging any connection problems is to understand those standard technologies.

 

Tech Tip: Connection Methods

 

Keep in mind that the choice of connection technology will influence how Manifold interacts with the DBMS. For example, ADO .NET connections typically are read-only and will not allow either Administrator Console or Database Console to make changes, such as adding a spatial index, to the database. In another example, to use a spatial DBMS's native geometry type we must connect to that spatial DBMS using the native connection technology (such as using OCI to connect to Oracle). See the Spatial DBMS Facilities topic for additional information.

 

Tech Tip: Deletions in Database Console

 

It's easy to delete unwanted database tables by writing a drop table query in the query pane at the bottom of the Database Console.

 

images\eg_drop_table_query.gif

 

Simply enter drop table and then double-click on the table (drawings and other images are stored as tables in Oracle) in the upper pane to add that name to the query without need to manually enter it. Press the ! run button to launch the query and the table will be deleted. Note that the upper list of objects in the database will not be refreshed to show the deletion until we press the Refresh button.

 

Tech Tip: Deleting Spatial Components in Database Console

 

Using Manfiold to store drawings, images or surfaces within a spatial DBMS opens the door to great flexibility, but that flexibility also makes it more complex to delete such components when stored into a spatial DBMS. Storing a component into a DBMS involves storing data in tables within that DBMS and forming metadata relationships with the MFD_META table used by Manifold to keep track of such components. It also makes it possible for other applications or other users to reference the data tables and otherwise form connections to the components in ways that can potentially be very complex.

 

For that reason, there is no automatic way to delete a component uploaded into a DBMS by just highlighting it and pressing a delete button. Instead, we must follow a simple procedure to delete a drawing, image or surface uploaded into a DBMS:

 

1. In Database Console, turn off Component View (friendly names) so we can see the full DBMS name of the item to be deleted. Note the name of the object, for example, dbo.Mexico.

 

2. Delete the tables for the component using a DROP TABLE query as given above. This will include the component as well as any spatial index created for it. Hit the Refresh button to see what has been deleted. If an error message such as "Cannot drop the table 'dbo.Mexico', because it does not exist or you do not have permission." pops open, that is a sign that references to that table should be removed from the MFD_META table.

 

3. Delete all references to that object name within the OBJ field of the MFD_META table. There are two ways to do this:

 

a. In Database Console run the following query: DELETE FROM "MFD_META" WHERE "OBJ"='dbo.Mexico'

 

b. Alternatively: In the Database Console's Filter button, temporarily uncheck the Hide objects matching the following masks box so that all tables are visible, even those we don't normally need to see. Use the Database Console to link the MFD_META table into the Manifold project. This table will appear as a database object with a name like (to use SQL Server as an example) dbo.MFD_META. Close the Database Console and use the Query Toolbar or mouse selection to select all records in the table where the OBJ field is equal to the value dbo.Mexico and delete those records. After finishing work with this linked table, close it and delete it from the project [deleting it delete the link, not the table itself within the DBMS].

 

Experienced Manifold DBMS administrators will usually use the second way of eliminating OBJ field references to a deleted table. It's easy to open the MFD_META table in a Manifold project, click on the OBJ column header to sort by the values it contains, and then click once and shift click twice to select all desired values and then press the delete button. This is a quick way of getting rid of similarly-named references to a drawing, the drawing's spatial index and so on. This is quicker and less prone to typographic errors than repeatedly entering a sequence of queries to be executed to delete unwanted records from the MFD_META table.

 

See Also

 

Tools - Administrator Console

Importing and Linking Tables

Oracle Spatial Facilities

Project Pane - Open Data Source

Oracle Express Edition

Spatial DBMS

Spatial DBMS Facilities

SQL Server Express Edition

The Data Source Dialog

Queries