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();
    }
}

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.