Reads a table from an OleDB datasource
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 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.