Developed at the

Polish-Japanese Institute of Information Technology

Chair of Software Engineering

SBA and SBQL home pages

© Copyright by ODRA team, © Copyright by PJIIT

 

 

ODRA – Object Database for Rapid Application development

Description and Programmer Manual

 

 

by Radosław Adamus, Tomasz Wardziak, Marcin Dąbrowski and the ODRA team

 

15. Interoperability with Java and .NET

15.1 Accessing Java Libraries via Reflection

ODRA supports calling external Java code directly from SBQL programs. You can invoke any arbitrary code written in Java, pass parameters to it and utilize its return value. The method is based on the reflexive capabilities of Java.

The following example creates a new java.util.Random object and invokes the nextInt method:

rndref:integer; //1

rndref:=external load_class("java.util.Random"); //2

external new_object(rndref); //3

external init_parameters(rndref); //4

external add_parameters(rndref, 5); //5

external invoke_integer(rndref, "nextInt"); //6


Operations defined above will return random number between 0 and 5.

Line 1 is responsible for creation of rndref variable that will store the reference to an external Java object.

Line 2 loads the java.util.Random class and stores its reference on the rndref variable. In this way any Java class can be loaded.

Line 3 creates a new instance of Java Random object.

Line 4 is always mandatory. It initializes an internal collection of parameters that will be used to invoke a method from an object.

Line 5 adds one parameter that will be used as an argument for the nextInt method. In this case number 5 is loaded as a first and only argument of the method. You can load as many arguments as the Java method requires. Consult Java documentation for method signatures.

Line 6 performs execution of external call and returns value to the SBQL stack (unless the return value is of type void). In this case invoke_integer method is executed, but you should choose one of the following methods to invoke, depending of a return type of Java method:

·        Invoke_integer – method’s return type is integer

·        Invoke_void – method’s return type is void

·        Invoke_library – method’s return type is reference

·        Invoke_string – method’s return type is string

·        Invoke_boolean – method’s return type is boolean

·        Invoke_real – method’s return type is double

At this moment Java arrays are not supported and methods returning Java arrays should be wrapped with methods returning Java collections.

Sample external invocations:

data:integer;

data:=external load_class("java.util.Date");

external new_object(data);

return external invoke_string(data, "toString");

Returns current date as a string.

ToUpper(val:string):string

{

 string_lib:=external 

         load_class("odra.sbql.external.lib.StringLib");

 external new_object(string_lib);

 init_string();

   

 result:string;

 external init_parameters(string_lib);

 external add_parameters(string_lib, val);

 result:=external invoke_string(string_lib, "ToUpper");

 return result;

}

The method ToUpper takes string as a parameter and returns this string in uppercase. It uses ToUpper method defined in odra.sbql.external.lib.StringLib.

SBQL code creating standard SBQL library can be found in res/standard_library folder.

15.2 Java Object Base Connectivity (JOBC)

ODRA JOBC (Java Object Base Connectivity) is defined and implemented according to the idea, syntax and semantics of JDBC (Java Data Base Connectivity). The differences concerns:

  • Input: we assume SBQL queries rather than SQL queries,
  • Output: we assume serialized ODRA objects rather than serialized relational tables.

In this section we introduce the ideas and usage of the ODRA JOBC API. In particular, we explain how to configure and connect an application to an ODRA database, how to prepare a query and how to execute it and how to process its result.

The implemented ODRA JOBC API driver is compatible with Java 1.4+.

15.2.1 Configuring connection

Connection configuration is accomplished during instantiating an JOBC class instance. Available constructor signatures are the following:

public JOBC(String user, String password, String host, int port);

public JOBC(String user, String password, String host);

where:

user – the name of the database user

password – the user password

host – the IP/DNS address of the ODRA server.

port – an optional  ODRA database instance port number (if not provided the default is assumed which is 1521).

Example

Connection to local host on the default port as user ‘admin’ with password ‘admin’.

JOBC db = new JOBC("admin", "admin", "localhost");

 

15.2.2 Connecting to and disconnecting from an ODRA database

Connection to a database is performed by the connect method. If connection cannot be established, an JOBC exception is thrown. The connect method signature is as follows:

void connect() throws IOException;

An opened connection can be explicitly closed by calling the close() method.

Example

db.connect();

 . . . . . . //execute queries

db.close();

 

15.2.3 Setting up a working module

An ODRA data is stored in a hierarchical structure of modules. Each user has an own root environment that is named with the username. A root module can contain any number of sub-modules storing programs and data. After connecting to the database the current module indicator is set to the user root module. To switch the current module the programmer can call the following method in the JOBC class:

void setCurrentModule(String moduleGlobalName) throws JOBCException;

where:

moduleGlobalName – the global name of the requested module. The global name starts with the username and contains zero or more sub-module names separated by dots.

Example

Switch to the module ‘reports’ that is a sub-module of ‘current’ module owns by the user ‘admin’:

db.setCurrentModule(“admin.current.reports”);

 

15.2.4 Executing SBQL queries

Executing SBQL queries requires the following actions:

  1. Call the execute method on the JOBC class instance that takes the SBQL query string as a single  parameter. In case the query without parameters this is the simplest way.

public Result execute(String query) throws JOBCException;

  1. If a query has parameters (described later), an instance of the SBQLQuery class has to be obtained from the JOBC class instance through the getSBQLQuery method call:

SBQLQuery getSBQLQuery(String query);

where ‘query’ is a string containing the SBQL query.

A query stored in an SBQLQuery class instance can be performed through a call to the overloaded execute method of the JOBC class instance.

public Result execute(SBQLQuery query) throws JOBCException

The execute method returns an instance of the Result class, as described below. The same method is used to read and update the data stored in the database.

Examples

Result result = db.execute(“2+2”);

 

Result result = db.execute(“startService()”);

 

Result result = db.execute(“(Employee where lName=\”York\” and worksIn.Dept.name = \“IT\”).salary := 2000”);

 

SBQLQuery query = db.getSBQLQuery(“Person where lName=\”York\””);

Result result = db.execute(query);

 

15.2.5 Queries with parameters

Parameters in a query string are represented by names enclosed in curly brackets. To set a parameter value, first the SBQLQuery class instance has to be obtained from a JOBC instance (as described in the previous sub-section). Setting actual values for query parameters of different types can be performed through the following SBQLQuery class instance methods:

void addIntegerParam(String name, int param) throws JOBCException;

void addBooleanParam(String name, boolean param) throws JOBCException;

void addStringParam(String name, String param) throws JOBCException;

void addRealParam(String name, double param) throws JOBCException;

where:

name – the parameter name

param – the actual value for the parameter

A parameter with a given name can occur in a query more than once. All the parameter occurrences will be substituted by the actual value set for it. An instance of JOBCException is thrown if the name does not match the parameter name in the target query.

Examples

SBQLQuery query = db.getSBQLQuery(“Person where name = {pname}”);

query.addString(“pname”, “Smith”);

 

SBQLQuery query = db.getSBQLQuery(“Person where name = {pname} and age > {page}”);

query.addStringParam(“pname”, “Smith”);

query.addIntegerParam(“page”, 30);

 

SBQLQuery query = db.getSBQLQuery(“Book where (pagesNo >= {pnumber} – {range} and pagesNo <= {pnumber} + {range}).(title, genre)”);

query.addIntegerParam(“pnumber”, 150);

query.addIntegerParam(“range”, 20);

 

15.2.6 Processing query results

Results of SBQL queries are represented by the Result class instance. It can represent different types of SBQL query results. The following types of results are supported:

1.      primitive value of the following types:

a.            integer

b.           real

c.            string

d.           boolean

e.            date

2.      object reference

3.      structure of results

4.      bag of results

5.      named result (i.e. a binder) – any of the result kinds equipped with a name.

Getting a primitive value

To check if the result is primitive the following method is to be used:

boolean isPrimitive();

If the result is primitive, its value can be obtained with the use of the following methods in the Result class that maps an ODRA result to a value of a Java equivalent type:

int getInteger() throws JOBCException;

double getReal() throws JOBCException;

String getString() throws JOBCException;

boolean getBoolean() throws JOBCException;

Date getDate() throws JOBCException;

Examples

Result result = db.execute(“2+2”);

int value = result.getInteger();

 

Result result = db.execute(“avg(Employee.salary)”);

double value = result.getReal();

 

Result result = db.execute(“forall(Person) address.city = \“Warsaw\””);

boolean result = result.getBoolean();

Object references

SBQL queries can return object references. Because in the context of JOBC calls object references are currently not supported, they are represented as string constant values - “object reference”.

Complex results

An SBQL query can return a structure, which can be the result of operators used in a query or can be returned by the dereference acting on an identifier of a complex object. Fields of a structure can be named or unnamed. To check if the result is complex the following method is to be used:

boolean isComplex();

Fields of a complex result can be accessed in the following ways:

  1. By calling the fields() method that returns a bag of results representing structure fields. The bag can be processed by iteration.
  2. If a field is named,  it can be accessed with the use of getByName(String name) method.

Example

The query returns two elements structure ( {integer, string} ) containing the Smith’s age and home address. We assume that there is only one Person with that name and the cardinality of fields ‘age’ and ‘address’ is [1..1].

Result result = db.execute(“(Person where lName = \“Smith\”).(deref(age), deref(address))”);

 

Result fields = result.fields();

System.out.println(fields.get(0) + “,” + fields.get(1));

Bag of results and empty results

SBQL queries can return a bag of results (primitive, structures, object references or named). The JOBC API allows for iteration over the results in a bag result. The Result class implements the Iterable<Result> interface.

To check if the result is complex the following method is to be used:

boolean isBag();

An empty result has the following properties:

result.isBag();  // returns true

result.size(); // returns 0

result.isEmpty(); // returns true

Examples

Result result = db.execute(“(Person where age > 20).lName”);

 

//java 1.5+ foreach loop (in java1.4 use result.iterator())

for(Result res : result.toArray()){

    System.out.println(res.getString());

}

Named results

Results returns by SBQL queries can be named. A name can be given explicitly with the SBQL auxiliary names operators (‘as’ and ‘groupas’) or can be a consequence of dereference operation on an identifier of a complex object. The difference between ‘as’ and ‘groupas’ SBQL operators is that the first names each element in the result bag and the latter names the entire bag.

In JOBC the names are to be used to navigate in the result searching for a sub-result with a given name. In other situations the result name is transparent.

Example:

Result result = db.execute(“2 + 2 as result”);

int ires = result.getInteger();

Result result = db.execute(“(Person where age > 21).(name as personName, age as personAge) as adult”);

Filtering results by name

A result name can be used to search for a sub-result with a particular name. The search can be performed with the use of Result class instance method getByName taking the string representing a result name as a parameter:

Result getByName(String name);

Result returned by the getByName method call is a Result class instance representing a sub-result that was named and the name is equal to the parameter. If nothing was found the empty result is returned. The search is performed to the first occurrence of a named result, thus if the searched named result is a part of the other named result it will not be returned.

Examples:

Result result = db.execute(“(2 + 2) as result”);

Result intresult = result.getByName(“result”);

 

Result namedAdults = db.execute(“(Person where age > 21).name as personName, age as personAge) as adult”);

      //we assume that there is more than one person

      //satisfying the condition

namedAdults.isNamed(); //returns true

namedAdults.isBag(); //returns true (the names are transparent)

 

//java 1.5+ foreach loop

for(Result adult : namedAdults.toArray()) {

   adult.isNamed(); //returns true (due to operator as used in

                    //the query)

   adult.isComplex(); //return true (names are transparent)

}

 

//java 1.4

for(Iterator iter = unnamedResult.iterator();iter.hasNext();) {

   Result adult = (Result)iter.next();

   adult.isNamed(); //returns true (due to operator as used in

                    //the query)

   adult.isComplex(); //returns true (names are transparent)

}

 

Result unnamedAdults = namedAdults.getByName(“adult”);

unnamedAdults.isNamed(); //returns false

unnamedAdults.isBag(); //returns true

 

//java 1.5+ foreach loop

for(Result adult : unnamedAdults.toArray()) {

   adult.isNamed(); //returns false

   adult.isComplex(); //returns true

}

 

//java 1.4

for(Iterator iter = unnamedResult.iterator();iter.hasNext();) {

   Result adult = (Result)iter.next();

   adult.isNamed(); //returns false

   adult.isComplex(); //return true

}

 

Result names = result.getByName(“adult”).getByName(“name”);

names.isNamed(); //returns false

names.isBag();returns true

 

Result ages = result.getByName(“adult”).getByName(“age”);

ages.isNamed(); //returns false

ages.isBag(); //returns true

 

15.3 .NET Object Base Connectivity for ODRA (NOBC)

 

The NOBC (.NET Object Base Connectivity) is an access client software to the ODRA system for the Microsoft .NET platform. It is similar to JOBC – Java Object Base Connectivity and SqlClient API. The software allows a .NET/C# programmer to call SBQL queries and programs from .NET-based applications. As an inherent part the software allows the programmer to process the results of SBQL queries and programs on the side of .NET. This section introduces the basic NOBC usage and presents its API.

The NOBC has been developed using the .NET Framework 2.0 and is compatible with all .NET applications that use framework 2.0+. The NOBC is written in C# 2.0 and all code excerpts in this chapter are presented in this programming language. Still all other .NET languages like VB.NET or J# can be used.

15.3.1 Importing NOBC to Project

The NOBC has been developed as a class library. It is delivered as a DLL (Dynamic Linking Library), a Class Library which can be included in any other .NET application in order to enable communication with an ODRA server. .NET application communicates with an ODRA server when the library is added as a reference to a .NET project. This is done by going to the Project/Add Reference menu command.

NewReference.jpg

In the opened window we need to go to the fourth tab called “Browse” and select the NOBC DLL library from the place where it can be found on your disk.

After these steps we are ready to connect with the ODRA server. In the following we have to write a code that connects with the server and passes the first query.

All classes from the NOBC library are placed in a separate namespace called “NOBC” that has to be either imported or all the class names must be preceded by the “NOBC” namespace prefix.

using NOBC;

 

NOBC.NOBCConnection conn = new NOBC.NOBCConnection(...);

 

15.3.2 Creating and configuring connection

In order to start working with the NOBC, the NOBCCommand class must be created. This class offers one empty constructor and another one where ConnectionString can be passed:

Example:

NOBCConnection conn = new NOBCConnection(

     "server=localhost; port=1521; user=admin; password=admin"

     );

A ConnectionString is a popular idea in the .NET world and can be found, for instance, in the Microsoft’s ADO.NET solution. A ConnectionString consists of substrings divided by a semicolon (“;”). Each set is formed by a key and a value, e.g. "server=localhost;” A ConnectionString may consist of many different keys and values. If some values that might be needed to establish the connection are omitted, default values are used. When default values are correct for the connection, a ConnectionString that is empty is also possible. Here is a list of elements that can be included in the ConnectionString along with the default values:

 


Key alias

Description

Default value

Server

Address of the ODRA server. Can be passed either as IP (i.e. 192.168.1.1) or a computer name (localhost, SERVER)

“localhost”

Port

TCP/IP port on which communication based on sockets with the ODRA server is performed. Information from the ODRA server administrator might be needed about the possible default value change.

1521

user or uid or user id

User name that will be used to log in to the ODRA server

Empty string

password or pwd

User password that will be used to verify user name at log in to the ODRA server.

Empty string

 

15.3.3 Connecting to and disconnecting from an ODRA database

Connection to a database is performed by the Open() method.  An opened connection can be explicitly closed by calling the Close() method. All commands execution must be performed on an opened connection.

Example:

conn.Open();

//work with ODRA

conn.Close();

Connection state can be checked any time by calling IsOpened property.

The NOBCException can be raised during an Open call if the ODRA server is either not responding or the logon credentials are not correct.

15.3.4 Executing SBQL queries

A special class has been created for executing SBQL commands. It is called NOBCCommand and can be initialised by using the following constructor:

public NOBCCommand(

string command, string module, NOBCConnection con);

The first parameter of the constructor is a query string. It should be a proper SBQL query. The second parameter is the module name on which we wish to execute an SBQL command. The third one is the NOBCConnection. After pasing this connection all the work concerning query execution can be performed.

Once a NOBCCommand is created it can be executed on the open connection. This is done by calling Execute() method:

public Result Execute();

The value returned by this method is a Result object that will be discussed in the next subchapter. The full code that executes an SBQL command using NOBC might look as follows:

Example:

NOBCConnection conn = new NOBCConnection(

         "server=localhost; port=1521;user=admin; password=admin");

NOBCCommand comm = new NOBCCommand(

         "count(Person.worksIn);", "admin.testm0", conn);

try

{

conn.Open();

Result res = comm.Execute();

conn.Close();

}

catch (NOBCException ex)

{

if (conn.IsOpened) conn.Close();

MessageBox.Show(ex.Message);

}

 

15.3.5 Working with results

The NOBC library supports working with returned SBQL results according to the SBA paradigm where results can be of various kinds and types:

§         Atomic value (e.g. 4, “Jan”, 0.1),

§         Binders (e.g. name(“Kowalski”) ) enveloping some result,

§         Structure of results

      (e.g. {firstName(”Jan”), lastName(”Kowalski”)},

§         Bag or sequence of results.

An atomic value can be one of the following primitive types:

§         Boolean (true, false)

§         Date

§         Double

§         Integer

§         String

Based on that knowledge and after a deep investigation concerning the ODRA source code the structure of the Result class and the subclasses were introduced in the NOBC solution. It is based, similarly to the ODRA solution, on an abstract class Result that is inherited by all other subclasses. Thanks to this inheritance, working with a returned result is easier.

 

Working with bags

A bag is an elementary element of the Result structure. Each time a result of the query is returned it is recommended to treat it as if it was a bag. It is important because when a single result is returned the ODRA server treats it as a bag having one element. Due to this type coercion a single element can be processed by where,  join, foreach and other SBQL macroscopic operators.

The Result class offers the following fields to allow convenient work with collections:

public abstract SingleResult this[int index] { get; }

public abstract bool Contains(SingleResult item);

public abstract int Count { get; }

public abstract IEnumerator<SingleResult> GetEnumerator();

Thanks to the last field the following call is possible:

NOBCCommand comm = new NOBCCommand(

"(unique(Person.worksIn.address.city) as x orderby x).x;", "admin.testm0", conn);

try{

conn.Open();

Result cities = comm.Execute();

conn.Close();

List<string> citiesList = new List<string>();

foreach (Result city in cities)

{

citiesList.Add(((StringResult)city).Value);

}

}

As a result of this code the generic list named citiesList has all the names of cities where employees work. This code will also work when the returned bag has no elements, one element or more.

 

Working with primitive values

A set of classes has been created to represent primitive values according to SBA. These are:

-     BooleanResult

-     DateResult

-     DoubleResult

-     IntegerResult

-     StringResult

They all inherit from a SingleResult class that inherits from the abstract class Result. A value of the primitive type can be obtained after casting Result to a particular type and calling a Value property. Information about the type of the result can be received by calling ResultType property.

Example:

NOBCCommand comm = new NOBCCommand(

"count(Person.worksIn);", "admin.testm0", conn);

Result res = comm.Execute();

if (res.ResultType == ResultType.Integer)

{

int result = ((IntegerResult)res).Value;

}

else

{

MessageBox.Show(string.Format(

                   "Invalid result type:{0} was returned.",

                    res.ResultType));

}

 

Examples:

NOBCCommand comm = new NOBCCommand(

"2+2;", "admin ", conn);

Result result = comm.Execute();

int value = ((IntegerResult)res).Value;

 

NOBCCommand comm = new NOBCCommand(

"avg(Employee.salary)", "admin.module0 ", conn);

Result result = comm.Execute();

double value = ((DoubleResult)res).Value;

 

Object references

SBQL queries can return object references, but the current NOBC implementation does not support them. Therefore all queries that return references will result in an exception with the message “Invalid query, reference was returned.”

 

Complex Objects

Special class “StructureResult” is developed to enable work with complex objects. An SBQL query can return a structure that is a set of other single results. In a program it can be checked whether the result is a structure by calling a ResultType property. Then it will be equal to “ResultType.Struct”. Fields of the structure can be found in the array named Fields.

The following code excerpt presents working with a bag of structures:

NOBCCommand comm3 = new NOBCCommand(

 "(Person as p join p.worksIn as f)."+

 "(p.fName, p.lName,p.age, f.name, f.address.city, f.address.street);",

 "admin.testm0", conn);

try

{

    conn.Open();

    Result persons = comm3.Execute();

    conn.Close();

    List<Person> personList = new List<Person>();

    foreach (StructResult person in persons)

    {

        Person p = new Person();

        //repack fields to the prepared class structure

  p.FName = ((StringResult)person.Fields[0]).Value;

        p.LName = ((StringResult)person.Fields[1]).Value;

        p.Age = ((IntegerResult)person.Fields[2]).Value;

        p.FirmName = ((StringResult)person.Fields[3]).Value;

        p.City = ((StringResult)person.Fields[4]).Value;

        p.Street = ((StringResult)person.Fields[5]).Value;

        personList.Add(p);

    }

}

 

Named results

A special class BinderResult has been developed to enable working with named results. SBQL query can return named results when asorgroupasoperators are used. BinderResult is a single result that has two additional fields named:

-        Value of a type Result where inner value of the binder is placed

-        Name of a type string

Besides these two fields working with binder is same as with any other SBQL result:

Example:

NOBCCommand comm = new NOBCCommand(2 + 2 as result;","admin", conn);

conn.Open();

Result res = comm.Execute();

conn.Close();

Result innerRes = ((BinderResult)result).Value;

int innerres = ((IntegerResult)innerRes).Value;

 

 

Last modified: October 28, 2008