EloquaJdbc Driver
Introduction
The EloquaJdbc driver allows reading objects from Eloqua via JDBC.
Using SQL queries, data from an Eloqua object can be extracted and processed just like from a database table.
Since not all objects in Eloqua can be read via an API, the user must understand the various API options. To support the user, the driver provides the metadata of Eloqua objects so that per API it is communicated which objects are respectively available.
Here is an overview of the "database objects" of the driver:
- Schema: Either the API such as rest, odata and bulk or system for the metadata tables
- Table: With schema system, the name of a system table. Otherwise, an object from the Eloqua API denoted by the schema
- Columns: The respective columns of the object or the system tables
- Identifiers: Identifiers such as schema names, table names or column names are not case sensitive in Eloqua. Identifiers should therefore not be specified in double quotes.
With the following query, for example, all records of the emailgroup object can be read using the driver via the odata interface:
SELECT email_group_id
, email_group
, email_group_description
, is_deleted
, last_modified_date
FROM odata.emailgroup
SQL Syntax
Deviating from the SQL syntax presented under SQL Parser, there are the following deviations/additions:
- The FILES() functionality is not supported. Thus, the pseudo-columns DIRECTORY and FILENAME are also not supported.
- The keywords COLUMNS, HEADLINE, SEPARATED BY, QUOTED BY and ENCODING are not supported.
- The FILTER keyword is supported. However, the syntax to use differs per API. If a filter is specified, the selection is performed at Eloqua. With a WHERE condition, all data is loaded and then filtered in the JDBC driver (performance).
- The ORDERBY keyword is only supported in the ODATA API.
System Tables
The driver supports system tables. These allow loading metadata about Eloqua objects, columns and primary keys. The system tables are always under the schema system.
The metadata of the system tables themselves is not returned when querying the system tables or the JDBC metadata methods getTables, getColumns and getPrimaryKeys.
Column values for catalog, schema, table, column identifiers, etc. are always in lowercase.
The following system tables exist:
| Schema | Table Name | Description |
|---|---|---|
| system | table_list | A list of all objects in Eloqua |
| system | column_list | A list of all columns of all objects in Eloqua |
| system | primary_key_list | A list of all primary key columns of all objects in Eloqua |
System Table for Table List
Here the metadata of all available objects in Eloqua is provided.
??? The set of visible objects depends on the permissions that the technical user possesses with which the connection is established. This applies to all other system tables as well.
The following metadata columns are available for selection:
| Name | Data Type | Value | Remark |
|---|---|---|---|
| table_cat | VARCHAR(100) | datasqill | The object catalog is always datasqill |
| table_schem | VARCHAR(100) | The object schema depends on the API that provides the object | |
| table_name | VARCHAR(100) | Object name | Name of the object |
| table_type | VARCHAR(100) | TABLE | Always has the value TABLE |
| remarks | VARCHAR(4000) | Description | A description of the object |
| type_cat | VARCHAR(100) | NULL | Not supported. Always NULL |
| type_schem | VARCHAR(100) | NULL | Not supported. Always NULL |
| type_name | VARCHAR(100) | NULL | Not supported. Always NULL |
| self_referencing_col_name | VARCHAR(100) | NULL | Not supported. Always NULL |
| ref_generation | VARCHAR(20) | NULL | Not supported. Always NULL |
The same ResultSet is returned when calling getTables via the JDBC interface java.sql.DatabaseMetaData.
System Table for Column List
Here the metadata of all columns of all available objects in Eloqua is provided.
The following metadata columns are available for selection:
| Name | Data Type | Value | Remark |
|---|---|---|---|
| table_cat | VARCHAR(100) | datasqill | The object catalog is always datasqill |
| table_schem | VARCHAR(100) | The object schema depends on the API that provides the object | |
| table_name | VARCHAR(100) | Object name | Name of the object |
| column_name | VARCHAR(100) | Column name | Name of the column |
| data_type | Integer | JDBC data type | The JDBC data type (see java.sql.JDBCType) |
| type_name | VARCHAR(100) | Data type | The name of the data type (see below) |
| column_size | Integer | Length | The length of the column |
| buffer_length | Integer | 0 | No longer supported by JDBC. Always 0 |
| decimal_digits | Integer | Decimal places | Only for data types with decimal places. Otherwise NULL |
| num_prec_radix | Integer | 10 | Always 10 |
| nullable | Integer | 2 | Stands for unknown (columnNullableUnknown) |
| remarks | VARCHAR(4000) | Comment | Column comment |
| column_def | VARCHAR(4000) | NULL | Default value (not supported) |
| sql_data_type | Integer | NULL | not supported |
| sql_datetime_sub | Integer | NULL | not supported |
| char_octet_length | Integer | NULL | not supported |
| ordinal_position | Integer | Position | Column number starting with 1 |
| is_nullable | VARCHAR(18) | empty string | not supported |
| scope_schema | VARCHAR(100) | empty string | not supported |
| scope_table | VARCHAR(100) | empty string | not supported |
| source_data_type | Integer | NULL | not supported |
| is_autoincrement | VARCHAR(10) | empty string | not supported |
| is_generatedcolumn | VARCHAR(10) | empty string | not supported |
Data types used:
| Name | Length | Remark |
|---|---|---|
| BIGINT | 0 | Integer. Can also be used as Integer data type |
| BOOLEAN | 0 | Boolean value true / false |
| DATE | 0 | Date |
| DECIMAL | as specified | Number with integer and decimal places. The number of decimal places is in decimal_digits |
| TIMESTAMP | 0 | Timestamp. The timestamp is always in local timezone |
| VARCHAR | as specified | Character string |
The same ResultSet is returned when calling getColumns via the JDBC interface java.sql.DatabaseMetaData.
System Table for Primary Key List
Here the metadata of all primary key columns of all objects in Eloqua is provided.
The following metadata columns are available for selection:
| Name | Data Type | Value | Remark |
|---|---|---|---|
| table_cat | VARCHAR(100) | datasqill | The object catalog is always datasqill |
| table_schem | VARCHAR(100) | The object schema depends on the API that provides the object | |
| table_name | VARCHAR(100) | Object name | Name of the object |
| column_name | VARCHAR(100) | Column name | Name of the primary key column |
| key_seq | Integer | Position | Position in the primary key starting with 1 |
| pk_name | VARCHAR(100) | = pk_$table_name | Name of the primary key. Automatically generated |
The same ResultSet is returned when calling getPrimaryKeys via the JDBC interface java.sql.DatabaseMetaData.
Eloqua APIs
The APIs are very different in all aspects. Metadata is queried differently, filters are implemented differently, data volumes are strongly limited in the rest API and the process flows are completely different.
This JDBC driver takes on the task of reducing these different access paths to a unified interface.
Rest API
The rest API provides only a few objects
Bulk API
In the bulk API, both custom objects and system objects are offered.
It is possible to filter the data (using the FILTER statement). The Eloqua Expression Language is used for this. See Eloqua Expression Language. Attention: The bulk API has a limit of 250 columns per SELECT.
Odata API
The filter conditions of the odata API use different syntax than the filters in the bulk API. They are specified according to the OData specification. Under Odata Grammar you can find the entry point to the grammar for the filter by searching for the definition of boolCommonExpr. It may be easier to understand by looking at Microsoft's documentation Filter in odata for example. Basically, you do not use =, !=, >, etc. operators, but eq, ne, gt, etc.
The ORDERBY keyword can be used. It passes the specified text to the API via the $orderby parameter. Example:
SELECT *
FROM odata.user
FILTER user_email_address ne null
ENDFILTER
ORDERBY user_name