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:




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.


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:






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



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:





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





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:




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




Click the “Ok” button. You obtain:





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.