How to Query EnterpriseDB Data in MySQL Workbench

You can use the SQL Gateway from the ODBC Driver for EnterpriseDB to query EnterpriseDB data through a MySQL interface. Follow the procedure below to start the MySQL remoting service of the SQL Gateway and work with live EnterpriseDB data in MySQL Workbench.

Connect to EnterpriseDB Data

If you have not already done so, provide values for the required connection properties in the data source name (DSN). You can use the built-in Microsoft ODBC Data Source Administrator to configure the DSN. This is also the last step of the driver installation. See the "Getting Started" chapter in the help documentation for a guide to using the Microsoft ODBC Data Source Administrator to create and configure a DSN.

The following connection properties are required in order to connect to data.

• Server: The host name or IP of the server hosting the EnterpriseDB database.
• Port: The port of the server hosting the EnterpriseDB database.

You can also optionally set the following:

• Database: The default database to connect to when connecting to the EnterpriseDB Server. If this is not set, the user's default database will be used.

Connect Using Standard Authentication

To authenticate using standard authentication, set the following:

• User: The user which will be used to authenticate with the EnterpriseDB server.
• Password: The password which will be used to authenticate with the EnterpriseDB server.

Connect Using SSL Authentication

You can leverage SSL authentication to connect to EnterpriseDB data via a secure session. Configure the following connection properties to connect to data:

• SSLClientCert: Set this to the name of the certificate store for the client certificate. Used in the case of 2-way SSL, where truststore and keystore are kept on both the client and server machines.
• SSLClientCertPassword: If a client certificate store is password-protected, set this value to the store's password.
• SSLClientCertSubject: The subject of the TLS/SSL client certificate. Used to locate the certificate in the store.
• SSLClientCertType: The certificate type of the client store.
• SSLServerCert: The certificate to be accepted from the server.

Configure the SQL Gateway

See the SQL Gateway Overview to set up connectivity to EnterpriseDB data as a virtual MySQL database. You will configure a MySQL remoting service that listens for MySQL requests from clients. The service can be configured in the SQL Gateway UI.

👁 Creating a MySQL Remoting Service in SQL Gateway (Salesforce is shown)

Query EnterpriseDB from MySQL Workbench

The steps below outline connecting to the virtual EnterpriseDB database created in the SQL Gateway from MySQL Workbench and issuing basic queries to work with live EnterpriseDB data.

Connect to EnterpriseDB through the SQL Gateway

1. In MySQL Workbench, click to add a new MySQL connection.
2. Name the connection (CData SQL Gateway for EnterpriseDB).
3. Set the Hostname, Port, and Username parameters to connect to the SQL Gateway.
4. Click Store in Vault to set and store the password.
5. Click Test Connection to ensure the connection is configured properly and click OK.

NOTE: When we refer to Username and Password, we mean the credentials for the user(s) created for the SQL Gateway.

Query EnterpriseDB Data

1. Open the connection you just created (CData SQL Gateway for EnterpriseDB).
2. Click File -> New Query Tab.
3. Write a SQL query to retrieve EnterpriseDB data, like SELECT * FROM `CData EnterpriseDB Sys`.Orders;

With access to live EnterpriseDB data from MySQL Workbench, you can easily work with EnterpriseDB, just like you would a MySQL database. Get started now with a free, 30-day trial of the CData ODBC Driver for EnterpriseDB and the CData SQL Gateway.