# How to query SQL database from Prolog and use the queried result as facts? **URL:** \ **Category:** Help!\ **Created:** [February 8, 2019, 4:51pm UTC](https://swi-prolog.discourse.group/t/how-to-query-sql-database-from-prolog-and-use-the-queried-result-as-facts/221 "2019-02-08T16:51:10Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![ielbert](https://avatars.discourse-cdn.com/v4/letter/i/e79b87/32.png) [@ielbert](https://swi-prolog.discourse.group/u/ielbert)\ **Post date:** [February 8, 2019, 4:51pm UTC](https://swi-prolog.discourse.group/t/how-to-query-sql-database-from-prolog-and-use-the-queried-result-as-facts/221/1 "2019-02-08T16:51:10Z")
I have a question similar to the one [asked at the Google Group a while ago](https://groups.google.com/d/msg/swi-prolog/XbTTY3deVRA/Usl_ewVxMKAJ). 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 ---
**Author:** ![abaljeu](https://yyz2.discourse-cdn.com/free1/user_avatar/swi-prolog.discourse.group/abaljeu/32/136_2.png) [@abaljeu](https://swi-prolog.discourse.group/u/abaljeu)\ **Post date:** [February 8, 2019, 8:22pm UTC](https://swi-prolog.discourse.group/t/how-to-query-sql-database-from-prolog-and-use-the-queried-result-as-facts/221/2 "2019-02-08T20:22:36Z")
[http://www.swi-prolog.org/pldoc/doc\_for?object=section('packages/odbc.html')](http://www.swi-prolog.org/pldoc/doc_for?object=section(%27packages/odbc.html%27)) 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. ---
**Author:** ![ielbert](https://avatars.discourse-cdn.com/v4/letter/i/e79b87/32.png) [@ielbert](https://swi-prolog.discourse.group/u/ielbert)\ **Post date:** [February 8, 2019, 10:27pm UTC](https://swi-prolog.discourse.group/t/how-to-query-sql-database-from-prolog-and-use-the-queried-result-as-facts/221/3 "2019-02-08T22:27:41Z")
@abaljeu, thanks for the explanation. Your code worked perfectly. ---
**Author:** ![jan](https://yyz2.discourse-cdn.com/free1/user_avatar/swi-prolog.discourse.group/jan/32/4_2.png) [@jan](https://swi-prolog.discourse.group/u/jan)\ **Post date:** [February 9, 2019, 8:19am UTC](https://swi-prolog.discourse.group/t/how-to-query-sql-database-from-prolog-and-use-the-queried-result-as-facts/221/4 "2019-02-09T08:19:25Z")
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](http://www.swi-prolog.org/pldoc/doc_for?object=section(%27packages/cql.html%27)). The learning curve is a bit steep. Once you master it it makes dealing with data in RDBMS really powerful. ---
**Author:** ![nicos](https://avatars.discourse-cdn.com/v4/letter/n/7feea3/32.png) [@nicos](https://swi-prolog.discourse.group/u/nicos)\ **Post date:** [February 9, 2019, 10:23pm UTC](https://swi-prolog.discourse.group/t/how-to-query-sql-database-from-prolog-and-use-the-queried-result-as-facts/221/5 "2019-02-09T22:23:15Z")
Dear Ielbert, you might find db\_facts pack interesting. [http://eu.swi-prolog.org/pack/list?p=db\_facts](http://eu.swi-prolog.org/pack/list?p=db_facts) [http://stoics.org.uk/~nicos/sware/db\_facts](http://stoics.org.uk/~nicos/sware/db_facts) Regards, ## Nicos Angelopoulos [http://stoics.org.uk/~nicos](http://stoics.org.uk/~nicos) ---
**Author:** ![ielbert](https://avatars.discourse-cdn.com/v4/letter/i/e79b87/32.png) [@ielbert](https://swi-prolog.discourse.group/u/ielbert)\ **Post date:** [February 11, 2019, 8:30pm UTC](https://swi-prolog.discourse.group/t/how-to-query-sql-database-from-prolog-and-use-the-queried-result-as-facts/221/6 "2019-02-11T20:30:16Z")
@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).