Query EnterpriseDB Data in DataGrip

DataGrip is a database IDE that allows SQL developers to query, create, and manage databases. When paired with the CData JDBC Driver for EnterpriseDB, DataGrip can work with live EnterpriseDB data. This article shows how to establish a connection to EnterpriseDB data in DataGrip and use the table editor to load EnterpriseDB data.

Create a New Driver Definition for EnterpriseDB

The steps below describe how to create a new Data Source in DataGrip for EnterpriseDB.

1. In DataGrip, click File -> New > Project and name the project
2. In the Database Explorer, click the plus icon () and select Driver.
3. In the Driver tab:
   • Set Name to a user-friendly name (e.g. "CData EnterpriseDB Driver")
   • Set Driver Files to the appropriate JAR file. To add the file, click the plus (), select "Add Files," navigate to the "lib" folder in the driver's installation directory and select the JAR file (e.g. cdata.jdbc.enterprisedb.jar).
   • Set Class to cdata.jdbc.enterprisedb.EnterpriseDB.jar
   Additionally, in the advanced tab you can change driver properties and some other settings like VM Options, VM environment, VM home path, DBMS, etc
   • For most cases, change the DBMS type to "Unknown" in Expert options to avoid native SQL Server queries (Transact-SQL), which might result in an invalid function error
4. Click "Apply" then "OK" to save the Connection

Configure a Connection to EnterpriseDB

1. Once the connection is saved, click the plus (), then "Data Source" then "CData EnterpriseDB Driver" to create a new EnterpriseDB Data Source.
2. In the new window, configure the connection to EnterpriseDB with a JDBC URL.

   Built-in Connection String Designer

   For assistance in constructing the JDBC URL, use the connection string designer built into the EnterpriseDB JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

   java -jar cdata.jdbc.enterprisedb.jar

   Fill in the connection properties and copy the connection string to the clipboard.

   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.
3. Set URL to the connection string, e.g., jdbc:enterprisedb:User=postgres;Password=admin;Database=postgres;Server=127.0.0.1;Port=5444
4. Click "Apply" and "OK" to save the connection string

At this point, you will see the data source in the Data Explorer.

Execute SQL Queries Against EnterpriseDB

To browse through the EnterpriseDB entities (available as tables) accessible through the JDBC Driver, expand the Data Source.

To execute queries, right click on any table and select "New" -> "Query Console."

In the Console, write the SQL query you wish to execute. For example: SELECT ShipName, ShipCity FROM Orders WHERE ShipCountry = 'USA'

Download a free, 30-day trial of the CData JDBC Driver for EnterpriseDB and start working with your live EnterpriseDB data in DataGrip. Reach out to our Support Team if you have any questions.