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 % DB dictionary info 58 odbc_current_table/2, % +Conn, -Table 59 odbc_current_table/3, % +Conn, -Table, ?Facet 60 odbc_table_column/3, % +Conn, ?Table, ?Column 61 odbc_table_column/4, % +Conn, ?Table, ?Column, ?Facet 62 odbc_type/3, % +Conn, ?Type, -Facet 63 odbc_data_source/2, % ?DSN, ?Description 64 65 odbc_table_primary_key/3, % +Conn, ?Table, ?Column 66 odbc_table_foreign_key/5, % +Conn, ?PkTable, ?PkColumn, ?FkTable, ?FkColumn 67 68 odbc_set_option/1, % -Option 69 odbc_statistics/1, % -Value 70 odbc_debug/1 % +Level 71 ]). 72:- autoload(library(lists),[member/2]). 73 74:- use_foreign_library(foreign(odbc4pl)). 75 76:- if(current_predicate(odbc_cancel_thread/1)). 77:- export(odbc_cancel_thread/1). % +ThreadId 78:- endif.
84odbc_current_connection(Conn, DSN) :-
85 odbc_current_connections(Conn, DSN, Pairs),
86 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.
102odbc_driver_connect(DriverString, Connection, Options) :-
103 odbc_connect(-, Connection, [driver_string(DriverString)|Options]).
109odbc_query(Connection, SQL, Row) :-
110 odbc_query(Connection, SQL, Row, []).
116odbc_query(Connection, SQL) :- 117 odbc_query(Connection, SQL, Row), 118 !, 119 ( Row = affected(_) 120 -> true 121 ; print_message(warning, odbc(unexpected_result(Row))) 122 ). 123 124odbc_execute(Statement, Parameters) :- 125 odbc_execute(Statement, Parameters, Row), 126 !, 127 ( Row = affected(_) 128 -> true 129 ; print_message(warning, odbc(unexpected_result(Row))) 130 ). 131 132odbc_prepare(Connection, SQL, Parameters, Statement) :- 133 odbc_prepare(Connection, SQL, Parameters, Statement, []). 134 135 /******************************* 136 * SCHEMA STUFF * 137 *******************************/
143odbc_current_table(Connection, Table) :- 144 odbc_tables(Connection, row(_Qualifier, _Owner, Table, 'TABLE', _Comment)). 145 146odbc_current_table(Connection, Table, Facet) :- 147 odbc_tables(Connection, Tuple), 148 arg(3, Tuple, Table), 149 table_facet(Facet, Connection, Tuple). 150 151table_facet(qualifier(Qualifier), _, Tuple) :- arg(1, Tuple, Qualifier). 152table_facet(owner(Owner), _, Tuple) :- arg(2, Tuple, Owner). 153table_facet(type(Type), _, Tuple) :- arg(4, Tuple, Type). 154table_facet(comment(Comment), _, Tuple) :- arg(5, Tuple, Comment). 155table_facet(arity(Arity), Connection, Tuple) :- 156 arg(3, Tuple, Table), 157 findall(C, odbc_table_column(Connection, Table, C), Cs), 158 length(Cs, Arity).
165odbc_table_column(Connection, Table, Column) :- 166 table_column(Connection, Table, Column, _Tuple). 167 168table_column(Connection, Table, Column, Tuple) :- 169 ( var(Table) 170 -> odbc_current_table(Connection, Table) 171 ; true 172 ), 173 ( ground(Column) % force determinism 174 -> odbc_column(Connection, Table, Tuple), 175 arg(4, Tuple, Column), ! 176 ; odbc_column(Connection, Table, Tuple), 177 arg(4, Tuple, Column) 178 ).
182odbc_table_column(Connection, Table, Column, Facet) :- 183 table_column(Connection, Table, Column, Tuple), 184 column_facet(Facet, Tuple). 185 186column_facet(table_qualifier(Q), T) :- arg(1, T, Q). 187column_facet(table_owner(Q), T) :- arg(2, T, Q). 188column_facet(table_name(Q), T) :- arg(3, T, Q). 189%column_facet(column_name(Q), T) :- arg(4, T, Q). 190column_facet(data_type(Q), T) :- arg(5, T, Q). 191column_facet(type_name(Q), T) :- arg(6, T, Q). 192column_facet(precision(Q), T) :- non_null_arg(7, T, Q). 193column_facet(length(Q), T) :- non_null_arg(8, T, Q). 194column_facet(scale(Q), T) :- non_null_arg(9, T, Q). 195column_facet(radix(Q), T) :- non_null_arg(10, T, Q). 196column_facet(nullable(Q), T) :- non_null_arg(11, T, Q). 197column_facet(remarks(Q), T) :- non_null_arg(12, T, Q). 198column_facet(type(Type), T) :- 199 arg(6, T, TypeName), 200 sql_type(TypeName, T, Type).
207sql_type(dec, T, Type) :- 208 !, 209 sql_type(decimal, T, Type). 210sql_type(numeric, T, Type) :- 211 !, 212 sql_type(decimal, T, Type). 213sql_type(decimal, T, Type) :- 214 !, 215 column_facet(precision(Len), T), 216 ( column_facet(scale(D), T), 217 D \== 0 218 -> Type = decimal(Len, D) 219 ; Type = decimal(Len) 220 ). 221sql_type(char, T, char(Len)) :- 222 !, 223 column_facet(length(Len), T). 224sql_type(varchar, T, varchar(Len)) :- 225 !, 226 column_facet(length(Len), T). 227sql_type(TypeName, _T, Type) :- 228 downcase_atom(TypeName, Type).
232odbc_type(Connection, TypeSpec, Facet) :- 233 odbc_types(Connection, TypeSpec, Row), 234 type_facet(Facet, Row). 235 236type_facet(name(V), Row) :- arg(1, Row, V). 237type_facet(data_type(V), Row) :- arg(2, Row, V). 238type_facet(precision(V), Row) :- arg(3, Row, V). 239type_facet(literal_prefix(V), Row) :- non_null_arg(4, Row, V). 240type_facet(literal_suffix(V), Row) :- non_null_arg(5, Row, V). 241type_facet(create_params(V), Row) :- non_null_arg(6, Row, V). 242type_facet(nullable(V), Row) :- arg(7, Row, I), nullable_arg(I, V). 243type_facet(case_sensitive(V), Row) :- bool_arg(8, Row, V). 244type_facet(searchable(V), Row) :- arg(9, Row, I), searchable_arg(I, V). 245type_facet(unsigned(V), Row) :- bool_arg(10, Row, V). 246type_facet(money(V), Row) :- bool_arg(11, Row, V). 247type_facet(auto_increment(V), Row) :- bool_arg(12, Row, V). 248type_facet(local_name(V), Row) :- non_null_arg(13, Row, V). 249type_facet(minimum_scale(V), Row) :- non_null_arg(14, Row, V). 250type_facet(maximum_scale(V), Row) :- non_null_arg(15, Row, V). 251 252non_null_arg(Index, Row, V) :- 253 arg(Index, Row, V), 254 V \== '$null$'. 255bool_arg(Index, Row, V) :- 256 arg(Index, Row, I), 257 int_to_bool(I, V). 258 259int_to_bool(0, false). 260int_to_bool(1, true). 261 262nullable_arg(0, false). 263nullable_arg(1, true). 264nullable_arg(2, unknown). 265 266searchable_arg(0, false). 267searchable_arg(1, like_only). 268searchable_arg(2, all_except_like). 269searchable_arg(4, true).
276odbc_data_source(DSN, Description) :- 277 odbc_data_sources(List), 278 member(data_source(DSN, Description), List). 279 280 /******************************* 281 * Primary & foreign keys * 282 *******************************/
288odbc_table_primary_key(Connection, Table, Column) :-
289 ( var(Table)
290 -> odbc_current_table(Connection, Table)
291 ; true
292 ),
293 ( ground(Column) % force determinism
294 -> odbc_primary_key(Connection, Table, Tuple),
295 arg(4, Tuple, Column), !
296 ; odbc_primary_key(Connection, Table, Tuple),
297 arg(4, Tuple, Column)
298 ).
304odbc_table_foreign_key(Connection, PkTable, PkColumn, FkTable, FkColumn) :- 305 odbc_foreign_key(Connection, PkTable, FkTable, Tuple), 306 ( var(PkTable) -> arg(3, Tuple, PkTable) ; true ), 307 arg(4, Tuple, PkColumn), 308 ( var(FkTable) -> arg(7, Tuple, FkTable) ; true ), 309 arg(8, Tuple, FkColumn). 310 311 312 /******************************* 313 * STATISTICS * 314 *******************************/ 315 316odbc_statistics(Key) :- 317 statistics_key(Key), 318 '$odbc_statistics'(Key). 319 320statistics_key(statements(_Created, _Freed)). 321 322 323 /******************************* 324 * MESSAGES * 325 *******************************/ 326 327:- multifile 328 prolog:message/3. 329 330prologmessage(error(odbc(ODBCCode, _NativeCode, Comment), _)) --> 331 [ 'ODBC: State ~w: ~w'-[ODBCCode, Comment] ]. 332prologmessage(error(context_error(Obj, Error, What), _)) --> 333 [ 'Context error: ~w ~w: '-[What, Obj] ], 334 context(Error). 335 336prologmessage(odbc(ODBCCode, _NativeCode, Comment)) --> 337 [ 'ODBC: State ~w: ~w'-[ODBCCode, Comment] ]. 338prologmessage(odbc(unexpected_result(Row))) --> 339 [ 'ODBC: Unexpected result-row: ~p'-[Row] ]. 340 341context(in_use) --> 342 [ 'object is in use' ]