on 02-24-2010 2:26 PM
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
answered
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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.
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
User | Count |
---|---|
85 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.