| Author |
Message
|
| bhupa_sri |
 Posted: Thu Sep 22, 2005 7:33 am Post subject: Calling a StoredProc in ESQL |
 |
|
Apprentice
Joined: 30 Dec 2004
Posts: 30
Location: India
|
Hi all,
Is it possible to call a storedprocedure (written in SQL) in ESQL code.
The follwing way i coded ESQL:
CREATE COMPUTE MODULE CallStoreProc_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
CALL CopyMessageHeaders();
DECLARE sno INT;
SET sno = 3;
CALL Test(sno)EXTERNAL SCHEMA 'schema';
-- CALL CopyEntireMessage();
RETURN TRUE;
END;
CREATE PROCEDURE CopyMessageHeaders() BEGIN
DECLARE I INTEGER 1;
DECLARE J INTEGER CARDINALITY(InputRoot.*[]);
WHILE I < J DO
SET OutputRoot.*[I] = InputRoot.*[I];
SET I = I + 1;
END WHILE;
END;
CREATE PROCEDURE Test(IN parm1 INT) EXTERNAL NAME student;
END MODULE;
i am using the following Environment:
windows 2000 Professional
SQL 2000 SP3. Driver 3.60
WBIMB 5.0 with fixpack 4.
Thanks in Advance.. |
|
| Back to top |
|
 |
| mgk |
| Posted: Thu Sep 22, 2005 8:16 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Stored Procedures on SQL Server invoked using CALL are only supported on (I think) fix pack six and above on V5. And when you are on fix pack six you do not need to use the EXTERNAL SCHEMA clause to call them unless you are using a '%' as the schema name on the EXTERNAL NAME clause.
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| bhupa_sri |
| Posted: Thu Sep 22, 2005 8:30 am Post subject: |
|
|
Apprentice
Joined: 30 Dec 2004
Posts: 30
Location: India
|
Stored procedures in SQLServer are not supported.
It is given in WBIMB Help, If any chance please let me know other than fixpack6 |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Sep 22, 2005 9:17 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
That is what I tried to say. SQL Server stored procedures are only supported from V5 fixpack 6 onwards.
So if you move to fix pack 6 then they will work.
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Sep 22, 2005 9:26 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
If you do not want to move to fix pack six then you can use PASSTHRU to call a stored procedure in SQL Server, but that will only allow IN parameters.
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| bhupa_sri |
| Posted: Thu Sep 22, 2005 9:44 am Post subject: |
|
|
Apprentice
Joined: 30 Dec 2004
Posts: 30
Location: India
|
| Thanks for your valuable suggestion.. |
|
| Back to top |
|
|
|
|
