I have a question similar to the one asked at the Google Group a while ago.
Does anyone have a reference code showing the best practice on how to retrieve facts from RDBMS and use them?
I have figured out ODBC and querying but not clear about turning returned rows into facts.
Thank you,
Igor
http://www.swi-prolog.org/pldoc/doc_for?object=section('packages/odbc.html')
This man page is not very easy to follow. It’s very technical how it’s written, describes the options but not in-context usage.
But you say you have the query down and want to make facts.
Given you want it all in memory anyway, I would suggest using the findall parameter, if it weren’t for this note:
The current implementation is incomplete. It does not allow arguments of row(...) to be instantiated. Plain instantiation can always be avoided using a proper SELECT statement. Potentially useful however would be the translation of compound terms, especially to translate date/time/timestamp structures to a format for use by the application.
So instead, follow this pattern (not tested):
query_to_facts(Source, Query, Options, FactName) :-
odbc_query(Source, Query, Row, Options),
Row =.. [row | Terms],
Fact =.. [FactName | Terms],
assertz(Fact),
fail.
query_to_facts(Source, Query, FactName) :- true.
You could instead use findall/3 yourself:
query_to_facts(Source, Query, Options, FactName) :-
findall(Row, odbc_query(Source, Query, Row, Options), Rows),
maplist(post_fact(FactName), Rows).
And define post_fact/2 to do the =… and assertz as above.
post_fact(FactName, Row) :-
Row =.. [row | Terms],
Fact =.. [FactName | Terms],
assertz(Fact).
One more note: Use odbc_prepare over odbc_query if you have user-defined inputs to your query.
@abaljeu, thanks for the explanation. Your code worked perfectly.
The answer is perfect if you want to materialize a predicate from a database. This is great if the database is not modified, the table is small enough to copy to Prolog’s memory (any machine can deal with a few million rows that way, on a big server 100M rows is no big problem). After copying your access is typically a lot faster from the Prolog DB.
But, if one or more of this does not hold and you need many different queries that often join tables or use value constraints, consider the CQL library. The learning curve is a bit steep. Once you master it it makes dealing with data in RDBMS really powerful.
Dear Ielbert,
you might find db_facts pack interesting.
http://eu.swi-prolog.org/pack/list?p=db_facts
http://stoics.org.uk/~nicos/sware/db_facts
Regards,
@jan, thanks for the clarifications. It’s good to know the boundaries of the solution. For the near future, we’ll be dealing with a table with less than 100M rows and we do have a big server so the approach by @abaljeu should work.
I’ll keep CQL in mind for the future. When the time comes I’ll ask for a reference end-to-end example here. (CDL’s quickstart example does not show how to connect and does not explain why provided query is a SELECT equivalent).