DB Visualizer and Virtuoso JDBC Connectivity

DB Visualizer 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:

  1. JDBC Driver JAR file name – “virtjdbc4_2.jar” (basic) or “virtjdbc4_2ssl.jar”
  2. JDBC Driver file location – {virtuoso-installation-folder}/lib/
  3. JDBC Driver URL pattern – jdbc:virtuoso://:/charset=UTF-8/SSL/kpass=/kpath=/ts=

Screenshot demonstrating a Virtuoso JDBC configuration for DB Visualizer

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 using 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 the DB Visualizer’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?

It 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).

Related:

This is great!. it works in Mac but not in Linux Ubuntu.
I tried with all the drivers here


see images below

@asanchez75: I get the same Tag 248 not defined error running both DBVisualizer and the latest Virtuoso open source develop/7 build on macOS.

DBVisualizer - 10.0.22
Virtuoso binary - Version 7.2.6-rc1.3230-pthreads as of Oct 14 2019 (0e746c7ab)
Virtuoso JDBC driver - Version 4.2 [Build 3.115]  

Thus what are your versions of these components on both macOS and Linux as I assume they differ ?

Also, why are you seeking to query the DB.DBA.RDF_QUAD table directly via SQL, rather than via the SPARQL query engine ?

see this image in Mac

Virtuoso version 07.20.3217 on Linux (x86_64-pc-linux-gnu), Single Server Edition

I use this client because I can interact with the another tables too :slight_smile:

@asanchez75: If you scroll down to the bottom of the DbVisualizer app you will see Max Rows =1000 so it is only displaying the first 1000 rows, so the error is not being seen with the older Virtuoso 3217 binary, see:

If you increase to say 1000000 then you will see the Tag 248 not defined error.

Thus as asked before do you have a specific use case for querying this table, which uses special serialisations for storing a returning the RDF data it hosts and thus it is not recommended it be queried directly ?