<< Click to Display Table of Contents >> Navigation:  5. Detailed description of the Actions > 5.2. Input Actions > 5.2.2. Generic ODBC reader

 
Icon:

 
Function: readODBC
 

Property window:

 

 

Short description:

 

Reads a table from an ODBC datasource.

 

Launch the execution any free-form SQL statement(s).

 

Long Description:

 

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)

3.SQL Queries computed using JavaScript code

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.

 

 

Operating Mode 3: SQL Queries computed using JavaScript code
 

 

 

 
 

This third operating mode allows you to execute SQL statements that are generated using a given JavaScript code. This has several benefits over simply writing directly a “constant” SQL statement:

 

1.You can use inside your JavaScript code some Global Variables. This allows you to parameterize your data-transformation-graph (see section 5.1.5 about Global Variables).

 

2.Sometime, it’s easier to write the (JavaScript) code that generates a very complex and long SQL statement than to try to write directly the statement.

 

3.When your SQL statement is a union of many different statements, it’s easier to use a JavaScript code.
 

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: