Using Stored Procedures connecting to SQL Server and JDBC in Reciever Side?

[Former Member]

Hi,

Iam very new to this Stored Procedures i even dont know how to use this in XI system.

My scenario i want to send data to SQL server and JDBC adapter in the Reciever side.

Please help me on this.

Thanks in advance,

Ravichandkone

[dharamveer_gaur2 · Accepted solution]

You Receiver data type struture look like where R_EMP is root node and stored procedure name same as create in Oracle server

R_EMP

-action-attribute(in mapping assign constant field "EXECUTE")

-name -Element

--isInput-attribute(assign "TRUE" during mapping)

--type-attribute(This is type of Database field .assign during mapping ex For Varchar2 or char use Varchar in constant field )

[dharamveer_gaur2]

You have to create this stored procedure on Oracle server and call this from XI.

how to call from XI?

You have to create Receiver Data type structue according to mentioned 1671 blog, in data type stuctrue root element name is Stored procedure name or optionaly you can assign in table field using constant.

[dharamveer_gaur2]

Hi

Create insert Stored procedure

For example :

Create R_EMP(name varchar2, employee_id NUMBER, age varchar2, sal number)

AS

BEGIN

INSERT INTO employee

VALUES (R_EMP.NAME, R_EMP.employee_id, R_EMP.AGE, R_EMP.SAL );

commit;

END;

Here R_EMP is stored procedure Name.

name,employee_id,age,sal are table column name.

Now create Data type structure according to Siva blog 1671.

[Former Member]

Where we have to insert this stored procedure in XI system

[santhosh_kumarv]

>>Where we have to insert this stored procedure in XI system

No. As I replied you earlier it needs to be written in the target system.

From XI only a call to this SP is made and the records are passed to it.

The execution happens in the SQL server and not in the XI system.

This [tutorial |http://www.coderecipes.net/sql-stored-procedure-tutorial.aspx]may help you to better understand the SP's.

Thanks

SaNv...

[Former Member]

Hi,

here is a blog about stored procedures:

Regards

Patrick

[santhosh_kumarv]

Hi,

This blog will help you in understanding the stored procedure By Sriram Vasudevan

By Sameer Shadab

Thanks

SaNv...

[Former Member]

Hi Santosh,

Your answer is very helpful..

But i want to send the data to SQL database using Stored Procedures..

Plz help on this.

Regards,

Ravi

[santhosh_kumarv]

>>But i want to send the data to SQL database using Stored Procedures..

It is explainded in the blog JDBC Stored Procedures. You need to create and save the SP in the SQL database. In the design you need to create the data type for the SP with the respective input parameters. In the configuration you need to specify the SQL server details in the Connection parameter of the receiver JDBC adapter.

Thanks

SaNv...