Using SQL in MATISSE-Eiffel Binding

1st Edition - August 1998

Contact: nakao@matisse.com
© 1998 Tokyo Gas Co., Ltd.

This note shows how to use MATISSE SQL through the MATISSE-Eiffel Binding.

The two classes, MT_SQL_STATEMENT and MT_SQL_RESULT_SET, provide interface to execute a SQL statement and obtain the results.

MT_SQL_STATEMENT
This class allows to execute a SQL statement and to obtain the results produced by the statement. You call the function execute_query with an argument which is a SQL statement as shown in the following example.

local
	sql_statement: MT_SQL_STATEMENT
	sql_result: MT_SQL_RESULT_SET
do
	!!sql_statement.make
	sql_result := sql_statement.execute_query(“SELECT * FROM PRESIDENCY WHERE number = 1”)
	...
end

MT_SQL_RESULT_SET
This class provides access to the set of objects generated by a SQL statement execution. It maintains a cursor to its current object. The next example shows how to access the U.S. presidents who served for consecutive 8 years.

local
	sql_statement: MT_SQL_STATEMENT
	sql_result: MT_SQL_RESULT_SET
	a_president: PERSON
do
	!!sql_statement.make
	sql_result := sql_statement.execute_query(“SELECT is_served_by FROM PRESIDENCY WHERE ending_year - starting_year = 8”)
	from
		sql_result.start
	until
		sql_result.exhausted
	loop
		a_president?= sql_result.item
		... -- do something on a_president
		sql_result.forth
	end
	sql_result.close

Features of class MT_SQL_RESULT_SET
The following is an extraction of the “short” form of the class MT_SQL_RESULT_SET.
feature -- Initialization
	make_from_selection (a_selection_name: STRING)
			-- This initializes the SQL result from a valid named selection.
			-- A selection result of a SELECT statement can be named with the
			-- keyword INTO.

feature -- Accessing
	item: MT_STORABLE
			-- This function returns the current object available through the
			-- SQL result selection.
			-- Was declared in MT_SQL_RESULT_SET as synonym of `item' and `object'.
		require
			not_off: not off

	selection_name: STRING
			-- This returns the name of the SQL result selection.
			-- If it is not named, an mepty string is returned.

feature -- Cursor movement
	start
			-- This procedure initializes the internal selection stream to access
			-- the objects available through the SQL result selection.

	forth
			-- This procedure moves current cursor to the next object
			-- available through the SQL result selection.
		require
			not_off: not off

feature -- Status	
	close
			-- This procedure closes the SQL result selection if it is not named.
			-- The closed SQL result selection cannot be used anymore.
		require
			started: started

	free_selection
			-- This procedure frees a named selection result. When it has been
			-- called, you cannnot anymore access the selection with a new
			-- MT_SQL_RESULT_SET.
			-- A named selection can be created with the keyword INTO in a
			-- SELECT statement.
		require
			stream_closed: not started

feature -- Status report
	off: BOOLEAN
			-- Is there no current object?

	exhausted: BOOLEAN
			-- This function tests whether or not the end of the cursor
			-- has been reached.
		require
			started: started

	started: BOOLEAN
			-- Is a stream on the reuslt set open and is the stream started?

feature -- Measurement

	count: INTEGER
			-- This function returns the number of objects contained in the
			-- SQL result selection.
	
end -- class MT_SQL_RESULT_SET

Appendix  - ODL definition for example code
interface PERSON : persistent
{
   attribute String last_name mt_make_entry "make-full-text-entry";
   attribute Char middle_initial = '\0';
   attribute String first_name mt_make_entry "make-entry";

   relationship Set<PERSON> spouse [0, 1]
	inverse PERSON::spouse;

   relationship Set<PRESIDENCY> is_in_charge_of
	inverse PRESIDENCY::is_served_by;

   mt_index PersonFullName criteria 
	{PERSON::last_name MT_ASCEND 16},
        {PERSON::first_name MT_ASCEND 16},
	{PERSON::middle_initial MT_ASCEND};
};

interface PRESIDENCY : persistent
{
   attribute Long number mt_make_entry "make-entry";
   attribute Long starting_year;
   attribute Long ending_year;

   relationship PERSON is_served_by
	inverse PERSON::is_in_charge_of;

};