Amazon Contextual Product Ads

Sunday, 13 May 2012

Accessing remote Oracle DB from Siebel server script and executing insert with bind variables

 

It is very common to be asked to develop an interface between Siebel and other systems. It could be that other system has some kind of API or maybe it doesn’t . In my case , remote system doesn’t has any API, but you can access its database. So how can you do it from Siebel server side script?

First of all lets create  some dummy table that will be used as insert target

create table test_insert (data varchar2)



 

Now define in some Business Service new server script.

Explanation of the code can be found in the code comments


function Service_PreInvokeMethod (MethodName, Inputs, Outputs) {
if (MethodName=="Connect")
{
//obtain connection object
var oConnection = COMCreateObject("ADODB.Connection");
//connection objects
var cmd;
var myparam;
var res;
var adVarChar =200;
var adParamInput =1;
var adCmdText = 1;

try
{
//here I use TNS style connection string to connect to remote Oracle database
oConnection.ConnectionString
= "Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=130.39.120.58)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));
uid
=system;pwd=manager;";
oConnection.
Open();
//create command object
cmd
= COMCreateObject("ADODB.Command");
//assign connection to command
cmd.ActiveConnection
= oConnection;
//this is SQL statement that I am about to execute
var insertStmt = "INSERT INTO TEST_INSERT (DATA) VALUES (?)";

cmd.CommandText
= insertStmt;
// command type is - text
cmd.CommandType
= adCmdText;
//create and append new parameter
// 1 = first parameter
// adVarChar = database column type
// adParamInput = parameter direction (input)
// 5 - the lenght of the parameter value
// TEST2 - parameter value
cmd.Parameters.Append(cmd.CreateParameter(
1, adVarChar, adParamInput,5,"TEST2"));

// Now, inserting into the data source...
cmd.Prepared
= true;
//execute update - no commit is needed
res
= cmd.Execute();

Outputs.SetProperty("DONE","DONE");


}
catch(e)
{
Outputs.SetProperty("DONE","
NOT OK "+e.toString());
}
finally
{
//release variables
adCmdText
=null;
adParamInput
= null;
adVarChar
= null;
myparam
= null;
cmd
= null;
oConnection
= null;
}
return (CancelOperation);
}
return (ContinueOperation);
}

No comments:

Post a Comment