OleDb Agent OleDb AgentData Flask works with OleDb and Odbc client drivers for data modification using the rowset cursor. SQL is usually used for data definition.
Grid and SQL preview's are available. Grid previews show changes that would be made through the client cursor. SQL previews will show data definition that would be executed. Previews allow you to compare the spreadsheet with external data, without commiting external changes.
The SQL preview will include data modification commands, which can be executed, depending on the availability of an update key.
Data Link Dialog. Connection String UDL Files.
SQL Support - Information for All providers.
| Microsoft Access | |
| Microsoft Excel | |
| Microsoft FoxPro, dBase | |
| My SQL | |
| Microsoft SQL Server | |
| Oracle | |
| Postgre SQL | |
| SQLite |
If an OleDb provider is not listed here, data modification should work through the client cursor. Initialize a connection string, then open a table or issue a SELECT query.
Data definition, where you would create and alter tables, will depend on compatibility with the default DDL syntax.
Figure OleDb-1: OleDb tab of the Data window.
Included in MDAC is support for Access .mdb files, Foxpro/dBase .dbf files, Excel .xls spreadsheets, MS Sql Server, Oracle. Available providers can be seen by opening Providers in the OleDb tab of the Data dockable window.
Windows 5.1 (XP) ships with MDAC 2.7, Windows 5.0 (2000) ships with MDAC 2.5. Microsoft Windows 5.1.26 (XP SP2) includes MDAC 2.8.
Flask Grid Files (*.dfg) that use the OleDb agent will contain a connection string. The connection string identifies the external database that the grid links to.
There are a number of ways to build a connection string:
| File/Open Data allows a filename to be selected, and a connection string built based on that name (useful for xls/mdb/dbf files). |
| Expanding Providers in the OleDb tab of the Data dockable window (figure OleDb-1). |
| Choosing prepared UDL files (figure OleDb-1). Choices will appear below Providers in the Data window. |
| Picking Open from the context menu of Providers (figure OleDb-1) will open the Data Link Properties dialog (figure OleDb-2). |
| Picking Properties in the data link dialog (figure OleDb-4) of an existing grid. |
| Entering the connection string manually (external) (figure OleDb-3). |
Once the connection string is constructed, it no longer matters which method was used to create it.
Figure OleDb-2: OleDb tab of the Data window.
Pick File/Data Link to view, test, and modify the data link of a grid that uses the OleDb agent.
OleDb-3 Provider Context Menu.
Connection strings can be saved to, or loaded from, UDL Data Link files using the OleDb context menu.
To save the connection string used by a Grid into a .udl file, select Connection/String from the Providers context menu. The connection string of the active grid will be the default connection string for a new, uninitialized data source. Choose UDL Save UDL Save from the resulting data source item.
Additional information regarding UDL files here.
Three kinds of items appear in the OleDb list in the data window:
| Provider | Drivers that provide data services through OleDb interfaces. All entries shown in figure OleDb-1 are provider items. Expanding the first entry (Providers ) will open a list registered on the current computer. Prepared .udl files, such as Access (OleDb) or Sql Server (OleDb) identify common provider choices and settings. |
| Source | Adding initialization information to a provider will create a data source. For example if you initialize Access (OleDb) by choosing C:\Data\Nwind.mdb, a Source will be created, and appear as Source - C:\Data\Nwind.mdb. Data Sources appearing in bold are initialized. |
| Table | When a data source is initialized, it will be queried for table information. Available tables and views will be listed. A New Query entry is available with the tables to enter an Sql Query. |
SIMPLE - Rowset is a special Table item. Some providers, like simple (OSP) providers, use filenames as table names. Use the simple provider to selection to manually enter a table name. A file name selection dialog will be presented when initializing SIMPLE - Rowset, but any string may be entered.
The existence of a prepared .udl does not guarantee that a provider is available. For example, if you Expand Providers/MSDASQL Enumerator and the MySql driver is not listed, attempting to initialize the prepared MySql (Oledb) .udl entry will fail until the driver is installed.
Hold down the Shift key to include system tables in the list when initializing.
If a provider type is not specified in the column properties dialog, Data Flask will map to a provider type based on more generic OleDb type information is a column needs to be created. Generic type information is considered when mapping to a provider specific type.
Generic OleDb provider types
Generic type information is identified by a basic type, such as STR, I4, DATE, BOOL. Column Flags may also be specified.
Consider MySQL provider types char and varchar: The default WSTR, size 50 maps to varchar(50). If ISFIXED is chosen, char(50) will be selected. Choosing ISLONG on a STR size over 255 will cause long varchar to be selected.
The following column flags have meaning when using the Write Data and Reload Data commands:
Column Data flags (figure 3-7) are set from the Advanced Column Properties Dialog (figure 3-6).
When a column is created, these properties will be requested. Support for these settings is provider specific. Where the provider does not support a column feature, these flags can still be set to identify update key columns.
ISAUTOINCRIMENT columns will be read when a row is appended to the external database.
Whether the KEYCOLUMN flag should be set, or a Primary Key index created, is provider specific. Index usage is more common.
Flags that have an affect on column type are
| ISLONG | Can be very large. Memo, Long Binary, image, ntext. |
| ISFIXED | Identifies a fixed width column (char in MsSql). |
| ISAUTOINCREMENT | Value generated by the database (frequently a primary key). |
| ISUNIQUE | Value must be unique (if not supported by index). |
| ISNULLABLE | Can be set NULL. |
| ISNULLABLESET | Indicates ISNULLABLE is valid. Default otherwise. |
| KEYCOLUMN | Primary Key column (if not indicated by an index). |
Support for these values is provider specific. A recommended way to determine what settings a provider supports is to experiment by loading a table from an existing database. Then review the Column Data settings, and the indexes in the Data Link.
OleDb-6 Admin Context Menu.
To see all OleDb flags, press SHIFT when pressing [...] button.
I'd love to hear from you if you are using a provider regularly that is not in the list at the top of this page.
Editorial comments : Tweaking DDL and identifying supported OleDb interfaces can be a simple thing, once I hook into a new source. Currently I have a dedicated test machine that runs MySQL, PSQL, MsSQL, Oracle. The others are file drivers. File based providers such as Access and FoxPro, that are not native SQL providers support both data definition (CREATE, ALTER) and data modification (INSERT, UPDATE, DELETE) commands. It was a surprise to discover just how extensive SQL support was for the file based drivers. It was also a surprise to me just how utterly horrible support for the OleDb native data admin interfaces is. Even the best supported driver, MSJet, failed to support IAlterTable except for renaming a column (as it happens there is no way to rename a column using JetSQL, so for that activity alone, dataflask uses IAlterTable). Clearly in writing the OleDb agent, I ignored the CW memo suggesting to avoid using native OleDb interfaces. CW has the right conclusion, but for the wrong reasons. The difficulty is not in using OleDb interfaces, it is discovering how each provider deals with the feature duplicity of executing DML as compared to native admin interfaces. The answer is that DML support far exceeds interface support, and Data Flask 1.1 will now prefer to use DML rather than interfaces preferred in 1.0. The good news is that this Prefer DML approach aligns with the direction MDA is taking with Sql Native Client. Microsoft can call it refactoring. I call it simply lopping off dead branches.
It also means that from version 1.1, Data Flask can route SQL to a text document, where users can validate it, tweak it, and choose to execute it in a different context, such as with a native front end.
Data
Flask Copyright © 2006 Interscape
Corporation