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 IntegerwhichYear = 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:
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.
