Custom SQL Connectors
To ship connectors faster and without the need to release a new version of Desktop, we built a "custom (SQL) connector" functionality. Custom connectors are saved and distributed in proprietary .dataedocon files (XML format). Connectors are built and delivered by the Dataedo team or can be built by users (they currently require signing by the Dataedo team).
What is a custom connector
Supported metadata
- Tables, Views, and all the tabular objects
- Columns
- Stored procedures, functions, and all the
- Parameters
- Foreign keys/relationships
- Triggers
- Unique constraints
- Dependencies
Supported features
- Data profiling
- Fetching lookup values
Local vs shared connectors
Custom connectors can be added in two different ways:
- Local - stored on local disk. Reimport of the connection using this connector from another machine will not work.
- Shared - stored in the 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.
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 a .dataedocon extension.
Adding custom connector
If you have a .dataedocon you can add it to Dataedo Desktop by opening Add > New connection > Custom Connectors and clicking New Custom Connector.
Then you select the file from your disk and choose where it should be stored.
Removing custom connector
To remove a connector go to Add > New connection > Custom Connectors > Manage Custom Connectors. Then in the new window select the connector, click Remove and confirm.
Building your own custom connector
You can build a custom connector by preparing a .dataedocon file as defined in the File structure section.
Please note that currently for security reasons every connector has to be signed by us before adding to Dataedo.
Where are local connectors stored?
Local connectors are stored in the following folder:
C:\Users\<username>\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
- 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 the 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 the proper name attribute (TABLE, VIEW, PROCEDURE, FUNCTION). For example, for tables, it looks like <Query name="TABLE">.
Each of them should return columns with the 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 columns query should return columns with the 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 relationships query should return columns with the 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 triggers query should return columns with the 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 unique constraints query should return columns with the 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 parameters query should return columns with the 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 dependencies query should return columns with the 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
