Reads a table from an OleDB datasource.
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 button: A (Microsoft-Generated) wizard open:
This wizard lists all the OleDB providers installed on your computer.
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 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 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.