Using Prolog ODBC Interface
I'm just studying the prologue. I have a task ahead of me. I need to insert some data into a database like mysql or MSSQL using Prolog ODBC INterface. I know there are some predicates ( SWI-PROLOG ) like
open_wordnet :-
odbc_connect('WordNet', _,
[ user(jan),
password(xxx),
alias(wordnet),
open(once)
]).
I don't know how to use these predicates exactly and show a working example. Can someone please tell me how I exactly use them to insert data into a database like MySSQL or MSSQL from the prologue. Can anyone tell me the exact requirements to achieve the same via ProLOG?
Any help or information is greatly appreciated.
Thank.
source to share
Thank you @ThomasH and @StarWind Software for the answers. I could find solutions using the codes you provided. Well here's the complete picture. I'm sure there are so many people out there who want clear information about connecting Prolog to a database.
Notes:
- You can connect to any database from swi-prologue. I used Oracle 10g and MySQL 5.
-
First of all download the "SWI-Prolog" ODBC interface from here .
-
The package "ODBCProlog.dll" and "OracleProlog.dll" have two main files "dll"
Next, this is a sample code that is similar to the previous one. Explain sections
CONNECTING MYSQL 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 part of the explanation:
db_open ('mysql5', 'root', 'admin'),
the first part 'mysql5' is the dsn name for mysql. If you have it installed on your system, you can download it from the MySQL website. Next is the username and password.
db_flag (show_query, _, off),
prints SQL statements on output. by commenting this, it will not let it output the SQL query.
db_import ('EMP' ('EMPID', 'EMPNAME'), emp),
Here "EMP" is the actual name of the table in the database and "emp" is its alias. It is important to create such a method, otherwise it will not work.
db_query (emp (EMPID, EMPNAME), emp (EMPID, EMPNAME)),
Next, to query the database, the above "db_query" call will take 2 arguments. You can either query two tables using this as a JOIN statement. If you are only using a query for one table, then you need to give the same query twice as this call, which expects two arguments.
You need to insert anything into uncomment database
emp_ins (109, 1, 221),
This convention is nothing more than adding _ins to the alias, which the prologue understands to be a call to insert into the database.
similarly
emp_del (109, 1, 221),
I think the rest is self-evident.
Now, the next part, if you need to connect to an Oracle database, then the only expression that changes is:
:- use_module(odbc).
The rest is very similar. One thing you should remember is that you need to use the INSTAN name for the oracle when specifying the database. Typically in Oracle 10g the instance name is 'orcl' , and for Oracle Express the convention is:
'your full computer name:port/XE','username','password'
You will be able to connect to the database and display the results with this block of code,
Hope it helps.
source to share
There is an older example built into this mailing list (search for "test_1"). I haven't worked with it, but I am assuming se_greenslades is the name of the ODBC database instance; you should look for yours in your local setup. After that, I think you are using regular SQL records (in odbc_prepare ?!) for insert and update. Check out the docs you already linked to and search the web for basic ODBC (I think the SWI is pretty standard).
What do you mean with "ProLOG"?
source to share
How 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
source to share