DB - database support

# need Auth module for algorithm names
# e.g. Auth->MD5
include "security.m";
include "db.m";
db := load db DB->PATH;
 
DB: module
{
	PATH : con "/dis/lib/db.dis";

	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);
	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, sqlstream: int;
			lock: chan of int;
		};
};

Description

The Database API module enables Limbo programs to connect to database management systems that support an ODBC interface.

The dbsrv daemon must be running to service database requests. See srv - start server daemons in Chapter 6.

DB and Security

If security features will be used in conjunction with the DB module, the Auth module definitions must be included (by including security.m).

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

/usr/<username>/keyring/net!machine

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

/usr/<username>/keyring/default

DB Functions

connect

connect: fn(addr, alg: string): (ref Sys->FD, string);

## returns (file descriptor, "") on success, (nil, error 
message) on failure.

Connect establishes a connection to the Inferno dbsrv daemon process at addr. This addr is of the form machine!service. Machine is a symbolic or numeric network address, and service is a service or port on that machine. Dbsrv causes execution of the infdb process. After some negotiation, infdb will take the appropriate authentication, digestion, and decryption actions, based on the alg parameter supplied by the client. If successful, connect will return a file descriptor required by dbopen.

dbopen

dbopen: fn(fd: ref Sys->FD, username, password, dbname:

	string): (ref DB_Handle, list of string);

## returns (DB_Handle, nil) on success, (nil, error message) 
on failure.

The dbopen function initiates a session with a database dbname, passing username and password on fd returned from connect. The return value is a tuple consisting of a reference to an instance of the DB_Handle abstract data type (adt) and an empty string on success, or nil and an error DB_Handlemessage on failure. The dbopen function implicitly opens an initial SQL stream via SQLOpen.

open

open: fn(addr, username, password, dbname: string):(ref

	DB_Handle, list of string);

## returns (DB_Handle, nil) on success, (nil, error message) 
on failure.

The open function performs a connect, then a dbopen.

oldh.SQLOpen

SQLOpen: fn(oldh: self ref DB_Handle): (int, ref 
DB_Handle);

## returns a non-zero integer on failure.

SQLOpen creates a new DB_Handle adt and opens a new SQL stream with which requests can be associated. The new adt shares the connection and transaction context with the oldh adt passed in the call. Other characteristics such as the current SQL command, the result set, and cursor position are independent. This allows the manipulation and interaction of multiple SQL statements within a transaction.

dbh.SQLClose

SQLClose: fn(dbh: self ref DB_Handle): int;

## returns a non-zero integer on failure.

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

handle.SQL

SQL: fn(handle: self ref DB_Handle, command: string): 

	(int, string);

## returns a non-zero integer on failure.

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

handle.columns

columns: fn(handle: self ref DB_Handle): int;

## returns number of columns; 0 on error.

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.

handle.nextRow

nextRow: fn(handle: self ref DB_Handle): int;

## returns row number; 0 (no more rows); negative (error).

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.

handle.read

read: fn(handle: self ref DB_Handle, column: int):

	(int, array of byte);

## returns (number of bytes, column data); (negative, error 
message) on error.

Read returns the data of the column column of the current row. If column is out of range, or there is no current row, or some other error occurred, the first element of the returned tuple will be negative, and the byte array 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.

handle.write

write: fn(handle: self ref DB_Handle, param: int,

	fld: array of byte): int

## returns number of bytes saved; -1 on error.

Write is used to send data for a binary field to the server, in anticipation of a subsequent SQL update request with placeholders. The fld byte array contains the binary data to be used for the placeholder param. Param should be 1 for the first placeholder, etc. All binary fields should be set with write calls before the SQL request is sent with SQL. The returned value is the number of bytes saved for the field, or -1 for failure.

handle.columnTitle

columnTitle: fn(handle: self ref DB_Handle,column: 

	int): string;

## returns column title; "" if out of range.

ColumnTitle returns the title of the column column, or the empty string if column is out of range.

handle.errmsg

errmsg: fn(handle: self ref DB_Handle): string;

## returns error message.

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