A database connector in EasyMorph is a set of connection properties (e.g. server address, port or database name) that are used to connect to particular database. See below example of database connector.
Connectors can be of 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 that connectors referred by a project have 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.
Supported database types:
|Oracle||ODBC / Native|
|SQL Server||ODBC / Native|
|MySQL||ODBC / Native|
|PostgreSQL||ODBC / Native|
|SQLite||ODBC / Native|
|Amazon Redshift||ODBC only|
|SAP HANA||ODBC only|
|Generic SQL:2003 dialect||ODBC only|
Connectors and repositories can be created and edited using the Connection Manager. It can be accessed from the Start screen, or in menu Project, or in Project Overview sidebar, or by pressing F7.
Operations that can be performed using the Connector Manager:
Hint: it is possible to protect a shared repository from accidental change by unqualified users 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.
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 accomodated using a visual query — for instance when a join or aggregation 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.
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). Such list can be loaded from another data source (e.g. spreadsheet), or be a 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 tables, and imports only database rows where the key fields match.