Used to select and retrieve tuples from one or more database relations. The tuples are retrieved one at a time on backtracking, i.e. every attempt at resatisfying the predicate will give another matching tuple. If there are no further tuples, the predicate fails.
Attributelist specifies the attributes of the selection to be projected. Theses attributes of the retrieved tuples will be unified with the corresponding elements of Valuelist.
Tables is the name of a relation, or several names separated by commas within the string (Tables is inserted after the FROM keyword in the SQL statement).
Where is the selection condition im SQL syntax and will be inserted after the WHERE keyword in the SQL statement.
MoreOptions can be used to specify further SQL clauses, e.g. GROUP BY or ORDER BY, as well as for combining selections via UNION, INTERSECT etc. (MoreOptions will be appended to the SQL statement constructed so far, separated by a blank space).
Important note: For every new invocation of sql_query/5 the interface opens a new cursor into the relation resulting from the selection. This cursor only gets closed when either all tuples have been read, or when backtracking retrieval was terminated prematurely by a cut (!/0). In complex programs this can exceed the maximum number of cursors allowed by the interface. A possible solution is to a priori retrieve all tuples into a list (using findall/3), and then process the list using member/2.
% List the tuples of relation Adressen (which has attributes
% Id Vorname and Name), assuming there are two such tuples:
?- sql_query(['Id','Vorname','Name'],'Adressen','','',[I,V,N]).
I = 113
V = 'Karl'
N = 'Krause' More? (;)
I = 17
V = 'Berta'
N = 'Braesig' More? (;)
no (more) solution.
% Find the names of persons, and their department (Abteilung),
% using a join of relations Adressen and Arbeitsplatz.
% The join is made via the attribute Id which is present in
% both relations
?- sql_query(['Name','Abteilung'],'Adressen,Arbeitsplatz',
'Adressen.Id=Arbeitsplatz.Id','',[Name,Abteilung]).
Name = 'Braesig'
Abteilung = 'Buchhaltung' More? (;)
Name = 'Krause'
Abteilung = 'Logistik' More? (;)
no (more) solution.
% List the Id numbers of persond which occur both in relation
% Addressen and Arbeitsplatz. It is implemeted here using a set
% intersection, in order to show a use of the MoreOptions argument.
% Normally, this problem would better be solved using a join.
?- sql_query(['Id'],'Adressen','',
'intersect select Id from Arbeitsplatz',[I]).
I = 113 More? (;)
I = 17 More? (;)
no (more) solution.