16.2.9. Calling SQL from SPARQL

A SPARQL expression can contain calls to Virtuoso/PL functions and built-in SQL functions in both the WHERE clause and in the result set. Two namespace prefixes, bif and sql are reserved for these purposes. When a function name starts with the bif: namespace prefix, the rest of the name is treated as the name of a SQL BIF (Built-In Function). When a function name starts with the sql: namespace prefix, the rest of the name is treated as the name of a Virtuoso/PL function owned by DBA with database qualifier DB, e.g. sql:example(...) is converted into DB.DBA."example"(...) .

In both cases, the function receives arguments in SQL format ('SQL valmode') and also returns the result in SQL format. The SPARQL compiler will automatically add code for format conversion into the resulting SQL code so SQL functions can be used even if define output:valmode 'LONG' forces the use of RDF representation in the result set.

Example with sql: namespace prefix

SQL>create procedure DB.DBA.ComposeInfo (
  in pname varchar,
  in pnick varchar := '',
  in pbox  varchar := '')
{
   declare ss varchar;
   ss := concat(pname, ' ', pnick, ' ', pbox);
   ss := rtrim (ss, ' ');
   return ss;

};
Done. -- 0 msec.

SQL>SPARQL
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
prefix rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
SELECT (sql:ComposeInfo (?name, ?nick, ?box))
FROM <http://www.w3.org/People/Berners-Lee/card>
WHERE
  {
    ?s rdf:type foaf:Person .
    optional{?s foaf:name ?name }.
    optional{?s foaf:nick ?nick }.
    optional{?s foaf:box ?box }.
    filter (?nick like '%TimBL%') .
  };
callret-0
VARCHAR
_______________________________________________________________________________

Timothy Berners-Lee TimBL

1 Rows. -- 30 msec.

Example with sql: namespace prefix and bif:contains

SQL>SPARQL
SELECT DISTINCT ?cityUri ?cityName (sql:BEST_LANGMATCH (?cityName, 'en, en-gb;q=0.8, fr;q=0.7, *;q=0.1', '')) as ?bestCityName
WHERE
  {
    ?cityUri ?predicate ?value.
    ?cityUri a <http://dbpedia.org/ontology/City>.
    ?value bif:contains "London".
    OPTIONAL
      {
        ?cityUri rdfs:label ?cityName
      }
  };

cityUri                                              cityName                      bestCityName
ANY                                                  ANY 	                         ANY
______________________________________________________________________________________________________________
http://dbpedia.org/resource/Anerley	                 Anerley	                     Anerley
http://dbpedia.org/resource/Felixstowe	             Felixstowe	                   Felixstowe
http://dbpedia.org/resource/Chesham	                 Chesham	                     Chesham
http://dbpedia.org/resource/Stratford%2C_London	     Stratford, London	           Stratford, London
http://dbpedia.org/resource/Ashford%2C_Surrey	       Ashford (Surrey)	 A           shford (Surrey)
http://dbpedia.org/resource/Newmarket%2C_Suffolk	   Newmarket (Suffolk)	         Newmarket (Suffolk)
http://dbpedia.org/resource/North_Rhine-Westphalia	 Renania d'o Norte-Westfalia	 Renania d'o Norte-Westfalia
http://dbpedia.org/resource/West_Bromwich	           West Bromwich	               West Bromwich
....

Example with bif: namespace prefix

SQL>SPARQL
SELECT *
FROM <http://www.w3.org/people#>
WHERE { ?s ?p ?o . ?o bif:contains '"Timo*"'};
s                                               p                                     o
VARCHAR                                         VARCHAR                               VARCHAR
_______________________________________________________________________________

 http://www.w3.org/People/Berners-Lee/card#i	http://xmlns.com/foaf/0.1/name	      Timothy Berners-Lee
 http://www.w3.org/People/Berners-Lee/card#i	http://xmlns.com/foaf/0.1/givenname   Timothy

2 Rows. -- 2 msec.