SQL Module in details

It is quite OK to put user and pass in connection string. For SQL Module the connection string should be exactly the same as in Servers tab. Before generating WSDL you should call SQL probably with some given parameter (that is replace ? or :1 with some real values). When you see that query works do the right job.



For my example I have created nasty SQL function returning clob with XML inside.



As you can see the result from WebService doesn't look like real business function.
Let's try to fix it. First, create XSD schema for the output coming from DB.



Looking at the WebService WSDL we can see address for SQL-Engine handling our DB function.



To fix beauty (or the lack of) of our WebService BPEL Module will be needed.



From the left there is Partner Link from more business friendly WSDL (that we created on the side), some magic inside, and Partner Link for SQL-Engine WSDL.

First obvious action - pass input of left Partner Link to SQL-Engine and Invoke it.



That was easy, but what's up with mangled XML string? We need to get some real XML nodes from it. OK, create variable of type covered by schema generated from db output.



Do unmarshall on XML string (output of operation might require manual edition of BPEL's source: from and to parameters - if drag-and-drop doesn't work; probably NetBeans sees foreign namespace 'http://sys.one.pl/db1' of XSD schema and refuses to use it).



Now we have XML tree and can take some data from it and put into 'beatifull' WS result.



Let's drop in BPEL module into CompositeApp and make connections. Here it is:



CompositeApp with 2 WebServices: one beautifull and one ugly.

We can see business accepted output in test of 2nd WS.





To sum up:

• SQL module for wrapping SQL command


• EnterpriseApp for deploying JDBC connections


• WebApp for nothing (EnterpriseApp cannot be empty and needs to have WebApp at least)


• BPEL module for transforming ugly WS (SQL-Engine based) into beautifull WS


• CompositeApp for hosting and linking modules