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);
}