1% BEGIN LICENSE BLOCK 2% Version: CMPL 1.1 3% 4% The contents of this file are subject to the Cisco-style Mozilla Public 5% License Version 1.1 (the "License"); you may not use this file except 6% in compliance with the License. You may obtain a copy of the License 7% at www.eclipse-clp.org/license. 8% 9% Software distributed under the License is distributed on an "AS IS" 10% basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See 11% the License for the specific language governing rights and limitations 12% under the License. 13% 14% The Original Code is The ECLiPSe Constraint Logic Programming System. 15% The Initial Developer of the Original Code is Cisco Systems, Inc. 16% Portions created by the Initial Developer are 17% Copyright (C) 1997 - 2006 Cisco Systems, Inc. All Rights Reserved. 18% 19% Contributor(s): Joachim Schimpf, Stefano Novello, IC-Parc 20% Kish Shen, CrossCore Optimization 21% 22% END LICENSE BLOCK 23% 24% ECLiPSe PROLOG LIBRARY MODULE 25% 26% $Header: /cvsroot/eclipse-clp/Eclipse/Oci/dbi.ecl,v 1.8 2013/06/20 20:59:09 kish_shen Exp $ 27% 28% 29% IDENTIFICATION: dbi.ecl 30% 31% AUTHOR: Joachim Schimpf 32% AUTHOR: Stefano Novello 33% 34 35 36/* 37 * char *sccsid = "@(#)oci.pl 1.2 96/11/21"; 38 * 39 * Contents: Prolog library for DB interface 40 * 41 * Author: Stefano Novello 42 * Author: Kish Shen, Generalised and updated from original OCI code, 43 * initially for MySQL, Jan - Feb 2006. 44 * 45 * 46 */ 47%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 48:- module(dbi). 49%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 50 51:- comment(categories, ["Interfacing"]). 52:- comment(summary, "Interface to MySQL databases"). 53:- comment(author, "Kish Shen, based on Oracle interface by Stefano Novello"). 54:- comment(date, "$Date: 2013/06/20 20:59:09 $"). 55:- comment(copyright, "Cisco Systems, 2006"). 56 57:- lib(lists). 58:- lib(suspend). 59 60:- export 61 session_start/4, 62 session_start/3, 63 session_close/1, 64 session_error_value/3, 65 66 session_commit/1, 67 session_rollback/1, 68 session_transaction/2, 69 session_transaction/3, 70 71 session_sql/3, 72 73 session_sql_prepare/4, 74 session_N_sql_prepare/5, 75 cursor_next_execute/2, 76 cursor_next_execute/3, 77 cursor_N_execute/4, 78 cursor_all_execute/2, 79 80 cursor_close/1, 81 session_sql_query/4, 82 session_sql_query/5, 83 session_sql_query_N/6, 84 85 cursor_next_tuple/2, 86 cursor_N_tuples/4, 87 cursor_all_tuples/2, 88 session_sql_prepare_query/5, 89 session_sql_prepare_query_N/6, 90 91 session_retrieve_tuple/4, 92 session_retrieve_N_tuples/5, 93 session_retrieve_lazy_tuples/5, 94 95 cursor_field_value/3. 96 97 98:- export struct(cursor( 99 handle, % ec_handle. C code assumes this 100 % is in argument position 1. 101 session % parent session 102 ) 103 ). 104 105 106:- pragma(expand). 107:- import symbol_address/2,get_cut/1,cut_to/1 from sepia_kernel. 108 109% load the dynamic MySQL library for Windows explicitly 110% to avoid path problems 111load_dynamic_mysql("i386_nt", SUF) :- !, 112 concat_string(["i386_nt/libmysql.", SUF], F), 113 load(F). 114load_dynamic_mysql("x86_64_nt", SUF) :- !, 115 concat_string(["x86_64_nt/libmysql.", SUF], F), 116 load(F). 117load_dynamic_mysql(_, _). 118 119:- symbol_address(p_dbi_init, _ ) -> true ; 120 get_flag(object_suffix,SUF), 121 get_flag(hostarch, Arch), 122 catch(load_dynamic_mysql(Arch, SUF), abort, 123 (printf(error, "Cannot find/load the dyanmic libmysql.%s" 124 " library.%n", [SUF]), 125 writeln(error, "You can obtain this file as part of MySQL."), 126 printf(error, "Place the file into lib/%s directory of ECLiPSe" 127 " to use Dbi.%n", [Arch]), 128 fail 129 ) 130 ), 131 concat_string([Arch,/,"dbi_mysql.",SUF],F), 132 load(F). 133 134 135:- external( s_init/1, p_session_init), 136 external( s_start/5, p_session_start), 137 external( s_error_value/3, p_session_error_value), 138 external( s_commit/1, p_session_commit), 139 external( s_rollback/1, p_session_rollback), 140 external( s_close/1, p_session_close), 141 142% external( s_sql_ddl/2, p_session_sql_ddl), 143 external( s_sql_dml/3, p_session_sql_dml), 144 external( s_sql_query/6, p_session_sql_query), 145 external( s_sql_prepare/5, p_session_sql_prepare), 146 external( s_sql_prepare_query/6, p_session_sql_prepare_query), 147 external( s_set_in_transaction/2, p_session_set_in_transaction), 148 external( s_is_in_transaction/1, p_session_is_in_transaction), 149 150 external( cursor_next_exec/3, p_cursor_next_execute), 151 external( cursor_N_execute/4, p_cursor_N_execute), 152 external( cursor_next_tuple/2, p_cursor_next_tuple), 153 external( cursor_N_tuples/4, p_cursor_N_tuples), 154 155 external( c_field_value/3, p_cursor_field_value), 156 external( cursor_free/1, p_cursor_free), 157 158 external( handle_free_eagerly/1, p_handle_free_eagerly), 159 160 external( dbi_init/1, p_dbi_init), 161 external( dbi_final/0, p_dbi_final). 162 163 164:- include(mysqlopts). 165 166 167:- mode extract_session(+,-). 168extract_session(s_sql_ddl(S,_),S). 169extract_session(s_sql_dml(S,_,_),S). 170extract_session(s_sql_query(S,_,_,_,_,_),S). 171extract_session(s_sql_prepare(S,_,_,_,_),S). 172extract_session(s_sql_prepare_query(S,_,_,_,_,_),S). 173extract_session(s_close(S),S). 174extract_session(s_commit(S),S). 175extract_session(s_rollback(S),S). 176extract_session(s_init(S),S). 177extract_session(s_start(S,_,_,_,_),S). 178 179:- mode extract_cursor(+,-). 180extract_cursor(cursor_next_exec(C,_,_),C). 181extract_cursor(cursor_next_execute(C,_),C). 182extract_cursor(cursor_next_execute(C,_,_),C). 183extract_cursor(cursor_N_execute(C,_,_,_),C). 184extract_cursor(cursor_next_tuple(C,_),C). 185extract_cursor(cursor_N_tuples(C,_,_,_),C). 186extract_cursor(cursor_close(C),C). 187 188session_abort(S) :- 189 (nonvar(S), s_is_in_transaction(S) -> 190 exit_block(dbi_fail) 191 ; 192 abort 193 ). 194 195cursor_session(cursor{session:S0},S) ?- S0 = S. 196 197 198 199dbi_error_handler(Error, Goal) :- 200 extract_cursor(Goal,Cursor), 201 !, 202 error_id(Error,Id), 203 cursor_field_value(Cursor,return_code_as_string,Message), 204 cursor_field_value(Cursor,return_code,Code), 205 printf(stderr,"%s Code:%d in %PQw%n%s%n",[Id,Code,Goal,Message]), 206 flush(stderr), 207 cursor_session(Cursor,Session), 208 session_abort(Session). 209 210dbi_error_handler(Error,Goal) :- 211 extract_session(Goal,S), 212 nonvar(S), 213 s_error_value(S,Code,Message), 214 !, 215 error_id(Error,Id), 216 printf(stderr,"%s Code:%d in %PQw%n%s%n",[Id,Code,Goal,Message]), 217 flush(stderr), 218 session_abort(S). 219 220dbi_error_handler(Error,Goal) :- 221 error_id(Error,Id), 222 printf(stderr,"%s in %PQw%n",[Id,Goal]), 223 flush(stderr), 224 abort. 225 226 227:- tool(session_transaction/2,session_transaction/3). 228session_transaction(Session,Goal,Module) :- 229 (s_is_in_transaction(Session) -> 230 once(call(Goal)@Module) 231 ; 232 s_set_in_transaction(Session, 1), 233 ( block( once(call(Goal)@Module) , Tag, 234 transaction_recovery(Session,Tag) ) -> 235 s_set_in_transaction(Session, 0), 236 s_commit(Session) 237 ; 238 transaction_recovery(Session,dbi_fail) 239 ) 240 ). 241 242 transaction_recovery(Session,Tag) :- 243 s_set_in_transaction(Session, 0), 244 s_rollback(Session), 245 (Tag = dbi_fail -> 246 fail 247 ; 248 exit_block(Tag) 249 ). 250 251get_user_and_host(User0@Host0, User, Host) :- !, 252 concat_string([User0], User), 253 concat_string([Host0], Host). 254get_user_and_host(User0, User, Host) :- 255 concat_string([User0], User), 256 Host = "". 257 258session_start(Login,Passwd,Session) :- 259 session_start(Login,Passwd,[],Session). 260 261session_start(Login,Passwd0,OptsList,Session) :- 262 get_user_and_host(Login, User, Host), 263 get_opts(session, OptsList,Opts), !, 264 concat_string([Passwd0], Passwd), 265 s_init(Session), 266 s_start(Session,User,Host,Passwd,Opts). 267session_start(Login,Passwd,OptsList,Session) :- 268 error(6,session_start(Login,Passwd,OptsList,Session)). 269 270 271session_error_value(Session,Code,Message) :- 272 ( is_handle(Session) -> 273 s_error_value(Session,Code,Message) 274 ; 275 error(5, session_error_value(Session,Code,Message)) 276 ). 277 278 279session_commit(Session) :- 280 (s_is_in_transaction(Session) -> true ; s_commit(Session)). 281 282session_rollback(Session) :- 283 (s_is_in_transaction(Session) -> 284 exit_block(dbi_fail) 285 ; 286 s_rollback(Session) 287 ). 288 289 290session_sql(Session,SQL,Tuples) :- 291 ( is_handle(Session) -> 292 s_sql_dml(Session,SQL,Tuples) 293 ; 294 error(5, session_sql(Session,SQL,Tuples)) 295 ). 296 297session_sql_query(Session,Template,SQL,OptsList,Cursor) :- 298 Cursor = cursor{handle:CursorH,session:Session}, 299 ( get_opts(cursor, OptsList, Opts) -> 300 s_sql_query(Session, Template,SQL,1,Opts,CursorH) 301 ; 302 printf(error, "Invalid option list in %w%n", [OptsList]), 303 error(6, session_sql_query(Session, Template,SQL,OptsList,Cursor)) 304 ). 305 306session_sql_query(Session,Template,SQL,Cursor) :- 307 session_sql_query_N(Session,Template,SQL,1,[],Cursor). 308 309 310session_sql_query_N(Session,Template,SQL,N,OptsList,Cursor) :- 311 Cursor = cursor{handle:CursorH,session:Session}, 312 ( get_opts(cursor, OptsList, Opts) -> 313 s_sql_query(Session,Template,SQL,N,Opts,CursorH) 314 ; 315 printf(error, "Invalid option list in %w%n", [OptsList]), 316 error(6, session_sql_query_N(Session, Template,SQL,N,OptsList,Cursor)) 317 ). 318 319 320session_sql_prepare(Session,Template,SQL,Cursor) :- 321 Cursor = cursor{handle:CursorH,session:Session}, 322 s_sql_prepare(Session,Template,SQL,1,CursorH). 323 324session_N_sql_prepare(Session,Template,SQL,N,Cursor) :- 325 Cursor = cursor{handle:CursorH,session:Session}, 326 s_sql_prepare(Session,Template,SQL,N,CursorH). 327 328 329session_sql_prepare_query(Session,ParamT,QueryT,SQL,Cursor) :- 330 Cursor = cursor{handle:CursorH,session:Session}, 331 s_sql_prepare_query(Session,ParamT,QueryT,SQL,1,CursorH). 332 333session_sql_prepare_query_N(Session,ParamT,QueryT,SQL,N,Cursor) :- 334 Cursor = cursor{handle:CursorH,session:Session}, 335 s_sql_prepare_query(Session,ParamT,QueryT,SQL,N,CursorH). 336 337 338session_close(Session) :- 339 ( is_handle(Session) -> 340 s_close(Session) 341 ; 342 error(5, session_close(Session)) 343 ). 344 345 346% 347% Mapping between atoms and field_t C enumerated type in dbi.h 348:- mode field_t(++,-). 349 350field_t( state, 0). 351field_t( rows_processed_count, 1). 352field_t( return_code, 2). 353field_t( return_code_as_string, 3). 354field_t( warning_flags, 4). 355field_t( row_ID, 5). 356 357cursor_close(cursor{handle:Handle}) ?- !, 358 cursor_free(Handle). 359cursor_close(Cursor) :- 360 error(5, cursor_close(Cursor)). 361 362cursor_field_value(Cursor,Field,Value) :- 363 atom(Field), 364 Cursor = cursor{}, 365 field_t(Field,F), 366 !, 367 c_field_value(Cursor,F,Value). 368cursor_field_value(Cursor,Field,Value) :- 369 error(5,cursor_field_value(Cursor,Field,Value)). 370 371 372session_retrieve_tuple(Session,SQL,Template,Tuple) :- 373 session_sql_query(Session,Template,SQL,10,Cursor), 374 handle_free_eagerly(Cursor), 375 repeat, 376 ( cursor_next_tuple(Cursor,Tuple0) -> 377 Tuple0 = Tuple 378 ; 379 !,fail 380 ). 381 382 383session_retrieve_N_tuples(Session,Template,SQL,N,Tuple) :- 384 session_sql_query(Session,Template,SQL,N,Cursor), 385 handle_free_eagerly(Cursor), 386 repeat, 387 cursor_N_tuples(Cursor,R,Tuples,[]), 388 (R < N -> 389 ! 390 ; 391 true 392 ), 393 member(Tuple, Tuples). 394 395cursor_all_tuples(Cursor,Tuples) :- 396 cursor_N_tuples(Cursor,N,Tuples,Rest), 397 ( N > 0 -> 398 cursor_all_tuples(Cursor,Rest) 399 ; 400 Rest = [] 401 ). 402 403 404session_retrieve_lazy_tuples(Session,Template,SQL,N,Tuples) :- 405 get_cut(Cut), 406 session_sql_query(Session,Template,SQL,N,Cursor), 407 cursor_lazy_tuples(Cursor,Cut,Tuples). 408 409cursor_lazy_tuples(Cursor,Cut,Tuples) :- 410 var(Tuples), 411 !, 412 suspend(cursor_lazy_tuples(Cursor,Cut,Tuples),3,Tuples->inst). 413 414cursor_lazy_tuples(Cursor,Cut,Tuples) :- 415 get_cut(CutNow), 416 (CutNow \= Cut -> 417 cut_to(Cut), 418 printf(stderr, 419 "warning: cutting in cursor_lazy_tuples/3\n") 420 ; 421 true 422 ), 423 cursor_N_tuples(Cursor,N,Tuples,Rest), 424 (N > 0 -> 425 cursor_lazy_tuples(Cursor,CutNow,Rest) 426 ; 427 Rest = [] 428 ). 429 430cursor_next_execute(Cursor, Tuple) :- 431 cursor_next_execute(Cursor, Tuple, []). 432 433cursor_next_execute(Cursor, Tuple, OptsList) :- 434 ( get_opts(cursor, OptsList, Opts) -> 435 cursor_next_exec(Cursor, Tuple, Opts) 436 ; 437 printf(error, "Invalid option list in %w%n", [OptsList]), 438 error(6, cursor_next_execute(Cursor, Tuple, OptsList)) 439 ). 440 441 442cursor_all_execute(_Cursor,[]) :- !. 443cursor_all_execute(Cursor,Tuples) :- 444 cursor_N_execute(Cursor,_,Tuples,Rest), 445 cursor_all_execute(Cursor,Rest). 446 447:- pragma(deprecated_warnings(off)). 448 449dbi_initialize :- 450 define_error("DBI General exception",E), 451 set_event_handler(E,dbi_error_handler/2), 452 dbi_init(E). 453 454dbi_finalize :- 455 dbi_final. 456 457:- local initialization(dbi_initialize), finalization(dbi_finalize). 458 459 460/******************************************************************************/ 461 462:- comment(desc, html("\ 463<P> 464 This library provides an interface to database management systems (DBMS) 465 that supports SQL (Structure Query Language), allowing SQL commands, 466 written in an ECLiPSe program, to be passed to the DBMS for processing, 467 and their results (if any) to be passed back to ECLiPSe. 468</P><P> 469 The exact SQL supported by the library is determined by the underlying 470 DBMS, as the SQL commands are passed directly to the DBMS via its C API. 471 If supported by the C API, the library also allows the use of prepared SQL 472 statements, where a SQL statement, possibly parameterised to accept 473 different input data, is parsed once by the DBMS, and efficiently executed 474 multiple times. Support for transactions (with commit and rollback) is also 475 provided. 476</P><P> 477 The library provides a relatively low-level interface to the DBMS, but 478 higher level interfaces (e.g. where a SQL query is viewed as a predicate, 479 yielding different results tuples on backtracking) can be constructed on 480 top of this. 481</P><P> 482 Currently, MySQL (version 5.0 or later) is supported by the library. 483 Note that the MySQL client dynamic library (libmysqlclient.so on Unix 484 systems, mysql.dll on Windows) is not included with the distribution, and 485 if this file is not found by ECLiPSe on loading lib(dbi), there will be 486 an error. This file can be obtained by downloading MySQL, and placing the 487 .so or .dll file into a place where ECLiPSe can find it, for example a 488 standard system library location, or in the ECLiPSe library location 489 (<eclipsedir>/lib/<arch>). 490 491</P><P> 492 Data is exchanged with the DBMS via: 493<DL> 494<P> 495<DT>SQL statements (input) 496 <DD>Directly in the SQL statements passed to the DBMS; 497<P> 498<DT>SQL query results (output) 499 <DD>The results returned by SQL queries are returned to ECLiPSe via 500 result tuples. Each tuple represents a single row of result, and is 501 returned to ECLiPSe as a Prolog structure, with each argument 502 represents one column in the result tuple. The ordering of the 503 arguments corresponds to the ordering defined by the SQL query. 504<DT>Parameters for prepared SQL statements (input) 505 <DD>If prepared statements are supported by the DBMS, data can also be 506 passed to the DBMS via parameters (also known as placeholders). Each 507 parameter functions like a variable, whose value is instantiated when 508 the statement is executed. The library passes parameter values to the 509 DBMS via Prolog structures, with each argument represent one parameter. 510 The ordering of the arguments is defined by the ordering of the 511 parameters in the prepared SQL statement. 512 </DL> 513<P> 514 Input and output data in the structures need to be converted from/to 515 ECLiPSe datatypes. The ECLiPSe datatype is specified by a template, which 516 specifies the datatype by example, i.e. an example result tuple structure 517 (for output) or parameter structure (for input) where each argument 518 specify the type for that argument, e.g. 519 <TT>emp(123,\"some name\",100.0,'some job',extra(data))</TT>, 520 specifies that the first argument should be an integer, the second a 521 string, the third a real, the fourth, an atom, and the last, a general 522 Prolog term. Note that general Prolog terms are stored in the database in 523 a binary representation, and the conversion is done by the library. 524</P><P> 525 The data is exchanged with the DBMS via buffers. Some DBMS require the 526 sizes of the buffers to be specified. This is done automatically by the 527 library, but if the default sizes need to overridden (e.g. if the argument 528 is large), the template can be used to specify the size of buffers for 529 variable length data, e.g. 530 <TT>emp(123,\"1000\",100.0,'2000',extra(5000))</TT>, 531 where the second argument <TT>\"1000\"</TT> specifies a buffer size of 1000 532 bytes, the third argument <TT>'2000'</TT> a buffer size of 2000 bytes, 533 and the last argument, <TT>extra(5000)</TT>, a buffer size of 5000 bytes. 534 Note that any size specifications are simply ignored if not required. 535")). 536 537:- comment(session_start/4, [ 538 amode: session_start(++,++,++,-), 539 args: ["Login": "DBMS login: ID or ID@Host. (ID,Host: string or" 540 " atom)", 541 "Password": "Password (string or atom)", 542 "Options": "Options (list or nil)", 543 "Session": "Session handle" 544 ], 545 summary: "starts a new session for the DBMS", 546 see_also:[session_close/1], 547 eg:" 548 % connecting to a database server running on the local machine, as 549 % database user sqluser with password password 550 session_start(\"sqluser\", \"password\", [], S). 551 552 % connecting to a database server running on machine my.sql.host, 553 % as database user sqluser with password password, and selecting 554 % to use the database mydb 555 session_start(sqluser@my.sql.host, password, [dbame:\"mydb\"], S). 556", 557 desc: html("\ 558<P> 559 Starts a new session with the DBMS. A new connection with the DBMS server is 560 established, with the supplied DBMS user ID. Optionally, a host could be 561 supplied, if the server is located on a remote machine. The host could be 562 either the machine name, or an IP address. A session handle for this new 563 connection is returned by the predicate. 564</P><P> 565 The session is closed automatically if the program backtracks or aborts back 566 beyond this call. 567</P><P> 568 Options is a (possibly empty) list of <TT>Option:Value</TT> pairs, specifying any 569 options for this session. The following options are supported: 570<DL> 571<P> 572<DT><STRONG><TT>dbname</TT></STRONG> 573 <DD>Specifies the name of the database to use. Value can be a 574 string or an atom. The same effect can also be achieved by issuing the 575 SQL statement <TT>USE ...</TT> (where ... is the dbname). 576<P> 577<DT><STRONG><TT>storage</TT></STRONG> 578 <DD>(MySQL specific) specifies the default storage model to use for 579 the tables. Value can be either the string/atom <TT>transactional</TT> 580 or <TT>non-transactional</TT>. For transactional tables, changes are 581 local until they are committed. Uncommitted changes can be rollback. 582 For non-transactional tables, changes are global. InnoDB (with autocommit 583 turned off) is the storage engine used for transactional, and MyISAM is 584 the storage engine used for non-transactional. See the MySQL manual for 585 details on storage engines and their effect on tables. 586</DL>"), 587 exceptions:[ 588 5: "Login, Password or Options not of the correct type.", 589 6: "Invalid option specification in Options.", 590 dbi_bad_field: "Problems with Option's argument.", 591 dbi_error: "Problems connecting to DBMS server."] 592 593]). 594 595:- comment(session_close/1, [ 596 amode: session_close(++), 597 args: ["Session": "A session handle"], 598 summary: "close a session for the DBMS", 599 see_also:[session_start/4,cursor_close/1], 600 exceptions: [ 601 5: "Session is not a session handle"], 602 desc: html("\ 603</P><P> 604 This closes a session explicitly, disconnecting from the DBMS server. It 605 takes effect immediately. Any uncommitted transactional updates are rolled 606 back. Some resources associated with the session are also freed. 607</P><P> 608 Cursors for this session are not closed by the closing of the session, 609 but they will no longer be able to access the database, but will still use 610 some resources. It is recommended that cursors be closed before closing 611 the session. All resources associated with a session are only freed when 612 both the session and all the cursors are closed. 613")]). 614 615:- comment(session_rollback/1, [ 616 amode: session_rollback(++), 617 args: ["Session": "A session handle"], 618 summary: "rollback transactional changes made to the database.", 619 see_also:[session_commit/1, session_transaction/2], 620 exceptions: [ 621 5: "Session is not a valid session handle", 622 dbi_error: "Problems from DBMS during rollback."], 623 eg: 624" 625 session_sql(Session, \"insert into mytable values (\'a\')\", _), 626 session_commit(Session), % committing the insert of a to table mytable 627 session_sql(Session, \"insert into mytable values (\'b\')\", _), 628 session_sql(Session, \"insert into mytable values (\'c\')\", _), 629 % undo the inserting of b and c into table mytable (if tranactional) 630 session_rollback(Session), 631", 632 desc: html("\ 633<P> 634 This undoes all <STRONG>transactional</STRONG> changes made to the database since the last 635 commit for this session. Note some DBMS can operate non-transactionally 636 (for example, non-transactional tables in MySQL), such operations cannot 637 be undone. Also, even for transactions, not all changes can be undone. 638 See DBMS's manual for details. 639</P><P> 640 Outside a transaction this predicate will either succeed if rollback was 641 successful, or raise an error otherwise. 642<P></P> 643 When executed within the scope of a session_transaction/2 goal, this 644 predicate will simply abort the complete outer transaction. 645</P><P> 646 NOTE: This predicate behaves very differently, depending on 647 whether it is in a transaction or not. It is always advisable not 648 to rely on it succeeding, but rather to fail or abort immediately 649 after it adopting a coding style that causes a Prolog failure 650 whenever a transaction cannot be completed.") 651]). 652 653:- comment(session_commit/1, [ 654 amode: session_commit(++), 655 args: ["Session": "A session handle"], 656 summary: "commits transactional changes made to the database.", 657 see_also:[session_rollback/1, session_transaction/2], 658 eg: 659" 660 session_sql(Session, \"insert into mytable values (\'a\')\", _), 661 session_commit(Session), % committing the insert of a to table mytable 662", 663 exceptions: [ 664 5: "Session is not a valid session handle", 665 dbi_error: "Problems from DBMS during commit."], 666 desc: html("\ 667<P> 668 This commits any transactional updates to the database made within Session. 669 By default, transactional updates are local to the session, and the changes 670 only become global when a commit occurs, e.g. when session_commit/1 is 671 executed. Note that non-transactional updates become globally visible 672 immediately. 673</P><P> 674 When executed within the scope of a session_transaction/2 goal, this 675 predicate simply succeeds without doing anything.") 676]). 677 678:- comment(session_transaction/2, [ 679 amode: session_transaction(++,+), 680 args: ["Session": "A session handle", 681 "Goal": "Prolog goal that implements a database update."], 682 summary: "executes Goal as a database transaction.", 683 see_also:[session_rollback/1, session_commit/1], 684 exceptions: [ 685 5: "Session is not a valid session handle", 686 abort: "session_rollback/1 called within Goal"], 687 eg: " 688transfer(Session, Amount,FromAccount,ToAccount) :- 689 session_transaction(Session, 690 transfer_(Session,Amount,FromAccount,ToAccount) 691 ). 692 693% note \'?\' in SQL in the syntax MySQL uses for placeholders. This may be 694% different in other DBMS 695transfer_(Session, Amount, FromAccount, ToAccount) :- 696 SQL = \"update accounts set balance = balance + ? \\ 697 where id = ?\", 698 Deduct is - Amount, 699 session_sql_prepare(Session,incbal(1.0,12),SQL,1,Update), 700 cursor_next_execute(Update,incbal(Deduct,FromAccount)), 701 cursor_next_execute(Update,incbal(Amount,ToAccount)). 702", 703 desc: html("\ 704<P> 705 This executes Goal as a database transaction. This predicate is only useful 706 if the database supports transactions (i.e. changes are local until committed). 707 If Goal succeeds session_transaction/2 commits the update, cuts any 708 alternative solutions to Goal and succeeds itself. 709</P><P> 710 If Goal fails or causes a database error, session_transaction/2 fails and 711 rolls back any changes done to the database. 712</P><P> 713 If Goal aborts, the update is rolled back, and session_transaction/2 aborts. 714</P><P> 715 MySQL specific note: transactions are supported for transactional tables only 716 (i.e. stored with a storage engine with transaction-safe capabilities). 717</P><P? 718 NOTE: It is a programming error to have some previous uncommitted 719 database updates at the time of calling session_transaction/2. 720</P><P> 721 Calls of this predicate can be nested, but only the outermost transaction 722 is real. All the inner transactions are simply equivalent to call(Goal). 723 This way it is possible to write a call to session_transaction/2, into 724 some code that implements a simple update, but then to include that simple 725 update into a larger transaction. 726</P><P> 727 Similarly session_commit/1 and session_rollback/1 alter their behaviour so 728 that they can be used within a transaction. 729</P><P> 730 Transactions are local to one session so there is no way to safely make an 731 update relating to several sessions.") 732]). 733 734 735:- comment(session_sql/3, [ 736 amode: session_sql(++,++,-), 737 args: ["Session": "A session handle", 738 "SQL":"A SQL statement (string)", 739 "RowProcessedCount": "Number of rows affected by this" 740 " statement" 741 ], 742 summary: "Executes a SQL statement on the database server.", 743 exceptions: [ 744 5: "Session is not a valid session handle or SQL not a string", 745 dbi_error: "database returned an error when executing SQL." 746 ], 747 eg:" 748make_accounts(Session) :- 749 session_sql(Session, 750 \"create table accounts \\ 751 (id decimal(4) not null,\\ 752 balance decimal(9,2) default 0.0 not null, \\ 753 overdraft decimal(9,2) default 0.0 not null \\ 754 )\" ,_), 755 session_sql(Session, 756 \"insert into accounts (id,balance) values (1001,1200.0)\",1), 757 session_sql(Session, 758 \"insert into accounts (id,balance) values (1002,4300.0)\",1). 759", 760 desc: html("\ 761<P> 762 Executes a SQL statement (without parameters) on the database server. The 763 SQL statement should not have a terminating semicolon (;). If supported by 764 the DBMS, SQL can consist of multiple statements separated by semicolons. 765 RowProcessedCount will be unified with the number of rows affected by this 766 statement. 767</P><P> 768 In the case of data definition language statements the RowProcessedCount 769 parameter is undefined and should be passed as a free variable. 770</P><P> 771 In the case of data manipulation language statements, the 772 RowProcessedCount is the number of rows, deleted, modified or inserted 773 by the SQL statement. 774</P><P> 775 The SQL statement must be valid for the DBMS to execute. It can contain 776 NULL characters, i.e. it can contain binary data. 777</P><P> 778 Any results generated by the statement is discarded.") 779]). 780 781 782:- comment(session_sql_query/4, [ 783 amode: session_sql_query(++,+,++, -), 784 args: ["Session": "A session handle", 785 "ResultTemplate": "Template defining the types of results" 786 " tuple (structure)", 787 "SQLQuery": "A SQL statement query (string)", 788 "Cursor": "Returned cursor handle" 789 ], 790 summary: "Executes a SQL query on the database server.", 791 see_also: [session_sql_query/5, cursor_next_tuple/2, 792 cursor_all_tuples/2, 793 cursor_N_tuples/4, session_sql_prepare_query/5, 794 cursor_close/1 795 ], 796 eg:" 797 check_overdraft_limit(Session, Account) :- 798 L = [\"select count(id) from accounts \\ 799 where id = \",Account,\" and balance < overdraft\"], 800 concat_string(L,SQL), 801 session_sql_query(Session,c(0),SQL,OverdraftCheck), 802 cursor_next_tuple(OverdraftCheck,c(Count)), 803 Count = 0. 804", 805 exceptions: [5: "Session is not a valid session handle, or SQLQuery" 806 " not a string, or ResultTemplate not a structure", 807 dbi_error: "Error from DBMS while executing SQLQuery.", 808 dbi_bad_template: "ResultTemplate has the wrong arity" 809 ], 810 desc: html("\ 811<P> 812 Executes a SQL query on the database server. The predicate returns in 813 Cursor the cursor handle for this SQL query, and the results can then be 814 retrieved using cursor_*_tuple family of predicates. 815</P><P> 816 The SQL query returns result in tuples of N elements each. Each tuple is 817 mapped to a Prolog structure of arity N. ResultTemplate is a structure of 818 arity N specifying the types of the return data for ECLiPSe. See the 819 general description of this library or the manual for a description of 820 the template specification. 821 </P><P> 822 The SQL query must be valid for the DBMS to execute. It can contain 823 NULL characters, i.e. it can contain binary data. 824 </P><P> 825 This predicate is called with default options for the cursor, i.e. it is 826 equivalent to calling session_sql_query/5 with an empty Options list. 827") 828]). 829 830:- comment(session_sql_query/5, [ 831 amode: session_sql_query(++,+,++, ++,-), 832 args: ["Session": "A session handle", 833 "ResultTemplate": "Template defining the types of results" 834 " tuple (structure)", 835 "SQLQuery": "A SQL statement query (string)", 836 "Options": "Options (list of Option:Value pairs or nil)", 837 "Cursor": "Returned cursor handle" 838 ], 839 summary: "Executes a SQL query on the database server with options" 840 " specified by Options.", 841 see_also: [session_sql_query/4, cursor_next_tuple/2, cursor_all_tuples/2, 842 cursor_N_tuples/4, session_sql_prepare_query/5, 843 cursor_close/1 844 ], 845 eg:" 846 check_overdraft_limit(Session, Account) :- 847 L = [\"select count(id) from accounts \\ 848 where id = \",Account,\" and balance < overdraft\"], 849 concat_string(L,SQL), 850 % the buffering:server option is MySQL specific 851 session_sql_query(Session,c(0),SQL,[buffering:server],OverdraftCheck), 852 cursor_next_tuple(OverdraftCheck,c(Count)), 853 Count = 0. 854", 855 exceptions: [5: "Session is not a valid session handle, or SQLQuery" 856 " not a string, or ResultTemplate not a structure", 857 6: "Invalid option specification in Options", 858 dbi_error: "Error from DBMS while executing SQLQuery.", 859 dbi_bad_template: "ResultTemplate has the wrong arity" 860 ], 861 desc: html("\ 862<P> 863 Executes a SQL query on the database server. The predicate returns in 864 Cursor the cursor handle for this SQL query, and the results can then be 865 retrieved using cursor_*_tuple family of predicates. Options is 866 a (possibly empty) list of <TT>Option:Value</TT> pairs, specifying 867 DBMS-specific options for the cursor. 868</P><P> 869 The SQL query returns result in tuples of N elements each. Each tuple is 870 mapped to a Prolog structure of arity N. ResultTemplate is a structure of 871 arity N specifying the types of the return data for ECLiPSe. See the 872 general description of this library or the manual for a description of 873 the template specification. 874 </P><P> 875 The SQL query must be valid for the DBMS to execute. It can contain 876 NULL characters, i.e. it can contain binary data. 877</P><P> 878 MySQL specific: 879</P><P> 880 Options is used to specify the type of cursor used. Currently this only 881 applies to cursors for SQL queries. The options are: 882<DL> 883<P> 884<DT><STRONG><TT>buffering</TT></STRONG> 885<DD>Specifies where the result set of a SQL query is buffered. Value can be 886 either <TT>client</TT> (the default) or <TT>server</TT>. By default, the 887 whole of the result set for a query is copied to the client (i.e. the 888 ECLiPSe process running lib(dbi)) after the SQL query is executed. The 889 alternative is to leave the result set on the DBMS server, and only get 890 the result tuples from the server one by one (with e.g. cursor_next_tuple/2). 891 </P><P> 892 The default buffering is on the client side, because this is the default 893 of the MySQL C API, and in addition, it imposes certain restrictions on 894 how the result tuples can be retrieved. However, as the whole result set 895 is retreived, this can impose significant memory overheads if there are 896 many tuples in the result set. On the other hand, there is no restrictions 897 on how many active client buffered cursor is allowed per session at the 898 same time, but only one active server buffered cursor is allowed at any 899 one time -- a query result must be exhausted or the cursor explicitly 900 closed before another query can be issued for that session. 901 </P><P> 902<DT><STRONG><TT>type</TT></STRONG> 903<DD>This option is not relevant for the direct SQL queries of 904 session_sql_query/4. It is only relevant for prepared queries, and has no 905 effect here. 906</DL> 907") 908]). 909 910:- comment(session_sql_prepare/4, [ 911 amode: session_sql_prepare(++,+,++,-), 912 args: ["Session": "A session handle", 913 "ParamTemplate": "Template defining the types of the" 914 " parameters (structure or [])", 915 "SQL": "A SQL statement in prepared syntax (string)", 916 "Cursor": "Returned cursor handle" 917 ], 918 summary: "Prepares a SQL statement for execution by the DBMS.", 919 see_also: [cursor_next_execute/2, cursor_all_execute/2, 920 cursor_N_execute/4, cursor_close/1, 921 session_sql/3, session_sql_prepare_query/5 922 ], 923 exceptions: [5: "Session is not a valid session handle, or SQL" 924 " not a string, or ParamTemplate not a structure", 925 dbi_error: "Error from DBMS while preparing SQL", 926 dbi_bad_template: "ParamTemplate has the wrong arity" 927 ], 928 eg:" 929 % note \'?\' in SQL in the syntax MySQL uses for placeholders. This may be 930 % different in other DBMS 931 transfer_(Session, Amount, FromAccount, ToAccount) :- 932 SQL = \"update accounts set balance = balance + ? \\ 933 where id = ?\", 934 Deduct is - Amount, 935 % incbal(1.0,12) is the parameter template 936 session_sql_prepare(Session,incbal(1.0,12),SQL,1,Update), 937 cursor_next_execute(Update,incbal(Deduct,FromAccount)), 938 cursor_next_execute(Update,incbal(Amount,ToAccount)).", 939 940 desc: html("\ 941<P> 942 Prepares a SQL statement for execution. The statement is not actually 943 executed, and a cursor_*_execute family of predicate is required to 944 execute the predicate. This facility is only available if the DBMS 945 supports prepared statements, and the SQL statement has to be written in 946 the prepared statement syntax of the DBMS. The predicate returns the 947 cursor handle representing this prepared statement in Cursor, which can 948 then be used in subsequent library predicates. 949</P><P> 950 A prepared SQL statement is parsed by the DBMS, so that it could be 951 executed more efficiently. It can also be parameterised, where the 952 parameters represent values to be filled in when the statement is 953 executed. The statement can be executed multiple times with different 954 parameters. The types of the parameters is specified by ParamTemplate, 955 which is a Prolog structure of arity M (where M is the number of 956 parameters for the statement), or the nil atom [] if there are no parameters. 957 See the general description of this library or the manual for a 958 description of the template specification. 959 </P><P> 960 The SQL statement must be valid for the DBMS to execute. It can contain 961 NULL characters, i.e. it can contain binary data. The SQL statement cannot 962 return any results. If it does, then an error would be raised when the SQL 963 statement is actually executed. 964</P><P> 965 Note that some DBMS restricts which SQL statements can be prepared. If an 966 SQL statement cannot be prepared, it can still be executed using 967 session_sql/3. ") 968]). 969 970:- comment(session_sql_prepare_query/5, [ 971 amode: session_sql_prepare_query(++,+,+,++,-), 972 args: ["Session": "A session handle", 973 "ParamTemplate": "Template defining the types of the" 974 " parameters (structure or [])", 975 "ResultTemplate": "Template defining the types of results" 976 " tuple (structure)", 977 "SQLQuery": "A SQL query in prepared syntax (string)", 978 "Cursor": "Returned cursor handle" 979 ], 980 summary: "Prepares a SQL query for execution by the DBMS.", 981 exceptions: [5: "Session is not a valid session handle, or SQLQuery" 982 " not a string, or ResultTemplate or ParamTemplate" 983 " not a structure", 984 dbi_error: "Error from DBMS while preparing SQLQuery.", 985 dbi_bad_template: "ResultTemplate or ParamTemplate has the wrong arity" 986 ], 987 see_also: [cursor_next_execute/2, 988 cursor_next_tuple/2, cursor_all_tuples/2, 989 cursor_N_tuples/4, 990 session_sql/3, session_sql_query/4 991 ], 992 eg:" 993 make_check_overdraft_limit(Session, Cursor) :- 994 % note \'?\' in SQL in the syntax MySQL uses for placeholders. This may be 995 % different in other DBMS 996 SQL = \"select count(id) from accounts where ID = ? \\ 997 and balance < overdraft\", 998 session_sql_prepare_query(Session,a(0),c(0),SQL,1,Cursor).", 999 1000 desc: html("\ 1001<P> 1002 Prepares a SQL query for execution. The query is not actually 1003 executed, and a cursor_next_execute/2 needs to be called to execute the 1004 SQL query, giving values to any parameters. Then the cursor_*_tuple family 1005 of predicates can be used to obtain the results. This facility is only 1006 available if the DBMS supports prepared statements, and the SQL query has 1007 to be written in the prepared statement syntax of the DBMS. The predicate 1008 returns the cursor handle representing this prepared query in Cursor, 1009 which can then be used in subsequent library predicates. 1010</P><P> 1011 A prepared SQL statement is parsed by the DBMS, so that it could be 1012 executed more efficiently. It can also be parameterised, where the 1013 parameters represent values to be filled in when the statement is 1014 executed. The statement can be executed multiple times with different 1015 parameters. The types of the parameters is specified by ParamTemplate, 1016 which is a Prolog structure of arity M (where M is the number of 1017 parameters for the statement), or the nil atom [] if there are no parameters. 1018 See the general description of this library or the manual for a 1019 description of the template specification. 1020</P><P> 1021 The SQL query returns result in tuples of N elements each. Each tuple is 1022 mapped to a Prolog structure of arity N. ResultTemplate is a structure of 1023 arity N specifying the types of the return data for ECLiPSe. See the 1024 general description of this library or the manual for a description of 1025 the template specification. 1026</P><P> 1027 The SQL query must be valid for the DBMS to execute. It can contain 1028 NULL characters, i.e. it can contain binary data. 1029</P><P> 1030 Note that some DBMS restricts which SQL statements can be prepared. If an 1031 SQL statement cannot be prepared, it can still be executed using 1032 session_sql/3. 1033</P><P> 1034 MySQL specific note: not all SQL statements can be prepared by MySQL. 1035 Refer to the MySQL manual for details. 1036") 1037]). 1038 1039:- comment(cursor_close/1, [ 1040 amode: cursor_close(++), 1041 args: ["Cursor": "A cursor handle"], 1042 summary: "Close the cursor associated with Cursor", 1043 exceptions: [ 1044 5: "Cursor is not a cursor handle." 1045 ], 1046 see_also: [session_close/1, 1047 session_sql_prepare/4,session_sql_query/4, 1048 session_sql_prepare_query/5], 1049 desc: html("\ 1050<P> 1051 Closes a cursor explicitly, and frees the resources associated with the 1052 cursor. It takes effect immediately. 1053</P><P> 1054 It is recommended that the user close all cursors before explicitly 1055 closing a session. Cursors that remain open after a session is closed 1056 cannot be used, but may still be using resources. 1057</P><P> 1058 Cursors are automatically closed if the program backtracks or aborts back 1059 beyond where they were created. 1060")]). 1061 1062:- comment(cursor_next_execute/2, [ 1063 amode: cursor_next_execute(++,+), 1064 args: ["Cursor": "A cursor handle", 1065 "Tuple": "A tuple of parameter values matching the template" 1066 " for this cursor (structure)" 1067 ], 1068 see_also: [cursor_next_execute/3, cursor_all_execute/2, 1069 cursor_N_execute/4, 1070 session_sql_prepare/4, session_sql_prepare_query/5 1071 ], 1072 summary: "Executes the parametrised prepared SQL statement" 1073 " represented by Cursor.", 1074 exceptions: [5: "Cursor is not a valid cursor handle", 1075 5: "Type mismatch between parameter template" 1076 " specification for Cursor and actual tuple data", 1077 dbi_buffer_over: "Parameter value(s) too big for the" 1078 " buffer", 1079 dbi_error: "Error from DBMS while executing SQL" 1080 " associated with Cursor.", 1081 dbi_bad_template: "ParamTemplate not specified when" 1082 " Cursor was created", 1083 dbi_bad_cursor: "The Cursor is not in a state to" 1084 " execute a query (e.g. it was cancelled)" 1085 ], 1086 eg:" 1087 % note \'?\' in SQL in the syntax MySQL uses for placeholders. This may be 1088 % different in other DBMS 1089 transfer_(Session, Amount, FromAccount, ToAccount) :- 1090 SQL = \"update accounts set balance = balance + ? \\ 1091 where id = ?\", 1092 Deduct is - Amount, 1093 % incbal(1.0,12) is the parameter template 1094 session_sql_prepare(Session,incbal(1.0,12),SQL,Update), 1095 cursor_next_execute(Update,incbal(Deduct,FromAccount)), 1096 cursor_next_execute(Update,incbal(Amount,ToAccount)).", 1097 1098 desc: html("\ 1099<P> 1100 Executes the parameterised prepared SQL statement represented by Cursor, 1101 previously prepared by session_sql_prepare/4 or session_sql_prepare_query/5. 1102 The parameter values for this execution is supplied by Tuple. 1103</P><P> 1104 Tuple is a structure whose name and arity match the parameter template 1105 when Cursor was prepared, and the arguments give the values for the 1106 parameters for this execution, and must be compatible with the type 1107 specified by the template, except that an argument can be an 1108 uninstantiated variable, to denote a NULL value for the corresponding 1109 parameter. 1110</P><P> 1111 If the SQL statement is a query, and was prepared as a query using 1112 session_sql_prepare_query/5, results can be obtained from the query by 1113 the cursor_*_tuple family of predicates. 1114</P><P> 1115 This predicate is called with default options for the cursor, i.e. it is 1116 equivalent to calling cursor_next_execute/3 with an empty Options list. 1117 1118") 1119]). 1120 1121:- comment(cursor_next_execute/3, [ 1122 amode: cursor_next_execute(++,+,++), 1123 args: ["Cursor": "A cursor handle", 1124 "Tuple": "A tuple of parameter values matching the template" 1125 " for this cursor (structure)", 1126 "Options": "Options (list of Option:Value pairs or nil)" 1127 ], 1128 see_also: [cursor_next_execute/2, cursor_all_execute/2, cursor_N_execute/4, 1129 session_sql_prepare/4, session_sql_prepare_query/5 1130 ], 1131 summary: "Executes the parametrised prepared SQL statement" 1132 " represented by Cursor, with options Options.", 1133 exceptions: [5: "Cursor is not a valid cursor handle", 1134 5: "Type mismatch between parameter template" 1135 " specification for Cursor and actual tuple data", 1136 6: "Invalid option specification in Options", 1137 dbi_buffer_over: "Parameter value(s) too big for the" 1138 " buffer", 1139 dbi_error: "Error from DBMS while executing SQL" 1140 " associated with Cursor.", 1141 dbi_bad_template: "ParamTemplate not specified when" 1142 " Cursor was created", 1143 dbi_bad_cursor: "The Cursor is not in a state to" 1144 " execute a query (e.g. it was cancelled)" 1145 ], 1146 1147 desc: html("\ 1148<P> 1149 Executes the parameterised prepared SQL statement represented by Cursor, 1150 previously prepared by session_sql_prepare/4 or session_sql_prepare_query/5. 1151 The parameter values for this execution is supplied by Tuple. Options is 1152 a (possibly empty) list of <TT>Option:Value</TT> pairs, specifying 1153 DBMS-specific options for the cursor. 1154</P><P> 1155 Tuple is a structure whose name and arity match the parameter template 1156 when Cursor was prepared, and the arguments give the values for the 1157 parameters for this execution, and must be compatible with the type 1158 specified by the template, except that an argument can be an 1159 uninstantiated variable, to denote a NULL value for the corresponding 1160 parameter. 1161</P><P> 1162 If the SQL statement is a query, and was prepared as a query using 1163 session_sql_prepare_query/5, results can be obtained from the query by 1164 the cursor_*_tuple family of predicates. 1165</P><P> 1166 MySQL specific: 1167</P><P> 1168 Options is used to specify the type of cursor used. Currently this only 1169 applies to cursors for SQL queries. The options are: 1170<DL> 1171<P> 1172<DT><STRONG><TT>buffering</TT></STRONG> 1173<DD>Specifies where the result set of a SQL query is buffered. Value can be 1174 either <TT>client</TT> (the default) or <TT>server</TT>. By default, the 1175 whole of the result set for a query is copied to the client (i.e. the 1176 ECLiPSe process running lib(dbi)) after the SQL query is executed. The 1177 alternative is to leave the result set on the DBMS server, and only get 1178 the result tuples from the server one by one (with e.g. cursor_next_tuple/2). 1179 </P><P> 1180 The default buffering is on the client side, because this is the default 1181 of the MySQL C API, and in addition, it imposes certain restrictions on 1182 how the result tuples can be retrieved. However, as the whole result set 1183 is retreived, this can impose significant memory overheads if there are 1184 many tuples in the result set. 1185 </P><P> 1186<DT><STRONG><TT>type</TT></STRONG> 1187<DD>Specifies the type of cursor, and is only meaningful if the buffering 1188 option is set to server. Value can be either <TT>no_cursor</TT> (the 1189 default) or <TT>read_only</TT>. These correspond to the MySQL statement 1190 attribute STMT_ATTR_CURSOR_TYPE of CURSOR_TYPE_NO_CURSOR and 1191 CURSOR_TYPE_READ_ONLY respectively (See the MySQL manual for details). 1192</P><P> 1193 Only one active cursor of type no_cursor is allowed per session, and this 1194 active cursor must be closed before another query can be issued to the 1195 DBMS server. read_only cursor does not have this restriction, and several 1196 such cursors can be active at the same time. 1197</DL> 1198") 1199]). 1200 1201:- comment(cursor_all_execute/2, [ 1202 amode: cursor_all_execute(++,+), 1203 args: ["Cursor": "A cursor handle", 1204 "TupleList": "A list of tuples of parameter values matching the template" 1205 " for this cursor" 1206 ], 1207 summary: "Executes the parametrised prepared SQL statement" 1208 " represented by Cursor, once for each tuple in TupleList.", 1209 see_also: [cursor_next_execute/2, cursor_N_execute/4, 1210 session_sql_prepare/4, session_sql_prepare_query/5 1211 ], 1212 exceptions: [5: "Cursor is not a valid cursor handle", 1213 5: "Type mismatch between parameter template" 1214 " specification for Cursor and actual tuple data", 1215 dbi_error: "Error from DBMS while executing SQL" 1216 " associated with Cursor.", 1217 dbi_bad_template: "ParamTemplate not specified when" 1218 " Cursor was created" 1219 ], 1220 desc: html("\ 1221<P> 1222 Executes the parameterised prepared SQL statement represented by Cursor, 1223 previously prepared by session_sql_prepare/4 or session_sql_prepare_query/5. 1224 The statement is executed once for each tuple in TupleList, with the 1225 parameter values supplied by that tuple. The tuples are executed in the 1226 order they are in TupleList, and any results produced are discarded when 1227 the next tuple is executed. 1228</P><P> 1229 Each tuple is a structure whose name and arity match the parameter 1230 template when Cursor was prepared, and the arguments give the values for 1231 the parameters for this execution, and must be compatible with the type 1232 specified by the template, except that an argument can be an 1233 uninstantiated variable, to denote a NULL value for the corresponding 1234 parameter.") 1235]). 1236 1237:- comment(cursor_N_execute/4, [ 1238 amode: cursor_N_execute(++,-,+,-), 1239 args: ["Cursor": "A cursor handle", 1240 "Executed": "Number of times the SQL statement was executed.", 1241 "TupleList": "A difference list (together with" 1242 " RestTupleList) of tuples of parameter values matching the template" 1243 " for this cursor", 1244 "RestTupleList": "Tail of tuple difference list" 1245 ], 1246 summary: "Executes the parametrised prepared SQL statement" 1247 " represented by Cursor, once for each tuple in TupleList.", 1248 see_also: [cursor_next_execute/2, cursor_N_execute/4, 1249 session_sql_prepare/4, session_sql_prepare_query/5 1250 ], 1251 exceptions: [5: "Cursor is not a valid cursor handle", 1252 5: "Type mismatch between parameter template" 1253 " specification for Cursor and actual tuple data", 1254 dbi_error: "Error from DBMS while executing SQL" 1255 " associated with Cursor.", 1256 dbi_bad_template: "ParamTemplate not specified when" 1257 " Cursor was created" 1258 ], 1259 eg:" 1260 transfer_(Session, Amount, FromAccount, ToAccount) :- 1261 SQL = \"update accounts set balance = balance + ? \ 1262 where id = ?\", 1263 Deduct is - Amount, 1264 session_sql_prepare(Session,incbal(1.0,12),SQL,2,Update), 1265 Updates = [incbal(Deduct,FromAccount),incbal(Amount,ToAccount)], 1266 % execute both updates with one call to cursor_N_execute/4 1267 cursor_N_execute(Update,2,Updates,[]). 1268", 1269 desc: html("\ 1270<P> 1271 Executes the parameterised prepared SQL statement represented by Cursor, 1272 previously prepared by session_sql_prepare/4 or session_sql_prepare_query/5. 1273 The predicate executes a number of tuples from TupleList, taking the 1274 tuples in order from the start of the list, leaving the unprocessed tuples 1275 in RestTupleList. The number of tuple processed is unified with Executed. 1276 Any results produced when executing a tuple is discarded when the next 1277 tuple is processed. 1278</P><P> 1279 The number of tuples processed per call is determined by the library. 1280 This freedom allows the predicate to process a multiple number of tuples 1281 in the most efficient way for the DBMS. 1282</P><P> 1283 Each tuple is a structure whose name and arity match the parameter 1284 template when Cursor was prepared, and the arguments give the values for 1285 the parameters for this execution, and must be compatible with the type 1286 specified by the template, except that an argument can be an 1287 uninstantiated variable, to denote a NULL value for the corresponding 1288 parameter. 1289</P><P> 1290 MySQL specific note: MySQL does not support the execution of multiple 1291 tuples in one go, so for MySQL, this predicate is essentially equivalent 1292 to executing cursor_next_execute/1 multiple times. 1293") 1294]). 1295 1296:- comment(cursor_next_tuple/2, [ 1297 amode: cursor_next_tuple(++,-), 1298 args: ["Cursor": "A cursor handle", 1299 "ResultTuple": "Returned result tuple" 1300 ], 1301 summary: "Retrieve the next result tuple from the SQL query in" 1302 " ResultTuple", 1303 fail_if: "No more results are available for the SQL query", 1304 resat: no, 1305 exceptions: [5: "Cursor is not a valid cursor handle", 1306 5: "Unable to convert tuple result to ECLiPSe type", 1307 dbi_error: "Error from DBMS while executing SQL" 1308 " associated with Cursor.", 1309 dbi_error: "Error from DBMS while fetching result", 1310 dbi_not_query: "The SQL associated with Cursor is not" 1311 " a query and so cannot return results.", 1312 dbi_buffer_over: "Result value(s) too big for the" 1313 " buffer", 1314 dbi_cancelled: "The cursor have been cancelled." 1315 ], 1316 eg:" 1317 check_overdraft_limit(Session, Account) :- 1318 L = [\"select count(id) from accounts \\ 1319 where id = \",Account,\" and balance < overdraft\"], 1320 concat_string(L,SQL), 1321 session_sql_query(Session,c(0),SQL,OverdraftCheck), 1322 cursor_next_tuple(OverdraftCheck,c(Count)), 1323 Count = 0. 1324", 1325 desc: html("\ 1326<P> 1327 Retrieve the next result tuple from the SQL query represented by Cursor, 1328 and unify it with ResultTuple. Cursor is a cursor previously created with 1329 session_sql_query/4 or session_sql_prepare_query/5. ResultTuple is a 1330 structure with the same name and arity as defined by the tuple template 1331 when the cursor was created. The predicate converts the result to the type 1332 specified in the template, except that NULL values are returned as 1333 variables. 1334</P><P> 1335 If the SQL query have not yet been executed, and it contains no 1336 parameters, then the SQL query will first be executed before retrieving 1337 the result. 1338</P><P> 1339 cursor_next_tuple/2 will fail when all the results tuples for the query 1340 have been returned. If it is then called again for the same SQL query, 1341 this cancels the cursor, and raise the cursor cancelled error. 1342</P><P> 1343 cursor_next_tuple/2 is not resatisfiable, so to return successive tuples 1344 on backtracking, use repeat/0 to re-execute cursor_next_tuple/2: 1345<TT><PRE> 1346 match_tuple(Cursor, Tuple) :- 1347 repeat, 1348 ( cursor_next_tuple(Cursor, Tuple0) -> 1349 Tuple0 = Tuple 1350 ; 1351 !, fail 1352 ). 1353</PRE></TT> 1354") 1355]). 1356 1357:- comment(cursor_all_tuples/2, [ 1358 amode: cursor_all_tuples(++,-), 1359 args: ["Cursor": "A cursor handle", 1360 "ResultTuples": "Returned list of result tuples" 1361 ], 1362 summary: "Retrieve all remaining result tuples from the SQL query in" 1363 " ResultTuples", 1364 see_also: [session_sql_query/4, session_sql_prepare_query/5, 1365 cursor_next_tuple/2, cursor_N_tuples/4 1366 ], 1367 exceptions: [5: "Cursor is not a valid cursor handle", 1368 5: "Unable to convert tuple result to ECLiPSe type", 1369 dbi_error: "Error from DBMS while executing SQL" 1370 " associated with Cursor.", 1371 dbi_error: "Error from DBMS while fetching result", 1372 dbi_not_query: "The SQL associated with Cursor is not" 1373 " a query and so cannot return results." 1374 ], 1375 desc: html("\ 1376<P> 1377 Retrieve the all remaining result tuples from the SQL query represented by 1378 Cursor, and unify them with ResultTuples list. Each element of the list is 1379 a single result tuple. Cursor is a cursor previously created with 1380 session_sql_query/4 or session_sql_prepare_query/5. Each tuple in 1381 ResultTuples is a structure with the same name and arity as defined by the 1382 tuple template when the cursor was created. The predicate converts the 1383 result to the type specified in the template, except that NULL values are 1384 returned as variables. 1385</P><P> 1386 If the SQL query have not yet been executed, and it contains no 1387 parameters, then the SQL query will first be executed before retrieving 1388 the results.") 1389]). 1390 1391:- comment(cursor_N_tuples/4, [ 1392 amode: cursor_N_tuples(++,-,-,-), 1393 args: ["Cursor": "A cursor handle", 1394 "Retrieved": "Number of result tuples retrieved", 1395 "ResultTuples": "Returned difference list (with" 1396 " RestResultTuples) of result tuples", 1397 "RestResultTuples": "Tail of returned result tuples" 1398 ], 1399 summary: "Retrieve result tuples from the SQL query in" 1400 " the difference list ResultTuples and RestResultTuples.", 1401 see_also: [session_sql_query/4, session_sql_prepare_query/5, 1402 cursor_next_tuple/2, cursor_all_tuples/2 1403 ], 1404 exceptions: [5: "Cursor is not a valid cursor handle", 1405 5: "Unable to convert tuple result to ECLiPSe type", 1406 dbi_error: "Error from DBMS while executing SQL" 1407 " associated with Cursor.", 1408 dbi_error: "Error from DBMS while fetching result", 1409 dbi_not_query: "The SQL associated with Cursor is not" 1410 " a query and so cannot return results." 1411 ], 1412 desc: html("\ 1413<P> 1414 This is similar to cursor_all_tuples/4 except it works on difference list. 1415 It is designed to efficiently retrieve a buffer full of tuples for DBMS 1416 that support the retrieval of multiple tuples in its API. Otherwise, all 1417 the remaining tuples are retrieved one by one and the ResultTuples list 1418 constructed. Retrieved is unified with the number of retrieved result 1419 tuples. If no tuples match the query, Retrieved will be 0. Each element 1420 of the list is a single result tuple. Cursor is a cursor previously 1421 created with session_sql_query/4 or session_sql_prepare_query/5. Each 1422 tuple in ResultTuples is a structure with the same name and arity as 1423 defined by the tuple template when the cursor was created. The predicate 1424 converts the result to the type specified in the template, except that 1425 NULL values are returned as variables. 1426</P><P> 1427 If the SQL query have not yet been executed, and it contains no 1428 parameters, then the SQL query will first be executed before retrieving 1429 the results. 1430</P><P> 1431 MySQL specific note: MySQL does not support the retrieval of multiple 1432 tuples in one go, so for MySQL, this predicate is essentially equivalent 1433 to retrieving the results one by one. 1434") 1435]). 1436 1437:- comment(cursor_field_value/3,hidden). 1438:- comment(dbi_error_handler/2, hidden). 1439:- comment(session_error_value/3, hidden). 1440:- comment(session_retrieve_N_tuples/5, hidden). 1441:- comment(session_retrieve_lazy_tuples/5, hidden). 1442:- comment(session_retrieve_tuple/4, hidden). 1443:- comment(session_N_sql_prepare/5, hidden). 1444:- comment(session_sql_prepare_query_N/6, hidden). 1445:- comment(session_sql_query_N/6, hidden). 1446:- comment(session_start/3, hidden). 1447 1448