Siebel Tools >  How to call Stored Procedure from eScript

How to call Stored Procedure from eScript

This comes directly from Oracle Support Web

CREATE PROCEDURE dbo.GetNewPolisnumber
AS
SET NOCOUNT ON
DECLARE @Pos1 CHAR(1),
SET @Verschil= 10
WHILE @Verschil = 10
BEGIN
insert into IP_Polis_Generator values (1)
select @Ident = REPLACE((STR(@@IDENTITY)),' ','0')
select @Pos1= SUBSTRING( STR(@Ident) , 1, 1),
@Pos2 = SUBSTRING( STR(@Ident) , 2, 1),
@Pos3 = SUBSTRING( STR(@Ident) , 3, 1),
@Pos4 = SUBSTRING( STR(@Ident) , 4, 1),
@Pos5 = SUBSTRING( STR(@Ident) , 5, 1),
@Pos6 = SUBSTRING( STR(@Ident) , 6, 1),
@Pos7 = SUBSTRING( STR(@Ident) , 7, 1),
@Pos8 = SUBSTRING( STR(@Ident) , 8, 1),
@Pos9 = SUBSTRING( STR(@Ident) , 9, 1),
@Pos10 = SUBSTRING( STR(@Ident) , 10, 1)
--
-- The interpolis method of calculating
--
SET @Getal = ( @Pos4 * 9 ) + ( @Pos5 * 10 ) + ( @Pos6 * 5 )+ ( @Pos7 * 8 ) + ( @Pos8 * 4 ) + ( @Pos9 * 2 ) + ( @Pos10 * 1 )
SET @Verschil = @Getal / 11
SET @Verschil = @Getal - ( @Verschil * 11)
DELETE IP_Polis_Generator WHERE ID = @Ident
END
SELECT REPLACE(STR((@Ident * 10) + @Verschil),' ','0')
GO

The procedure above gives us a unique number back. This unique number we would like to store in a Siebel Table. How can we launch the stored procedure and capture the returned value?

SOLUTION
var sProvider = "sqloledb";

var sDSource = "SIEBEL_D04\\SIEBEL_CONFIG";
var sCatalog = "Siebel_Int";
var sSSPI = "SSPI";
var sQuery = "exec GetNewPolisnumber";
var sPolisnummer;
var oConnect;

sPolisnummer = Inputs.GetProperty("");
oConnect = COMCreateObject("ADODB.Connection");
oConnect.Open("Provider="+sProvider+";Data Source="+sDSource+";Initial Catalog="+sCatalog+";Integrated Security="+sSSPI);

var oRecords new Object;

oRecords = oConnect.Execute(sQuery);
sPolisnummer = oRecords.Fields.Item(0).Value;
oRecords = null;
oConnect.Close();
oConnect = null;
return (ContinueOperation);

Question:

I would like to that Data Source and Initial Catalog is not hardcoded in the eScript as here above. I mean I do not like hardcode SIEBEL_D04\\SIEBEL_CONFIG as DB Server name and Siebel_Int as name of database. Is there a possibility of using System Preferences?

Solution:

var sProvider = "sqloledb";
var sDSource = "SIEBEL_D04\\SIEBEL_CONFIG";
var sCatalog = "Siebel_Int";
var sSSPI = "SSPI";
var sQuery = "exec GetNewPolisnumber";
var sPolisnummer;
var oConnect;

sPolisnummer = Inputs.GetProperty("");
oConnect = COMCreateObject("ADODB.Connection");
oConnect.Open("Provider="+sProvider+";Data Source="+sDSource+";Initial Catalog="+sCatalog+";Integrated Security="+sSSPI);

var oRecords new Object;

oRecords = oConnect.Execute(sQuery);
sPolisnummer = oRecords.Fields.Item(0).Value;
oRecords = null;
oConnect.Close();
oConnect = null;
return (ContinueOperation);