Importing and Linking Tables

Tables in Manifold projects may come from a variety of sources. They may be imported into the project or left outside the project and linked into the project. In addition, when either importing or linking a table into a project we have a choice of opening common file types (such as MDB or XLS) directly or using OLE DB or ODBC. OLE DB is a general means of accessing almost any type of provider that is a more modern and efficient method than ODBC, an earlier technology intended to provide universal access to different databases. Manifold supports both OLE DB and ODBC since different databases may have either OLE DB or ODBC drivers.

To import or link a table into a Manifold project

Choose File - Import - Table or File - Link - Table depending on whether you wish to import the table or to link it.
In the Files of type box choose the access method desired. Use ODBC Data Sources or OLE DB Data Sources to connect via ODBC or OLE DB. Choose one of the standard file types, such as MDB or XLS to connect directly to common file types.
Browse over to the database file and open it. When using ODBC or OLE DB data sources, this will involve sub-dialogs.
Choose the tables to be imported. Choose more than one table if desired.
When linking tables, check Read Only if you wish a read-only link.
Press OK.

After the initial choice of File - Import or File - Link, all dialogs are the same. The only difference is whether the data in the table is copied into the project or if it is left outside in an external file or data source.

Alternate method to import or link a table

Open the Tools - Database Console dialog.
In the Data source box, specify an OLE DB data source or use the browse button next to the box to browse to an OLE DB data source. When browsing to an OLE DB data source, in the Provider tab of the Data Link Properties subdialog use the Microsoft Jet OLE DB Provider to connect to common file types such as MDB or XLS.
The upper pane will show the contents of that data source, including any tables and the fields they contain. Click on a table to highlight it.
Click on the Import button in the dialog's toolbar to import the highlighted table. Click on the Link button to link the table.
Press Close.

In all cases, tables that are imported or linked will appear to be part of the project like any other table. Manifold can import data from or link to a very wide range of database files and database providers.

Importing vs. Linking

Importing a table will bring a copy of that table's data into the Manifold project file.
Linking a table will leave the table's data in a file or data source outside the Manifold project.

Both methods have advantages and disadvantages depending on how the project will be used.

Advantages of Importing

Speed - Imported tables usually will provide much faster performance than using an external database system.
Convenience - When tables are imported into the project, copying the .map project will automatically copy all tables in the project as well. There is no need to worry about tables that may be resident in other files or maintaining links to database systems that may be inaccessible from different systems.
Control - Importing the data provides complete control over the data and assures that no external program will alter it in a way that is incompatible with its use within the project. If other programs could alter the table (if it were linked and not imported) they might alter it in a way that is incompatible with the Manifold project, for example by removing a field that is used elsewhere within the project.

Disadvantages of Importing

Size Limitation - Manifold cannot import tables that exceed four gigabytes in size. This limitation does not apply to external, linked tables.
Single User Tables - Only one user can work with Manifold at a time, so that once a table is imported into Manifold it is not accessible by other programs in a multi-user way.

Advantages / Disadvantages of Linking

External Modifiability - The main advantage of linking tables is that it leaves the data in an external file or data source where other programs can easily modify it. Of course, if we don't want to risk uncontrolled modifications this is a disadvantage.
One Copy of Data - Linking tables avoids making copies of potentially very large data sets. When a database is a standard archival source, always linking to it instead of copying it assures that the version in use in the project is the currently archived version. The disadvantage, of course, is that one must remember to make copies of linked tables when interchanging projects. Also, changes made in Manifold operations can result in unwanted alteration of the archival original.
Multi User Operations - When linking to external tables, Manifold System is fully multi-user capable and can simultaneously work with, for example, an SQL Server or Oracle table on a remote server even as other users continue to work with the same database. If we are looking at an external table in which data is unexpectedly changing, we must keep in mind the possibility that users on other machines are also working on that same table at the same time and are making changes to the data it contains.
Larger Tables - External (linked) tables can be much larger than the two gigabyte limit on internal (imported) tables.

Unlinking a Table

A table that has been linked into a project can be converted into an unlinked table, as though it had been imported instead of linked. To do so, right click on the table in the project pane and choose Unlink. This will import a local copy of the table into the project and will eliminate the link.

Relinking a Table

A linked table may be disconnected from its data source. For example, a data source might be resident on a different machine that crashes. Use the Relink command (in the Table menu and in the context menu when clicking on a table in the project pane) to reconnect the linked table to a data source.

For example, one might create a .map file that contains a linked table that is linked to an Access .mdb file. If the .map file and the .mdb file are moved to another machine the link might not continue to work. In that case one can use the Relink command to reconnect the table in the project to the .mdb file.

Preserving Paths to Linked Tables

Linked tables are saved within Manifold projects using relative pathnames. Moving a .map file that contains a linked table will preserve the link to the table provided that the relative path from the .map file to the linked table file has not changed.

For example, suppose we have a .map file located in C:\projects\Carson\mymap.map that contains a linked table located in the file C:\projects\Carson\tables\airport.dbf. If we move the .map file to D:\backups\January\mymap.map it will still preserve the link to the table if the table file is moved to D:\backups\January\tables\airport.dbf.

OLE DB, ODBC and Common File Types

Both the File - Import - Table and the File - Link - Table dialogs allow opening a wide range of file types. In addition to common file types such as Access MDB and Excel XLS, Manifold can also open "file types" using OLE DB or ODBC data sources. Choosing OLE DB or ODBC uses Microsoft OLE DB or ODBC drivers to connect to a data source. Choosing a common file type uses a direct connection via Microsoft's Jet database engine to import or to link to file formats understood by Jet.

Common file types allow import tables from CSV (comma separated values ASCII files), DB (Borland Paradox), DBF (dBase and FoxPro), HTML (tables in web pages), MDB (Access 2000 and Access 97), UDL (Microsoft Universal Data Link), WKx (Lotus) and XLS (Excel).

Using OLE DB or ODBC opens dialogs for table imports using OLE DB providers or ODBC drivers. Tables from schema-enabled OLE DB providers like SQL Server and Oracle may be imported and linked using full table names ([schema].[name]). Manifold's ODBC system will work with ODBC.NET providers as well.

Using Tools - Database Console launches the Database Console for interactive browsing of OLE DB sources to see what tables are available and what fields and type of fields they contain. Toolbar buttons allow importing or linking tables from OLE DB sources as can be done via the File - Import and File - Link commands. The Database Console dialog also allows interactive execution from within Manifold of command language statements to manipulate the external database.

Use OLE DB to connect to enterprise databases such as SQL Server 2000 or Oracle. The disadvantage of using OLE DB is that the dialogs required to initiate a connection are more complex. If we routinely need to connect to a specific OLE DB connection we can create a UDL file that will enable us to connect via the simpler, non-OLE DB dialog.

See the Tables topic for more discussion of direct connections to common file types as compared to using OLE DB or ODBC.

Files of Type Choices

In step 3 above, we can specify many different types of data sources for importing or linking tables. Manifold's internal copy of the Jet database engine can directly read database files in the following formats, including all the various sub-variations normally found of these. In addition, we can choose to connect via OLE DB or ODBC or via UDL or DSN files (that capture, respectively, a predefined OLE DB or ODBC connection).

CSV	Comma separated values ASCII files
DB	Borland Paradox
DBF	dBase and FoxPro
DSN	ODBC file data source that describes an ODBC driver to use.
HTML	Tables in web pages
MDB	Access 2000 and Access 97
UDL	Microsoft Universal Data Link file that describes an OLE DB connection.
WKx	Lotus tables
XLS	Excel tables
ODBC Data Sources	Connect to a data source using ODBC drivers installed on this system.
OLE DB Data Sources	Connect to an OLE DB data source using an OLE DB provider installed on this system.

No other database software in addition to Manifold System is required to read such files.

For example, virtually all .dbf format files common to dBase and similar systems can be directly read. When text file types (.txt, .asc) are opened, Manifold will open sub-dialogs as necessary to allow user assisted parsing of the text file format. Note that import of text table follows usual PC industry conventions. For example, the first line of the text table should contain the names of the fields in the table.

Some UDL (Universal Data Link), DSN, OLE DB and ODBC data sources may be dependent on the presence of third party database systems or ODBC drivers in the Windows system. For example, to connect to Btrieve databases, we will need a Btrieve ODBC driver on our system. To connect to Oracle databases, we will need an Oracle database server to which we can connect. Manifold automatically installs a variety of OLE DB providers that will connect to most popular data sources.

Choosing Multiple Tables

When using File - Import - Table or File - Link - Table we can choose to import or link more than one table at a time from a database file that contains multiple tables.

For example, if we wish to import tables from Microsoft's sample Nwind.mdb database, we choose several at a time.

Click on any table to highlight it. Click on more tables using CTRL click or SHIFT click in the usual Windows way to highlight additional tables.

SHIFT clicking on another table will highlight all the tables in between.

Note: Some databases have many tables. To see the tables in sorted order, click on the Name column head in the dialog.

Data Link Properties Dialog

Manifold System uses the standard Microsoft OLE DB data source Data Link Properties dialog to allow connection to OLE DB data sources. It's a lot easier to use than it looks.

Choose the OLE DB data link type desired. Note that in Microsoft's world there are often many different ways to access the same type of data. For example, Manifold can open any Access .mdb file directly, or we can open an .mdb file by using the Microsoft Jet 4.0 OLE DB Provider. If we've installed any "Office" software or other database capable software, we will also have an ODBC driver on our system that can open Access .mdb files as well. So, we could use the OLE DB Provider for ODBC Drivers to open an ODBC data source for .mdb files.

After choosing the provider, click the Next button to move to the Connection tab.

The Connection tab will be pre-loaded with the right connection parameter choices for the type of OLE DB provider selected. In the illustration above, we've chosen the provider for ODBC Drivers and so we see the right connection options for connecting via ODBC. The data source name list box will be pre-loaded with the ODBC data sources currently configured on our system.

If we had chosen the OLE DB Provider for SQL Server and then pressed Next, the Connection tab would be loaded with the right choices to connect to a SQL Server database via OLE DB.

Provide the necessary information for the provider chosen and then press OK. The Advanced tab shows various advanced options for the specified provider, and the All tab provides a summary of the data link properties specified.

Sophisticated database systems such as SQL Server or Oracle may be configured in sophisticated ways. The information in the Connection tab and, possibly, the Advanced tab reflect the sophistication of such systems. If you are unfamiliar with the specific expectations of the SQL Server or Oracle or other provider that you wish to use, you may have to consult with the database administrator in charge of the installation to determine the correct settings to use.

Connecting Via ODBC

ODBC as a database connection technology preceded OLE DB, so there are more ODBC drivers for different types of databases than there are OLE DB drivers. Manifold System connects to ODBC data sources through whatever ODBC drivers are installed on the computer.

ODBC is easy to use if we remember that the slightly tedious part is creating a new "source." Some ODBC sources are quite simple and consist of specifying what type of database driver to use (dBase, Access, etc.). Other ODBC sources, such as connecting to SQL Server via ODBC, are more complex and require what type of driver to use, which database is to be opened and from which machine or server it is fetched. In the case of some systems, such as SQL Server and Oracle, there are both OLE DB and ODBC drivers.

Set up ODBC data sources using the Windows ODBC Data Sources administrator dialog that is called by clicking on the ODBC Data Sources icon in the Windows Control Panel. This icon is located in the Administrative Tools folder in the Control Panel in Windows 2000.

Using UDL Files and OLE DB Data Sources

A Microsoft UDL file is simply a pre-packaged shortcut to an OLE DB data source. We create a UDL file as noted below. We can then use OLE DB data link properties dialogs to configure the data link that will be used by the UDL file. Note that when opening an OLE DB data source or creating a UDL file we end up using exactly the same OLE DB dialogs.

Why bother with creating a UDL file if doing so requires us to use the OLE DB dialogs anyway? We would create a UDL file as a shortcut if we thought we might wish to use that same OLE DB data source again in the future in the same way. The UDL file saves our OLE DB dialog choices so that in the future the UDL may be "opened" like any other file using the simple File - Import - Table or File - Link - Table dialogs. This simplifies subsequent connections to the data source: simply click open the UDL file with no need to deal with the OLE DB dialogs.

Creating and Using a UDL File

Creating a UDL file is a standard Microsoft procedure that is documented in Windows (search for "UDL" in Windows Help). Suppose we have an Excel spreadsheet that contains a table and we would like to connect to that table using a UDL file. Our spreadsheet is called MySheet.xls. We would proceed as follows:

In Windows Explorer, right click and choose New - Text Document. Create a text document called MySheet.udl. Windows will complain about changing the extension from .txt to .udl. That's OK.
Double-click on MySheet.udl to open the Data Link Properties dialog.
Configure the Data Link Properties dialog as shown in the illustration below. Press OK. The UDL will now contain a data link to the Excel spread sheet.
Launch Manifold and choose File - Import - Table and then open MySheet.udl. Choose the table desired.

UDL files are normally used to automate more complicated connections than shown above. They may be used to provide user credentials such as a user name and password or to use a complex connection string when required. Windows 2000 and XP both include documentation for creating UDL files as part of Windows help. Other versions of Windows may not include this documentation, although the capability is there if you have installed any one of many packages that install the Microsoft data access routines that enable universal data links.

Performance

The internal Manifold database engine used to maintain imported tables is almost always substantially faster than external database systems. Although Manifold includes a copy of Microsoft's "Jet" database engine (the same used in Access), Jet is not used to maintain tables that are imported into Manifold. Jet is used only as an accessory to handle external files. All imported tables are managed using the Manifold engine and all queries are executed using the Manifold SQL engine.

External database systems such as Access (using Jet), SQL Server or Oracle are forced by their general purpose nature into maintaining a lot of overhead that Manifold need not encounter in a purpose-built engine. For example, the overhead required for handling terabyte-sized databases means that SQL Server will not usually operate with databases under two gigabytes as rapidly as Manifold's internal engine. However, as a practical matter essentially all databases encountered in desktop GIS will be well under two gigabytes in size.

When linking external tables into Manifold one ends up using whatever database engine or system is associated with those external tables. Performance will therefore almost always decrease when using linked tables as compared to the speed of imported tables, unless the external tables are very large and the provider is a sophisticated engine such as SQL Server. Even if they are not as fast as Manifold's dedicated engine for tables smaller than two gigabytes, modern database systems such as SQL Server nonetheless are still very fast. Therefore, it is not likely that any performance difference between imported tables or linked tables will be noticed with smaller tables.

Working with SQL Server and Oracle

Very important: When working with server-based OLE DB providers such as SQL Server and Oracle, users are strongly encouraged to maintain primary keys in all tables linked into the Manifold project. A side effect of how such servers interact through OLE DB is that if the table does not have a primary key, performance will be greatly reduced.

Linking to Oracle Tables

When linking to tables resident in an Oracle DBMS, make sure to follow these tips:

Always set the Allow saving password option. If this option is not set, the system will connect for the first time but not at all other times. The intricacies of the OLE DB / ODBC interaction that takes place within the Microsoft OLE DB Provider for ODBC Drivers are such that it is almost never possible for the Manifold to jump into the middle of the connection process and prompt the user for a username and password.
Do not use the native Oracle ODBC driver. Use the Microsoft OLE DB Provider for ODBC and use Microsoft ODBC for Oracle. The native Oracle drivers are limited to forward-only cursors (which is by far the simplest and the lowest performance type of database cursor allowed in ODBC drivers) and thus are unusable in Manifold or many other programs, such as Microsoft Access. Future editions of Manifold will add special routines to allow use of the Oracle driver.

Working with OLAP

OLE DB drivers allow connections to many data sources besides simple database tables. For work with OLAP, for example, Microsoft's SQL Server Decision Support Services (the Microsoft OLAP package) has an OLE DB that can be used to import or link a "table" that pulls data from the OLAP cube. The driver supports Microsoft's MDX language that can be used to fetch information from OLAP cubes. The MDX language is very rich and thus allows sophisticated work with OLAP cubes. Needless to say, the driver works fine with Manifold.

Windows Locale Settings and Table Import

Manifold's table import routines will recognize Windows locale settings for .mdb, .xls, some .dbf files (depending on drivers used) and some other formats and will automatically convert text fields to Unicode when the database file locale is incompatible with current regional options. For example, an .mdb database created with Spanish locale settings will have text fields automatically converted to Unicode if it is imported on a Windows machine employing French settings.

When Logins and Passwords are Required

When linking tables via a connection to some database providers such as Oracle or SQL Server the connection may fail without proper user credentials if security settings in the database provider require a username and password. In such cases Manifold will raise a login dialog that allows specification of a user name and password.

The dialog includes a Remember username and password checkbox that is off by default. Check this box to save the provided user name and password with the table within the .map file. This will enable automatic linking using these credentials the next time the .map file is opened. Note: the user name and password will be saved with the table in encrypted form within the .map file. It is strongly recommended that any such .map files containing your user credentials be protected by setting appropriate Windows access privileges on the .map file or the folder that contains it.

Effect of Tools - Options Settings

When importing tables, keep in mind that the default setting in Tools - Options is to not import empty columns. Check the Import empty columns in tables option to import tables with columns that do not contain any values in the originating table.

Note also the setting of Trim strings imported from external databases (on by default). This will delete leading and trailing token separator characters from imported table strings.

The Trim strings option (just like the Trim table transform operators) removes characters listed in the Tool Properties pane's list of token separators. By default, these are the "white space" characters consisting of the space character, tab, newline and carriage return. Note that adding any other characters to the separator list in Tool Properties will subject them to removal as well if they occur as leading or trailing characters.

Linked Tables and Disconnects

One risk with using linked tables is that the data source might not be available when we want to use the table within our project. For example, the linked table may reside within a database server on a different machine that might experience a hardware crash. To deal with such situations, Manifold uses a conservative strategy when linked tables cannot reach their data sources.

Linked tables that can not reach their data sources will not break any operations in which they participate. Instead, the linked table absorbs the error returned by the database access layer and will configure itself with the same columns that were in use the last time it was opened and the loading process will continue. As a result, occasional crashes of a database server will not break relations between the linked table and other tables in the project. Columns within the linked table will be filled with default values until the server comes back to life and we can reconnect to the server using the Refresh Data command.

One risk of this "fail safe" strategy for dealing with disconnects is that it is possible for a linked table to exist within a Manifold project after some external agency (such as an unwary database administrator) has eliminated the table from its data source. In such cases we will have to delete the linked table manually from the project. Manifold will not automatically delete such tables because in such (hopefully rare) circumstances it is better to have to manually delete in order to be able to take any necessary clean up measures should the linked table have been used in any relations, scripts or elsewhere in the project.

Notes

In a perfect world, every OLE DB provider or ODBC driver or external DBMS they serve that is installed on our systems would work without error. Regrettably, in real life the various database drivers and providers installed in our systems will contain bugs. Even in the case of the high quality drivers provided by Microsoft, one only need read the Microsoft Knowledge Base and the release notes for various Microsoft service packs to see that many bugs have been identified in such drivers. Manifold uses Microsoft drivers and OLE DB providers to connect to various types of database tables. If there are errors in the Microsoft drivers, such errors will affect Manifold as they would any other application.

If you suspect a database driver bug, take time to research the issue within the online Microsoft Knowledge Base. A useful experiment might be to import the table instead of linking to it and seeing if the problem persists. If it goes away, that is evidence that the problem lies in the external database system or is a bug in the drivers for that system. To reduce the likelihood of bugs in external software make sure you have applied the latest service pack for the external database system you are using.

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 can contain numerous bugs. For example, even as well crafted an SQL as Jet SQL used within Microsoft's Access products contains numerous 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.

Enterprise Edition

If you have installed the Enterprise Edition of Manifold System you will have an additional, very powerful means of importing and linking tables. Enterprise Edition allows centralized storage of components, including tables, within Enterprise servers that are hosted by external database systems, such as DB2, Oracle or SQL Server. Tables may be imported from an Enterprise server or linked into the project while remaining resident on the Enterprise server.

See the Enterprise Edition topic for more information.

See Also

Database Console

View - Refresh Data

Back to Manifold Home Page