Developing a REST API Extension

Q&A
1

Good morning,

So I need to connect to an external database to retrieve data in my custom page application. One of the proposed method is the use of a Rest API Extension (ps: Im new to use a Rest API EXtension).

So, I have followed the steps mentionned in the example https://github.com/Bonitasoft-Community/rest-api-sql-data-source + The documentation, but nothing seems to be working ! When I try to deploy my Extension from the studio , I got this error when the test is run 

  "should_return_a_json_representation_as_result(com.advaas.rest.api.IndexTest): No signature of method: groovy.json.JsonSlurper.parseText() is applicable for argument types: (java.util.LinkedHashMap) values: [[error:The parameter queryId is missing.]](..)"

Any idea why I got this error?

regards!
 

2

Hello,

Could you provide the complete source of the Groovy, please? So we can understand what's happening.

PS: I used personally Gasoline to do this requirement: so, you don't have any development to do, only configuration.

https://community.bonitasoft.com/project/pagegasoline

3

package com.advaas.rest.api

import groovy.json.JsonBuilder
import groovy.sql.Sql

import javax.naming.Context
import javax.naming.InitialContext
import javax.servlet.http.HttpServletRequest
import javax.servlet.http.HttpServletResponse
import javax.sql.DataSource

import org.bonitasoft.web.extension.ResourceProvider
import org.bonitasoft.web.extension.rest.RestAPIContext
import org.bonitasoft.web.extension.rest.RestApiController
import org.bonitasoft.web.extension.rest.RestApiResponse
import org.bonitasoft.web.extension.rest.RestApiResponseBuilder
import org.slf4j.Logger
import org.slf4j.LoggerFactory

class Index implements RestApiController {

private static final Logger LOGGER = LoggerFactory.getLogger(Index.class)

	private static final String getInterventions="SELECT advaas.intervention.id,advaas.intervention.device_id,advaas.intervention.intervener_name,advaas.date,advaas.intervention.amount,advaas.intention.intervention_type,advaas.intervention.insertion_type,advaas.intervention.ignored FROM advaas.intervention INNER JOIN advaas.device ON advaas.intervention.device_id = advaas.device.id"
	private static final String getInterventionsByDeviceId="SELECT advaas.intervention.id,advaas.intervention.device_id,advaas.intervention.intervener_name,advaas.date,advaas.intervention.amount,advaas.intention.intervention_type,advaas.intervention.insertion_type,advaas.intervention.ignored FROM advaas.intervention INNER JOIN advaas.device ON advaas.intervention.device_id = advaas.device.id where device_id=:device"
	private static final String getInterventionsByStartDate="SELECT advaas.intervention.id,advaas.intervention.device_id,advaas.intervention.intervener_name,advaas.date,advaas.intervention.amount,advaas.intention.intervention_type,advaas.intervention.insertion_type,advaas.intervention.ignored FROM advaas.intervention INNER JOIN advaas.device ON advaas.intervention.device_id = advaas.device.id where device_id=:device"
	
	
		@Override
		RestApiResponse doHandle(HttpServletRequest request, RestApiResponseBuilder responseBuilder, RestAPIContext context) {
	
			// Get query id from the REST request. See queries.properties for declared queries ids.
			String queryName = (String)request.getParameter ("queryName")
			if (queryName == null || (!queryName.equals("getInterventions") && !queryName.equals("getInterventionsByDeviceId"))) {
				LOGGER.info("the parameter queryName is invalid")
			}
	
			// Get the query SQL definition from the queries.properties file using query id.
			
	String query=queryName.equals("getInterventions") ? getInterventions : getInterventionsByDeviceId
	   Map<String,String> params= [:]
	for(String parameterName : request.getParameterNames()) {
		params.put(parameterName,request.getParameter(parameterName))
		}
		params.remove("queryName")

	// Build a map will all SQL queries parameters (all REST call parameters expect "queryId").

	// Get the database connection using the data source declared in datasource.properties
	Sql sql = buildSql context.resourceProvider

	try {
		// Run the query with or without parameters.
		def rows = params.isEmpty() ? sql.rows(query) : sql.rows(query, params)

		// Build the JSON answer with the query result
		JsonBuilder builder = new JsonBuilder(rows)
		String table = builder.toPrettyString()
		def br=buildResponse(responseBuilder, table) 
		println("buildResponse"+ br)
		return br
	} finally {
		sql.close()
	}
}


protected RestApiResponse buildErrorResponse(RestApiResponseBuilder apiResponseBuilder, String message) {
	LOGGER.error message

	Map<String, String> result = [:]
	result.put "error", message
	apiResponseBuilder.withResponseStatus(HttpServletResponse.SC_BAD_REQUEST)
	buildResponse apiResponseBuilder, result
}

protected RestApiResponse buildResponse(RestApiResponseBuilder apiResponseBuilder, Serializable result) {
	apiResponseBuilder.with {
		withResponse(result)
		build()
	}
}

protected Map<String, String> getSqlParameters(HttpServletRequest request) {
	Map<String, String> params = [:]
	for (String parameterName : request.getParameterNames()) {
		params.put(parameterName, request.getParameter(parameterName))
	}
	params.remove("queryName")
	params
}

protected Sql buildSql(ResourceProvider pageResourceProvider) {
	Properties props = new Properties();

props.setProperty(Context.PROVIDER_URL,“java:jboss/datasources/ADVAASDS”);
Context ctx = new InitialContext(props)
DataSource dataSource = (DataSource) ctx.lookup(“java:jboss/datasources/ADVAASDS”)
new Sql(dataSource)
}

}

4

its a complete source of the groovy !

5

hello,

Add a "catch" in your source and log what's happen. 

6

Hum, look good. You should use the debugger to debug it. I suspect if you have a SQL error syntax: you don't have any catch in your try{} so what's happen at this moment? I think this is what you faced.

If you don't know how to run a debugger session, see the PY Tutorial related to this topic ( https://youtu.be/luIlf7SoVCE )