Uniface-HOWTO Series

	Activate Stored Procedures

Introduction

	One of the design goals behind UNIFACE I/O is to eliminate the need for application developers to write low-level DBMS-specific code
	However, sometimes developers need to be able to write their own SQL commands or stored procedures .

Reasons

	Main reasons
		Complicated queries
		Performance
		Specific DBMS features
		Re-use existing stored procedures
	Current situation
		“SQL” instruction limited
		“SQL/print” returns string

Approach taken

	The approach taken fits well with the component based architecture of UNIFACE
				use of “best of breed” components
	Using a database component means you take the good and the bad
				use of database constructs as joins and packages
				no hitlist , overflow tables etc
				not portable

"IMPLEMENTATION"

	IMPLEMENTATION

Overview

	a “stored procedure” should be viewed by UNIFACE like any other non-UNIFACE component, such as a 3GL
			- activated via an “activate” command
			- present in the Signature Repository
			- definable in the Assembly Workbench etc

Overview: Example

	Input Parameter: The stored procedure takes this value in and completes the SQL in the stored procedure
	Output Parameter: The UNIFACE component takes this value in and populates the external structure

Overview: steps to take

1. Create Component
Activate SP component from a UNIFACE Form

	activate “<componentname>”.<operation>(input,output)

2. Define Signature Definition

	Define in same way as any non-UNIFACE component with:
		COMPONENT NAME
		OPERATION NAMES
		INPUT/OUTPUT PARAMETERS

UNIFACE Stored Procedure Component

	Arguments
	must contain:
		same number of fields as output from SP
		same order of fields as output from SP

UNIFACE Stored Procedure Component

	Parameters
	Basic parameters:
		All basic UNIFACE 7.2 data types are supported
	Entity parameters:
		used to return result of a stored procedure to UNIFACE component
		cannot be used as an input parameter
		entity and basic parameters can be mixed

3. Mapping: operations to stored procedures

	Components can be assigned to stored procedures via .asn files
	[PATH]
	$ORA1 ORA:DB|SCOTT|TIGER
	[SERVICES-EXEC]
	COMPONENT1 = $ORA1:COMPONENT1
	The $SSP path (Service Stored Procedure) can be used -which goes via PSV

Mapping: operations to stored procedures

	[SERVICES-EXEC]
	COMPONENTA   = ORA PATH1
	op1
	op2 ; all operations go to ORAPATH1
	Assigning individual operations
	[SERVICES-EXEC]
	op1.COMPONENTA     = ORA PATH1
	op2.COMPONENTA     = ORA PATH2
	op3.COMPONENTA     = SYB PATH1

4. Create the Stored Procedure

Stored Procedure - characteristics

	A stored procedure should provide
				a name
				an  input parameter
				an output parameter
	so it maps to the activate  command
	eg) active “component”.SP_name(input,output)
	NB: UNIFACE is oblivious to the code written in the stored procedure.
		DBMS developer is responsible for code in the SP

Stored Procedure - characteristics

	Stateless (terminate when complete)
	No Hitlist
	Support for: ORA,SYB,INF
		SQL Server & Others scheduled for later depending on requests made

"EXAMPLE"

	EXAMPLE

Activate Stored Procedures Example

Create the Stored Procedure

The database side

	Input
		Title name
	Output
		Set of occurrences
		Number of titles found

Define form: entity must have quivalent number of fields (in same order) as Stored Procedure output

	ENTITY:
	TITLE string c50
	TYPE string c10
	PRICE numeric n5.2
	PUBLISHINGDATE datetime
	AUTHORLASTNAME string c40
	AUTHORFIRSTNAME string c40

Proc code

	<DETAIL>
	activate "PUBLICATIONS".TITLEINFO("PUB.MODEL", TITLE.PUB, PUBCOUNT.STATUS)
	PUBSTATUS.STATUS = $status
	if ( $procerror < 0 )
	putmess $procerrorcontext
	endif

Define Signature Definition

	Define in same way as any non-UNIFACE component with:
		COMPONENT NAME
		OPERATION NAMES
		INPUT/OUTPUT PARAMETERS

ASN setting

	[PATHS]
	$SYB1 SYB:aServer|?|?
	[SERVICES_EXEC]
	PUBLICATIONS = $SYB1:PUBLICATIONS

Benefits

	Performance enhancement
	use of specific DBMS features
	ability to use existing stored procedures effectively
	not tied to UNIFACE DML code
	“best of breed” approach

Performance test

	Environment:
	Win95(client) --------SQL*NET----------> UNIX(sun)----->ORA8  (5000 occs)
	Uniface retrieve:
	Begintime : 27-nov-98 11:37:28
	Endtime   : 27-nov-98 11:37:41
	Elapsed   : 00:00:13
	Stored Procedure:
	Begintime : 27-nov-98 11:37:41
	Endtime   : 27-nov-98 11:37:49
	Elapsed   : 00:00:08
	Result  : 5 seconds faster
		End date/time : 27-nov-1998, 11:37:50