Using the SEM_APIS.SPARQL_TO_SQL Function to Query RDF Data

1.9 Using the SEM_APIS.SPARQL_TO_SQL Function to Query RDF Data

You can use the SEM_APIS.SPARQL_TO_SQL function as an alternative to the SEM_MATCH table function to query RDF data.

The SEM_APIS.SPARQL_TO_SQL function is provided as an alternative to the SEM_MATCH table function. It can be used by application developers to obtain the SQL translation for a SPARQL query. This is the same SQL translation that would be executed by SEM_MATCH. The resulting SQL translation can then be executed in the same way as any other SQL string (for example, with EXECUTE IMMEDIATE in PL/SQL applications or with JDBC in Java applications).

The first (sparql_query) parameter to SEM_APIS.SPARQL_TO_SQL specifies a SPARQL query string and corresponds to the query argument of SEM_MATCH. In this case, however, sparql_query is of type CLOB, which allows query strings longer than 4000 bytes (or 32K bytes with long VARCHAR enabled). All other parameters are exactly equivalent to the same arguments of SEM_MATCH (described in Using the SEM_MATCH Table Function to Query RDF Data). The SQL query string returned by SEM_APIS.SPARQL_TO_SQL will produce the same return columns as an execution of SEM_MATCH with the same arguments.

The following PL/SQL fragment is an example of using the SEM_APIS.SPARQL_TO_SQL function.

DECLARE
  c           sys_refcursor;
  sparql_stmt clob;
  sql_stmt    clob;
  x_value     varchar2(4000);
BEGIN
  sparql_stmt := 
    'PREFIX : <http://www.example.org/family/>
     SELECT ?x
     WHERE {
       ?x :grandParentOf ?y . 
       ?x rdf:type :Male
     }';

  sql_stmt := sem_apis.sparql_to_sql(
                sparql_stmt,
                sem_models('family'),
                SEM_Rulebases('RDFS','family_rb'),
                null,
                null,
                ' PLUS_RDFT=VC ', null, null,
                'RDFUSER', 'NET1');

  open c for 'select x$rdfterm from(' || sql_stmt || ')';
  loop
    fetch c into x_value;
    exit when c%NOTFOUND;
    
    dbms_output.put_line('x_value: ' || x_value);    
  end loop;
  close c;

END;
/

Parent topic: RDF Graph Overview

1.9.1 Using Bind Variables with SEM_APIS.SPARQL_TO_SQL

The SEM_APIS.SPARQL_TO_SQL function allows the use of PL/SQL and JDBC bind variables. This is possible because the SQL translation returned from SEM_APIS.SPARQL_TO_SQL does not involve an ANYTYPE table function invocation. The basic strategy is to transform simple SPARQL BIND clauses into either JDBC or PL/SQL bind variables when the USE_BIND_VAR=PLSQL or USE_BIND_VAR=JDBC query option is specified. A simple SPARQL BIND clause is one with the form BIND (<constant> AS ?var).

With the bind variable option, the SQL translation will contain two bind variables for each transformed SPARQL query variable: one for the value ID, and one for the RDF term string. An RDF term value can be substituted for a SPARQL query variable by specifying the value ID (from RDF_VALUE$ table) as the first bind value and the RDF term string as the second bind value. The value ID for a bound-in RDF term is required for performance reasons. The typical workflow would be to look up the value ID for an RDF term from the RDF_VALUE$ table (or with SEM_APIS.RES2VID) and then bind the ID and RDF term into the translated SQL.

Multiple query variables can be transformed into bind variables in a single query. In such cases, bind variables in the SQL translation will appear in the same order as the SPARQL BIND clauses appear in the SPARQL query string. That is, the (id, term) pair for the first BIND clause should be bound first, and the (id, term) pair for the second BIND clause should be bound second.

The following example shows the use of bind variables for SEM_APIS.SPARQL_TO_SQL from a PL/SQL block. A dummy bind variable ?n is declared..

DECLARE
  sparql_stmt clob;
  sql_stmt    clob;
  cur         sys_refcursor;
  vid         number;
  term        varchar2(4000);
  c_val       varchar2(4000);
BEGIN
  -- Add a dummy bind clause in the SPARQL statement
  sparql_stmt := 'PREFIX : <http://www.example.org/family/>
                  SELECT ?c WHERE { 
                  BIND("" as ?s)
                  ?s :parentOf ?c }';
  -- Get the SQL translation for SPARQL statement
  sql_stmt := sem_apis.sparql_to_sql(
                sparql_stmt,
                sem_models('family'),
                SEM_Rulebases('RDFS','family_rb'),
                null,
                null,' USE_BIND_VAR=PLSQL PLUS_RDFT=VC ', null, null,
                'RDFUSER', 'NET1');

  -- Execute with <http://www.example.org/family/Martha>
  term := '<http://www.example.org/family/Martha>';
  vid := sem_apis.res2vid('RDFUSER.NET1#RDF_VALUE$',term);

  dbms_output.put_line(chr(10)||'?s='||term);
  open cur for 'select c$rdfterm from('|| sql_stmt || ')' using vid,term;
  loop
    fetch cur into c_val;
    exit when cur%NOTFOUND;
    dbms_output.put_line('|-->?c='||c_val);
  end loop;
  close cur;

  -- Execute with <http://www.example.org/family/Sammy>
  term := '<http://www.example.org/family/Sammy>';
  vid := sem_apis.res2vid('RDFUSER.NET1#RDF_VALUE$',term);

  dbms_output.put_line(chr(10)||'?s='||term);
  open cur for 'select c$rdfterm from('|| sql_stmt || ')' using vid,term;
  loop
    fetch cur into c_val;
    exit when cur%NOTFOUND;
    dbms_output.put_line('|-->?c='||c_val);
  end loop;
  close cur;

END;
/

The following example shows the use of bind variables from Java for SEM_APIS.SPARQL_TO_SQL. In this case, the hint USE_BIND_VAR=JDBC is used.

public static void sparqlToSqlTest() {

    try {
        // Get connection
        Connection conn=DriverManager.getConnection( 
                "jdbc:oracle:thin:@localhost:1521:orcl","testuser","testuser");          

        String sparqlStmt =
            "PREFIX : http://www.example.org/family/ \n" +
            "SELECT ?c WHERE {  \n" +
            "  BIND(\"\" as ?s) \n" +
            "  ?s :parentOf ?c      \n" +
            "}";

        // Get SQL translation of SPARQL statement
        // through sem_apis.sparql_to_sql
        OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall(
            "begin" +
            "  ? := " +
            "    sem_apis.sparql_to_sql('" +
            "      "+sparqlStmt+"'," +
            "      sem_models('family')," +
            "      SEM_Rulebases('RDFS','family_rb')," +
            "      null,null," +
            "  ' USE_BIND_VAR=JDBC PLUS_RDFT=VC " +
            " ',null,null,'RDFUSER','NET1');" +
            "end;");          
        ocs.registerOutParameter(1,Types.VARCHAR);
        ocs.execute();
        String sqlStmt = ocs.getString(1);
        ocs.close();

        // Set up statement to look up value ids
        OracleCallableStatement ocsVid = (OracleCallableStatement)conn.prepareCall(
            "begin" +
            "  ? := sem_apis.res2vid(?,?);" +
            "end;");          

        // Execute SQL setting values for a bind variable
        PreparedStatement stmt=conn.prepareStatement(sqlStmt);

        // Look up value id for first value
        long valueId = 0;
        String term = "<http://www.example.org/family/Martha>";
        ocsVid.registerOutParameter(1,Types.NUMERIC);
        ocsVid.setString(2,"RDFUSER.NET1#RDF_VALUE$");
        ocsVid.setString(3,term);
        ocsVid.execute();
        valueId = ocsVid.getLong(1);

        stmt.setLong(1, valueId);
        stmt.setString(2, term);
        ResultSet rs=stmt.executeQuery();

        // Print results
        System.out.println("\n?s="+term);
        while(rs.next()) {
            System.out.println("|-->?c=" + rs.getString("c$rdfterm"));
        }
        rs.close();

        // Execute the same query for a different URI
        // Look up value id for next value
        valueId = 0;
        term = "<http://www.example.org/family/Sammy>";
        ocsVid.registerOutParameter(1,Types.NUMERIC);
        ocsVid.setString(2,"RDFUSER.NET1#RDF_VALUE$");
        ocsVid.setString(3,term);
        ocsVid.execute();
        valueId = ocsVid.getLong(1);

        stmt.setLong(1, valueId);
        stmt.setString(2, term);
        rs=stmt.executeQuery();

        // Print results
        System.out.println("\n?s="+term);
        while(rs.next()) {
            System.out.println("|-->?c=" + rs.getString("c$rdfterm"));
        }
        rs.close();

        stmt.close();
        ocsVid.close();
        conn.close(); 

    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Parent topic: Using the SEM_APIS.SPARQL_TO_SQL Function to Query RDF Data

1.9.2 SEM_MATCH and SEM_APIS.SPARQL_TO_SQL Compared

The SEM_APIS.SPARQL_TO_SQL function avoids some limitations that are inherent in the SEM_MATCH table function due to its use of the rewritable table function interface. Specifically, SEM_APIS.SPARQL_TO_SQL adds the following capabilities.

SPARQL query string arguments larger than 4000 bytes (32K bytes with long varchar support) can be used.
The plain SQL returned from SEM_APIS.SPARQL_TO_SQL can be executed against read-only databases.
The plain SQL returned from SEM_APIS.SPARQL_TO_SQL can support PL/SQL and JDBC bind variables.

SEM_MATCH, however, provides some unique capabilities that are not possible with SEM_APIS.SPARQL_TO_SQL..

Support for projection optimization: If only the VAR$RDFVID column of a projected variable is selected from the SEM_MATCH invocation, the RDF_VALUE$ join for this variable will be avoided.
Support for advanced features that require the procedural start-fetch-close table function execution: SERVICE_JPDWN=T and OVERLOADED_NL=T options with SPARQL SERVICE.
The ability to execute queries interactively with tools like SQL*Plus.

Parent topic: Using the SEM_APIS.SPARQL_TO_SQL Function to Query RDF Data