% Demonstrate SQL queries and exception handling for opening a database
%    (requires a SCOP MySQL database) 
:- eval(consult("utils.prova")).

:- eval(scop002()).

% The first location contains a wrong connection string
location(database,scop,"jdbc:mysql2://localhost/scop","guest","guestdb").
% The second location contains a wrong password
location(database,scop,"jdbc:mysql://localhost/scop","guest","").
% The third connection parameters are correct and work
location(database,scop,"jdbc:mysql://localhost/scop","guest","guestdb").

on_dbopen_exception() :-
	exception(Ex),
	println([Ex]),
	fail().

scop002() :-
	println(["==================scop example 002===================="]),
	on_exception(java.sql.SQLException,on_dbopen_exception()),
	dbopen(scop,DB),
	scop002_inner(DB).

scop002_inner(DB) :-
	println([DB]),
	println(["10 first pairs of domains PXA<PXB in multi-domain proteins PDB_ID"]),
	% select c1.px,c2.px,c1.pdb_id from cla as c1, cla as c2
	%    where c1.pdb_id=c2.pdb_id and c1.px<c2.px limit 10
	sql_select(DB,'cla as c1,cla as c2','c1.px'(PXA),'c2.px'(PXB),
		'c1.pdb_id'(PDB_ID),where('c1.pdb_id=c2.pdb_id and c1.px<c2.px'),
		options('limit 10')),
	println([PXA,PXB,PDB_ID]," ").
scop002_inner(DB) :-
	println(["10 first protein descriptions"]),
	% select * from des limit 10
	sql_select(DB,des,options('limit 10')|Tail),
	println(Tail,",").
