Get blob from mysql with groovy

1
0
-1

I have a simple table with these fields:

  1. `ID` int(11) NOT NULL AUTO_INCREMENT,
  2. `audio` longblob NOT NULL,
  3. `callid` varchar(45) NOT NULL,
  4. `causa` varchar(15) DEFAULT NULL,
  5. `duracion` varchar(45) DEFAULT NULL,
  6. `codigo` int(11) DEFAULT NULL,
  7. `numeroagente` int(8) DEFAULT NULL,
  8. `numeroreferencia` int(8) DEFAULT NULL,
  9. `solicitud` varchar(45) DEFAULT NULL,
  10. `fin` timestamp NULL DEFAULT NULL,
  11. `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

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

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:

  1. package org.bonitasoft.rest.api.extension.ws.llamada
  2.  
  3. import java.io.Serializable
  4. import java.util.Map
  5. import java.util.Properties
  6.  
  7. import groovy.json.JsonBuilder
  8. import groovy.json.JsonSlurper
  9. import groovy.sql.Sql
  10.  
  11. import javax.naming.Context
  12. import javax.naming.InitialContext
  13. import javax.servlet.http.HttpServletRequest
  14. import javax.servlet.http.HttpServletResponse
  15. import javax.sql.DataSource
  16.  
  17.  
  18. import org.bonitasoft.web.extension.ResourceProvider
  19. import org.bonitasoft.web.extension.rest.RestAPIContext
  20. import org.bonitasoft.web.extension.rest.RestApiController
  21. import org.bonitasoft.web.extension.rest.RestApiResponse
  22. import org.bonitasoft.web.extension.rest.RestApiResponseBuilder
  23. import org.json.CDL
  24. import org.json.JSONArray
  25. import org.slf4j.Logger
  26. import org.slf4j.LoggerFactory
  27.  
  28. import wslite.json.JSONObject
  29. import wslite.soap.SOAPClient
  30. import wslite.soap.SOAPResponse
  31.  
  32.  
  33.  
  34.  
  35. class Get implements RestApiController {
  36.  
  37. private static final Logger LOGGER = LoggerFactory.getLogger(Get.class)
  38.  
  39.  
  40.  
  41. @Override
  42. RestApiResponse doHandle(HttpServletRequest request, RestApiResponseBuilder responseBuilder, RestAPIContext context) {
  43.  
  44. String queryId = request.getParameter "queryId"
  45. if (queryId == null) {
  46. return buildErrorResponse(responseBuilder, "The parameter queryId is missing.")
  47. }
  48.  
  49.  
  50.  
  51. // Get the query SQL definition from the queries.properties file using query id.
  52. String query = getQuery queryId, context.resourceProvider
  53. if (query == null) {
  54. return buildErrorResponse(responseBuilder, "The queryId does not refer to an existing query. Check your query id and queries.properties file content.")
  55. }
  56.  
  57.  
  58. // Build a map will all SQL queries parameters (all REST call parameters expect "queryId").
  59. Map<String, String> params = getSqlParameters request
  60.  
  61. // Get the database connection using the data source declared in datasource.properties
  62. Sql sql = buildSql context.resourceProvider
  63.  
  64. try {
  65. // Run the query with or without parameters.
  66.  
  67. if(!params.isEmpty()){
  68. for (String parameterName : request.getParameterNames()) {
  69. query = query.replace(":"+parameterName, request.getParameter(parameterName))
  70. }
  71. }
  72.  
  73.  
  74.  
  75.  
  76. def rows = sql.rows query
  77.  
  78.  
  79.  
  80. // Build the JSON answer with the query result
  81. Map<String,String> result = [:]
  82.  
  83. for (row in rows) {
  84. def headers = row.keySet()
  85.  
  86. for (header in headers) {
  87.  
  88. if(header == "audio"){
  89.  
  90. //result.put(header, serialize(row.getProperty(header)).encodeBase64().toString())
  91. //row.getProperty("")
  92. //return buildResponseWav(responseBuilder, row.get(header))
  93. result.put(header, row.get(header))
  94. }else{
  95. result.put(header, row.get(header))
  96. }
  97.  
  98. }
  99. }
  100. /*while (rows.next()) {
  101.  
  102. def columnCount = rows.metaData.columnCount
  103.  
  104. for(int i=0; i<columnCount;i++){
  105. def columnName = rows.metaData.getColumnName(i)
  106.  
  107. if(columnName == "audio"){
  108. result.put(columnName,rows.getClob(columnName))
  109. }else{
  110. result.put(columnName,rows.getString(columnName))
  111. }
  112.  
  113. }
  114.  
  115. for (header in headers) {
  116.  
  117. if(header == "audio"){
  118.  
  119. result.put(header, serialize(row.getProperty(header)).encodeBase64().toString())
  120. }else{
  121. result.put(header, row.getProperty(header))
  122. }
  123.  
  124. }
  125. }*/
  126.  
  127. JsonBuilder builder = new JsonBuilder(result)
  128. String table = builder.toPrettyString()
  129. return buildResponse(responseBuilder, table)
  130. //return buildResponseWav(responseBuilder, file)
  131. }catch(Exception e){
  132. return buildErrorResponse(responseBuilder, e.getMessage()+" query: "+query)
  133. } finally {
  134. sql.close()
  135. }
  136.  
  137.  
  138. }
  139.  
  140. protected byte[] serialize(Object obj) throws IOException {
  141. ByteArrayOutputStream out = new ByteArrayOutputStream();
  142. ObjectOutputStream os = new ObjectOutputStream(out);
  143. os.writeObject(obj);
  144. return out.toByteArray();
  145. }
  146.  
  147.  
  148. protected RestApiResponse buildErrorResponse(RestApiResponseBuilder apiResponseBuilder, String message) {
  149. LOGGER.error message
  150.  
  151. Map<String, String> result = [:]
  152. result.put "error", message
  153. apiResponseBuilder.withResponseStatus(HttpServletResponse.SC_BAD_REQUEST)
  154. buildResponse apiResponseBuilder, result
  155. }
  156.  
  157. protected RestApiResponse buildExitoResponse(RestApiResponseBuilder apiResponseBuilder, String message) {
  158. LOGGER.info message
  159.  
  160. Map<String, String> result = [:]
  161. result.put "exito", message
  162. apiResponseBuilder.withResponseStatus(HttpServletResponse.SC_BAD_REQUEST)
  163. buildResponse apiResponseBuilder, result
  164. }
  165.  
  166. protected RestApiResponse buildResponse(RestApiResponseBuilder apiResponseBuilder, Serializable result) {
  167. apiResponseBuilder.with {
  168. withResponse(result)
  169. build()
  170. }
  171. }
  172.  
  173. protected Map<String, String> getSqlParameters(HttpServletRequest request) {
  174. Map<String, String> params = [:]
  175. for (String parameterName : request.getParameterNames()) {
  176. params.put(parameterName, request.getParameter(parameterName))
  177. }
  178. params.remove("queryId")
  179. params
  180. }
  181.  
  182. protected Sql buildSql(ResourceProvider pageResourceProvider) {
  183. Properties props = loadProperties "datasource.properties", pageResourceProvider
  184. Context ctx = new InitialContext(props)
  185. DataSource dataSource = (DataSource) ctx.lookup(props["datasourceLlamada.name"])
  186. //DataSource dataSource = (DataSource) ctx.lookup("java:comp/env/jdbc/llamadaDS")
  187. new Sql(dataSource)
  188. }
  189.  
  190. protected String getQuery(String queryId, ResourceProvider resourceProvider) {
  191. Properties props = loadProperties "queries.properties", resourceProvider
  192. props[queryId]
  193. }
  194.  
  195. protected Properties loadProperties(String fileName, ResourceProvider resourceProvider) {
  196. Properties props = new Properties()
  197. resourceProvider.getResourceAsStream(fileName).withStream { InputStream s ->
  198. props.load s
  199. }
  200. props
  201. }
  202.  
  203. protected RestApiResponse buildResponseWav(RestApiResponseBuilder apiResponseBuilder, Serializable result) {
  204. apiResponseBuilder.with {
  205. withMediaType("audio/wav")
  206. //withCharacterSet("ISO-8859-5")
  207. withAdditionalHeader("Content-disposition","attachment; filename=audio.wav")
  208. withResponse(result)
  209. build()
  210. }
  211. }
  212. }

Comments

Submitted by Sean McP on Tue, 02/21/2017 - 09:56

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:

  1. 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

Submitted by eduardo.cortez_1 on Tue, 02/21/2017 - 12:11

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

Submitted by Sean McP on Tue, 02/21/2017 - 18:37

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

Submitted by eduardo.cortez_1 on Mon, 03/06/2017 - 15:18

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:

  1. package org.bonitasoft.rest.api.extension.ws.llamada
  2.  
  3. import java.io.BufferedInputStream
  4. import java.io.OutputStream
  5. import java.io.Serializable
  6. import java.sql.Blob
  7. import java.sql.Connection
  8. import java.sql.PreparedStatement
  9. import java.sql.ResultSet
  10. import java.util.Map
  11. import java.util.Properties
  12.  
  13. import groovy.json.JsonBuilder
  14. import groovy.json.JsonSlurper
  15. import groovy.sql.Sql
  16.  
  17. import javax.naming.Context
  18. import javax.naming.InitialContext
  19. import javax.servlet.http.HttpServletRequest
  20. import javax.servlet.http.HttpServletResponse
  21. import javax.sql.DataSource
  22.  
  23. import org.bonitasoft.engine.io.IOUtil
  24. import org.bonitasoft.engine.io.IOUtils
  25. import org.bonitasoft.web.extension.ResourceProvider
  26. import org.bonitasoft.web.extension.rest.RestAPIContext
  27. import org.bonitasoft.web.extension.rest.RestApiController
  28. import org.bonitasoft.web.extension.rest.RestApiResponse
  29. import org.bonitasoft.web.extension.rest.RestApiResponseBuilder
  30. import org.json.CDL
  31. import org.json.JSONArray
  32. import org.slf4j.Logger
  33. import org.slf4j.LoggerFactory
  34.  
  35. import wslite.json.JSONObject
  36. import wslite.soap.SOAPClient
  37. import wslite.soap.SOAPResponse
  38.  
  39.  
  40.  
  41.  
  42. class Get implements RestApiController {
  43.  
  44. private static final Logger LOGGER = LoggerFactory.getLogger(Get.class)
  45.  
  46. Connection conn = null;
  47.  
  48.  
  49. @Override
  50. RestApiResponse doHandle(HttpServletRequest request, RestApiResponseBuilder responseBuilder, RestAPIContext context) {
  51.  
  52. String queryId = request.getParameter "queryId"
  53. if (queryId == null) {
  54. return buildErrorResponse(responseBuilder, "The parameter queryId is missing.")
  55. }
  56. //OutputStream os = null
  57.  
  58. List<Map> lstResultados = new ArrayList<Map>()
  59.  
  60.  
  61.  
  62.  
  63. // Get the query SQL definition from the queries.properties file using query id.
  64. String query = getQuery queryId, context.resourceProvider
  65. if (query == null) {
  66. return buildErrorResponse(responseBuilder, "The queryId does not refer to an existing query. Check your query id and queries.properties file content.")
  67. }
  68.  
  69.  
  70. // Build a map will all SQL queries parameters (all REST call parameters expect "queryId").
  71. Map<String, String> params = getSqlParameters request
  72.  
  73. // Get the database connection using the data source declared in datasource.properties
  74. //Sql sql = buildSql context.resourceProvider
  75.  
  76. conn = buildConnection(context.resourceProvider)
  77.  
  78. if(!params.isEmpty()){
  79. for (String parameterName : request.getParameterNames()) {
  80. query = query.replace(":"+parameterName, request.getParameter(parameterName))
  81. }
  82. }
  83.  
  84. PreparedStatement statement =
  85. conn.prepareStatement(query);
  86. try {
  87. // Run the query with or without parameters.
  88.  
  89.  
  90.  
  91.  
  92. //def rows = sql.rows query
  93.  
  94. ResultSet rs = statement.executeQuery();
  95.  
  96. while (rs.next()) {
  97. Map<String, String> resultado = [:]
  98. for(int i=1;i <= rs.getMetaData().columnCount;i++){
  99.  
  100. if(rs.getMetaData().getColumnType(i) == -4){
  101. Blob blob = rs.getBlob(rs.getMetaData().getColumnName(i))
  102. byte[] bytes = IOUtil.getAllContentFrom(blob.binaryStream)
  103.  
  104. //return buildResponseWav(responseBuilder,bytes)
  105.  
  106. resultado.put(rs.getMetaData().getColumnName(i), bytes.encodeBase64().toString())
  107. }else{
  108. resultado.put(rs.getMetaData().getColumnName(i), rs.getObject(i))
  109. }
  110. //LOGGER.error "Tipo: "+ rs.getMetaData().getColumnType(i)+ " Columna "+rs.getMetaData().getColumnName(i)
  111. }
  112.  
  113. lstResultados.add(resultado)
  114.  
  115.  
  116.  
  117.  
  118.  
  119. }
  120.  
  121.  
  122. return buildJsonResponse(responseBuilder, lstResultados)
  123. }catch(Exception e){
  124. return buildErrorResponse(responseBuilder, e.getMessage()+" query: "+query)
  125. } finally {
  126. statement.close()
  127. conn.close()
  128. //sql.close()
  129. }
  130.  
  131.  
  132. }
  133.  
  134.  
  135. protected RestApiResponse buildJsonErrorResponse(RestApiResponseBuilder apiResponseBuilder, Map<String,String> message) {
  136. //LOGGER.error message
  137.  
  138. Map<String, String> result = [:]
  139. result.put "error", message
  140. apiResponseBuilder.withResponse new JsonBuilder(result).toPrettyString()
  141. apiResponseBuilder.withResponseStatus(HttpServletResponse.SC_BAD_REQUEST)
  142. apiResponseBuilder.build()
  143.  
  144.  
  145. }
  146.  
  147. protected RestApiResponse buildJsonResponse(RestApiResponseBuilder apiResponseBuilder, List<Map> values) {
  148. //LOGGER.error message
  149.  
  150.  
  151. apiResponseBuilder.withResponse new JsonBuilder(values).toPrettyString()
  152. apiResponseBuilder.withResponseStatus(HttpServletResponse.SC_OK)
  153. apiResponseBuilder.build()
  154.  
  155.  
  156. }
  157.  
  158. protected byte[] serialize(Object obj) throws IOException {
  159. ByteArrayOutputStream out = new ByteArrayOutputStream();
  160. ObjectOutputStream os = new ObjectOutputStream(out);
  161. os.writeObject(obj);
  162. return out.toByteArray();
  163. }
  164.  
  165.  
  166. protected RestApiResponse buildErrorResponse(RestApiResponseBuilder apiResponseBuilder, String message) {
  167. LOGGER.error message
  168.  
  169. Map<String, String> result = [:]
  170. result.put "error", message
  171. apiResponseBuilder.withResponseStatus(HttpServletResponse.SC_BAD_REQUEST)
  172. buildResponse apiResponseBuilder, result
  173. }
  174.  
  175. protected RestApiResponse buildExitoResponse(RestApiResponseBuilder apiResponseBuilder, String message) {
  176. LOGGER.info message
  177.  
  178. Map<String, String> result = [:]
  179. result.put "exito", message
  180. apiResponseBuilder.withResponseStatus(HttpServletResponse.SC_BAD_REQUEST)
  181. buildResponse apiResponseBuilder, result
  182. }
  183.  
  184. protected RestApiResponse buildResponse(RestApiResponseBuilder apiResponseBuilder, Serializable result) {
  185. apiResponseBuilder.with {
  186. withResponse(result)
  187. build()
  188. }
  189. }
  190.  
  191. protected RestApiResponse buildResponseWav(RestApiResponseBuilder apiResponseBuilder, byte[] result) {
  192. apiResponseBuilder.with {
  193. withMediaType("audio/wav")
  194. withResponse(result)
  195. build()
  196. }
  197. }
  198. protected Map<String, String> getSqlParameters(HttpServletRequest request) {
  199. Map<String, String> params = [:]
  200. for (String parameterName : request.getParameterNames()) {
  201. params.put(parameterName, request.getParameter(parameterName))
  202. }
  203. params.remove("queryId")
  204. params
  205. }
  206.  
  207. protected Sql buildSql(ResourceProvider pageResourceProvider) {
  208. Properties props = loadProperties "datasource.properties", pageResourceProvider
  209. Context ctx = new InitialContext(props)
  210. DataSource dataSource = (DataSource) ctx.lookup(props["datasourceLlamada.name"])
  211. new Sql(dataSource)
  212. }
  213.  
  214. protected Connection buildConnection(ResourceProvider pageResourceProvider) {
  215. Properties props = loadProperties "datasource.properties", pageResourceProvider
  216. Context ctx = new InitialContext(props)
  217. DataSource dataSource = (DataSource) ctx.lookup(props["datasourceLlamada.name"])
  218. return dataSource.getConnection()
  219. }
  220.  
  221. protected String getQuery(String queryId, ResourceProvider resourceProvider) {
  222. Properties props = loadProperties "queries.properties", resourceProvider
  223. props[queryId]
  224. }
  225.  
  226. protected Properties loadProperties(String fileName, ResourceProvider resourceProvider) {
  227. Properties props = new Properties()
  228. resourceProvider.getResourceAsStream(fileName).withStream { InputStream s ->
  229. props.load s
  230. }
  231. props
  232. }
  233.  
  234. protected RestApiResponse buildResponseWav(RestApiResponseBuilder apiResponseBuilder, File result) {
  235. apiResponseBuilder.with {
  236. withMediaType("audio/wav")
  237. //withCharacterSet("ISO-8859-5")
  238. withAdditionalHeader("Content-disposition","attachment; filename=audio.wav")
  239. withResponse(result)
  240. build()
  241. }
  242. }
  243. }
Submitted by Sean McP on Mon, 03/06/2017 - 20:36

Excellent work. Very good.

regards

Submitted by whitedong_1350377 on Fri, 05/18/2018 - 22:21

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.

1 answer

1
0
-1

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.

Notifications