db/src/DB.i3


 Copyright (C) 1996-2000, Critical Mass, Inc.  All Rights Reserved. 
 See file COPYRIGHT-CMASS for details. 

Modified by Darko Volaric September 2002 Added functionality to support use of multiple database client interfaces concurrently. Supported interfaces are: PostgreSQL, MySQL and ODBC. This interface should be source compatible with the previous version. Please email darko@peter.com.au with any problems.

DB is the safe Modula-3 interface for relational databases. It defines two primary abstractions: database connections and statements.

INTERFACE DB;
------------------------------------------------------- connections ---

TYPE
	InterfaceList = REF ARRAY OF Interface;
	(* A database interface, usualy one for each type of database server *)
	Interface <: InterfacePublic;
	InterfacePublic = OBJECT
		name: TEXT;
	METHODS
		set_default();
		connect(
			database, user_id, password: TEXT;
			server: TEXT := NIL;
		): T  RAISES {Error};
		get_data_sources(): DescList  RAISES {Error};
		get_drivers(): DescList  RAISES {Error};
	END;

PROCEDURE GetInterfaces(): InterfaceList;
Returns a list of Interface objects that describe the available interfaces in this implementation

PROCEDURE GetDefaultInterface(): Interface;

PROCEDURE FindInterface(name: TEXT): Interface  RAISES {Error};
Returns the interface with the given name, or NIL if not found
  The following functions are provided for compatibily with the previous version of the interface. 
		The methods of the Interface object should be used instead. 

PROCEDURE Connect (
	database, user_id, password: TEXT; (* Establish a connection to the named "database" using "user_id" and
																				"password" as authentication credentials. *)
	server: TEXT := NIL;	(* Connect to the server running on this host. May be a host name or IP number *)
	interface: Interface := NIL; (* Use this interface to connect. If not specified the default interface is used *)
): T  RAISES {Error};

PROCEDURE GetDataSources (): DescList  RAISES {Error};
Returns the names and descriptions of the data sources that are available from the local server.

PROCEDURE GetDrivers (): DescList  RAISES {Error};
Returns the names and descriptions of the drivers that are available from the local server.

TYPE DescList = REF RECORD  name, description: TEXT;  next: DescList;  END;

A DB.T, or connection, represents a single connection to a database. Multiple connections may exist within one application and each may be used concurrently by multiple threads.

TYPE
  (* a database connection *)
  T = OBJECT METHODS
    disconnect ()              RAISES {Error};
    new_stmt (): Stmt          RAISES {Error};
    auto_commit (on: BOOLEAN)  RAISES {Error};
    commit ()                  RAISES {Error};
    abort ()                   RAISES {Error};
  END;

Given a database connection db,

db.disconnect() closes the connection to db. Further attempts to use the connection are checked runtime errors. If a connection is garbage collected, the runtine will attempt to disconnect it. But, users should not rely on timely garbage collection to close their connections.

db.new_stmt() returns a new statement that can be used to query or update the database db.

The current transaction is any sequence of one or more statements that has been executed, but not commited or aborted.

db.auto_commit(on) enables (disables) automatic commits on db after each statement is executed if on is TRUE (FALSE). Initially connections have auto commiting enabled. When auto commiting is disabled, db.commit() or db.abort() must be explicitly called to finish the current transaction.

db.commit() commits the current transaction on db.

db.abort() aborts the current transaction on db.

-------------------------------------------------------- statements --- A db.Stmt, or statement, represents a database query or update. Each statement is bound to and executes against one connection. A single connection may have multiple outstanding statements. Operations on a statement are serialized.

TYPE
  (* a SQL database statement (query or update) *)
  Stmt = MUTEX OBJECT METHODS
    prepare (operation: TEXT)       RAISES {Error};
    execute (operation: TEXT)       RAISES {Error};
    fetch ():  Results              RAISES {Error};
    done ()                         RAISES {Error};
    close ()                        RAISES {Error};
    get_cursor_name (): TEXT        RAISES {Error};
    set_cursor_name (nm: TEXT)      RAISES {Error};
    num_rows (): INTEGER            RAISES {Error};
    describe_result (): ResultDesc  RAISES {Error};
    connection (): T;
  END;

  Results = REF ARRAY OF REFANY;

  ResultDesc = REF ARRAY OF ColumnDesc;
  ColumnDesc = RECORD
    name      : TEXT;
    type      : DataType;
    precision : INTEGER;  (* total number of digits *)
    scale     : INTEGER;  (* number of digits right of the decimal place *)
    nullable  : Nullable;
  END;

  Nullable = { Yes, No, Unknown };

ANSI SQL and its extensions define a set of primitive data types. The values stored in a column of the database are of a single type. For each SQL type, this interface defines a corresponding Modula-3 reference type. The vector of results returned by st.fetch() will contain values corresponding to these Modula-3 types.

TYPE
  (* ANSI and extended SQL datatypes  *)
  DataType = {     (* Modula-3 type in the result vector *)
    Null,          (* NULL *)
    Char,          (* RefString *)
    VarChar,       (* RefString *)
    LongVarChar,   (* RefString *)
    Numeric,       (* REF LONGREAL -- ?? *)
    Decimal,       (* REF LONGREAL -- ?? *)
    BigInt,        (* RefBigInt    (64-bit signed integer) *)
    Integer,       (* REF INTEGER  (32-bit signed integer) *)
    SmallInt,      (* REF INTEGER  (16-bit signed integer) *)
    TinyInt,       (* REF INTEGER  (8-bit signed integer) *)
    Real,          (* REF REAL *)
    Float,         (* REF LONGREAL *)
    Double,        (* REF LONGREAL *)
    Bit,           (* REF BOOLEAN *)
    LongVarBinary, (* RefString *)
    VarBinary,     (* RefString *)
    Binary,        (* RefString *)
    Date,          (* RefDate *)
    Time,          (* RefTime *)
    Timestamp      (* RefTimestamp *)
  };
  RefString = REF UNTRACED REF ARRAY OF CHAR;
  RefBigInt = REF RECORD lo, hi: INTEGER END; (* == 2^32*hi + lo *)
  RefDate   = REF RECORD year, month, day: INTEGER END;
  RefTime   = REF RECORD hour, minute, second: INTEGER END;
  RefTimestamp = REF RECORD
                  year, month, day: INTEGER;
                  hour, minute, second, fraction: INTEGER;
                 END;

Given a statement st,

st.prepare(s) will prepare the SQL statement s for execution.

st.execute(NIL) will excute or reexecute the statment previously prepared by st.prepare(s). It is a checked runtime error if no statement is prepared.

st.execute(s) will execute the SQL statement s once. Any prepared statements are lost.

st.fetch() returns a non-NIL array of references to the values corresponding to the next row of the result of the last statement executed by st. If there are no more results, NIL is returned. Note, the array of references and the reference values themselves are reused by subsequent calls to fetch. It is the programmer's responsibility to copy values out of the result vector if they're needed across multiple calls to fetch. The result elements corresponding to columns containing nullified values will be NIL. It is a checked runtime error to call fetch before the statement has been executed.

st.done() finishes a single statement execution and result fetching cycle. Then, st can be reused for another cycle.

st.close() releases the resources used by st. It is a checked runtime error to reuse a closed statement. If a statement is garbage collected, the runtine will attempt to disconnect it. Users should not rely on timely garbage collection to close their statements.

st.get_cursor_name() returns the name attached to sts cursor. If no name is attached, NIL is returned. Attached cursor names can be referenced by other statements to coordinate multi-statement transactions.

st.set_cursor_name(nm) attaches the name nm to sts cursor.

st.connection() returns the underlying database connection used by st.

--------------------------------------------- errors and exceptions ---

EXCEPTION Error (ErrorDesc);

Routines in this interface raise DB.Error whenever an operation fails. The failure may be due to a failure in the underlying database or network, or the failure may be due to programming errors. Blatant programming errors detected by this implementation will trigger checked runtime errors. That is, your program will crash.


TYPE
  ErrorDesc = REF RECORD
    state       : ARRAY [0..5] OF CHAR;  (* e.g. "M1001" with zero termination *)
    description : TEXT;
    native_err  : INTEGER; (* lower level driver or DBMS error code *)
  END;

END DB.