include "security.m";   # need Auth for algorithm names
include "db.m";
db := load DB DB->PATH;

DB_Handle: adt {
   SQLOpen:     fn(oldh: self ref DB_Handle): (int, ref DB_Handle);
   SQLClose:    fn(dbh: self ref DB_Handle): int;
   SQL:         fn(handle: self ref DB_Handle, command: string):
                  (int, string);
   columns:     fn(handle: self ref DB_Handle): int;
   nextRow:     fn(handle: self ref DB_Handle): int;
   read:        fn(handle: self ref DB_Handle, column: int):
                  (int, array of byte);
   write:       fn(handle: self ref DB_Handle, param: int,
                  fld: array of byte): int
   columnTitle: fn(handle: self ref DB_Handle,
                    column: int): string;
   errmsg:      fn(handle: self ref DB_Handle): string;

   datafd:    ref Sys->FD;
   sqlconn:   int;
   sqlstream: int;
   lock:      chan of int;
};

connect:  fn(addr, alg: string): (ref Sys->FD, string);
dbopen:   fn(fd: ref Sys->FD, username, password, dbname: string):
            (ref DB_Handle, list of string);
open:     fn(addr, username, password, dbname: string):
            (ref DB_Handle, list of string);

Dbsrv(7) must be running (usually in a hosted emu(1)) to service database requests.

If security features will be used in conjunction with DB, the Auth module definitions (see security-auth(2)) from security.m must be included.

If authentication is in use, DB will use the certificate in the file

/usr/user/keyring/net!machine

if that file exists. Otherwise, db will attempt to find a certificate in the file

/usr/user/keyring/default.

Connect establishes a connection to the dbsrv at addr. Addr has the form machine!service. Machine is a symbolic or numeric network address, and service is a service or port on that machine. Dbsrv starts a corresponding infdb process. After some negotiation, infdb will take the appropriate authentication, digesting, and decryption actions, based on the alg requested by the client. If successful, connect will return a file descriptor required by dbopen.

Dbopen initiates a session with a database dbname, passing username and password down the fd returned by connect. Dbopen returns a reference to an instance of DB_Handle and an error string, which is nil on success. On error, the reference is nil, but the string contains a diagnostic. Dbopen implicitly opens an initial SQL stream via SQLOpen.

Open returns the result of a connect followed (if successful) by a dbopen.

oh.SQLOpen

Creates a new DB_Handle and opens a new SQL stream with which requests can be associated. The new handle shares the connection and transaction context with oh. Other characteristics such as the current SQL command, the result set, and cursor position are independent, which allows the manipulation and interaction of many SQL statements within a transaction. It returns zero on success, non-zero on error.

h.SQLClose

Closes the SQL stream opened by SQLOpen. Closing all SQL streams associated with a connection causes the connection to be closed.

h.SQL(command)

Sends the SQL statement command to the database. If the call fails, the first element of the returned tuple is non-zero and the second is an error message.

h.columns()

Returns the number of columns in the result set of the previous SQL select command sent to the database. A returned value of 0 indicates there was a problem with the previous command, or there was no previous select command.

h.nextRow()

Advances the current row, then returns the current row number of the selection results. A return value of 0 indicates there are no more rows; a negative value is returned in case of an error. The initial current row is 0 following a select, so nextRow must be called before any data is read.

h.read(c)

Returns the data of column c of the current row. If c is out of range, there is no current row, or some other error occurred, the first element of the returned tuple will be negative, and the byte array (sic) will contain an error message. Otherwise, it will be the number of bytes in the field requested. This could be greater than the length of the returned array, if the DB module could not allocate enough memory to contain the entire field. In this case the returned array contains the initial portion of the field.

h.write(p,data)

Write sends data for a binary field to the server, in anticipation of a subsequent SQL `update request with placeholders'. Data is an array of bytes containing the data for placeholder p (counting from 1). All binary fields should be set by write before the SQL request is sent to the server with SQL. It returns the number of bytes saved for the field, or -1 on failure.

h.columnTitle(n)

ColumnTitle returns the title of column n. It returns nil if n is out of range.

h.errmsg()

Returns the error message associated with the failure of a previous columns, nextRow, columnTitle or read.