REQUEST A DEMO

Calling SOAP web services from cbbatch on UNIX using an Oracle procedure

Yes, you read that right – calling SOAP web services from cbbatch on UNIX using an Oracle stored procedure. Even reading that kind of makes my head hurt. But, when you’re between a rock and a hard place, sometimes you gotta do what you gotta do.

I’ve been working with a customer recently who has created some web services using .NET and our Dovetail SDK. He needs to able to call these web services synchronously from ClearBasic code, both from the Clarify Classic Client (running on Windows) and from cbbatch (running on UNIX).

Calling web services from the Clarify Classic Client

Calling web services from the Clarify Classic Client is pretty straightforward. Since we’re on Windows, we can use COM and ActiveX objects to do so.

A few examples of this:

Calling web services from CBBATCH on UNIX

The trouble comes in when we want to make these same calls from cbbatch on UNIX. We can’t use any of those handy COM or ActiveX or Microsoft toolkits. If the result of the web service didn’t need to be handled synchronously, we could simply shell out and use something like curl to make an HTTP request. But, this task isn’t quite that easy.

We kicked around a few ideas, including using cb2java to call a Java function.

Where the customer ultimately ended up was making a SOAP request from an Oracle stored procedure. This stored proc could be easily called using the SQLDB object in ClearBasic, and the same code could be used from within the Clarify Client and from within cbbatch (even on UNIX).

Once this was all going, we wanted to share the details – hopefully making it easier should someone else find themselves in a similar situation.

So let’s see how this all works. Giddy Up.

SOAP API

Finding a SOAP API written in PL/SQL was the first hurdle. Tim Hall over on http://www.oracle-base.com has a huge collection of SQL scripts, including a set of SOAP related functions for consuming web services. Well that was easy. Thanks Tim!

 

Stored Procedure

Next up, an Oracle stored procedure that uses the SOAP package and makes the web service call.

As an example, we’ll use a simple, publically available SOAP web service. Specifically, it’s a web service that tells us on what day Mother’s Day will fall on for a particular year. It takes one input parameter – a year, and returns a date in an ISO format.

CREATE OR REPLACE PROCEDURE SA.get_mothers_day (
   p_year                   number,
   p_mothersday  OUT   VARCHAR2
)
IS 
  l_request   soap_api.t_request;
  l_response  soap_api.t_response;
  l_url          VARCHAR2(32767);
  l_namespace    VARCHAR2(32767);
  l_method       VARCHAR2(32767);
  l_soap_action  VARCHAR2(32767);
  l_result_name  VARCHAR2(32767);
BEGIN
  l_url         := ‘
http://www.27seconds.com/Holidays/US/Dates/USHolidayDates.asmx’;
  l_namespace   := ‘xmlns="http://www.27seconds.com/Holidays/US/Dates/"’;
  l_method      := ‘GetMothersDay’;
  l_soap_action := ‘
http://www.27seconds.com/Holidays/US/Dates/GetMothersDay’;
  l_result_name := ‘GetMothersDayResult’;

  l_request := soap_api.new_request(p_method       => l_method,
                                    p_namespace    => l_namespace);
  soap_api.add_parameter(p_request => l_request,
                         p_name    => ‘year’,
                         p_type    => ‘int’,
                         p_value   =>  p_year);
  l_response := soap_api.invoke(p_request => l_request,
                                p_url     => l_url,
                                p_action  => l_soap_action);
  p_mothersday := soap_api.get_return_value(p_response  => l_response,
                                        p_name      => l_result_name,
                                        p_namespace => l_namespace);                                         
END get_mothers_day;
.
/
SHOW ERRORS PROCEDURE get_mothers_day

–SQL> var t_out varchar2(100);
–SQL> exec get_mothers_day(‘2011’,:t_out);
–SQL> print t_out;

ClearBasic Code

Now we can use a SQLDB object to call the stored procedure.

We’ll need a user-defined type for our output parameters:

Type get_mothers_day_output_struct&n
bsp;    
  mothersDay as String   
End Type

Then a function that calls the web service using the SQLDB ExecuteProc method:

Function GetMothersDay(whichYear as Integer) As String
   Dim DB                    As New SQLDB
   Dim InputParameters       As New List
   Dim OutputParameters      As get_mothers_day_output_struct
   InputParameters.ItemType = "variant"
   InputParameters.AppendItem whichYear
   DB.ExecuteProc "get_mothers_day", InputParameters, OutputParameters
   mothersDay = Trim(OutputParameters.mothersDay)

   GetMothersDay = mothersDay
End function

And a simple subroutine to call that function:

Sub test1()
    Dim mothersDay as String
    Dim whichYear as Integer

    whichYear = 2012
    mothersDay = GetMothersDay(2012)

    Debug.Print "Mother’s Day " & whichYear & " is " & cstr(mothersDay)
End Sub

Then we can run our cbbatch routine, and see our results:

cbbatch_output

Success! We’ve just used cbbatch to call a stored procedure which in turn makes a web service call.

You’ll notice that I ran this on Windows, but because we’re not using any Windows specific objects, the exact same code will execute on UNIX.

In addition, we can use the exact same code and function calls from ClearBasic on the client-side as well.

Code

All of this code is available at: http://github.com/gsherman/oracle_soap_web_service

Summary

This may not be the simplest or most elegant solution for accomplishing the task at hand, but it does seem to work. And sometimes you just have to get shit done and move on.

Hopefully you’ll find this useful should you find yourself in a similar scenario.

 

 

Thanks

I wanted to give a big shout out and thanks to xxxxx xxxxxxx at xxxxxxx, who is the customer who did all the hard work on this and made it happen for their implementation. Unfortunately, he works for one of those organizations that doesn’t allow its employees to share this kind of information publically. But you know who you are – and I appreciate you letting me share it for you.

Rock on.

1 Comment

  • Anonymous

    Many Thanks Mr. Gary, realy it was very usefull.

  • ADD COMMENT

    Your email address will not be published.