Database Connections

1. Supported Databases

All our Xtractor products must connect to and use data from external relational databases. Supported database types are grouped within three categories:

1.1. Featured Databases

With full free access to any available feature, from any application.

• SQLite – file-based free and open-source compact database engine. Included System.Data.SQLite driver, with the full SQLite engine. Latest tested and supported SQLite version is 3.28. Our own system database project files are SQLite-based.
• Firebird – FDB file-based free and open-source client-server RDBMS. Included FirebirdSql.Data.FirebirdClient driver. Supported and tested versions from 2.5 to 3.0+.
• Microsoft SQL Server CE – free compact SDF file-based database, owned by Microsoft, but no longer supported. Included System.Data.SqlServerCe.4.0 driver, from the Microsoft SQL Server Compact Data Provider 4.0. That’s actually the full SQL CE engine. We support version 4.0, and you can always easily upgrade from 3.5.
• Microsoft Access – ACCDB/MDB file-based commercial database, owned by Microsoft, shipped with Office. Unlike all other databases, we connect through OLE-DB (System.Data.OleDb driver), not ADO.NET. Must have Microsoft Access installed locally, with a Microsoft.Jet.OLEDB.4.0 or Microsoft.ACE.OLEDB.12.0 provider. We support versions from 2007 to 2016+.

1.2. Open-Source Databases

With 7-day free trial for full access to anything, then limited visual SQL query design and SQL generation or paid monthly subscription.

• MySQL – free, but owned by Oracle. MySql.Data.MySQLClient driver, from included MySQL Data Provider. Supported and tested versions from 5.5 up to 8.0+.
• MariaDB – free and open-source database forked few years ago from MySQL.  Same included MySQL Data Provider. Supported and tested versions from 5.5 to 10.4+.
• Amazon Aurora – cloud commercial database owned by Amazon, in AWS (Amazon Web Services). Derived from MySQL v5.6. Same included MySQL Data Provider. We support all versions (MySQL 5.6+).
• PostgreSQL – free and open-source, including Heroku Postgres, a proprietary cloud PostgreSQL installation. Included Npgsql driver. Supported and tested versions from 8.4 to 11.4+.
• Amazon Redshift – cloud columnar commercial database for big data analytics, forked from PostgreSQL 8.0.2 years ago, owned by Amazon, in AWS (Amazon Web Services). Same included Npgsql driver. We support all versions (PostgreSQL 8.0.2+).

1.3. Commercial Databases

With 7-day free trial for full access to anything, then limited visual SQL query design and SQL generation or paid monthly subscription.

• Microsoft SQL Server – commercial RDBMS, owned by Microsoft, with free Express version. Included System.Data.SqlClient built-in .NET driver. Supported and tested versions from 2008 to 2017+.
• Microsoft Azure SQL Database – remote SQL Server commercial RDBMS hosted in Microsoft’s cloud. Included the same System.Data.SqlClient built-in .NET driver. We support all versions (v12+).
• Oracle – commercial RDBMS, owned by Oracle, with free Express edition. Included Oracle.ManagedDataAccess.Client ODP.NET managed driver, owned by Oracle. Support and tested versions from 10g to 18c+.
• IBM DB2 – commercial RDBMS, owned by IBM, with free Express version. The IBM.Data.DB2 driver must be installed by the user, as it is not included in our applications. You can download and install the IBM Data Server Driver Package (DS Driver) from IBM’s website. We support and we tested the LUW (Linux, Unix, and Windows) distribution, starting with version 10.5.
• SAP/Sybase ASE – commercial RDBMS, formerly owned by and known as Sybase, owned today by SAP. ASE (Adaptive Server Enterprise) has also a free evaluation version. A  Sybase.Data.AseClient driver must be installed by the user, as it is not included in our applications. Supported and tested versions from 16 to 17+.
• SAP/Sybase SQL Anywhere – commercial database, owned today by SAP, with free Express version. Included iAnywhere.Data.SQLAnywhere driver, from the SQL Anywhere Data Provider. Support and tested versions from 16 to 17+.
• Ingres – commercial database, owned by Actian. No longer supported, as they moved away from the free evaluation version. Included Ingres.Client driver, from the Ingres .NET Data Provider. We used to support version 10.2+.

2. Connect to a Database

Connections are entry points to your databases, represented by top level nodes in your left area. There are three ways to establish a connection to a local or remote database:

2.1. Database Connection Popup

This can be called anytime, even after the connection has been established (through any of these methods) and metadata imported, to eventually change some properties.

• Click on the “add connection” hyperlink.
• Through the File – Open Database Connection menu item.
• With the Connect toolbar button, using a specific database type.
• Contextual Properties item, while on a database connection node, after the metadata was imported.

• A connection Password is never exposed in clear, ever. If saved, it will be masked by ***** in the Connection String property. This supports scenarios such as DBA (DataBase Administrator) establishing a connection string to another end-user, the end-user never knowing what the password was.
• When User Name and Password credential are provided, and Save Password is True, the encrypted Password is saved in our local database and you automatically later reconnect to the database. If Save Password is False, you’ll be required to provide the Password again when you restart the application.
• If you connect to a server with more than one database, you have to select one single database, from the Database field, when exposed. The list is usually repopulated after a valid connection to the server was possible, by filling-in the other valid related fields (from the Data Source and Authentication categories).
• A Test may also show (and save) the database server version, if available, for a successful connection.
• Other Properties is the field where you may pass any other property=value; extensions. On an established connection, you may see here other properties automatically added by our application and usually required for that type of connection.

2.2. Open Database File

For file-based small databases with no security (password) set, such as:

• • SQLite – from .DB, .DB3, .SL3, .SQLITE files.
  • Firebird – from .FDB files.
  • SQL CE – from .SDF files.
  • Microsoft Access – from .MDB or .ACCDB files.
  • SQL Anywhere – from .DB files.

2.3. Connection Strings Loader

From connection strings exposed in clear in a text file (including passwords!). This is not a recommended approach, but if you repeatedly reconnect to many databases and do not have security concerns exposing passwords in a local file on your computer, customize the default ConnectionStrings.txt file from your AppData\Local\Xtractor user folder. Line format:

• {database_type}; – like {MySQL}, {MariaDB}, {Amazon Aurora} etc, with the same exact name identifier as the menu items under the Connect toolbar button. Prefix with a – (minus sign) a temporarily disabled connection.
• valid ADO.NET connection string – this might include the Password, in clear.
• one TAB character, followed by an optional short textual description.

Select one or more entries and click Test to check the connectivity. When ready for import, click OK. We wil automatically create one new database connection and import metadata from each selected string.