View source with raw comments or as raw
    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:- use_module(library(shlib)).   74:- use_module(library(lists)).   75
   76:- use_foreign_library(foreign(odbc4pl)).
 odbc_current_connection(?Conn, ?DSN) is nondet
True if Conn is an open ODBC connection to DSN.
   82odbc_current_connection(Conn, DSN) :-
   83    odbc_current_connections(Conn, DSN, Pairs),
   84    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.

To be done
- Add facilities to deal with prompted completion of the driver options.
  100odbc_driver_connect(DriverString, Connection, Options) :-
  101    odbc_connect(-, Connection, [driver_string(DriverString)|Options]).
 odbc_query(+Connection, +SQL, -Row)
Run query without options.
  107odbc_query(Connection, SQL, Row) :-
  108    odbc_query(Connection, SQL, Row, []).
 odbc_query(+Connection, +SQL)
Execute SQL-statement that does not produce a result
  114odbc_query(Connection, SQL) :-
  115    odbc_query(Connection, SQL, Row),
  116    !,
  117    (   Row = affected(_)
  118    ->  true
  119    ;   print_message(warning, odbc(unexpected_result(Row)))
  120    ).
  121
  122odbc_execute(Statement, Parameters) :-
  123    odbc_execute(Statement, Parameters, Row),
  124    !,
  125    (   Row = affected(_)
  126    ->  true
  127    ;   print_message(warning, odbc(unexpected_result(Row)))
  128    ).
  129
  130odbc_prepare(Connection, SQL, Parameters, Statement) :-
  131    odbc_prepare(Connection, SQL, Parameters, Statement, []).
  132
  133                 /*******************************
  134                 *          SCHEMA STUFF        *
  135                 *******************************/
 odbc_current_table(-Table, -Facet)
Enumerate the existing tables.
  141odbc_current_table(Connection, Table) :-
  142    odbc_tables(Connection, row(_Qualifier, _Owner, Table, 'TABLE', _Comment)).
  143
  144odbc_current_table(Connection, Table, Facet) :-
  145    odbc_tables(Connection, Tuple),
  146    arg(3, Tuple, Table),
  147    table_facet(Facet, Connection, Tuple).
  148
  149table_facet(qualifier(Qualifier), _, Tuple) :- arg(1, Tuple, Qualifier).
  150table_facet(owner(Owner), _, Tuple) :-         arg(2, Tuple, Owner).
  151table_facet(type(Type), _, Tuple) :-           arg(4, Tuple, Type).
  152table_facet(comment(Comment), _, Tuple) :-     arg(5, Tuple, Comment).
  153table_facet(arity(Arity), Connection, Tuple) :-
  154    arg(3, Tuple, Table),
  155    findall(C, odbc_table_column(Connection, Table, C), Cs),
  156    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.
  163odbc_table_column(Connection, Table, Column) :-
  164    table_column(Connection, Table, Column, _Tuple).
  165
  166table_column(Connection, Table, Column, Tuple) :-
  167    (   var(Table)
  168    ->  odbc_current_table(Connection, Table)
  169    ;   true
  170    ),
  171    (   ground(Column)              % force determinism
  172    ->  odbc_column(Connection, Table, Tuple),
  173        arg(4, Tuple, Column), !
  174    ;   odbc_column(Connection, Table, Tuple),
  175        arg(4, Tuple, Column)
  176    ).
 odbc_table_column(+Connection, +Table, ?Column, -Facet)
  180odbc_table_column(Connection, Table, Column, Facet) :-
  181    table_column(Connection, Table, Column, Tuple),
  182    column_facet(Facet, Tuple).
  183
  184column_facet(table_qualifier(Q), T) :- arg(1, T, Q).
  185column_facet(table_owner(Q), T)     :- arg(2, T, Q).
  186column_facet(table_name(Q), T)      :- arg(3, T, Q).
  187%column_facet(column_name(Q), T)    :- arg(4, T, Q).
  188column_facet(data_type(Q), T)       :- arg(5, T, Q).
  189column_facet(type_name(Q), T)       :- arg(6, T, Q).
  190column_facet(precision(Q), T)       :- non_null_arg(7, T, Q).
  191column_facet(length(Q), T)          :- non_null_arg(8, T, Q).
  192column_facet(scale(Q), T)           :- non_null_arg(9, T, Q).
  193column_facet(radix(Q), T)           :- non_null_arg(10, T, Q).
  194column_facet(nullable(Q), T)        :- non_null_arg(11, T, Q).
  195column_facet(remarks(Q), T)         :- non_null_arg(12, T, Q).
  196column_facet(type(Type), T) :-
  197    arg(6, T, TypeName),
  198    sql_type(TypeName, T, Type).
 sql_type(+TypeName, +Row, -Type)
Create a canonical Prolog representation for the type. This is very incomplete code.
  205sql_type(dec, T, Type) :-
  206    !,
  207    sql_type(decimal, T, Type).
  208sql_type(numeric, T, Type) :-
  209    !,
  210    sql_type(decimal, T, Type).
  211sql_type(decimal, T, Type) :-
  212    !,
  213    column_facet(precision(Len), T),
  214    (   column_facet(scale(D), T),
  215        D \== 0
  216    ->  Type = decimal(Len, D)
  217    ;   Type = decimal(Len)
  218    ).
  219sql_type(char, T, char(Len)) :-
  220    !,
  221    column_facet(length(Len), T).
  222sql_type(varchar, T, varchar(Len)) :-
  223    !,
  224    column_facet(length(Len), T).
  225sql_type(TypeName, _T, Type) :-
  226    downcase_atom(TypeName, Type).
 odbc_type(+Connection, +TypeSpec, ?Facet)
  230odbc_type(Connection, TypeSpec, Facet) :-
  231    odbc_types(Connection, TypeSpec, Row),
  232    type_facet(Facet, Row).
  233
  234type_facet(name(V), Row)           :- arg(1, Row, V).
  235type_facet(data_type(V), Row)      :- arg(2, Row, V).
  236type_facet(precision(V), Row)      :- arg(3, Row, V).
  237type_facet(literal_prefix(V), Row) :- non_null_arg(4, Row, V).
  238type_facet(literal_suffix(V), Row) :- non_null_arg(5, Row, V).
  239type_facet(create_params(V), Row)  :- non_null_arg(6, Row, V).
  240type_facet(nullable(V), Row)       :- arg(7, Row, I), nullable_arg(I, V).
  241type_facet(case_sensitive(V), Row) :- bool_arg(8, Row, V).
  242type_facet(searchable(V), Row)     :- arg(9, Row, I), searchable_arg(I, V).
  243type_facet(unsigned(V), Row)       :- bool_arg(10, Row, V).
  244type_facet(money(V), Row)          :- bool_arg(11, Row, V).
  245type_facet(auto_increment(V), Row) :- bool_arg(12, Row, V).
  246type_facet(local_name(V), Row)     :- non_null_arg(13, Row, V).
  247type_facet(minimum_scale(V), Row)  :- non_null_arg(14, Row, V).
  248type_facet(maximum_scale(V), Row)  :- non_null_arg(15, Row, V).
  249
  250non_null_arg(Index, Row, V) :-
  251    arg(Index, Row, V),
  252    V \== '$null$'.
  253bool_arg(Index, Row, V) :-
  254    arg(Index, Row, I),
  255    int_to_bool(I, V).
  256
  257int_to_bool(0, false).
  258int_to_bool(1, true).
  259
  260nullable_arg(0, false).
  261nullable_arg(1, true).
  262nullable_arg(2, unknown).
  263
  264searchable_arg(0, false).
  265searchable_arg(1, like_only).
  266searchable_arg(2, all_except_like).
  267searchable_arg(4, true).
 odbc_data_source(?DSN, ?Description)
Enumerate the available data-sources
  274odbc_data_source(DSN, Description) :-
  275    odbc_data_sources(List),
  276    member(data_source(DSN, Description), List).
  277
  278                 /*******************************
  279                 *    Primary & foreign keys    *
  280                 *******************************/
 odbc_table_primary_key(+Connection, +Table, ?Column)
Enumerate columns in primary key for table
  286odbc_table_primary_key(Connection, Table, Column) :-
  287    (   var(Table)
  288    ->  odbc_current_table(Connection, Table)
  289    ;   true
  290    ),
  291    (   ground(Column)              % force determinism
  292    ->  odbc_primary_key(Connection, Table, Tuple),
  293        arg(4, Tuple, Column), !
  294    ;   odbc_primary_key(Connection, Table, Tuple),
  295        arg(4, Tuple, Column)
  296    ).
 odbc_table_foreign_key(+Connection, ?PkTable, ?PkCol, ?FkTable, ?FkCol)
Enumerate foreign keys columns
  302odbc_table_foreign_key(Connection, PkTable, PkColumn, FkTable, FkColumn) :-
  303    odbc_foreign_key(Connection, PkTable, FkTable, Tuple),
  304    ( var(PkTable) -> arg(3, Tuple, PkTable) ; true ),
  305    arg(4, Tuple, PkColumn),
  306    ( var(FkTable) -> arg(7, Tuple, FkTable) ; true ),
  307    arg(8, Tuple, FkColumn).
  308
  309
  310                 /*******************************
  311                 *           STATISTICS         *
  312                 *******************************/
  313
  314odbc_statistics(Key) :-
  315    statistics_key(Key),
  316    '$odbc_statistics'(Key).
  317
  318statistics_key(statements(_Created, _Freed)).
  319
  320
  321                 /*******************************
  322                 *            MESSAGES          *
  323                 *******************************/
  324
  325:- multifile
  326    prolog:message/3.  327
  328prolog:message(error(odbc(ODBCCode, _NativeCode, Comment), _)) -->
  329    [ 'ODBC: State ~w: ~w'-[ODBCCode, Comment] ].
  330prolog:message(error(context_error(Obj, Error, What), _)) -->
  331    [ 'Context error: ~w ~w: '-[What, Obj] ],
  332    context(Error).
  333
  334prolog:message(odbc(ODBCCode, _NativeCode, Comment)) -->
  335    [ 'ODBC: State ~w: ~w'-[ODBCCode, Comment] ].
  336prolog:message(odbc(unexpected_result(Row))) -->
  337    [ 'ODBC: Unexpected result-row: ~p'-[Row] ].
  338
  339context(in_use) -->
  340    [ 'object is in use' ]