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.
![[Tip]](images/tip.png)
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.