Using Stored Procedures

Data import can use stored procedures for more complex processes or for processes that require performance. The following notes must be considered to create stored procedure.

Procedure name:

Stored procedure name must have the format sp_xxxxxx, where:

• sp_ is the constant identification prefix
• xxxxxx, any valid name in SGDB.

Examples:

SP_CALCSALDO

SP PREP_PRODUTO

SP_MONTAFLUXODECAIXA

Parameters:

If there are parameters for stored procedure execution, inflow parameters must be started with IN_ and return parameters must be started with OUT_.

• For SIGADW, the return of at least one parameter is compulsory. See the examples at the end of this text.

Tables

• Stored procedure uses a table, conventionally called Transfer Table, whose data are volatile, that is, whenever stored procedure is run, your data must be removed, to be then made available for SIGADW.
• Transfer table and stored procedure must be previously created, through available tools of SGDB, for example, iSQL Plus for Oracle, Query Analyzer for MS-SQL, or any other tool that you want, since you have access to SIGADW database or to the origin database.
• For transfer table name, there is no standard. However, you should use the same name of stored procedure, without the prefix sp_.

How to run stored procedure in SIGADW:

• In By Starting event of data source, you request the stored procedure execution, through one of the functions displayed in the item Routines for Stored Procedure Execution.
• Code block associated with By Starting event must return .T., indicating that import process must continue, or .F. to indicate that the process must be interrupted.

Accessing transfer table data:

• In the field SQL (data), enter SQL command to read the transfer table, as a traditional table, for example, select * from CALCPEDIDO.
• In the field SQL (structure), enter SQL command to read the transfer table structure, for example, select top 1 * from PREPIMP.

Routines for stored procedure execution:

• “Stored-procedure” stored in SIGADW database DWEXECSP(<cNomeSP> , <parameter list>[,...]) -> <array>

<cNameSP> - String that identifies the stored procedure to be run.

<list of parameters> - Expression, parameters to be sent to the stored procedure. At least one return parameter is required.

<array> - Array of strings, with values set in output parameters.

• “Stored-procedure” storade in the origin database DWREMOTESP(<cNomeSP> , <parameter list>[,...]) -> <array>

<cNameSP> - String that identifies the stored procedure to be run, without the company suffix.

<list of parameters> - Expression, parameters to be sent to the stored procedure. At least one return parameter is required.

<array> - Array of strings, with values set in output parameters.

Examples:

MS-SQL

create SP_CALCPEDIDO ( @IN_NUMPED varchar(8), @OUT_Ret varchar(1) output) as 
select @OUT_Ret = '1'; -- indicates the SP start ; 
delete from CALCPEDIDO; -- clears the transfer table 
if @@ERROR = 0 // delete successful 
begin 
 // here follows the code required  
 // to feed the table CALCPEDIDO 
 // For every point/command you must check 
 // the for errors ; just test 
 // the variable“reserved” @@ERROR. Refer to MS-SQL 
 // help for other more complete examples 
end else 
begin 
 select @OUT_Ret = '9'; -- indicates successful end 
end

Oracle

CREATE OR REPLACE PROCEDURE SP_CALCPEDIDO (IN_NUMPED IN varchar2, OUT_RET OUT varchar2) is begin 
OUT_RET := '1'; -- indicate the SP start 
 truncate table CALCPEDIDO; -- clears the transfer table 
 EXCEPTION -- In case of error in procedure execution 
 // here follows the code required 
 // to feed the table CALCPEDIDO 
 // For error treatment, use the clause Exception 
 // Refer to Oracle help for more complete examples 
 OUT_Ret = '9'; -- indicates successful end 
WHEN OTHERS then -– treat every and all error 
BEGIN 
 OUT_Ret = '2'; -- indicates an error occurred 
END 
end SP_CALCPEDIDO_01;

Adv/PL code of \"By Starting\" event

dw_Ret := DWEXECSP(\"SP_CALCPEDIDO\",”12345678”,”OUT_RET”) 
if len(dw_Ret) == 0 // there is no SP or there is an error in it 
 DWLOG(“SP_CALCPEDIDO not available”) // log fail 
 return .f. // abort 
elseif dw_Ret[1] == '1'// stared, but aborted with serious error 
 DWLOG(“SP_CALCPEDIDO finished with error”) // log fail 
 return .f. // abort 
elseif dw_ret[2] == ‘2’ // indicates a controlled error occurred 
 DWLog(“SP_CALCPEIDOD finished with error type ‘2’”) 
 return .f. 
endif 
return .t. // continue to import

 

It is recommended that stored procedures have capture commands and process of error for a good execution control and to facilitate the solution of problems during the process.