Reads a table from an ODBC datasource.
Launch the execution any free-form SQL statement(s).
ODBC is a generic technique to access the content of almost any relational databases.
ODBC is the oldest technique available to access data contained into relational databases.
All the databases have an ODBC driver.
Before executing the ReadODBC Action, you must first define an ODBC connection: See the section 5.1.6. to know how to create an ODBC connection from Anatella to your database. Once you have a working ODBC connection, you can directly execute any SQL command: For example: Let’s extract the table “censusincome” from your database:
What happens if your SQL query has some error in it? Don’t worry! Anatella will give you a complete description of the error inside the “Log window”:
The ODBC reader Action submits your SQL statments 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). The queries including JOIN’s are usually too CPU-intensive (on large tables) and, very often, it’s faster to compute the joins with Anatella.
2.Some database engines 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 ODBC error message explaining the nature of the error, allowing you to track and resolve the error.
3.The ODBC error messages are produced by your database. Please, refer to your database documentation for more information about the reported errors.
There are 6 operating modes for the ReadODBC Action:
1.Direct, Static SQL Query
2.Direct, Static SQL Query (each statement executed in its own query)
4.SQL Queries from Input Pin
5.SQL Queries from Input Pin + Input Pin Data
6.SQL Queries from Input Pin + Input Pin Data ; Returns "_Status" only
These 6 operating modes are described in the remainder of this section.
Operating Mode 1: Direct, Static SQL Query
This text box can include many different SQL statements (the statements are typically separated from each other with a “;” character):
In “Operating Mode 1”, the whole content of the text box is sent in “one go” to the ODBC driver for execution. Unfortunately, some databases (e.g. Teradata) are limited: i.e. They cannot run more than one SQL statement at-a-time. This means that, for example, if your text box contains two SQL statements, the Teradata database will return an error and the graph execution will stop. To bypass this limitation of the database, there now exists an “Operating Mode 2” inside Anatella.
Operating Mode 2: Direct, Static SQL Query (each statement executed in its own query)
When using “Operating Mode 2”, Anatella splits the content of the text box in many different SQL statements (currently the “split procedure” is primitive: Anatella just splits the text box simply looking for the “;” character). Each SQL statement is then sent one-by-one to the ODBC driver. This allows to bypass the limitation of some ODBC drivers that are only able to execute one SQL statement for each string that they receive.
For example, instead of writing:
… you can simply write:
In this way, the “union” is performed by Anatella (and not by the database engine). This allows more flexibility because there are nearly no restrictions in Anatella on the “union” operation (as opposed to the union performed by the database).
Operating Mode 4: SQL Queries from Input Pin
Operating Mode 5: SQL Queries from Input Pin + Input Pin Data
For example, these last 2 modes are usefull when you want to extract from the relational database some specific rows about some specific customers:
Operating Mode 6: SQL Queries from Input Pin + Input Pin Data ; Returns "_Status" only
This mode forwards the input table to the output pin, execute the SQL command given in input and adds a new column named “_status”. The content of the column “_status” is a number code:
No error + the SQL command returned some data
No error + the SQL command did not return any data
SQL command is Still executing?
The SQL command that was received was empty?