jdbc stored procedure

[Former Member]

hello

I need to insert data from a File into a database using a stored procedure.

statement to insert in table is:

Exec WRITE_table1('r13','10095','m18','D','','c','f','p','Y',to_date('20-JUL-2008','dd-mon-yyyy');

how to pass this from PI in the map ?

Appreciate your response.

Thanks

[former_member206760 · Accepted solution]

receiver jdbc structure for stored procedures is as follows

<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.

[Former Member · Accepted solution]

how do we put the delimiter?

Thanks

[rodrigoalejandro_pertierr · Accepted solution]

what you mean with how do we put the delimiter?

[Former Member · Accepted solution]

in the statement to be triggered:

Exec WRITE_table1('r13','10095','m18','D','','c','f','p','Y',to_date('20-JUL-2008','dd-mon-yyyy');

"," is the delimiter b/w the fields. how to pass this ?

Thnks