% Demonstrates new features of Prova 1.8 and above
% - the sql_update built-in predicate for executing SQL UPDATE via JDBC;

:-eval(consult("utils.prova")).

:- eval(sql_update_001()).

location(database,gtd,"jdbc:mysql://localhost/gtd","root","").

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

sql_update_001() :-
	on_exception(java.lang.Exception,on_sql_exception("")),
	dbopen(gtd,DB),
	println(["===========================sql_update_001==========================="]),
	issue_query(DB).

issue_query(DB) :-
	on_exception(java.sql.SQLException,on_sql_exception("update gtd set scop_id=5,prim_dbname='gi' where id=1")),
	println(["--------------------------------------------------------------------"]),
	% update gtd t set t.scop_id=5,t.prim_dbname='gi' where t.id=1
	println(["Update the first record in the gtd table"]),
	% Note that we use single quotes in double qoutes for "'gi'" for it to be passed to the statement.
	% If we used something like "t.scop_id = t.scop_id+1", it would have to be encoded as ["t.scop_id","t.scop_id+1"]. 
	sql_update(DB,"gtd t",["t.scop_id",5],["t.prim_dbname","'gi'"],[where,"t.id=1"]),
	println(["Successful update."]).
issue_query(DB) :-
	on_exception(java.sql.SQLException,on_sql_exception("update gtd set scop_id=0,prim_dbname='gi' where id=1 and sec_dbname='ref'")),
	println(["--------------------------------------------------------------------"]),
	% In this example, we are passing (bound) variables to sql_update.
	% Note the embedded list with pairs [field,value] passed to the "where" part of the query
	ID=1,
	Sec_dbname="'ref'",
	% update gtd t set t.scop_id=0,t.prim_dbname='gi' where t.id=1 and t.sec_dbname='ref'
	println(["Update the first record in the gtd table"]),
	% Note that we use single quotes in double qoutes for "'gi'" for it to be passed to the statement.
	% If we used something like "t.scop_id = t.scop_id+1", it would have to be encoded as ["t.scop_id","t.scop_id+1"]. 
	sql_update(DB,"gtd t",["t.scop_id",0],["t.prim_dbname","'gi'"],[where,["t.id",ID],["t.sec_dbname",Sec_dbname]]),
	println(["Successful update."]).

