Stored procedures in XI

[Former Member]

Hi everybody

I need to know how the stored procedures works in PI with the database. When I sending a query to a structure, for example, the return or the answer requires a structure of fields?, or how it works.

Thanks

Paul.

[udo_martens]

Hi Paul,

from [SAP help: Document Formats for the Receiver JDBC Adapter|http://help.sap.com/saphelp_nw2004s/helpdata/en/2e/96fd3f2d14e869e10000000a155106/frameset.htm]:

<StatementName5>

<storedProcedureName action=u201D EXECUTEu201D>

    <table>realStoredProcedureeName</table>

<param1 [isInput=u201Dtrueu201D] [isOutput=true] type=SQLDatatype>val1</param1>

</storedProcedureName >  

  </StatementName5>

● action=EXECUTE

Statements with this action result in a stored procedure being executed. The name of the element is interpreted as the name of the stored procedure in the database. If you use the optional <table> element, the value specified here is used as the stored procedure name. This enables you, for example, to define stored procedure names containing non-XML-compatible characters or characters that prevent them from being used in interface definitions in the Integration Builder/PCK. If specified, <table> must be the first element in the block within <dbTableName>.

The elements within the stored procedure are interpreted as parameters. They can optionally have the attribute isInput=u201C1u201C (input parameter) or isOutput=u201C1u201C (output parameter) or both (INOUT parameter). If both attributes are missing, the element is interpreted as an input parameter. The parameter names must be identical to those of the stored procedure definition.

The attribute type=<SQL-Datatype> , which describes the valid SQL data type, is mandatory for all parameter types (IN, OUT, INOUT).

The following SQL data types are supported:

INTEGER, BIT, TINYINT, SMALLINT, BIGINT, FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL, CHAR, VARCHAR, STRING, LONGVARCHAR, DATE, TIME, TIMESTAMP, BINARY, VARBINARY, LONGVARBINARY, BLOB (input and output),CLOB (input and output), CURSOR (output; only in conjunction with the Oracle JDBC driver).

The binary data for BLOB is hexadecimal encoded.

All return values are returned in an XML structure. The results within the stored procedure are returned either as a table or as the element <update_count>. This depends on the SQL statements executed within the stored procedure. The return parameters of a stored procedure are attached in a separate structure

The response document contains the result of the action in XML format as follows:

u201C

<row>

<column1>value11</column1>

<column2>value12</column2>

...

</row>

...

<row>

<column1>valueN1</column1>

<column2>valueN2</column2>

...

</row>

u201C

Regards,

Udo