Execute SQL's from Java ( WS-AppServer) layer

Mahesh P

Hi,

I have created a Java model and created a webservices out of those methods. Now in one of the method, I want to run a SQL to get the data from custom tables to generate my output. I can create Get webservices from those custom tables and execute the webservice but in my case, there could be huge amount of data sometimes so thinking if there is a way to call a SQL directly from the Java layer.

Appreciate the thoughts.

Mahesh

Karen Weir

Interesting scenario @Mahesh P , have you been able to resolve this on your own or is this still open for discussion?

Mahesh P

Yes, we ended up to generate webservices directly on DB Tables using custom SQL queries joining multiple tables and we noticed that the response is in a different format when you try to use the same webservice in BPM. We opened a ticket with support. The response structure is same if its a simple query. So not sure if we need to do anything special for complex queries. But this is very powerful way to retrieve data from non-appworks tables

jpluimers

@Mahesh P , using WsAppServer is indeed a very powerful way to retrieve data from a database and expose that via a web-service. What differences do you see in the response, can you share examples?

Mahesh P

webservice tool request,response.txt

Here is the attached request & response from the webservice tool and also from BPM.

When we try to get the response object in BPM in Expression Editor, we dont see the same structure i.e. <tuple><old><TABLE>

Not sure if you have access to OT Support ticket system but here is the OT #4651600.

I can certainly create a project and upload here if you need a full working project that demonstrate the issue if its easier that way.

Thank you

jpluimers

I've checked the support ticket. It seems that the XSD for the web-service is different from the response structure.

Could you add a small project with example and generated XSD etc. to the support ticket?

Mahesh Pinnamaneni

Hi Johan,

I have attached the sample project and the external db (SQL Server 2014) to the ticket. Also attaching it here just in case if any one wants to refer...Btw, the AWP version is 20.2

Order Mgmt.zip

AdventureWorksLT2014-1.zip

BPM Msg map.PNG