6 Porting WDB to other database systems

With WDB 1.2 the Sybase specific code has been isolated into a separate Database interface package (DBI). This should make it relatively easy to port WDB to other databases like Oracle, Informix, Ingres etc. (Any database which supports standard SQL and has a Perl interface).

Currently there are interfaces to Sybase, Informix and mSQL (a public domain database). A port to Oracle is on its way. If you want to use WDB with another database system the first thing you have to do is to get a perl interface to it. Several of these are available on the Internet. If you can't find a ready interface you have to write one your self. That's a bit more tricky, but you can always cheat and only include the few commands that WDB needs to run ( open connection, query, fetch next row, close connection). The Perl Meta-FAQ lists where you can find the perl source for various versions and also the database interfaces etc.\ . The Meta-FAQ is available from :

http://www.khoros.unm.edu/staff/neilb/perl/metaFAQ/metaFAQ.html http://www.khoros.unm.edu/staff/neilb/perl/metaFAQ/metaFAQ.html

A collection of pointers to Perl reference material in general are available at :

    http://www.eecs.nwu.edu/perl/perl.html

Another important factor when porting WDB to another database is of course the SQL used. Each database system seems to implement it's own flavor of SQL, but fortunately the only SQL command that WDB sends directly to the DBI is a select statement. The select statement used by wdb has the following format :

select column field, column field, ... from table where where-list and constraints and join order by order

column

is the value of the column attribute in the FDF. It is inserted literally in the select statement without any parsing in WDB, so computations etc.\ supported by the database can be used in the FDF.

field

is the value of the FIELD attribute in the FDF. This is used as the label in the select, as the dbi_nextrow function should return the rows as an associative array keyed on this value. Not all databases support this notation, so a second mechanism is also supported. See below ...

table

is this is the value of the TABLE attribute in the FDF. It can contain one or more table names separated by commas.

where-list

is a list of search conditions composed by WDB from the input fields in the query form. The conditions of each field is and'ed together, and the standard operators <=, >=, !=, <, >, = and like are used.

constraints

is the value of the CONSTRAINTS attribute in the FDF.

join

is the value of the JOIN attribute in the FDF.

order

is the value of the ORDER attribute in the FDF.

If your database does not support the column field notation described above, an alternative notation is supported. Rather than specifying the field names as column labels in the select list they can be send separately to the DBI via a function called dbi_fieldnames. WDB automatically detects if such a function is defined in the DBI and uses this second notation. See the msql_dbi.pl file for an example of its use.

To give you an idea of what it takes to write a DBI file I have included the Sybase DBI file below. As you can see it is simply an encapsulation of the sybperl functions.

#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#.IDENTIFICATION  syb_dbi.pl
#.LANGUAGE	  SybPerl script
#
#.PURPOSE	  Database Interface to Sybase.
#
#		  &dbi_connect( $user, $pswd, $server, $database );
#		  &dbi_dosql( "... SQL commands ...");
#		  %row = &dbi_nextrow;
#		  &dbi_disconnect;
#		  &dbi_rowcount( $rows );
#		  &dbi_dateformat( $format );
#
#.AUTHOR	  Bo Frese Rasmussen [ST-ECF]   <bfrasmus@eso.org>
#
#.VERSION	1.0	22/12-1994	Creation
#------------------------------------------------------------------------------	
package WDB_DatabaseInterface;
require	"sybperl.pl";

#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#.PURPOSE	Connects to a database.
#
#.REMARKS	This function must be called before any of the other functions
#		in this package.
#		It logs in to the given database server and connects to a
#		database.
#
#.RETURNS	Dies on error !
#------------------------------------------------------------------------------
sub main'dbi_connect
{
    local(  $user, $pswd, $server, $database ) = @_;
    $dbproc = &main'dblogin( $user, $pswd, $server );
    $dbproc != -1 || die "Can't connect to server $server ...\n";
    &main'dbuse( $database ) || die "Can't connect to database $database ...\n";
}

#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#.PURPOSE	Prepares and executes an SQL statement, with error check etc.
#
#.REMARKS	Dies on error !
#
#.RETURNS	Return value of &dbresults (not used in WDB)
#------------------------------------------------------------------------------
sub main'dbi_dosql 
{
    local($sql) = @_;

    &main'dbcmd( $sql ) || die "Error in dbcmd.\n" ;
    &main'dbsqlexec || die "Error in dbsqlexec.\n" ;
    &main'dbresults;
}
 
#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#.PURPOSE	Gets the next row from a previous select (dbi_dosql).
#
#.REMARKS	After a dbi_dosql("select ... ") call, this function can be 
#		called repeatedly to retrieve all rows of the query.
#
#		Example :
#			&dbi_dosql("select * from mytab");
#			while( %row = &dbi_nextrow  ) {
#			    print $row{'columnname'};
#			    ...
#			}
#
#.RETURNS	An associative array (keyed on the column label) of formatted 
#		data, based on the datatype of the corresponding columns.
#------------------------------------------------------------------------------
sub main'dbi_nextrow
{
    %row = &main'dbnextrow($dbproc, 1);
    return %row;
}

#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#.PURPOSE	Disconnects the current database connection
#
#.REMARKS	After this function is called there are no current database 
#		connection => no other function from this package can be 
#		called before a new dbi_connect call.
#
#.RETURNS	nothing.
#------------------------------------------------------------------------------
sub main'dbi_disconnect
{
    &main'dbclose($dbproc);
}

#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#.PURPOSE	Sets the maximum number of rows returned from a query.
#
#.REMARKS	Causes SQL server to stop processing the query ( select, 
#		insert update, delete) after the specified number of rows 
#		are affected.
#		
#		PORTING NOTE: This function is added for efficiency only. 
#		When used with WDB it can safely be ignored ( leave an
#		empty function body : {} )
#
#.RETURNS	nothing.
#------------------------------------------------------------------------------
sub main'dbi_rowcount
{
    local ($rowcount) = @_;
    &main'dbi_dosql( "set rowcount $rowcount");
}

#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#.PURPOSE	Sets the default output format for dates.
#
#.REMARKS	Sets the order of the date parts month/day/year for entering
#		datetime data. Valid arguments are mdy, dmy, ymd, ydm, myd, dym.
#
#.RETURNS	nothing.
#------------------------------------------------------------------------------
sub main'dbi_dateformat
{
    local ($dateformat) = @_;
    &main'dbi_dosql( "set dateformat $dateformat");
}

1;