My account Book a demo

Custom SQL Connectors

Piotr Kononow - Dataedo Team Piotr Kononow 11th November, 2023

To ship connectors faster and without the need to release new version Desktop, we build a "custom (SQL) connector" functionality. Custom connectors are saved and distributed in proprietary .dataedocon files (XML format). Connectors are built and delivered by Dataedo team or can be build by users (they currently require signing by Dataedo team).

What is a custom connector

Supported metadata

  1. Tables, Views and all the tabular objects
  2. Columns
  3. Stored procedures, functions and all the
  4. Parameters
  5. Foreign keys/relationships
  6. Triggers
  7. Unique constraints
  8. Dependencies

Supported features

  1. Data profiling
  2. Fetching lookup values

Local vs shared connectors

Custom connectors can be added in two different ways:

  1. Local - stored on local disk. Reimport of the connection using this connector from other machine will not work.
  2. Shared - stored in repository. Reimport will work on all the machines.

Existing connectors

Dataedo is shipped with a number of predefined custom connectors. This list will be regularly extended.

Image title

Requesting a connector

You can request a custom connector for your source. Our team will do its best to build it for you. Any existing queries and helping with testing/debugging will help that process.

Please note that custom connectors only support SQL sources. NoSQL, BI, ETL sources cannot be supported.

To request a custom connector contact our support team.

Connector format

Custom connectors are shared as a proprietary XML file with .dataedocon extension.

Adding custom connector

If you have a .dataedocon you can add it to Dataedo Desktop opening Add > New connection > Custom Connectors and clicking New Custom Connector.

Image title

Then you select the file from your disk and choose where it should be stored.

Image title

Removing custom connector

To remove connector go to Add > New connection > Custom Connectors > Manage Custom Connectors. Then in the new window select connector, click Remove and confirm.

Image title

Building your own custom connector

You can build a custom connector by preparing a .dataedocon file as defined to File structure section.

Please note that currently for security reasons every connector has to be signed by us before adding to Dataedo.

Where local connectors are stored?

Local connectors are stored in the following folder:

C:\Users\\AppData\Roaming\Dataedo {mainDataedoVersion}\Connectors>\Connectors*

mainDataedoVersion - the main version number of Dataedo, for example, it is 23 for version 23.1 and 24 for version 24.1.

File structure

Image title

  • Connector - name of the connector
    • CustomConnectorVersion - version, e.g. 1.0
    • ConnectorCode - defines what native connector should be used ODBC, MYSQL, ORACLE (currently we support only ODBC)
    • SupportedDBMSVersions - minimum and maximum supported version of datasource
    • SqlDialect - Code of the SQL dialect for parsing: TSQL (Transact-SQL - SQL Server, Azure), MySQL, PLSQL (PL/SQL - Oracle, DB2), PostgreSQL, Snowflake Full list
    • VersionQuery - SQL query that returns version of the DBMS server
    • MetadataQueries - set of SQL queries that return metadata from the source

Queries specification

ObjectsQueries Queries for selecting objects are placed in Query tags with proper name attribute (TABLE, VIEW, PROCEDURE, FUNCTION) For example for tables it looks like <Query name="TABLE">.

Each of them should return columns with following names:

  • DATABASE_NAME
  • SCHEMA
  • NAME
  • TYPE
  • SUBTYPE
  • DESCRIPTION
  • MODIFY_DATE
  • CREATE_DATE
  • DEFINITION
  • FUNCTION_TYPE

Each of the columns above is mandatory. The columns DESCRIPTION, MODIFY_DATE, CREATE_DATE, DEFINITION, and FUNCTION_TYPE may have a NULL value if they do not apply to the object or if their values are not available.

TABLE

The query for selecting metadata about tables should be enclosed within the <Query name="TABLE"></Query> tags. The query is expected to retrieve all previously mentioned columns. Columns "DEFINITION" and "FUNCTION_TYPE" do not apply to table objects and should have a value of NULL.

VIEW

The query for selecting metadata about views should be enclosed within the <Query name="VIEW"></Query> tags. The query is expected to retrieve all previously mentioned columns. In the DEFINITION column, you can add a view script, and it will be visible in the Script tab in the Dataedo application. The "FUNCTION_TYPE" column does not apply to view objects and should have a value of NULL.

PROCEDURE

The query for selecting metadata about procedures should be enclosed within the <Query name="PROCEDURE"></Query> tags. The query is expected to retrieve all previously mentioned columns. In the DEFINITION column, you can add a procedure script, and it will be visible in the Script tab in the Dataedo application.

FUNCTION

The query for selecting metadata about functions should be enclosed within the <Query name="FUNCTION"></Query> tags. The query is expected to retrieve all previously mentioned columns. In the DEFINITION column, you can add a function script, and it will be visible in the Script tab in the Dataedo application.

ColumnsQueries

Queries for columns for objects can be defined within one or more Query tags inside the ColumnsQueries tag.

Each of columns query should return columns with following names:

  • DATABASE_NAME
  • TABLE_SCHEMA
  • TABLE_NAME
  • NAME
  • POSITION
  • DATATYPE
  • DESCRIPTION
  • CONSTRAINT_TYPE
  • NULLABLE
  • DEFAULT_VALUE
  • COMPUTED_FORMULA
  • IS_COMPUTED
  • IS_IDENTITY
  • DATA_LENGTH

RelationshipsQueries

Queries for relationships can be defined within one or more Query tags inside the RelationshipsQueries tag.

Each of relationships query should return columns with following names:

  • FK_TABLE_DATABASE_NAME
  • REF_TABLE_DATABASE_NAME
  • FK_TABLE_NAME
  • REF_TABLE_NAME
  • FK_TABLE_SCHEMA
  • REF_TABLE_SCHEMA
  • FK_COLUMN
  • REF_COLUMN
  • ORDINAL_POSITION
  • NAME
  • DESCRIPTION
  • UPDATE_RULE
  • DELETE_RULE

TriggersQueries

Queries for triggers can be defined within one or more Query tags inside the TriggersQueries tag.

Each of triggers query should return columns with following names:

  • TRIGGER_NAME
  • TABLE_SCHEMA
  • TABLE_NAME
  • DATABASE_NAME
  • TYPE
  • DISABLED
  • DEFINITION
  • DESCRIPTION
  • ISBEFORE
  • ISAFTER
  • ISINSTEADOF
  • ISINSERT
  • ISUPDATE
  • ISDELETE

UniqueConstraintsQueries

Queries for unique constraints can be defined within one or more Query tags inside the UniqueConstraintsQueries tag.

Each of unique constraints query should return columns with following names:

  • DATABASE_NAME
  • TABLE_NAME
  • TABLE_SCHEMA
  • NAME
  • TYPE
  • COLUMN_NAME
  • COLUMN_ORDINAL
  • DESCRIPTION
  • DISABLED

ParametersQueries

Queries for parameters can be defined within one or more Query tags inside the ParametersQueries tag.

Each of parameters query should return columns with following names:

  • DATABASE_NAME
  • PROCEDURE_NAME
  • PROCEDURE_SCHEMA
  • NAME
  • POSITION
  • PARAMETER_MODE
  • DATATYPE
  • DESCRIPTION
  • DATA_LENGTH

DependenciesQueries

Queries for dependencies can be defined within one or more Query tags inside the DependenciesQueries tag.

Each of dependencies query should return columns with following names:

  • REFERENCING_TYPE
  • REFERENCING_SERVER
  • REFERENCING_SCHEMA_NAME
  • REFERENCING_DATABASE_NAME
  • REFERENCING_ENTITY_NAME
  • REFERENCED_SERVER
  • REFERENCED_DATABASE_NAME
  • REFERENCED_SCHEMA_NAME
  • REFERENCED_TYPE
  • REFERENCED_ENTITY_NAME
  • IS_CALLER_DEPENDENT
  • IS_AMBIGUOUS
  • DEPENDENCY_TYPE
Contact support Report issue
Found issue with this article? Comment below
Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.