Informix Service Stored Procedures

Uniface can execute Informix stored procedures by activating a stored procedure service, define in the Signature Editor.

Stored Procedure Names

The stored procedure name used for execution by Uniface is derived from the component name and the operation name as shown in the table:

Component Name and Component Operation Name Specification

Literal component name

Literal operation name

Stored procedure name

Not specified

Not specified

4GLOperationName

Not specified

Specified

LiteralOperationName

Specified

Not specified

LiteralComponentName_4GLOperationName

Specified

Specified

LiteralComponentName_LiteralOperationName

Note:   Object names have a maximum size of 18 bytes in Informix.

Parameters

Parameter names are not used during the execution of stored procedures. However, their ordering (that is, their relative position) is used for mapping purposes.

Informix only allows one single output entity parameter. You cannot have both basic and entity parameters for output. Input/output parameters are treated as two separate parameters.

Data Type Conversions

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

The supported parameter data type mappings for Informix are shown in the table:

Uniface to Informix parameter data type mappings

Informix data type

String

Boolean

Numeric

Float

(L)Date

(L)Time

(L)Datetime

char

Y

Y

Y

Y

1

2

3

varchar

Y

Y

Y

Y

     

nchar

Y

Y

Y

Y

     

nvarchar

Y

Y

Y

Y

     

smallint

Y

 

Y

Y

     

int

Y

 

Y

Y

     

smallfloat

Y4

  

Y4

Y4

      

float

Y

 

Y

Y

     

decimal

Y

 

Y

Y

     

money

Y

 

Y

Y

     

serial

             

date

5

      

Y

 

Y

datetime

6

      

Y

Y

Y

interval

             

text

             

bytes

             
  1. For input parameters, the format is not relevant. For output parameters, the format must be consistent with the Informix DBDATE or GL_DATE environment variables.
  2. For input parameters, the format is not relevant. For output parameters, the format HH:MM:SS[T[T]] must be used.
  3. For input parameters, the format is not relevant. For output parameters, the format YYYY-MM-DD HH:MM:SS[.T[T]] must be used.
  4. On certain operating systems, the output value of smallfloats can contain more least significant digits than expected.
  5. For input parameters, the format used must be consistent with the Informix DBDATE or GL_DATE environment variables. For output parameters, the format is not relevant.
  6. For input parameters, the format used must be consistent with the Informix DBTIME or GL_DATETIME environment variables and the specified Informix data type qualifier. For output parameters, the format is not relevant.

When the Informix binding data type does not map to the actual Informix data type, implicit conversion is performed for all Informix data types. For information on Informix implicit data conversion, see your Informix documentation.

Input and Output Parameters

All parameter data is passed to Informix as bound data.

As no data type information can be obtained from the input parameters of Informix stored parameters, Uniface operation signature information is used. For each Uniface parameter, an Informix variable is declared with a binding data type which matches the required data type as closely as possible. For output parameters, both the Informix parameter data types and the operator parameter data types are used to determine the optimal Informix binding data types.

Default Parameters

You can map Informix stored procedures with default parameter values to stored procedure components. However, to do this, the parameter list of the operation must exclude the default stored procedure parameters. Because stored procedure parameters are bound by position, default parameters must be placed at the end of the stored procedure argument list. However, if the default parameter is mapped to a Uniface parameter, the default parameter is always passed as a value.

Null Support

All empty input string values are mapped to Informix NULL values, except the Uniface Boolean data type which, when empty, is mapped to FALSE. This is standard Uniface functionality.

USYS$INF_PARAMS

The trim connector option must be set to retrieve trimmed char data from Service Stored Procedures.

Transaction Behavior

If the Informix database for Service Stored Procedures has transactions, the Informix connector ensures that the Service Stored Procedure is executed within a transaction. If necessary, a transaction is started for the Service Stored Procedure. However, the Informix connector does not end a transaction after execution of the Service Stored Procedure. This can be managed through the ProcScript commit and rollback statements.

Exception Handling

The exceptions detected by the INF connector are shown in the table:

Exception message numbers and meanings
$procerror Possible exceptions
-21 Connection failure
-150 No entity input parameter allowed

No occurrence parameter allowed

No mixture of entity and basic parameters allowed

No raw or IMAGE parameters allowed

Only one entity output parameter allowed

SQL buffer overflow

Specified data type mapping not supported

Data type conversion failed

Deallocation of resources failed

INFORMIX SQL failure

-166 Operation is not stateless
-1406 Memory allocation failure

Informix Service Stored Procedure with three basic parameters.

This example illustrate the use of Informix Service Stored Procedures. It shows a Service Stored Procedure with three basic parameters. It is executed with the following activate ProcScript statement: 

activate "EXAMPLE".example1($in$,$out1$,$out2$)

The operation has the parameters shown in the table:

Example1 operation parameters
Name Type Data Type Input/Output
u_inpar Basic String Input
u_outpar1 Basic Date Output
u_outpar2 Basic String Output

The Informix SQL script is as follows: 

create procedure example1 ( param1 char(20) )
returning date, char(20);
return today, param1;
end procedure;

Informix Service Stored Procedure with one entity parameter.

The Service Stored Procedure is executed with the following activate ProcScript statement: 

activate "EXAMPLE".example2("table_x.model")

The example2 operation has the parameters shown in the table:

Example operation parameters
Name Type Data Type Input/Output
u_outpar Entity - Output

The table_x entity has the fields shown in the following table:

Table_x entity structure
Name Type Data Type
pk N I4
fld S C20

The Informix SQL script is as follows: 

create procedure example2 ()
returning int, char(20);
define p_field1 int;
define p_field2 char(20);
foreach select pk, fld
into p_field1, p_field2,
from table_x
return p_field1, p_field2
with resume;
end foreach;
end procedure;

Related Topics