Get blob from mysql with groovy

Q&A
1

I have a simple table with these fields:

ID int(11) NOT NULL AUTO_INCREMENT,
audio longblob NOT NULL,
callid varchar(45) NOT NULL,
causa varchar(15) DEFAULT NULL,
duracion varchar(45) DEFAULT NULL,
codigo int(11) DEFAULT NULL,
numeroagente int(8) DEFAULT NULL,
numeroreferencia int(8) DEFAULT NULL,
solicitud varchar(45) DEFAULT NULL,
fin timestamp NULL DEFAULT NULL,
inicio timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

I need to get the audio field, i use the external rest api datasource example to get all fields, but i need to get the audio field as a wave file.

I use,

def rows = sql.rows query

And for get my connection i use this method

protected Sql buildSql(ResourceProvider pageResourceProvider) { Properties props = loadProperties "datasource.properties", pageResourceProvider Context ctx = new InitialContext(props) DataSource dataSource = (DataSource) ctx.lookup(props["datasourceLlamada.name"]) //DataSource dataSource = (DataSource) ctx.lookup("java:comp/env/jdbc/llamadaDS") new Sql(dataSource) }

if i try to serialize my object and convert to a base64 string i don’t have the correct result.

I don’t know if is because that my table is in format: latin1 and i need utf8 to see the same result.

Thanks for the help.

Eduardo Cortez

This is my entire Get.groovy class:

package org.bonitasoft.rest.api.extension.ws.llamada

import java.io.Serializable
import java.util.Map
import java.util.Properties

import groovy.json.JsonBuilder
import groovy.json.JsonSlurper
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.json.CDL
import org.json.JSONArray
import org.slf4j.Logger
import org.slf4j.LoggerFactory

import wslite.json.JSONObject
import wslite.soap.SOAPClient
import wslite.soap.SOAPResponse

class Get implements RestApiController {

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



@Override
RestApiResponse doHandle(HttpServletRequest request, RestApiResponseBuilder responseBuilder, RestAPIContext context) {
	
	String queryId = request.getParameter "queryId"
	if (queryId == null) {
		return buildErrorResponse(responseBuilder, "The parameter queryId is missing.")
	}
	
	
	
	// Get the query SQL definition from the queries.properties file using query id.
	String query = getQuery queryId, context.resourceProvider
	if (query == null) {
		return buildErrorResponse(responseBuilder, "The queryId does not refer to an existing query. Check your query id and queries.properties file content.")
	}
	
	
	// Build a map will all SQL queries parameters (all REST call parameters expect "queryId").
	Map<String, String> params = getSqlParameters request

	// 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.
		
		if(!params.isEmpty()){
			for (String parameterName : request.getParameterNames()) {
				query = query.replace(":"+parameterName, request.getParameter(parameterName))					
			}
		}
		
		
		
		
		def rows = sql.rows query
		
		
			
		// Build the JSON answer with the query result
		Map<String,String> result = [:] 
		
		for (row in rows) {
			def headers = row.keySet()
			
			for (header in headers) {
				
				if(header == "audio"){
					
					//result.put(header, serialize(row.getProperty(header)).encodeBase64().toString())
					//row.getProperty("")
					//return buildResponseWav(responseBuilder, row.get(header))
					result.put(header, row.get(header))
				}else{
					result.put(header, row.get(header))
				}
				
			}
		}
		/*while (rows.next()) {
			
			def columnCount = rows.metaData.columnCount
			
			for(int i=0; i<columnCount;i++){
				def columnName = rows.metaData.getColumnName(i)
				
				if(columnName == "audio"){
					result.put(columnName,rows.getClob(columnName))
				}else{
					result.put(columnName,rows.getString(columnName))
				}
				
			}
			
			for (header in headers) {
				
				if(header == "audio"){
					
					result.put(header, serialize(row.getProperty(header)).encodeBase64().toString())
				}else{
					result.put(header, row.getProperty(header))
				}
				
			}
		}*/
		
		JsonBuilder builder = new JsonBuilder(result)
		String table = builder.toPrettyString()
		return buildResponse(responseBuilder, table)
		//return buildResponseWav(responseBuilder, file)
	}catch(Exception e){
		return buildErrorResponse(responseBuilder, e.getMessage()+" query: "+query)
	} finally {
		sql.close()
	}
	
	
}

protected byte[] serialize(Object obj) throws IOException {
	ByteArrayOutputStream out = new ByteArrayOutputStream();
	ObjectOutputStream os = new ObjectOutputStream(out);
	os.writeObject(obj);
	return out.toByteArray();
}


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 buildExitoResponse(RestApiResponseBuilder apiResponseBuilder, String message) {
	LOGGER.info message

	Map<String, String> result = [:]
	result.put "exito", 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("queryId")
	params
}

protected Sql buildSql(ResourceProvider pageResourceProvider) {
	Properties props = loadProperties "datasource.properties", pageResourceProvider
	Context ctx = new InitialContext(props)
	DataSource dataSource = (DataSource) ctx.lookup(props["datasourceLlamada.name"])
	//DataSource dataSource = (DataSource) ctx.lookup("java:comp/env/jdbc/llamadaDS")
	new Sql(dataSource)
}

protected String getQuery(String queryId, ResourceProvider resourceProvider) {
	Properties props = loadProperties "queries.properties", resourceProvider
	props[queryId]
}

protected Properties loadProperties(String fileName, ResourceProvider resourceProvider) {
	Properties props = new Properties()
	resourceProvider.getResourceAsStream(fileName).withStream { InputStream s ->
		props.load s
	}
	props
}

protected RestApiResponse buildResponseWav(RestApiResponseBuilder apiResponseBuilder, Serializable result) {
	apiResponseBuilder.with {
		withMediaType("audio/wav")
		//withCharacterSet("ISO-8859-5")
		withAdditionalHeader("Content-disposition","attachment; filename=audio.wav")
		withResponse(result)
		build()
	}
}

}
2

Have a look at my post here for getting a BLOB (document) from MySQL

http://community.bonitasoft.com/questions-and-answers/upload-document-instantiation-form-other-data-wellusing-api#node-25968

regards
Seán

PS: As this reply answers your question, please mark as resolved.

3

A Tip on displaying CODE/LOGS correctly in Posts:

Do not use the Supplied Buttons above, for some reason they refuse to work correctly, and despite bringing it to Bonitasofts attention, it’s low priority.

To Show Code/Logs correctly use

< code >
your code/log
< /code >

removing the spaces to allow the showing of code/logs correctly as here:

your code/log

You should still be able to edit your post to ensure the correct formatting of the code to help us understand it easier.

Thanks and regards
Seán

4

Thanks. I make the question more easy to read With your advice

5

first things first, BLOB data is not usually defined as Latin1 or UTF8. This would have to be done prior to saving in the field.

It also looks like you’re assuming the data is WAV format - what happens if it’s in MP3? Or is it absolutely guarenteed that the field will be WAV?

The last thing is - you don’t actually say what you’re going to do with the Audio file…can you explain. Would help in determining what to do next.

thanks and regards
Seán

6

Yes, the format always be a wav file, i want to play the wav file on html with the base64 data. I make it finally with statements and jdbc connection. Parse the data to byte and then parse to base64 with groovy.

Thanks for the help:

This is the code with do that:

package org.bonitasoft.rest.api.extension.ws.llamada

import java.io.BufferedInputStream
import java.io.OutputStream
import java.io.Serializable
import java.sql.Blob
import java.sql.Connection
import java.sql.PreparedStatement
import java.sql.ResultSet
import java.util.Map
import java.util.Properties

import groovy.json.JsonBuilder
import groovy.json.JsonSlurper
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.engine.io.IOUtil
import org.bonitasoft.engine.io.IOUtils
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.json.CDL
import org.json.JSONArray
import org.slf4j.Logger
import org.slf4j.LoggerFactory

import wslite.json.JSONObject
import wslite.soap.SOAPClient
import wslite.soap.SOAPResponse

class Get implements RestApiController {

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

Connection conn = null;


@Override
RestApiResponse doHandle(HttpServletRequest request, RestApiResponseBuilder responseBuilder, RestAPIContext context) {
	
	String queryId = request.getParameter "queryId"
	if (queryId == null) {
		return buildErrorResponse(responseBuilder, "The parameter queryId is missing.")
	}
	//OutputStream os = null
	
	List<Map> lstResultados = new ArrayList<Map>()
	
	 
	
	
	// Get the query SQL definition from the queries.properties file using query id.
	String query = getQuery queryId, context.resourceProvider
	if (query == null) {
		return buildErrorResponse(responseBuilder, "The queryId does not refer to an existing query. Check your query id and queries.properties file content.")
	}
	
	
	// Build a map will all SQL queries parameters (all REST call parameters expect "queryId").
	Map<String, String> params = getSqlParameters request

	// Get the database connection using the data source declared in datasource.properties
	//Sql sql = buildSql context.resourceProvider
	
	conn = buildConnection(context.resourceProvider)
	
	if(!params.isEmpty()){
		for (String parameterName : request.getParameterNames()) {
			query = query.replace(":"+parameterName, request.getParameter(parameterName))
		}
	}
	
	PreparedStatement statement =
	conn.prepareStatement(query);
	try {
		// Run the query with or without parameters.
		
		
		
		
		//def rows = sql.rows query
		
		ResultSet rs = statement.executeQuery();
		
		while (rs.next()) {
			Map<String, String> resultado = [:]
			for(int i=1;i <= rs.getMetaData().columnCount;i++){
				
				if(rs.getMetaData().getColumnType(i) == -4){
					Blob blob = rs.getBlob(rs.getMetaData().getColumnName(i))
					byte[] bytes = IOUtil.getAllContentFrom(blob.binaryStream)
					
					//return buildResponseWav(responseBuilder,bytes)
					
					resultado.put(rs.getMetaData().getColumnName(i), bytes.encodeBase64().toString())
				}else{
					resultado.put(rs.getMetaData().getColumnName(i), rs.getObject(i))
				}
				//LOGGER.error "Tipo: "+ rs.getMetaData().getColumnType(i)+ " Columna "+rs.getMetaData().getColumnName(i)
			}
			
			lstResultados.add(resultado)
			
			
			
			
			
		}
		
		
		return buildJsonResponse(responseBuilder, lstResultados)
	}catch(Exception e){
		return buildErrorResponse(responseBuilder, e.getMessage()+" query: "+query)
	} finally {
		statement.close()
		conn.close()
		//sql.close()
	}
	
	
}


protected RestApiResponse buildJsonErrorResponse(RestApiResponseBuilder apiResponseBuilder, Map<String,String> message) {
	//LOGGER.error message

	Map<String, String> result = [:]
	result.put "error", message
	apiResponseBuilder.withResponse new JsonBuilder(result).toPrettyString()
	apiResponseBuilder.withResponseStatus(HttpServletResponse.SC_BAD_REQUEST)
	apiResponseBuilder.build()
	
	
}

protected RestApiResponse buildJsonResponse(RestApiResponseBuilder apiResponseBuilder, List<Map> values) {
	//LOGGER.error message
	
	
	apiResponseBuilder.withResponse new JsonBuilder(values).toPrettyString()
	apiResponseBuilder.withResponseStatus(HttpServletResponse.SC_OK)
	apiResponseBuilder.build()
	
	
}

protected byte[] serialize(Object obj) throws IOException {
	ByteArrayOutputStream out = new ByteArrayOutputStream();
	ObjectOutputStream os = new ObjectOutputStream(out);
	os.writeObject(obj);
	return out.toByteArray();
}


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 buildExitoResponse(RestApiResponseBuilder apiResponseBuilder, String message) {
	LOGGER.info message

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

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

protected RestApiResponse buildResponseWav(RestApiResponseBuilder apiResponseBuilder, byte[] result) {
	apiResponseBuilder.with {
		withMediaType("audio/wav")
		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("queryId")
	params
}

protected Sql buildSql(ResourceProvider pageResourceProvider) {
	Properties props = loadProperties "datasource.properties", pageResourceProvider
	Context ctx = new InitialContext(props)
	DataSource dataSource = (DataSource) ctx.lookup(props["datasourceLlamada.name"])
	new Sql(dataSource)
}

protected Connection buildConnection(ResourceProvider pageResourceProvider) {
	Properties props = loadProperties "datasource.properties", pageResourceProvider
	Context ctx = new InitialContext(props)
	DataSource dataSource = (DataSource) ctx.lookup(props["datasourceLlamada.name"])
	return dataSource.getConnection()
}

protected String getQuery(String queryId, ResourceProvider resourceProvider) {
	Properties props = loadProperties "queries.properties", resourceProvider
	props[queryId]
}

protected Properties loadProperties(String fileName, ResourceProvider resourceProvider) {
	Properties props = new Properties()
	resourceProvider.getResourceAsStream(fileName).withStream { InputStream s ->
		props.load s
	}
	props
}

protected RestApiResponse buildResponseWav(RestApiResponseBuilder apiResponseBuilder, File result) {
	apiResponseBuilder.with {
		withMediaType("audio/wav")
		//withCharacterSet("ISO-8859-5")
		withAdditionalHeader("Content-disposition","attachment; filename=audio.wav")
		withResponse(result)
		build()
	}
}

}
7

Excellent work. Very good.

regards

8

The work seems great. !
I have the same issue with this. It is really helpful to figure out the problem.
In my case, I have an error index.groovy database connection part.

protected Connection buildConnection(ResourceProvider pageResourceProvider) {
Properties props = loadProperties “datasource.properties”, pageResourceProvider
Context ctx = new InitialContext(props)
DataSource dataSource = (DataSource) ctx.lookup(props[“datasource.name”])
return dataSource.getConnection()
}
In datasource.properties, I put “datasource.name = java:comp/env/demoDS”
But error occured like as follows.

Do I have to put more datasource inside the file?
I put the database information on bonita.xml file like this.

I appreciate your help in advance.

Thanks.