Service Stored Procedures on Microsoft SQL Server

Service stored procedures are supported for the Microsoft SQL Server connector.

The stored procedure name used for execution by Uniface is depends on the whether literal names have been defined in the signature of the Stored Procedure Component.

Component Name and Operation Name Specification

Literal Component Name

Literal Operation Name

Stored Procedure Name

Example

—

—

UnifaceComponentName.UnifaceOperationName

MY_CPT.SP1

—

Specified

LiteralOperationName1

sp1

SchemaName.OperationName2

schema.sp1

DbOwner.LiteralOperationName3

dbo.sp1

Specified

—

LiteralComponentName.UnifaceOperationName

sp.SP1

Specified

Specified

LiteralComponentName.LiteralOperationName

sp.sp1

SchemaName.OperationName2

schema.sp1

  • UnifaceComponentName: MY_CPT
  • UnifaceOperationName: SP1
  1. If LiteralOperationName does not include a schema name, the default schema of the user is used.
  2. If LiteralOperationName includes a schema name (for example, SchemaName.OperationName), the stored procedure will be called as SchemaName.OperationName. If a literal component name is also specified, it is ignored.

    Caution: This is not recommended because it will not run on other databases connectors.

  3. If the dbowner option is specified, it is prefixed to the operation name and used instead of the default schema of the user.

Parameter names are not used during the execution of stored procedures. However, their order (that is, their relative position) is used for mapping parameters. For more information, see Naming Specifications for Service Store Procedures .

Database Case Sensitivity

If the SQL database is defined as case-sensitive and your schema name and procedure name are not all in uppercase, you can use literal component name and literal operation name to resolve the issue.

For example, a database is defined as case-sensitive, the database schema is named MySchema, and the stored procedure is named myproc1. When defining the operation name in the Uniface signature component, you can do one of the following:

  • Define the component literal name as: MySchema1, and define operation literal name as myproc1.
  • Put all stored procedures in a shared schema and use the dbowner option to specify this schema. For more information, see dbowner.

Entity Parameters

When defining an operation with an entity parameter in a Uniface signature, you can define an entity parameter only as OUT or INOUT, but when creating the corresponding stored procedure in SQL Server, you must define the parameter as an OUTPUT string. Also, the name of the Uniface entity parameter must be identical to the name of the table in the database. See the example below.

Data Conversions

The data types of operation parameters must closely match the stored procedure parameters. If necessary, explicit and implicit conversions are used to achieve an optimal mapping for all data types.

The supported parameter data type mappings for the MS SQL Server are shown in the following table. (The data type mappings shown in the table apply to both basic parameters and entity parameters.)

Uniface to Microsoft SQL Server Parameter–Data Type Mappings
  Uniface Parameter Data Types
MS SQL Data Types String Boolean Numeric Float (L)Date (L)Time (L)Datetime
char Y Y Y Y      
nchar Y Y Y Y      
varchar Y Y Y Y      
nvarchar Y Y Y Y      
decimal Y Y Y Y      
numeric Y Y Y Y      
tinyint Y Y Y Y      
smallint Y Y Y Y      
mediumint Y Y Y Y      
bigint Y Y Y Y      
int Y Y Y Y      
money Y Y Y Y      
smallmoney Y Y Y Y      
float Y Y Y Y      
real Y Y Y Y      
date Y       Y   Y
datetime Y       Y   Y
datetime2         Y   Y
smalldatetime         Y   Y
datetimeoffset         Y   Y
time Y         Y  
binary Y   Y        
varbinary Y   Y        

Note:  The date, time, datetime2, and datetimeoffset data types are only supported in Microsoft SQL Server 2008.

IN, OUT, and INOUT Parameters

All parameter data is passed to MS SQL Server as bound data. To achieve optimal mapping for all parameters, MS SQL Server parameter data type and operation parameter data type information is used to determine the SQL Server binding data types.

NULL Support

Empty input string values are mapped to MS SQL Server values as NULL values. There is one exception to this: empty Uniface boolean data types are always mapped to FALSE.

Exception Behavior

The exceptions shown in the following table are detected by the MSS connector:

Exception message numbers and meanings
$procerror Meaning
-21 Connection failure
-55 Input parameters failure
-56 Output parameter failure
-150 Connector or MS SQL failure (for example is stored procedure not found)
-166 Component signature is not stateless
-1406 Memory allocation failure

Service Stored Procedures

The following example shows a Service Stored Procedure with two basic parameters. It is executed with the following activate ProcScript statement: 

activate "WORKTODO".STR_TEST($in$, $out$)

The STR_TEST operation is defined in a Uniface signature for the stored procedure service component, and has the following parameters:

Parameter name Type Data type Input/Output
P1 Basic String Input
P2 Basic String Output

The MS SQL Server script is as follows: 

CREATE  procedure [WORKTODO].[STR_TEST] 
@in char(40), 
@out char(8000) OUTPUT 
as 
begin 
set @out = @in 
end

Service Stored Procedure with One Entity Parameter

The second example shows a Service Stored Procedure with one entity parameter. It is executed with the following activate ProcScript statement: 

activate "WORKTODO".ENT_TEST("mytable.mymodel")

The ENT_TEST operation has the single output entity parameter shown in the table:

Parameter Name Type Data Type Input/Output
MYTABLE Entity — Output
MYTABLE Entity Fields
Field Name Field Type Data Type
STR S VC40
NUM N C20

The MS SQL Server script is as follows: 

create  procedure [WORKTODO].[ENT_TEST]
@mytable char(40) OUTPUT 
as
begin 
   select * from mytable; 
end

Related Topics