cancel
Showing results for 
Search instead for 
Did you mean: 

Stored Procedure Calls

Former Member
0 Kudos

Hi All,

Stored Procedure Calls

1.What exactly are the Stored Procedure Calls?

2.Where do we write them?Do we write them in JAVA?

3.Are they applicable to both sender as well as receiver JDBC Adapters?

Thanks in advance

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

answered

former_member206760
Active Contributor
0 Kudos

hi shweta the stored procedure are like small funtions written in the corresponding database language( SQL/Oracle ).

they return a resultset of records to XI/PI. They are avaiable both for the sender and receiver.

we write them when a lot of commands needs to be performed before a set of records are sent to PI.

the set of commands are written say between BEGIN .....END in the native database lang

Edited by: Tarang Shah on Feb 24, 2010 7:59 PM

Former Member
0 Kudos

Hi Tarang,

Thanks for the reply.

Can you please tell me do we need to write any code for Stored Procedure Calls as have referred a few posts on SDN?

If so, then where do we need to write it?

Thanks in avance.

former_member206760
Active Contributor
0 Kudos

yes you have to write a set of commands in the native data base language between say begin and end..

Very similar to function modules in ABAP.

e.g stored procedure in oracle

http://dtemplatelib.sourceforge.net/fmtStoredProcReadData.htm

jose_augastine3
Active Participant
0 Kudos

Hi Shweta,

You don't have to write any code for Stored Procedure calls. You just need to call the Stored Procedure in the adapter.

Regards,

J Augastine

former_member206760
Active Contributor
0 Kudos

As specified by augstin. stored procedure do have native database language commands....but in XI we just need to call that stored procedure

Former Member
0 Kudos

Hi Tarang and Augastine,

Thanks for the reply.

Can you please tell me where exactly do we need to write the Stored Procedure Call ?In the Adapter communication channel where SELECT is written?

Also, if there are more than one SELECTs then can we write them all in the Adapter communication channel ?

Please explain with the example.

Thanks in advance.

Former Member
0 Kudos

Hi Tarang,

Thanks for the reply.

As specified by augstin. stored procedure do have native database language commands....but in XI we just need to call that stored procedure

How to call the Stored Procedure Call?In Communication Channel?Please give me an example.

Thnaks in advance.

jose_augastine3
Active Participant
0 Kudos

Hi Shweta,

Hope this helps !!!

Regards,

J Augastine

former_member206760
Active Contributor
0 Kudos

Example for stored procedure

create procedure student_details

@Sno varchar(10)

as

select student.Name,student.Sno,student.Street,student.city from student

where student.Sno = @Sno

The above procedure will create in the Database. We need to write Execute statement in the QuerySql statement in Communication channel like

execute student_details 222

here Number 222 is passed as a parameter to the stored procedure and the result of the stored procedure is sent back to PI

PS---the syntax may not be the exact one.

Former Member
0 Kudos

to make it a bit more clear,

step 1: the creation and maintenance of the stored procedure is not done in XI, not ABAP, not Java. It has to be done with tools from the database vendor or common JDBC database tools. With such a tool, you edit and compile the stored proc into the database (e.g. Oracle). Each database has its own native SQL programming-language, e.g. PL/SQL for Oracle, T-SQL for MS SQL...

step 2: this stored proc is now available to XI (via the JDBC adapter, which connects to the database). Proceed lke described in this thread, call it in the QuerySQL statement of the JDBC communication channel

CSY