Get blob from mysql with groovy
I have a simple table with these fields:
`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
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:
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()
}
}
}
Have a look at my post here for getting a BLOB (document) from MySQL
http://community.bonitasoft.com/questions-and-answers/upload-document-in...
regards
Seán
PS: As this reply answers your question, please mark as resolved.
Comments
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:
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
Thanks. I make the question more easy to read With your advice
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
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:
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()
}
}
}
Excellent work. Very good.
regards
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.
auth="Container"
type="javax.sql.DataSource"
maxActive="10"
minIdle="1"
maxWait="10000"
initialSize="3"
removeAbandoned="true"
logAbandoned="true"
username="postgres"
password="ddd"
driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://localhost:5432/postgres"/>
I appreciate your help in advance.
Thanks.