Using Prolog ODBC Interface

Question

Hi Guys,

I am just learning prolog. I have a task ahead. I have to insert some data in to a database like mysql or MSSQL using Prolog ODBC INterface. I know there are some example predicates(SWI-PROLOG) like

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 MySSQL or MSSQL from prolog. Can anyone please tell me the exact requirements to achieve the same through ProLOG?

Any help or information would be greatly appreciated.

Thank you.

Answer 1

There is an older example embedded in this mailing list conversation (look for 'test_1'). I haven't worked with it but I guess that *se_greenslades* is the ODBC data base instance name; you have to look up yours in your local setup. After that, I guess you use normal SQL statments (in odbc_prepare?!) to do inserts and updates. Check the SWI docs you already linked to, and search the internet for basic ODBC usage (I think the SWI interface is pretty standard).

What do you mean with "ProLOG"?

Answer 2

What about this example:

:- use_module(oracle).

go :-
    db_open('Your Database Name', 'scott', 'tiger'),
    db_import('DEPT'('DEPTNO', 'DNAME', 'LOC'), dept),
    db_import('EMP'('EMPNO', 'ENAME', 'JOB', 'MGR', 'HIREDATE', 'SAL', 'COMM', 'DEPTNO'), emp),
    %% Uncomment it, if you do not want to see SQL statements.
    %% db_flag(show_query, _, off),
    db_query(empinfo(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME, LOC),
     ( emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO),
      dept(DEPTNO, DNAME, LOC)
     )
    ),
    get_result,
    db_close.

get_result:-
    empinfo(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME, LOC),
    write_ln([EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME, LOC]),
    fail.
get_result.

Taken from here: http://www.geocities.com/SiliconValley/Bit/1116/PrologSQLex01.html

Answer 3

Hi,

Thank you for you responses @ThomasH and @StarWind Software. I could figure out the solutions using the code sames that you pointed out. Well here is a complete picture. I am sure there are so many people out there who need a clear picture of connection from Prolog to a Database.

Notes:

1. You can connect to any database from within swi-prolog. I used Oracle 10g and MySQL 5.

2. First of all download the 'SWI-Prolog' ODBC Interface from here.

3. There are two main 'dll' files in the package 'ODBCProlog.dll' and 'OracleProlog.dll'

   Next, this is the sample code which is same as above one. I Will explain the sections

MYSQL CONNECTION IN PROLOG

:- use_module(oracle).
go :-
    db_open('mysql5', 'root', 'admin'),    
    db_import('EMP'('EMPID', 'EMPNAME'), emp),
    %%db_flag(show_query, _, off),

    db_query(emp(EMPID, EMPNAME), emp(EMPID, EMPNAME)),
    %% Run the query.
    get_result,
    %% Modify the database.
    %%emp_ins(109, 1, 221),
    %%test_del(109, 1, 221),
    %% Commit changes.
    db_transaction(commit),
    db_close.

%% Retrieve all records over backtracking.
get_result:-
    emp(EMPID, EMPNAME),
    write_ln([EMPID, EMPNAME]),
    fail.
get_result.

Now the explanation part:

db_open('mysql5', 'root', 'admin'),

the first part 'mysql5' the dsn name for mysql. If you do nmot have it installed in your system, you can download it from the MySQL website. The next one is username and password.

db_flag(show_query, _, off),

prints the SQL statements in the output. commenting it will prevent it from outputting the SQL query.

db_import('EMP'('EMPID', 'EMPNAME'), emp),

Here 'EMP' is the actual table name in the database and 'emp' is its alias. It is important to create this way otherwise it will not work.

db_query(emp(EMPID, EMPNAME), emp(EMPID, EMPNAME)),

next for quering the database the above call 'db_query' will take 2 arguments. You can either query two tables using this like a JOIN statement. If you are using just quering one table then it is necessary to give the same query twice as this call expects two arguments.

Is you need to insert anything in the database uncomment

emp_ins(109, 1, 221),

This convention is nothing but you are adding _ins to the alias name which is understood by the prolog that it is an insert call to database.

similarly

emp_del(109, 1, 221),

I guess the rest is self explanatory.

Now, the next part is if you need to connect to Oracle database then the only statement that changes is :

:- use_module(odbc).

The rest is pretty same. One thing you have to remember is that you need to use the oracle INSTANCE name while specifying the database. Usually in Oracle 10g the instance name is 'orcl' and for Oracle Express edition the convention is :

'your full computer name:port/XE','username','password'

You WILL be able to connect to database and display the results with this block of code,

Hope this helps.