DbVisualizer and Virtuoso JDBC Connectivity

kidehen · September 25, 2019, 1:41pm

DbVisualizer is a JDBC-compliant tool that provides generic SQL-access to any DBMS via a JDBC Driver. At the current time, the generic nature of their JDBC support isn’t immediately obvious, so you might think that it is just another tool with hard-coded connectivity to a selection of DBMS backends.

Looking deeper, you will see that it actually supports generic JDBC access.

Adding support for our Virtuoso JDBC Driver simply boils down to understanding:

JDBC Driver JAR file name – virtjdbc4_2.jar (basic) or virtjdbc4_2ssl.jar (TLS-secured connections)
JDBC Driver file location – {virtuoso-installation-folder}/lib/
JDBC Driver URL pattern – jdbc:virtuoso://<host>:<port>/charset=UTF-8/SSL/kpass=<pkcs-file-password>/kpath=<pkcs-file-name>/ts=<ca-trust-chain-file-name>

Screenshot demonstrating a Virtuoso JDBC configuration for DbVisualizer

Screenshot demonstrating an actual connection to our public Demo Server.

Schemas overview from the Demo Server.

Customer Table from the Demo Server

Actual Data from the Customer Table

A SQL Query enhanced with SPARQL that integrates data from across the LOD Cloud. Note, this requires a “pass through” mode query achieved by disabling the SQL-preprocessor option of DbVisualizer’s Query Command Tool.

SPASQL Query

SELECT DISTINCT        DBpedia.name AS "Actor Label", 
                      DBpedia.actor AS "DBpdia ID", 
                     DBpedia.school AS "School ID", 
                DBpedia.schoolLabel AS "School Label"
FROM
(
  SPARQL
  PREFIX dct: <http://purl.org/dc/terms/>
 
  SELECT *
  WHERE 
  {
    SERVICE <http://dbpedia.org/sparql> 
        {
          ?actor 
                  dct:subject                              <http://dbpedia.org/resource/Category:Tony_Award_winners> ;
                  <http://dbpedia.org/ontology/almaMater>  ?school ;
                  foaf:name                                ?name .
          ?school rdfs:label                               ?schoolLabel . 
              FILTER (LANG(?name) = “en”)
              FILTER (LANG(?schoolLabel)= “en”)
        }
  }
  ORDER BY ASC(?school)
  LIMIT 100
) AS DBpedia

What’s the benefit of a Virtuoso JDBC Driver?

A Virtuoso JDBC Driver provides any JDBC-compliant application with an ability to access and merge data from disparate data sources using SQL and/or SPARQL.

Remember, SPARQL includes built-in federation, entity relationship graph traversal, and the use of fine-grained relationship type semantics to produce powerful query solutions that extend all the way to the public Web (i.e., the Web and its LOD Cloud enclave are just additional relational data sources).

DbVisualizer and Virtuoso JDBC Connectivity

What’s the benefit of a Virtuoso JDBC Driver?

Related