Mike's place: 2012

Monday 6 August 2012

Read response from invoking Siebel CRM “EAI HTTP Transport” business service

I wrote a post some time ago about how to call URL from Siebel CRM. This post will deal with processing the response from remote page.

So here you go, follow the instructions from my previous post and submit data to remote page. Many times you expect not only to send data, but also receive a response back.

To remind you , in order to send data you have to invoke

oService.InvokeMethod("SendReceive", oInputs, oOutputs);

Common sense tells you that response is probably located in object.

But if you try to run

oOutputs.GetValue();

You will get the following error message

PropertySet GetValue call failed. PropertySet Value is marked as binary data, starting with….

And this is because Siebel can process data only in UTF-16 encoding. If it is not, Siebel “thinks” that it is a binary data.

Bellow is very simple code to convert the HTTP output to a proper format

var oTransService = TheApplication().GetService("Transcode Service");
var oTransOutputs = TheApplication().NewPropertySet();
oOutputs.SetProperty("ConversionMode", "EncodingToString");
oOutputs.SetProperty("SourceEncoding", "CP1252");
oTransService.InvokeMethod("Convert", oOutputs, oTransOutputs);
var sResponse = oTransOutputs.GetValue();

After you execute it. You will fine HTTP response string in sResponse variable.

P.S. You can find more information in 536101.1 metalink note

Tuesday 31 July 2012

Invoking web service from Siebel with EAI HTTP Transport Business Service

Some recent task I did was to invoke a web-service from Siebel to send SMS message.

I have to admit that I never did such a thing before. So I started to read Siebel “bookshelf” documentation (you can throw this crap to the garbage . It is worthless), search Google. Found plenty bad written tutorials about invoking workflow process that can invoke web service or call http URL.

Half of the staff I didn’t understand, the other half didn’t like, but I understood one thing: at the end Siebel works with a Business Service (BC) called “EAI HTTP Transport “.

As I mentioned I am not to good with clicking on buttons in Siebel Tools, but pretty good with writing code. So if I know that Siebel uses a BC, why not to use it directly?

The result is this simple function that defines XML message and uses POST method to submit data to remote web-service


function SendSms(Inputs, Outputs)
{
     //get Business Service
     var bs = TheApplication().GetService("EAI HTTP Transport");
     var inp = TheApplication().NewPropertySet();
     var outputs1 = TheApplication().NewPropertySet(); 
     // it is web-service, so use POST method
    inp.SetProperty("HTTPRequestMethod","POST");
     //Content type as in all HTTP request
    inp.SetProperty("HTTPContentType", "text/xml; charset=UTF-8"); 
    //web-service end point
    inp.SetProperty("HTTPRequestURLTemplate","https://**********/imsc/interfaces/largeaccount/la3.sms");
    //define the data to be send
    var reqVal = '<?xml version="1.0" encoding="utf-8" ?> '+
    '<request> '+
    '  <head> '+
    '    <auth> '+
    '      <account>CompanyName</account> '+
    '     <user>userName</user> '+
    '    <pass>Pass</pass> '+
    '</auth> '+
    '      <action>sendsms</action> '+
    '  </head> '+
    ' <body> '+
    '   <addr> '+
    '     <from>039535640</from> '+
    '    <to> '+
    '      <cli>97254545450</cli> '+
    '  </to> '+
    '</addr> '+
    '<data> '+
    '  <msgtype>text</msgtype> '+
    '   <text>This is SMS message text</text> '+
    ' </data> '+
    '  <billing> '+
    '      <port>0</port> '+
    '    </billing> '+
    '  </body> '+
    '</request>';
    //set the data
    inp.SetProperty("HTTPRequestBodyTemplate",reqVal);
    //invoke. The result can be found in "Outputs" 
    bs.InvokeMethod("SendReceive",inp,Outputs);
}

Wednesday 13 June 2012

Sending SOAP message as plain XML with java

There are many Java implementations for working with web services, but all of them request having some heavy framework (e.g. AXIS). You need to generate proxy classes figure how to use them and eventually you finish with 20MB program that maybe does something very simple like calling a web - service that converts money from US dollar to Euro.

So I will show how can you use only Java core classes without any additional libraries and send SOAP message as plain XML text.

//create URL object
URL url = new URL( "http://someurl.com/soap" );  
rc = (HttpURLConnection)url.openConnection();  
//you need to check if server expects POST or GET
//but to be honest I never saw web-service expecting GET reqiest

 rc.setRequestMethod("POST");  
 rc.setDoOutput( true );  
 rc.setDoInput( true );   
 //it is very important to specify the content type. Web-service will reject the request if it is 
 //not XML
 rc.setRequestProperty( "Content-Type", "text/xml; charset=utf-8" );  
//and here comes some dummy SOAP message
String reqStr = "<soapenv:Envelope xmlns:soapenv=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:ord=\"http://someurl.com/OfflineBatchSync/Order\">"+
         "<soapenv:Header/>"+
         "<soapenv:Body>"+
         "   <ord:CreateOrderHeader_Input>"+
         "      <ord:CustomerPO></ord:CustomerPO>"+
         "      <ord:SubOrderType>VMI</ord:SubOrderType>"+
         "      <ord:PreSaleFlg></ord:PreSaleFlg>"+
         "      <ord:DistributorId></ord:DistributorId>"+
         "      <ord:AccountId>1-6T4N</ord:AccountId>"+
         "      <ord:OrderStatus></ord:OrderStatus>"+
         "      <ord:RequestedDate></ord:RequestedDate>"+
         "      <ord:ShipInstructions></ord:ShipInstructions>"+
         "      <ord:PriceListId></ord:PriceListId>"+
         "   </ord:CreateOrderHeader_Input>"+
         "</soapenv:Body>"+
         "</soapenv:Envelope>";
//several more definitions to request
int len = reqStr.length();  
rc.setRequestProperty( "SOAPAction:", "\"document/http://someurl.com/OfflineBatchSync/Order:CreateOrderHeader\"" );  
rc.setRequestProperty( "Content-Length", Integer.toString( len ) );
rc.setRequestProperty( "Connection:", "Keep-Alive" );
rc.connect();  
//write XML to the server
OutputStreamWriter outStr = new OutputStreamWriter( rc.getOutputStream() );   
outStr.write( reqStr, 0, len );  
outStr.flush();

/* Here is the important part, if something goes wrong and excetion will
* be thrown and you will have some meaningless exception saying blah.. blahh HTTP 500
* which actually doesn't tell you a lot about what happen.
* However most web-services provide as a response some error page that displays what 
* was wrong. It whould be nice to see this page instead of stupid HTTP 500.
* It is not difficult . All you need is actually read not the response stream , but the error stream
*/

try
{
  read = new InputStreamReader( rc.getInputStream() );  
}
catch(Exception exception)
{ 
  //if something wrong instead of the output, read the error
  read = new InputStreamReader( rc.getErrorStream() );  
}

//read server response
StringBuilder sb = new StringBuilder();     
int ch = read.read();  
while( ch != -1 ){  
  sb.append((char)ch);  
  ch = read.read();  
}  
String responseTr = sb.toString();

Basically this is all you need . After execution of the code above responseTr variable will hold the response SOAP message in case of success or response error (In case of error it will be HTML script so you may want to display it in browser or to strip HTML tags)

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

Sunday 15 April 2012

Dynamically Execute SQL statement stored in CLOB field with bind variables

I saw many examples in the past when SQL statement was actually stored in CLOB field and programmer had to execute the statement dynamically.

All Oracle PL/SQL developers are familiar with “execute immediate” statement, but unfortunately it cannot use CLOB field as an input.

But it is not the only way to execute the code dynamically. Oracle also provides DBMS_SQL package that can execute the code dynamically and it can get the statement as an array data type. We will use it

Here how you can do it.

Let assume that your SQL will be stored in the following variable

l_stmt CLOB;

Now we need to split the CLOB to chunks that will became a member of array I mentioned previously

v_upperbound NUMBER;

This is array definition

v_sql DBMS_SQL.VARCHAR2S;

Calculate array length

v_upperbound := CEIL(DBMS_LOB.GETLENGTH(l_stmt)/256);

Fill array members:

FOR i IN 1..v_upperbound
LOOP
         v_sql(i) := DBMS_LOB.SUBSTR(l_stmt
                                  ,256 -- amount
                                  ,((i-1)*256)+1 -- offset
                                  );
END LOOP;

Create new cursor:

v_cur INTEGER;

v_cur := DBMS_SQL.OPEN_CURSOR;

Prepare your statement for execution , but parsing array members

DBMS_SQL.PARSE(v_cur, v_sql, 1, v_upperbound, FALSE, DBMS_SQL.NATIVE);

You can also use bind variables at this point

dbms_sql.bind_variable( v_cur, ':USER_NAME', ‘SYSADMIN’);

All what is left is to execute the statement

v_ret NUMBER;

v_ret := DBMS_SQL.EXECUTE(v_cur);

Bellow if full example that uses approach described above

declare

  l_stmt       CLOB;
  v_upperbound NUMBER;
  v_cur        INTEGER;
  v_sql        DBMS_SQL.VARCHAR2S;
  v_ret        NUMBER;

begin
   -- get sql statement into CLOB
  select statement
    into l_stmt
    from XX_NEW_STATEMENTS_V --some view that holds CLOB 
                                         --with SQL
   where statement_type = 'SETUP'
     and seq_num = 10;

  v_upperbound := CEIL(DBMS_LOB.GETLENGTH(l_stmt) / 256);

  FOR i IN 1 .. v_upperbound LOOP
    v_sql(i) := DBMS_LOB.SUBSTR(l_stmt, -- clob statement
                                256,     -- amount
                                ((i - 1) * 256) + 1 
                                );
  END LOOP;
        
  v_cur := DBMS_SQL.OPEN_CURSOR;
        -- parse sql statement
  DBMS_SQL.PARSE(v_cur, v_sql, 1, v_upperbound, FALSE, DBMS_SQL.NATIVE);
        -- use bind variable if you need
  dbms_sql.bind_variable(v_cur, ':USER_ID', 235341);
  -- execute
  v_ret := DBMS_SQL.EXECUTE(v_cur);

end;