Notes

Outline

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