WSO2 ESB DBLookup broker requests multiple rows

Question

As stated in the DBLookup Mediator documentation, it returns only the first row of the query, other results, if any, are ignored.

I want to know if there is a “better way” to execute a query (SELECT * FROM X) that returns multiple records and then processes them. Now the days that we do are introducing axis2 services, but there is another way using the combination of intermediaries provided by wso2 esb to fulfill this requirement

Thanks in advance.

Santiago.

Answer 1

Yes DBlookup will not return multiple rows. You can use two alternatives.

1) Use the WSO2 Server data services to create a data service and call this service from the ESB using a call broker.

2) You can write a class mediator to request data from a database, and then create a payload and then send it through a sequence.

Answer 2

To avoid writing another service or creating a full-blown WSO2 Data Services Server to overcome the lack of a database broker, I expanded the existing mediator but did not bring my code back into the community due to time constraints. Here is the code for the updated org.apache.synapse.mediators.db.DBLookupMediator.

Basically, it converts the ResultSet to XML format and sets the result to the DB_SEARCH_RESULT property. It will probably also require polishing and testing under race conditions.

package org.apache.synapse.mediators.db; import org.apache.synapse.MessageContext; import org.apache.synapse.SynapseException; import org.apache.synapse.SynapseLog; import org.w3c.dom.Attr; import org.w3c.dom.Document; import org.w3c.dom.Element; import java.io.StringWriter; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Connection; import javax.xml.parsers.DocumentBuilder; import javax.xml.parsers.DocumentBuilderFactory; import javax.xml.parsers.ParserConfigurationException; import javax.xml.transform.OutputKeys; import javax.xml.transform.Transformer; import javax.xml.transform.TransformerFactory; import javax.xml.transform.dom.DOMSource; import javax.xml.transform.stream.StreamResult; /** * Simple database table lookup mediator. Designed only for read/lookup */ public class DBLookupMediator extends AbstractDBMediator { public static final String DB_SEARCH_RESULTS_PROPERTY_NAME = "DB_SEARCH_RESULT"; protected void processStatement(Statement stmnt, MessageContext msgCtx) { SynapseLog synLog = getLog(msgCtx); // execute the prepared statement, and extract the first result row and // set as message context properties, any results that have been specified Connection con = null; ResultSet rs = null; try { PreparedStatement ps = getPreparedStatement(stmnt, msgCtx); con = ps.getConnection(); rs = ps.executeQuery(); // convert RS to XML String rsXML = convertRSToXML(rs); // add result XML to the Message Context msgCtx.setProperty(DB_SEARCH_RESULTS_PROPERTY_NAME, rsXML); // rollback to the beginning of ResultSet to allow standard processing rs = ps.executeQuery(); if (rs.next()) { if (synLog.isTraceOrDebugEnabled()) { synLog.traceOrDebug( "Processing the first row returned : " + stmnt.getRawStatement()); } for (String propName : stmnt.getResultsMap().keySet()) { String columnStr = stmnt.getResultsMap().get(propName); Object obj; try { int colNum = Integer.parseInt(columnStr); obj = rs.getObject(colNum); } catch (NumberFormatException ignore) { obj = rs.getObject(columnStr); } if (obj != null) { if (synLog.isTraceOrDebugEnabled()) { synLog.traceOrDebug("Column : " + columnStr + " returned value : " + obj + " Setting this as the message property : " + propName); } msgCtx.setProperty(propName, obj.toString()); } else { if (synLog.isTraceOrDebugEnabled()) { synLog.traceOrDebugWarn("Column : " + columnStr + " returned null Skip setting message property : " + propName); } } } } else { if (synLog.isTraceOrDebugEnabled()) { synLog.traceOrDebug("Statement : " + stmnt.getRawStatement() + " returned 0 rows"); } } } catch (SQLException e) { handleException("Error executing statement : " + stmnt.getRawStatement() + " against DataSource : " + getDSName(), e, msgCtx); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) {} } if (con != null) { try { con.close(); } catch (SQLException ignore) {} } } } private String convertRSToXML(ResultSet rs) throws SQLException { ResultSetMetaData rsmd = rs.getMetaData(); // create XML document DocumentBuilderFactory dbfac = DocumentBuilderFactory.newInstance(); DocumentBuilder docBuilder; Document doc = null; try { docBuilder = dbfac.newDocumentBuilder(); doc = docBuilder.newDocument(); } catch (ParserConfigurationException pce) { throw new SynapseException("Failed to transform Resultset to XML", pce); } // create Root element Element rootElement = doc.createElement("table"); doc.appendChild(rootElement); while (rs.next()) { // add Record element Element recordElement = doc.createElement("record"); rootElement.appendChild(recordElement); for (int i = 1; i <= rsmd.getColumnCount(); i++) { String columnName = rsmd.getColumnName(i); String columnValue = rs.getObject(i).toString(); // add Field element Element fieldElement = doc.createElement("field"); fieldElement.appendChild(doc.createTextNode(columnValue)); // set Name attribute to Field element Attr nameAttr = doc.createAttribute("name"); nameAttr.setValue(columnName); fieldElement.setAttributeNode(nameAttr); // add Field to Record recordElement.appendChild(fieldElement); } } //Output the XML String xmlString = null; try { //set up a transformer TransformerFactory transfac = TransformerFactory.newInstance(); Transformer trans = transfac.newTransformer(); trans.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes"); trans.setOutputProperty(OutputKeys.INDENT, "yes"); //create string from XML tree StringWriter sw = new StringWriter(); StreamResult result = new StreamResult(sw); DOMSource source = new DOMSource(doc); trans.transform(source, result); xmlString = sw.toString(); } catch (javax.xml.transform.TransformerException te) { throw new SynapseException("Failed to transform Resultset to XML", te); } return xmlString; } } 

Answer 3

You cannot get multiple rows using the DBLookUp broker. But you can use Data Services Server (DSS) and create queries. You can then call them using the call broker or send a broker. WSO2 EI also has DSS. See Documentation

https://docs.wso2.com/display/EI611/Generating+a+Data+Service https://docs.wso2.com/display/EI611/Exposing+Data+as+a+REST+Resource

Here, data can be displayed as REST (in a DSS resource) or SOAP (in a DSS operation).

Answer 4

Just use DBSelect Mediator here https://github.com/ichakios/dbselect-wso2-mediator is easy to use and returns a JSON result