Supported database types:
|SQL Server, Azure SQL Database, Oracle, MySQL, MariaDB, PostgreSQL, Amazon Redshift, Snowflake, SQLite||Native / ODBC (64-bit)|
|Google BigQuery, Apache Hive, DB2, DB2 for iSeries (AS/400), Exasol, SAP HANA, Vertica, Generic SQL:2003||ODBC (64-bit) only|
EasyMorph stores connectors (to databases and other systems) in a repository, which is basically just a special file. Connectors and repositories can be created and edited using the Connector Manager. It can be accessed from the Start screen, or by pressing F7.
The Connector Manager allows creating connectors to relational databases, as well as to any other external system supported by EasyMorph, e.g. email services, REST APIs, etc.
Operations that can be performed using the Connector Manager:
Hint: It is possible to protect a shared repository from accidental change by unqualified users by using a write password. Note that users will still be able to use (read) all connectors in the repository even if it's protected with a password. To protect a repository from unauthorised access use standard Windows file permissions applied to the repository file, or provide access to the repository through EasyMorph Server.
A database connector in EasyMorph is a set of connection properties (e.g. server address, port or database name) that is used to connect to particular database. See below example of database connector.
Connectors can be of two types: shared (default type) and embedded.
Shared connectors are stored in a repository. The repository is a file that can be located anywhere (e.g. in a network folder). Therefore, shared connectors in the repository can be used by multiple users in multiple projects. On the first launch EasyMorph automatically creates an empty repository (AppData\Local\EasyMorph\repo.sqlite) if no repository is present. You can switch between different repositories, although EasyMorph works with only one repository at a time. Projects refer to shared connectors by name. Therefore, if you're using the same project with two different repositories (e.g. production and development) make sure the connectors referred to by a project have the same names in both repositories.
Embedded connectors are stored within the project that uses them. Therefore, if the project is opened on a different computer it will use exactly the same connection settings. Embedded connectors can be used in cases when a project needs to be portable or shareable. Note that EasyMorph includes native drivers for some popular databases, therefore projects with embedded connectors that use native drivers work out of the box, without database client software installed.
Once you have created a database connector, you can start querying the database using the Query Editor. The Query Editor opens automatically when you click the "Add data" button and then select "Query database".
Alternatively, the Query Editor can be invoked by pressing F8.
There are three main areas in the Query Editor: column selector (1), filtering condition (2), and result preview (3).
Video: Visual Query Builder
Other operations that can be performed using the Query Editor:
Besides using visual queries, it is possible to import data from databases using custom SQL queries. This can be convenient in cases that can't be accommodated using a visual query — for instance when a join is required.
Switch Query Editor to the "Custom SQL" mode in order to edit a custom SQL query. You can drag table and field names from the list on the left into the custom query text.
Hint: For databases that have a 64-bit ODBC driver but are not officially supported by EasyMoprh you can configure in EasyMorph an ODBC connector with the "Other SQL" dialect. The Query Editor automatically switches to the "Custom SQL" mode when a connector with the "Other SQL" dialect is used.
Sometimes it's necessary to load only a subset of rows from a database table, and this subset is defined by a list of identifiers or keys (primary or foreign) in an EasyMorph table. Such a list can be loaded from another data source (e.g. spreadsheet), or be the result of complex transformation logic in EasyMorph. In this case, another action can be of help — "Select matching database rows". This action matches key fields in an EasyMorph table and in a database table, and imports only database rows where the key fields match.