Copyright © TIBCO Software Inc. All Rights Reserved
Copyright © TIBCO Software Inc. All Rights Reserved


Chapter 6 Invoking Stored Procedures with TIBCO Service Gateway for ODBC : Sample Invocation

Sample Invocation
Sample Stored Procedure
The following is a stored procedure as defined in MS SQL Server:

 
create procedure sstub (@p1 decimal(3)=9,@p2 decimal (3)=6 output) as
declare @pp decimal (3)
select @pp=@p2
select title_id,pubdate,royalty from pubs.dbo.titles where royalty <36
select title_id,royalty from pubs.dbo.titles where royalty >36
select @p2=@p2-@p1
return @pp

 
Sample Stored Procedure Mapping
The following is a stored procedure mapping in a SLK definition:

 
COMMAND==> TABLE DEFINITION
Table: SQLL Type: SLK Unit: HURON
DBSource: sqls ServerType: SLK
ServerId: SLK2 Proc/ProcResSet: P (P/R/N)
Procedure: sstub;1 ServerOrders: Y (Y/N)
Qualifier: pubs ImpliedUpdates: N (Y/N)
Owner: dbo Dictionary: X (X/I)
Location Parm Default Src Sourcename Event Rule Typ Acc
---------------- ---------------- - ---------------- ---------------- - -
_ LOCATION _
|--- External Field --------------|--- Metadata Field -------------------
ExternalName Vrt Name Typ Syn Len Dec Ord Req
------------------------------ - ---------------- - -- ----- -- - -
K _ @HANDLE@ @HANDLE@ Q B 2 0
K _ @RESULT@ @RESULT@ Q B 2 0
S _ RETURN_VALUE R RETURN_VALUE Q P 6 0
S _ @p1 I @P1 Q B 2 0
S _ @p2 B @P2 Q B 2 0
(P - parameter K - key, S - select, I - insert, R - replicate, D - delete)
PFKEYS: 3=SAVE 22=DEL 13=PRINT 2=DOC 4=PROCS 5=COLUMNS 6=QUALIFIERS 9=OWNERS

 
Sample Result Set Mappings
The stored procedure STUB builds two result sets. We map the first of them with the following SLK definition:

 
COMMAND==> TABLE DEFINITION
Table: SQLL1 Type: SLK Unit: HURON
DBSource: sqls ServerType: SLK
ServerId: SLK2 Proc/ProcResSet: R (P/R/N)
Procedure: titles ServerOrders: Y (Y/N)
Qualifier: pubs ImpliedUpdates: N (Y/N)
Owner: dbo Dictionary: X (X/I)
Location Parm Default Src Sourcename Event Rule Typ Acc
---------------- ---------------- - ---------------- ---------------- - -
_ LOCATION _
|--- External Field --------------|--- Metadata Field -------- ---------
ExternalName Vrt Name Typ Syn Len Dec Ord Req
------------------------------ - ---------------- - -- ----- -- - -
P _ @HANDLE@ @HANDLE@ Q B 2 0
S _ title_id S TITLE_ID S V 6 0
S _ pubdate S PUBDATE D B 4 0
S _ royalty S ROYALTY Q P 6 0
_
(P - parameter, K - key, S - select, I - insert, R - replicate, D - delete)
PFKEYS: 3=SAVE 22=DEL 13=PRINT 2=DOC 4=PROCS 5=COLUMNS 6=QUALIFIERS 9=OWNERS

 
We map the second result set with the following SLK definition:

 
COMMAND==> TABLE DEFINITION
Table: SQLL2 Type: SLK Unit: HURON
DBSource: sqls ServerType: SLK
ServerId: SLK2 Proc/ProcResSet: R (P/R/N)
Procedure: titles ServerOrders: Y (Y/N)
Qualifier: pubs ImpliedUpdates: N (Y/N)
Owner: dbo Dictionary: X (X/I)
Location Parm Default Src Sourcename ’ Event Rule Typ Acc
-------------- ---------------- - ---------------- ’ ---------------- - -
_ LOCATION
|--- External Field ------------------|-------------- Metadata Field ---------
ExternalName Vrt Name Typ Syn Len Dec Ord Req
------------------------------ - ---------------- - -- ----- -- - -
P _ @HANDLE@ @HANDLE@ Q B 2 0
S _ title_id S TITLE_ID S V 6 0
S _ royalty S ROYALTY Q B 4 0
_
_
(P - parameter, K - key, S - select, I - insert, R - replicate, D - delete)
PFKEYS: 3=SAVE 22=DEL 13=PRINT 2=DOC 4=PROCS 5=COLUMNS 6=QUALIFIERS 9=OWNERS

 
For stored procedure result set mappings, external table characteristics (name, qualifier, owner) are ignored at runtime; however, it is helpful sometimes to have these values in the definition. In the example, they assist in defining the mapping, as they refer to the table used by the procedure to build the result set.
Sample Event Rule
The following event rule is executed when a result set is returned (Access=U):

 
RULE EDITOR ==>
_ C(PROC, HANDLE, RESULT, COUNT);
_ ----------------------------------------------------------------------------
_ COUNT = 1; | Y N N
_ COUNT = 2; | Y N
------------------------------------------------------------------+---------
_ FORALL SQLL1 WHERE @HANDLE@=HANDLE : | 1
_ CALL MSGLOG(SQLL1.TITLE_ID ||','|| SQLL1.PUBDATE) || ',' |
_ || SQLL1.ROYALTY); |
_ END; |
_ FORALL SQLL2(HANDLE) : | 1
_ CALL MSGLOG(SQLL2.TITLE_ID ||',' || SQLL2.ROYALTY); |
_ END; |
----------------------------------------------------------------------------

 
Sample Invocation Statements

1
This is possible because the procedure returns a value.

After EXECUTE and TRANSFERCALL statements, you can use the statement CALL MSGLOG(GETENMSG) to extract the return value produced by the procedure. GETENDMSG returns a NULL if the definition contains no return value. In all cases, upon return of control after the execution of the procedure, the current occurrence of the SLK table STUB contains the values of the procedure parameters (including the return value) as set by the procedure.

Copyright © TIBCO Software Inc. All Rights Reserved
Copyright © TIBCO Software Inc. All Rights Reserved