Database Integration Using jBASE Technology

birdlogo.jpg (29494 bytes)

 

 

 

Status:

Original January, 1994
Rev 1 March, 1994,
Rev 2 June 1996
Rev 3 Sep 1997

Copyright (c) 1999 TEMENOS HOLDINGS NV. All rights reserved.
All Trademarks are recognized

Abstract

As both users and developers become more sophisticated in their requirements of the database world, new technology and new integration products are required. This new generation of tools must allow an application to be completely free of the underlying database or its data model and be flexible enough to adapt to future database communications requirements. The jBASE product is the first tool to provide these facilities for applications whose original view of the world was an NF2 data model.

jEDI - jBASE External Device Interface

jBASE was designed from its very beginnings to divorce the needs of the Database from the needs of the application. This objective was satisfied by the jEDI interface. The jEDI interface provides a consistent view of all I/O to the calling program. The generic interface will then generate the required calls to the underlying database or file system, which is determined when a "file" is opened or a database is connected. The entire jBASE system and jBASE OBjEX conduct all database I/O through the jEDI interface.

The system is supplied with a number of internal file drivers for jBASE hashed j-files (j3, j4), UNIX directories viewed as files and base templates for Oracle and DB2 (SQL Server, Informix and Ingress will follow). These templates are for use by developers to extend the capabilities of the system. In this manner any type of file or database may connected to the jBASE engine by anyone with a knowledge of C (or any link compatible language) and the target database system. It is a fairly simple matter to write an SQL client for a SQL database server.

The application should view the underlying database in the most generic manner possible (usually a First Normal Form view, (FNF)), in order to completely disregard the underlying file system. However jBASE also supports the Non First Normal Form (NF2) database model, where the underlying database will not change from this view.  jEDI does not deny the programmer the facilities available from any database model.

The jEDI system provides a number of very useful generic facilities to the application developer, such as transaction boundary support across multiple databases and secondary indexing (Release 3.x). The soon to be released Transaction Journalling option uses the jEDI interface to log transactions to just about any device(s). This even allows a "hot-standby" system to be maintained. Transactions will be journalled for any and all databases.

The jEDI interface not only provides access to "files" and databases of different types but also handles any device connectable to the base UNIX/Windows system in which it resides. This allows the developer or end user to specify the characteristics of any device on the system to the jEDI interface. This device is then available to any program using the jBASE/jEDI system.

Additional "jEDIs" may be added by the user and these extensions may directly utilize the client server mechanisms built into jBASE and the jEDI system. This technology therefore allows jBASE to support a myriad of databases including, Oracle, Informix, Ingress, Unidata, UniVerse and others.

Finally, all inquiry tools and jBASE utilities are written to conform to the jEDI interface, thus propagating the theme of database independence throughout the entire product. For example, the Query language, jQL and the Job Control Language, jCL inquire, read and write across multiple databases.

Embedded SQL Capabilities

jBASE provides the ability to use generic SQL directly within the jBASE programming language, jBC. This allows an SQL database such as SQL Server, Ingress, Sybase, Oracle et al to be interrogated and updated using SQL.

Embedded SQL provides a generic and industry standard method of divorcing an application from its underlying databases. Although the jEDI interface is more generic, embedded SQL allows the application programmer to use a well established method of manipulating databases. As a bonus, embedded SQL usually provides the most efficient means possible to update a relational database via ANSI standard SQL. jBASE is the only NF2 aware database management system to provide true (as in the entire system is compiled to machine code)  embedded SQL for database access.

Embedded SQL is programmed using standard SQL syntax as specified in the ANSI standards. The jBASE compiler (jbc) takes care of every aspect of its use from type conversions when using jBC variables to combining jBASE executables with the correct code library for a specific database such as Ingress or Sybase.

jBASE provides access to any database supporting embedded SQL using the same source code and a simple compilation option.

Example of Embedded SQL Coding 

* 
* Declare the SQL tables we wish to use in the target database 
* 
EXEC SQL DECLARE Invoice TABLE 

{ 
	STRING(45) Description, 
	FLOAT Value, 
	INT CustNo 
}; 
* Setup the error handler (jBC program) 
EXEC SQL WHENEVER SQLERROR CALL MySqlErrorProgram("Eg1"); 
* connect to the named database in a particular engine 
DataBaseManager = "Oracle" 
CRT "Connecting to relational database 'eg1' in ":DataBaseManager 
EXEC SQL CONNECT 'eg1'; 
*
* READ data file 
* 
OPEN "DATAFILE" ELSE ABORT 201, "DATAFILE"; 	* open a j#2 file 
SELECT TO SelList; 				* Ready to read each record 
LOOP 
	READNEXT Id FROM SelList ELSE BREAK;	* Read off record Id's until we run out 
	CRT Id; 				* Indicate the record 
	READ Invoice FROM Id ELSE CONTINUE; 	* Read the record, if missing then ignore it 
	* Now update the SQL database from the hashed file record. 
	* The :Variable tells the compiler the Variable is a jBC variable from which to take 
	* the database record. 
	EXEC SQL INSERT INTO Invoice VALUES ( :Id, :Invoice); 
REPEAT 
EXEC SQL COMMIT; 				* Tell the database manager to commit the updates 
CRT DataBaseManager:" database eg1 has been updated!" 
STOP

Using jBC (BASIC) to Write jEDI Interfaces

jBASE is shipped with a number of example jEDI drivers under the src directory of your jBASE installation. These drivers show how external programs can call jEDI to perform I/O and also, how you can create a jBC subroutine to service your database.

This subroutine can handle one or more traditional files and will translate the read, write, lock etc requests it receives into the equivalent SQL statements for the table(s) in say, SQL Server, that match your original file. This allows people who do not understand C or C++ programming to create an efficient database driver that transparently moves your legacy database into SQL server or any other database. Interested users should contact jBASE Software for further information.

Summary