% Demonstrate exceptions in SQL queries (requires a SCOP MySQL database) 
% Show how handlers defined in inner calls take precedence over the
%    the ones defined outside.
:- eval(consult("utils.prova")).

:- eval(scop003()).

% Wrong connection string
location(database,scop,"jdbc:mysql1://localhost/scop","guest","guestdb").
% Correct connection string
location(database,scop,"jdbc:mysql://localhost/scop","guest","guestdb").

% A failure style exception handler: it has fail() at the end.
on_dbopen_exception() :-
	exception(Ex),
	println([Ex]),
	fail().

% A compensation style exception handler:
%   - it does not fail;
%   - it instantiates the variables that were supposed to be instantiated
%     by the SQL SELECT.
on_sql_exception(Tail) :-
	exception(Ex),
	println([Ex]),
	% Just substitute the first record from SCOP for example
	Tail=[["id",14982],["type","px"],["sccs","a.1.1.1"],["sid","d1dlwa_"],["description","1dlw A:"]].

scop003() :-
	println(["==================scop example 003===================="]),
	on_exception(java.sql.SQLException,on_dbopen_exception()),
	dbopen(scop,DB),
	scop003_inner(DB).

scop003_inner(DB) :-
	println(["WRONG TABLE NAME--TWO HANDLERS: 10 first protein descriptions"]),
	on_exception(java.sql.SQLException,on_sql_exception(Tail)),
	% select * from des limit 10
	% NOTE that an exception is thrown as the table name is misspelled:
	% 		des -> des1
	sql_select(DB,des1,options('limit 10')|Tail),
	println(Tail,",").
scop003_inner(DB) :-
	println(["WRONG TABLE NAME--NO HANDLER: 10 first protein descriptions"]),
	on_exception(java.sql.SQLException,clear),
	% select * from des limit 10
	% NOTE that an exception is thrown as the table name is misspelled:
	% 		des -> des1
	% BUT there is no handler SO the call below simply fails
	sql_select(DB,des1,options('limit 10')|Tail),
	println(Tail,",").
scop003_inner(DB) :-
	println(["CORRECT TABLE NAME: 10 first protein descriptions"]),
	on_exception(java.sql.SQLException,clear),
	% select * from subchain limit 10
	% NOTE that the table name is correct below
	sql_select(DB,subchain,options('limit 10')|Tail),
	println(Tail,",").
