1/* Part of SWI-Prolog 2 3 Author: Jan Wielemaker 4 E-mail: J.Wielemaker@vu.nl 5 WWW: http://www.swi-prolog.org 6 Copyright (c) 2002-2013, University of Amsterdam 7 All rights reserved. 8 9 Redistribution and use in source and binary forms, with or without 10 modification, are permitted provided that the following conditions 11 are met: 12 13 1. Redistributions of source code must retain the above copyright 14 notice, this list of conditions and the following disclaimer. 15 16 2. Redistributions in binary form must reproduce the above copyright 17 notice, this list of conditions and the following disclaimer in 18 the documentation and/or other materials provided with the 19 distribution. 20 21 THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS 22 "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT 23 LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS 24 FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE 25 COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, 26 INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, 27 BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; 28 LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER 29 CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT 30 LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN 31 ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE 32 POSSIBILITY OF SUCH DAMAGE. 33*/ 34 35:- module(odbc, 36 [ odbc_connect/3, % +DSN, -Conn, +Options 37 odbc_driver_connect/3, % +DriverString, -Conn, +Options 38 odbc_disconnect/1, % +Conn 39 odbc_current_connection/2, % ?Conn, -DSN 40 odbc_set_connection/2, % +Conn, +Option 41 odbc_get_connection/2, % +Conn, ?Option 42 odbc_end_transaction/2, % +Conn, +CommitRollback 43 44 odbc_query/4, % +Conn, +SQL, -Row, +Options 45 odbc_query/3, % +Conn, +SQL, -Row 46 odbc_query/2, % +Conn, +SQL 47 48 odbc_prepare/4, % +Conn, +SQL, +Parms, -Qid 49 odbc_prepare/5, % +Conn, +SQL, +Parms, -Qid, +Options 50 odbc_execute/2, % +Qid, +Parms 51 odbc_execute/3, % +Qid, +Parms, -Row 52 odbc_fetch/3, % +Qid, -Row, +Options 53 odbc_next_result_set/1, % +Qid 54 odbc_close_statement/1, % +Statement 55 odbc_clone_statement/2, % +Statement, -Clone 56 odbc_free_statement/1, % +Statement 57 odbc_cancel_thread/1, % +ThreadId 58 % DB dictionary info 59 odbc_current_table/2, % +Conn, -Table 60 odbc_current_table/3, % +Conn, -Table, ?Facet 61 odbc_table_column/3, % +Conn, ?Table, ?Column 62 odbc_table_column/4, % +Conn, ?Table, ?Column, ?Facet 63 odbc_type/3, % +Conn, ?Type, -Facet 64 odbc_data_source/2, % ?DSN, ?Description 65 66 odbc_table_primary_key/3, % +Conn, ?Table, ?Column 67 odbc_table_foreign_key/5, % +Conn, ?PkTable, ?PkColumn, ?FkTable, ?FkColumn 68 69 odbc_set_option/1, % -Option 70 odbc_statistics/1, % -Value 71 odbc_debug/1 % +Level 72 ]). 73:- autoload(library(lists),[member/2]). 74 75:- use_foreign_library(foreign(odbc4pl)).
81odbc_current_connection(Conn, DSN) :-
82 odbc_current_connections(Conn, DSN, Pairs),
83 member(Conn-DSN, Pairs).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.
99odbc_driver_connect(DriverString, Connection, Options) :-
100 odbc_connect(-, Connection, [driver_string(DriverString)|Options]).
106odbc_query(Connection, SQL, Row) :-
107 odbc_query(Connection, SQL, Row, []).113odbc_query(Connection, SQL) :- 114 odbc_query(Connection, SQL, Row), 115 !, 116 ( Row = affected(_) 117 -> true 118 ; print_message(warning, odbc(unexpected_result(Row))) 119 ). 120 121odbc_execute(Statement, Parameters) :- 122 odbc_execute(Statement, Parameters, Row), 123 !, 124 ( Row = affected(_) 125 -> true 126 ; print_message(warning, odbc(unexpected_result(Row))) 127 ). 128 129odbc_prepare(Connection, SQL, Parameters, Statement) :- 130 odbc_prepare(Connection, SQL, Parameters, Statement, []). 131 132 /******************************* 133 * SCHEMA STUFF * 134 *******************************/
140odbc_current_table(Connection, Table) :- 141 odbc_tables(Connection, row(_Qualifier, _Owner, Table, 'TABLE', _Comment)). 142 143odbc_current_table(Connection, Table, Facet) :- 144 odbc_tables(Connection, Tuple), 145 arg(3, Tuple, Table), 146 table_facet(Facet, Connection, Tuple). 147 148table_facet(qualifier(Qualifier), _, Tuple) :- arg(1, Tuple, Qualifier). 149table_facet(owner(Owner), _, Tuple) :- arg(2, Tuple, Owner). 150table_facet(type(Type), _, Tuple) :- arg(4, Tuple, Type). 151table_facet(comment(Comment), _, Tuple) :- arg(5, Tuple, Comment). 152table_facet(arity(Arity), Connection, Tuple) :- 153 arg(3, Tuple, Table), 154 findall(C, odbc_table_column(Connection, Table, C), Cs), 155 length(Cs, Arity).
162odbc_table_column(Connection, Table, Column) :- 163 table_column(Connection, Table, Column, _Tuple). 164 165table_column(Connection, Table, Column, Tuple) :- 166 ( var(Table) 167 -> odbc_current_table(Connection, Table) 168 ; true 169 ), 170 ( ground(Column) % force determinism 171 -> odbc_column(Connection, Table, Tuple), 172 arg(4, Tuple, Column), ! 173 ; odbc_column(Connection, Table, Tuple), 174 arg(4, Tuple, Column) 175 ).
179odbc_table_column(Connection, Table, Column, Facet) :- 180 table_column(Connection, Table, Column, Tuple), 181 column_facet(Facet, Tuple). 182 183column_facet(table_qualifier(Q), T) :- arg(1, T, Q). 184column_facet(table_owner(Q), T) :- arg(2, T, Q). 185column_facet(table_name(Q), T) :- arg(3, T, Q). 186%column_facet(column_name(Q), T) :- arg(4, T, Q). 187column_facet(data_type(Q), T) :- arg(5, T, Q). 188column_facet(type_name(Q), T) :- arg(6, T, Q). 189column_facet(precision(Q), T) :- non_null_arg(7, T, Q). 190column_facet(length(Q), T) :- non_null_arg(8, T, Q). 191column_facet(scale(Q), T) :- non_null_arg(9, T, Q). 192column_facet(radix(Q), T) :- non_null_arg(10, T, Q). 193column_facet(nullable(Q), T) :- non_null_arg(11, T, Q). 194column_facet(remarks(Q), T) :- non_null_arg(12, T, Q). 195column_facet(type(Type), T) :- 196 arg(6, T, TypeName), 197 sql_type(TypeName, T, Type).
204sql_type(dec, T, Type) :- 205 !, 206 sql_type(decimal, T, Type). 207sql_type(numeric, T, Type) :- 208 !, 209 sql_type(decimal, T, Type). 210sql_type(decimal, T, Type) :- 211 !, 212 column_facet(precision(Len), T), 213 ( column_facet(scale(D), T), 214 D \== 0 215 -> Type = decimal(Len, D) 216 ; Type = decimal(Len) 217 ). 218sql_type(char, T, char(Len)) :- 219 !, 220 column_facet(length(Len), T). 221sql_type(varchar, T, varchar(Len)) :- 222 !, 223 column_facet(length(Len), T). 224sql_type(TypeName, _T, Type) :- 225 downcase_atom(TypeName, Type).
229odbc_type(Connection, TypeSpec, Facet) :- 230 odbc_types(Connection, TypeSpec, Row), 231 type_facet(Facet, Row). 232 233type_facet(name(V), Row) :- arg(1, Row, V). 234type_facet(data_type(V), Row) :- arg(2, Row, V). 235type_facet(precision(V), Row) :- arg(3, Row, V). 236type_facet(literal_prefix(V), Row) :- non_null_arg(4, Row, V). 237type_facet(literal_suffix(V), Row) :- non_null_arg(5, Row, V). 238type_facet(create_params(V), Row) :- non_null_arg(6, Row, V). 239type_facet(nullable(V), Row) :- arg(7, Row, I), nullable_arg(I, V). 240type_facet(case_sensitive(V), Row) :- bool_arg(8, Row, V). 241type_facet(searchable(V), Row) :- arg(9, Row, I), searchable_arg(I, V). 242type_facet(unsigned(V), Row) :- bool_arg(10, Row, V). 243type_facet(money(V), Row) :- bool_arg(11, Row, V). 244type_facet(auto_increment(V), Row) :- bool_arg(12, Row, V). 245type_facet(local_name(V), Row) :- non_null_arg(13, Row, V). 246type_facet(minimum_scale(V), Row) :- non_null_arg(14, Row, V). 247type_facet(maximum_scale(V), Row) :- non_null_arg(15, Row, V). 248 249non_null_arg(Index, Row, V) :- 250 arg(Index, Row, V), 251 V \== '$null$'. 252bool_arg(Index, Row, V) :- 253 arg(Index, Row, I), 254 int_to_bool(I, V). 255 256int_to_bool(0, false). 257int_to_bool(1, true). 258 259nullable_arg(0, false). 260nullable_arg(1, true). 261nullable_arg(2, unknown). 262 263searchable_arg(0, false). 264searchable_arg(1, like_only). 265searchable_arg(2, all_except_like). 266searchable_arg(4, true).
273odbc_data_source(DSN, Description) :- 274 odbc_data_sources(List), 275 member(data_source(DSN, Description), List). 276 277 /******************************* 278 * Primary & foreign keys * 279 *******************************/
285odbc_table_primary_key(Connection, Table, Column) :-
286 ( var(Table)
287 -> odbc_current_table(Connection, Table)
288 ; true
289 ),
290 ( ground(Column) % force determinism
291 -> odbc_primary_key(Connection, Table, Tuple),
292 arg(4, Tuple, Column), !
293 ; odbc_primary_key(Connection, Table, Tuple),
294 arg(4, Tuple, Column)
295 ).301odbc_table_foreign_key(Connection, PkTable, PkColumn, FkTable, FkColumn) :- 302 odbc_foreign_key(Connection, PkTable, FkTable, Tuple), 303 ( var(PkTable) -> arg(3, Tuple, PkTable) ; true ), 304 arg(4, Tuple, PkColumn), 305 ( var(FkTable) -> arg(7, Tuple, FkTable) ; true ), 306 arg(8, Tuple, FkColumn). 307 308 309 /******************************* 310 * STATISTICS * 311 *******************************/ 312 313odbc_statistics(Key) :- 314 statistics_key(Key), 315 '$odbc_statistics'(Key). 316 317statistics_key(statements(_Created, _Freed)). 318 319 320 /******************************* 321 * MESSAGES * 322 *******************************/ 323 324:- multifile 325 prolog:message/3. 326 327prologmessage(error(odbc(ODBCCode, _NativeCode, Comment), _)) --> 328 [ 'ODBC: State ~w: ~w'-[ODBCCode, Comment] ]. 329prologmessage(error(context_error(Obj, Error, What), _)) --> 330 [ 'Context error: ~w ~w: '-[What, Obj] ], 331 context(Error). 332 333prologmessage(odbc(ODBCCode, _NativeCode, Comment)) --> 334 [ 'ODBC: State ~w: ~w'-[ODBCCode, Comment] ]. 335prologmessage(odbc(unexpected_result(Row))) --> 336 [ 'ODBC: Unexpected result-row: ~p'-[Row] ]. 337 338context(in_use) --> 339 [ 'object is in use' ]