This is the generic variant of the selection command. StatementList is a fully instantiated list of atoms, strings and numbers, which get concatenated, separated by blanks, and prefixed with SELECT, to form a valid SQL SELECT statement.
Valuelist must be a list of fixed length, containing variables or values. For every result tuple, the tuple gets unified with Valuelist, such that the first list element corresponds to the first attribute of the selection, the second to the second, etc. Every resatisfaction of the predicate delivers another tuple of the selection result.
The important difference between sql_select/2 and sql_query/5 is that sql_select/2 is more general, but does not take advantage of elements in Valuelist which are already instantiated: first, all tuples of the selection are retrieved, and only then do they get unified with Valuelist, which may lead to failure. With sql_query/5, instantiated elements of Valuelist will be incorporated into the selection criteria of the SELECT statement. As a consequence, the result set is already (possibly vastly) reduced on the database side.
Hint: In SQL, strings must be enclosed in single quotes. If such strings are to be used within Statementlist, it is convenient to surround the single-quoted SQL strings with double quotes (which can contain single quotes without the need for further escape symbols).
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.
% The relation Adressen has attributes Id, Vorname and Name.
% Find Vorname and Name of persons in relation Adressen, whose
% Id lie in the interval (Min,Max).
% Assuming there are two solution in the interval (17,200):
?- Min=17, Max=200,
sql_select(['Vorname, Name from Adressen where Id between',
Min,and,Max],[V,N]).
V = 'Karl'
N = 'Krause' More? (;)
V = 'Berta'
N = 'Braesig' More? (;)
no (more) solution.