5.2.3. Generic OleDB reader

<< Click to Display Table of Contents >>

Navigation:  5. Detailed description of the Actions > 5.2. Input Actions >

5.2.3. Generic OleDB reader

 
Icon: ANATEL~2_img149

 

Function: readOleDB
 
Property window:

 

ANATEL~2_img147

ANATEL~2_img148

 
Short description:

Reads a table from an OleDB datasource

 

Long Description:

OleDB is a generic technique to access the content of almost any relational databases.

 
 

Initially, the objective of OleDB was to supersede and to replace the older ODBC technique that was invented in 1991. When Microsoft introduced OleDB, Microsoft claimed that OleDB will run 100 times faster than ODBC in may common situations. Unfortunately, OleDB was a flop: Most of the database vendors ignored it and never produced any OleDB drivers. A few years later, Microsoft introduced ODBC v3 that is providing the same benefit as OleDB (i.e. fast INSERT speed and a database connection based on a simple Connection-String). ODBC v3 was (and still is) a great success. This success can be explained by the fact that ODBC v3 is retro-compatible with the old ODBC v1: i.e. If you have an application (such as Anatella) that is using ODBC v3, it can still access ODBC v1 drivers. This means that, with ODBC v3, you can access any tables stored in any database (whatever the ODBC version: v1,v2,v3). The final position of Microsoft with regard to OleDB is ambiguous: At one point they declared it obsolete and deprecated, but a few month later OleDB was declared un-deprecated? Anyway, because of the lack of support from most database vendors and the “deprecation” declared by Microsoft, OleDB is mostly a dead technology right now.

 

All OleDB providers are required to produce as output fully compliant Unicode Strings.

 

With the proper OleDB driver installed on your computer, you can access these databases:

SAS, AS/400, VSAM, VSAM-VSE, VSAM-MVS, dBase, Acceler8-DB, Microsoft SQL Server, ALLBASE, Btrieve, C-ISAM/D-ISAM, CorVision, DB2, IBM DB2/400 on iSeries (AS/400), Enscribe, IDMS, IMAGE, IMS/DB, Informix, Informix OnLine Dynamic Server, Ingres/Ingres II, Jasmine, jBASE, MUMPS, NonStop SQL/MP, ObjectStore, Oracle, QueryObject, Rdb, Red Brick, RMS, Sybase, SQLite, Firebird/Interbase, MySQL, ADABAS, Approach, Btrieve, DataFlex, DBMS (CODASYL), DMS II (CODASYL), DMS 2200 (CODASYL), Domino, FoxPro, IMS, Lotus, Micro Focus, Microsoft Access, Microsoft Excel, Paradox, PowerFlex, PostgreSQL, Centura, Datacom, IDMS, OS/390 sequential files, Pervasive SQL, Progress, SAP, Advantage Database Server, ADDS, D3, General Automation, Mentor, mvBase, mvEnterprise, Pick, Reality, Reality/X, Sequoia, Unidata, Universe, Ultimate, UltPlus, SQLBase, Essbase, Peoplesoft, Lawson, Active Directory Provider, Analysis Services Provider, Commerce Server Provider, Provider for Internet Publishing, Index Server Provider, SNA Server, Office documents, Teradata, OpenLink Virtuoso, Microsoft Exchange 5.5 and 2000. MAPI compliant sources, CodeBase Server, Clipper, XML, HTML tables, LINC II, MCP Data Files, Successware Engine, Apollo Database Server, Outlook 2000.

 

Let’s give a small example of usage. The first thing that you want to obtain when working with an OleDB datasource is an “OleDB connection string”. An “OleDB connection string” contains all the information required to access the database: the type of database (Access, SQLServer, MySQL, Oracle,...), the database files and name, your login and password. Click on the ANATEL~2_img150 button: A (Microsoft-Generated) wizard open:

 

 

ANATEL~2_img151

 

 

This wizard lists all the OleDB providers installed on your computer.
 

 

ANATEL~2_img8

If you can’t see an OleDB driver that you just installed, it means that you are using Anatella 64-bit and you installed some 32-bit OleDB drivers (or vice versa).

Be sure to install 64-bit OleDB drivers to be able to use Anatella 64-bit

(or install 32-bit OleDB drivers, if you want to use Anatella 32-bit).

OleDB drivers are different from ODBC drivers: i.e. If you installed on your machine some ODBC drivers, it does NOT mean that you also installed at the same time OldDB drivers: These are different.

 

 
For example, if you want to read an Access database, you should select the “Microsoft Office 12 Access Database Engine OleDB provider” (you can also use the “Microsoft Jet 4.0 OleDEB provider”, for older Access versions). In this example, we will open an Access 2007 database: click on “Microsoft Office 12 Access Database Engine OleDB provider” and then click on the “next” button. The next tabs depend on the chosen driver and might vary from one driver to the other.

 
 

Fill-in the “Data source” field:

 

ANATEL~2_img153

 

 

When you click the “Test Connection” button, you should have:

 

ANATEL~2_img154

 

 

Close the test-connection window and click Ok in the “Data-link Properties” window.

 

 
Let’s go back in Anatella. The “OleDB connection string” box is now setup properly:

 

ANATEL~2_img155

 

 
Click on the ANATEL~2_img156 button. A list of table inside your database appears:

 

ANATEL~2_img157

 

 
Click the “Ok” button. You obtain:

 

ANATEL~2_img158

 

clip0023

When using the “SQL mode” access, you can define your SQL statement in 4 different ways:

 
These 4 ways of defining your SQL statement are the same as for the ANATEL~2_img46 readODBC action and are described in more details in section 5.2.2.
 

 

The OleDB reader Action submits SQL script to the database and, then, waits for the output rows that the database can (optionally) return (some SQL statements do not return any results). This means that, in particular:

 

1.If you are experiencing very long running time, it means that the database engine has difficulties processing your SQL statement (…and Anatella is innocent! J ). Try simplifying your SQL statements (adding “top 100” usually helps).

 

2.Some database engine have strong limitations on the number of tables that can be included inside a JOIN statement (e.g. Access2010 is limited to 5 tables, so it’s best to extract one table at a time and compute the JOIN with Anatella). If the SQL statement fails because of some limitations of the database engine, Anatella will display an OleDB error message explaining the nature of the error, allowing you to track and resolve the error. The content of the OleDB error message is produced by your database. Please, refer to your database documentation for more information about the reported errors.