load data between prolog and db2

Question

I am just learning prolog. I have a task ahead. I have to insert some data in to a database like Db2 express c v9.7.1 using Prolog ODBC INterface. I know there are some example predicates(SWI-PROLOG) by documentation swi-prolog home

open_wordnet :- odbc_connect('WordNet', _, [ user(jan), password(xxx), alias(wordnet), open(once) ]). I do not know how to exactly use these predicates and show a working example. Can anyone please tell me how do I exactly use these to insert data into a database like Db2 express C v9.7.1 once etablished the connection between prolog and db2 from into eclipse sdk-win 32. Write me at the address mail [email protected].

Any help or information would be greatly appreciated.

Thank you.

Thanos Tintinidis Thanos Tintinidis · Accepted Answer · 2011-04-29T00:29:41

a few years ago i have done something like that; the database was about academic relationships/info and i used prolog and ODBC (plus xpce for the gui).

first i opened a connection to the database:

fconn(C):-
    odbc_connect('academia_info', C, [user(academia_info_user), 
                  password(secret), alias(academia_info), open(once)]).

open(once) is used to avoid reconnecting to the db and C is a handler

then i used this predicate to submit queries:

%run_statement(+SQL,-Row)
rs( SQL, Row):-
   fconn(Connection),
   odbc_query(Connection, SQL, Row).

and if i wanted all the solutions I did this

%returns all solutions (findall)
%rsall(+SQL,-Rows)
rsall(SQL, Rows):-
    findall(Row,rs(SQL,Row), Rows).

for example:

rsall('SELECT * FROM institution', D).

this predicate prepares a SQL query:

prep(SQL, Type, Row,Values):-
    fconn(Connection),
    odbc_prepare(Connection, SQL, Types,Q), %uses the types of the parameters to prepare Q
    odbc_execute(Q,Values,Row). %executes Q by replacing the parameters with their values

for example:

prep('UPDATE professor SET title = ? WHERE id IN (SELECT professor.id FROM professor, researcher, "hadPosition" WHERE professor.id = researcher.id AND professor.id = "hadPosition".researcher_id AND hadPosition.title = ? AND professor.title=? GROUP BY professor.id HAVING sum("hadPosition".end_date - "hadPosition".start_date) > ?*365 )',
         [varchar,varchar,varchar, integer],_, [MT, LT,LT, Y]).

where ?s are replaced by the values [MT,LT,LT,Y]

here I delete some stuff

delete(ID,Table):-
    swritef(S,'DELETE FROM %t WHERE id = ?', [Table]),
    prep(S, [integer], _ , [ID]).

prepare a statement to insert new values (it's executed later):

prep('INSERT INTO professor (id, title) VALUES (?,?)', [integer, varchar], _, [ID,T]).

btw odbc_query returns a value with the following format row(D1,D2,D3,....,Dn) so perhaps you will have to convert it to a list.

i hope that the examples above might help.. although i have kinda forgotten the details :/ anyway, the whole project can be found here but the code is not so readable

load data between prolog and db2

1 Answers