Connecting with JDBC

CData Virtuality provides a standard JDBC driver that enables external Java applications and Java-based tools to access data in virtual databases hosted on the CData Virtuality Server using SQL. This guide uses DbVisualizer as an example to demonstrate the connection process, but you can use other tools, such as DBeaver or SQuirrel which offer similar functionality for interacting with data.

For installation instructions for the JDBC Driver for Windows, please refer to the relevant section of the Windows installation guide.

For instructions for the JDBC Driver for SaaS, please contact the support team.

DbVisualizer

DB Visualizer can be downloaded here for free.

Driver Manager Settings

1. Start DbVisualizer and open the menu Tools → Driver Manager:

2. When the Driver Manager opens, click + button in the upper left corner, then select Create a New Driver from Template button. Choose Custom from the list of templates and double-click it to proceed:

3. Custom will be added under User Drivers in the Driver Manager:

4. In the Driver Settings tab, enter CData Virtuality under Name. Under Driver artifacts and jar files, click the + button. Select Add Files... to locate and upload the required JAR file.:

5. Select the JDBC Driver file (datavirtuality-jdbc.jar):

The default location for the drivers in the on-premise version is C:\CData_Virtuality_Suite\Drivers.

6. Confirm that the DriverClass has been loaded correctly. Close the Driver Manager by clicking the X button in the upper right corner:

Creating a Connection

1. Click + Create a Database Connection then Create a Connection or the + button under the Databases tab on the left:

2. Select CData Virtuality from the Driver Name list:

3. On the Connection tab, navigate to the Database → Settings Format section. In the Settings Format, change Server Info to Database URL:

The step-by-step guide below provides detailed instructions for configuring JDBC in both Windows and SaaS environments.

JDBC Configuration in Windows

JDBC URL Configuration for localhost Specification

Set the following details to configure the connection:

• Database URL: jdbc:cdatavirtuality:datavirtuality@mm://localhost:31000

• Database Userid: CData Virtuality Server user (e.g. admin)

• Database Password: User password for CData Virtuality Server (e.g. admin)

JDBC URL Configuration for Host Specification

Set the following details to configure the connection:

• Database URL: jdbc:cdatavirtuality:datavirtuality@mm://{Host}:31000
  For SSL connections, use @mms instead of @mm;

• Database Userid: CData Virtuality Server user (e.g. admin);

• Database Password: User password for CData Virtuality Server (e.g. admin)

Host name can be checked in the Preferences section:

JDBC Configuration in SaaS

Specifying JDBC URLs in SaaS

1. Set the following details to configure your connection:

• Driver URL: jdbc:cdatavirtuality:datavirtuality@mms://{Host}:{Port}
  (e.g.,c:datavirtuality@mms://aws-us-east-1.platform.datavirtuality.com:45026 )
  For SSL connections, use @mms instead of @mm;

• Database Userid: Database Username (e.g. support_cdata_com);

• Database Password: User password for CData Virtuality Server

2. Details about Host can be found in the Preferences section:

3. Ensure that JDBC is enabled under Ports.

Connection Test

1. Click the Connect button to establish a connection to the CData Virtuality Server.

2. Verify that the connection is successful.

3. Navigate to the Databases tab on the left side and open the Connection name (e.g. CData Virtuality) → datavirtuality.

4. Several schemas should be visible, including:

• INFORMATION_SCHEMA

• Data sources such as mysql and postgres

• SYS systems

• Views and more:

Data Acquisition

1. Open the mysql schema of the sample database.

To connect to the sample database, follow the instructions here.

2. Select TABLE → Customers and double-click it.

3. The information for the Customers table will be displayed on the right side:

4. Next, try running an SQL query that joins tables from multiple data sources. In the menu bar, select SQL Commander → New SQL Commander:

5. Copy and paste the following SQL query into the SQL Editor. This query joins the Customers table from MySQL and the Orders table from PostgreSQL:

SELECT
"Customers.customerid", "customerid".
"Customers.companyname", "Customers.
"Customers.address", "Customers.address".
"Customers.country", "Customers".
"Customers.city", "Customers".
"Customers.sector", "Customers.sector".
"Customers.industry", "Customers".
"Customers.city_id", "Customers.
"Customers.iso3_country_code", "iso3_country_code".
"Orders.orderid", "orderid".
"Orders.customerid" AS "Orders_customerid", "Orders.
"Orders.orderdate", "orderdate".
"Orders.cost", "Orders.cost".
"Orders.subtotal", "Orders.subtotal".
"Orders.taxes", "Orders.taxes".
"Orders.total", "Orders.total".
"Orders.uuid_group", "Orders.
"Orders.segment".
FROM
"mysql.Customers" INNER JOIN "postgres.Orders" ON "Customers.customerid" = "Orders.customerid"

6. Click the Play (Execute the buffer as an SQL script) button to run the SQL query. If the query executes successfully, a preview of the data will appear at the bottom of the editor:

7. After finishing the session, double-click the connection name to return to the connection screen. Click the Disconnect button to terminate the connection and exit:

Installing Additional JDBC Drivers

For instructions on installing JDBC drivers that are not included in the CData Virtuality installation package, such as Microsoft Dataverse, NetSuite, SAP ADS, SAP HANA, Sybase ASE and Sybase IQ, and Teradata, please refer to Installing JDBC Drivers.

JDBC connection string changed in v4.10: both jdbc:datavirtuality and jdbc:cdatavirtuality are acceptable