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)). 76 77%! odbc_current_connection(?Conn, ?DSN) is nondet. 78% 79% True if Conn is an open ODBC connection to DSN. 80 81odbc_current_connection(Conn, DSN) :- 82 odbc_current_connections(Conn, DSN, Pairs), 83 member(Conn-DSN, Pairs). 84 85%! odbc_driver_connect(+DriverString, -Connection, +Options) is det. 86% 87% Connects to a database using SQLDriverConnect(). This API allows 88% for driver-specific additional options. DriverString is passed 89% without checking. Options should *not* include =user= and 90% =password=. 91% 92% Whenever possible, applications should use odbc_connect/3. If 93% you need this predicate, please check the documentation for 94% SQLDriverConnect() and the documentation of your driver. 95% 96% @tbd Add facilities to deal with prompted completion of the 97% driver options. 98 99odbc_driver_connect(DriverString, Connection, Options) :- 100 odbc_connect(-, Connection, [driver_string(DriverString)|Options]). 101 102%! odbc_query(+Connection, +SQL, -Row) 103% 104% Run query without options. 105 106odbc_query(Connection, SQL, Row) :- 107 odbc_query(Connection, SQL, Row, []). 108 109%! odbc_query(+Connection, +SQL) 110% 111% Execute SQL-statement that does not produce a result 112 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 *******************************/ 135 136%! odbc_current_table(-Table, -Facet) 137% 138% Enumerate the existing tables. 139 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). 156 157%! odbc_table_column(+Connection, +Table, +Column) is semidet. 158%! odbc_table_column(+Connection, +Table, -Column) is nondet. 159% 160% True if Column appears in Table on Connection. 161 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 ). 176 177%! odbc_table_column(+Connection, +Table, ?Column, -Facet) 178 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). 198 199%! sql_type(+TypeName, +Row, -Type) 200% 201% Create a canonical Prolog representation for the type. This 202% is very incomplete code. 203 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). 226 227%! odbc_type(+Connection, +TypeSpec, ?Facet). 228 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). 267 268 269%! odbc_data_source(?DSN, ?Description) 270% 271% Enumerate the available data-sources 272 273odbc_data_source(DSN, Description) :- 274 odbc_data_sources(List), 275 member(data_source(DSN, Description), List). 276 277 /******************************* 278 * Primary & foreign keys * 279 *******************************/ 280 281%! odbc_table_primary_key(+Connection, +Table, ?Column) 282% 283% Enumerate columns in primary key for table 284 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 ). 296 297%! odbc_table_foreign_key(+Connection, ?PkTable, ?PkCol, ?FkTable, ?FkCol) 298% 299% Enumerate foreign keys columns 300 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 327prolog:message(error(odbc(ODBCCode, _NativeCode, Comment), _)) --> 328 [ 'ODBC: State ~w: ~w'-[ODBCCode, Comment] ]. 329prolog:message(error(context_error(Obj, Error, What), _)) --> 330 [ 'Context error: ~w ~w: '-[What, Obj] ], 331 context(Error). 332 333prolog:message(odbc(ODBCCode, _NativeCode, Comment)) --> 334 [ 'ODBC: State ~w: ~w'-[ODBCCode, Comment] ]. 335prolog:message(odbc(unexpected_result(Row))) --> 336 [ 'ODBC: Unexpected result-row: ~p'-[Row] ]. 337 338context(in_use) --> 339 [ 'object is in use' ]. 340