swi-prolog-odbc 6.6.4-2ubuntu1 / usr / lib / swi-prolog / library / odbc.pl

/*  $Id$

    Part of SWI-Prolog

    Author:        Jan Wielemaker
    E-mail:        jan@swi.psy.uva.nl
    WWW:           http://www.swi-prolog.org
    Copyright (C): 1985-2002, University of Amsterdam

    This program is free software; you can redistribute it and/or
    modify it under the terms of the GNU General Public License
    as published by the Free Software Foundation; either version 2
    of the License, or (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU Lesser General Public
    License along with this library; if not, write to the Free Software
    Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA

    As a special exception, if you link this library with other files,
    compiled with a Free Software compiler, to produce an executable, this
    library does not by itself cause the resulting executable to be covered
    by the GNU General Public License. This exception does not however
    invalidate any other reasons why the executable file might be covered by
    the GNU General Public License.
*/


:- module(odbc,
	  [ odbc_connect/3,		% +DSN, -Conn, +Options
	    odbc_driver_connect/3,	% +DriverString, -Conn, +Options
	    odbc_disconnect/1,		% +Conn
	    odbc_current_connection/2,	% ?Conn, -DSN
	    odbc_set_connection/2,	% +Conn, +Option
	    odbc_get_connection/2,	% +Conn, ?Option
	    odbc_end_transaction/2,	% +Conn, +CommitRollback

	    odbc_query/4,		% +Conn, +SQL, -Row, +Options
	    odbc_query/3,		% +Conn, +SQL, -Row
	    odbc_query/2,		% +Conn, +SQL

	    odbc_prepare/4,		% +Conn, +SQL, +Parms, -Qid
	    odbc_prepare/5,		% +Conn, +SQL, +Parms, -Qid, +Options
	    odbc_execute/2,		% +Qid, +Parms
	    odbc_execute/3,		% +Qid, +Parms, -Row
	    odbc_fetch/3,		% +Qid, -Row, +Options
	    odbc_close_statement/1,	% +Statement
	    odbc_clone_statement/2,	% +Statement, -Clone
	    odbc_free_statement/1,	% +Statement

					% DB dictionary info
	    odbc_current_table/2,	% +Conn, -Table
	    odbc_current_table/3,	% +Conn, -Table, ?Facet
	    odbc_table_column/3,	% +Conn, ?Table, ?Column
	    odbc_table_column/4,	% +Conn, ?Table, ?Column, ?Facet
	    odbc_type/3,		% +Conn, ?Type, -Facet
	    odbc_data_source/2,		% ?DSN, ?Description

	    odbc_table_primary_key/3,	% +Conn, ?Table, ?Column
	    odbc_table_foreign_key/5,	% +Conn, ?PkTable, ?PkColumn, ?FkTable, ?FkColumn

	    odbc_statistics/1,		% -Value
	    odbc_debug/1		% +Level
	  ]).
:- use_module(library(shlib)).
:- use_module(library(lists)).

:- use_foreign_library(foreign(odbc4pl)).

%%	odbc_current_connection(?Conn, ?DSN) is nondet.
%
%	True if Conn is an open ODBC connection to DSN.

odbc_current_connection(Conn, DSN) :-
	odbc_current_connections(Conn, DSN, Pairs),
	member(Conn-DSN, Pairs).

%%	odbc_driver_connect(+DriverString, -Connection, +Options) is det.
%
%	Connects to a database using SQLDriverConnect(). This API allows
%	for driver-specific additional options.   DriverString is passed
%	without  checking.  Options  should  *not*  include  =user=  and
%	=password=.
%
%	Whenever possible, applications should   use  odbc_connect/3. If
%	you need this predicate,  please   check  the  documentation for
%	SQLDriverConnect() and the documentation of your driver.
%
%	@tbd	Add facilities to deal with prompted completion of the
%		driver options.

odbc_driver_connect(DriverString, Connection, Options) :-
	odbc_connect(-, Connection, [driver_string(DriverString)|Options]).

%%	odbc_query(+Connection, +SQL, -Row)
%
%	Run query without options.

odbc_query(Connection, SQL, Row) :-
	odbc_query(Connection, SQL, Row, []).

%%	odbc_query(+Connection, +SQL)
%
%	Execute SQL-statement that does not produce a result

odbc_query(Connection, SQL) :-
	odbc_query(Connection, SQL, Row), !,
	(   Row = affected(_)
	->  true
	;   print_message(warning, odbc(unexpected_result(Row)))
	).

odbc_execute(Statement, Parameters) :-
	odbc_execute(Statement, Parameters, Row), !,
	(   Row = affected(_)
	->  true
	;   print_message(warning, odbc(unexpected_result(Row)))
	).

odbc_prepare(Connection, SQL, Parameters, Statement) :-
	odbc_prepare(Connection, SQL, Parameters, Statement, []).

		 /*******************************
		 *	    SCHEMA STUFF	*
		 *******************************/

%%	odbc_current_table(-Table, -Facet)
%
%	Enumerate the existing tables.

odbc_current_table(Connection, Table) :-
	odbc_tables(Connection, row(_Qualifier, _Owner, Table, 'TABLE', _Comment)).

odbc_current_table(Connection, Table, Facet) :-
	odbc_tables(Connection, Tuple),
	arg(3, Tuple, Table),
	table_facet(Facet, Connection, Tuple).

table_facet(qualifier(Qualifier), _, Tuple) :- arg(1, Tuple, Qualifier).
table_facet(owner(Owner), _, Tuple) :-         arg(2, Tuple, Owner).
table_facet(type(Type), _, Tuple) :-           arg(4, Tuple, Type).
table_facet(comment(Comment), _, Tuple) :-     arg(5, Tuple, Comment).
table_facet(arity(Arity), Connection, Tuple) :-
	arg(3, Tuple, Table),
	findall(C, odbc_table_column(Connection, Table, C), Cs),
	length(Cs, Arity).

%%	odbc_table_column(+Connection, +Table, +Column) is semidet.
%%	odbc_table_column(+Connection, +Table, -Column) is nondet.
%
%	True if Column appears in Table on Connection.

odbc_table_column(Connection, Table, Column) :-
	table_column(Connection, Table, Column, _Tuple).

table_column(Connection, Table, Column, Tuple) :-
	(   var(Table)
	->  odbc_current_table(Connection, Table)
	;   true
	),
	(   ground(Column)		% force determinism
	->  odbc_column(Connection, Table, Tuple),
	    arg(4, Tuple, Column), !
	;   odbc_column(Connection, Table, Tuple),
	    arg(4, Tuple, Column)
	).

%%	odbc_table_column(+Connection, +Table, ?Column, -Facet)

odbc_table_column(Connection, Table, Column, Facet) :-
	table_column(Connection, Table, Column, Tuple),
	column_facet(Facet, Tuple).

column_facet(table_qualifier(Q), T) :- arg(1, T, Q).
column_facet(table_owner(Q), T)	    :- arg(2, T, Q).
column_facet(table_name(Q), T)	    :- arg(3, T, Q).
%column_facet(column_name(Q), T)    :- arg(4, T, Q).
column_facet(data_type(Q), T)	    :- arg(5, T, Q).
column_facet(type_name(Q), T)	    :- arg(6, T, Q).
column_facet(precision(Q), T)	    :- non_null_arg(7, T, Q).
column_facet(length(Q), T)	    :- non_null_arg(8, T, Q).
column_facet(scale(Q), T)	    :- non_null_arg(9, T, Q).
column_facet(radix(Q), T)	    :- non_null_arg(10, T, Q).
column_facet(nullable(Q), T)	    :- non_null_arg(11, T, Q).
column_facet(remarks(Q), T)	    :- non_null_arg(12, T, Q).
column_facet(type(Type), T) :-
	arg(6, T, TypeName),
	sql_type(TypeName, T, Type).

%%	sql_type(+TypeName, +Row, -Type)
%
%	Create a canonical Prolog representation for the type.  This
%	is very incomplete code.

sql_type(dec, T, Type) :- !,
	sql_type(decimal, T, Type).
sql_type(numeric, T, Type) :- !,
	sql_type(decimal, T, Type).
sql_type(decimal, T, Type) :- !,
	column_facet(precision(Len), T),
	(   column_facet(scale(D), T),
	    D \== 0
	->  Type = decimal(Len, D)
	;   Type = decimal(Len)
	).
sql_type(char, T, char(Len)) :- !,
	column_facet(length(Len), T).
sql_type(varchar, T, varchar(Len)) :- !,
	column_facet(length(Len), T).
sql_type(TypeName, _T, Type) :-
	downcase_atom(TypeName, Type).

%%	odbc_type(+Connection, +TypeSpec, ?Facet).

odbc_type(Connection, TypeSpec, Facet) :-
	odbc_types(Connection, TypeSpec, Row),
	type_facet(Facet, Row).

type_facet(name(V), Row)	   :- arg(1, Row, V).
type_facet(data_type(V), Row)	   :- arg(2, Row, V).
type_facet(precision(V), Row)	   :- arg(3, Row, V).
type_facet(literal_prefix(V), Row) :- non_null_arg(4, Row, V).
type_facet(literal_suffix(V), Row) :- non_null_arg(5, Row, V).
type_facet(create_params(V), Row)  :- non_null_arg(6, Row, V).
type_facet(nullable(V), Row)	   :- arg(7, Row, I), nullable_arg(I, V).
type_facet(case_sensitive(V), Row) :- bool_arg(8, Row, V).
type_facet(searchable(V), Row)	   :- arg(9, Row, I), searchable_arg(I, V).
type_facet(unsigned(V), Row)	   :- bool_arg(10, Row, V).
type_facet(money(V), Row)	   :- bool_arg(11, Row, V).
type_facet(auto_increment(V), Row) :- bool_arg(12, Row, V).
type_facet(local_name(V), Row)	   :- non_null_arg(13, Row, V).
type_facet(minimum_scale(V), Row)  :- non_null_arg(14, Row, V).
type_facet(maximum_scale(V), Row)  :- non_null_arg(15, Row, V).

non_null_arg(Index, Row, V) :-
	arg(Index, Row, V),
	V \== '$null$'.
bool_arg(Index, Row, V) :-
	arg(Index, Row, I),
	int_to_bool(I, V).

int_to_bool(0, false).
int_to_bool(1, true).

nullable_arg(0, false).
nullable_arg(1, true).
nullable_arg(2, unknown).

searchable_arg(0, false).
searchable_arg(1, like_only).
searchable_arg(2, all_except_like).
searchable_arg(4, true).


%%	odbc_data_source(?DSN, ?Description)
%
%	Enumerate the available data-sources

odbc_data_source(DSN, Description) :-
	odbc_data_sources(List),
	member(data_source(DSN, Description), List).

		 /*******************************
		 *    Primary & foreign keys    *
		 *******************************/

%%	odbc_table_primary_key(+Connection, +Table, ?Column)
%
%	Enumerate columns in primary key for table

odbc_table_primary_key(Connection, Table, Column) :-
	(   var(Table)
	->  odbc_current_table(Connection, Table)
	;   true
	),
	(   ground(Column)		% force determinism
	->  odbc_primary_key(Connection, Table, Tuple),
	    arg(4, Tuple, Column), !
	;   odbc_primary_key(Connection, Table, Tuple),
	    arg(4, Tuple, Column)
	).

%%	odbc_table_foreign_key(+Connection, ?PkTable, ?PkCol, ?FkTable, ?FkCol)
%
%	Enumerate foreign keys columns

odbc_table_foreign_key(Connection, PkTable, PkColumn, FkTable, FkColumn) :-
	odbc_foreign_key(Connection, PkTable, FkTable, Tuple),
	( var(PkTable) -> arg(3, Tuple, PkTable) ; true ),
	arg(4, Tuple, PkColumn),
	( var(FkTable) -> arg(7, Tuple, FkTable) ; true ),
	arg(8, Tuple, FkColumn).


		 /*******************************
		 *	     STATISTICS		*
		 *******************************/

odbc_statistics(Key) :-
	statistics_key(Key),
	'$odbc_statistics'(Key).

statistics_key(statements(_Created, _Freed)).


		 /*******************************
		 *	      MESSAGES		*
		 *******************************/

:- multifile
	prolog:message/3.

prolog:message(error(odbc(ODBCCode, _NativeCode, Comment), _)) -->
	[ 'ODBC: State ~w: ~w'-[ODBCCode, Comment] ].
prolog:message(error(context_error(Obj, Error, What), _)) -->
	[ 'Context error: ~w ~w: '-[What, Obj] ],
	context(Error).

prolog:message(odbc(ODBCCode, _NativeCode, Comment)) -->
	[ 'ODBC: State ~w: ~w'-[ODBCCode, Comment] ].
prolog:message(odbc(unexpected_result(Row))) -->
	[ 'ODBC: Unexpected result-row: ~p'-[Row] ].

context(in_use) -->
	[ 'object is in use' ].