Android Question Register in database in mysql list type variable (Solved)

[jose luis gudino]

hello everyone
a question? It is possible to save the content of a list type variable in a database in Mysql.

I have the following code:

Sub btnStartRecording_Click

    Private buffers As List
    rp.CheckAndRequest(rp.PERMISSION_RECORD_AUDIO)
    Wait For Activity_PermissionResult (Permission As String, Result As Boolean)
    If Result = False Then
        xui.MsgboxAsync("Debe Habilitar los permisos", "")
        Return
    End If
    If streamer.PlayerBufferSize = 0 Then
        streamer.Initialize("streamer", 22050, True, 16, streamer.VOLUME_MUSIC)
    End If
    buffers.Initialize
    streamer.StartRecording
   
End Sub

In this list type variable the byte array that represents the sound is recorded: "buffers"

Sub streamer_RecordBuffer(Buffer() As Byte)
    buffers.Add(Buffer)
End Sub

Sub GuardarAudio(lista As List)
   
    kvs.Put("sound01",lista)

End Sub

In a local KVS type database, you can store the variable and then retrieve it from the database and it works correctly, my question is how can I do it in a database in mysql

thank you

 

[aeric]

Do you define the column type as blob?

 

[DonManfred]

Serialize the List

    Dim ser As B4XSerializator
    Dim serialized() As Byte = ser.ConvertObjectToBytes(yourlist)
    ' Write the serialized bytes to the database

Convert the bytes back to list

 Dim ser As B4XSerializator
Dim newlist As List =  ser.ConvertBytesToObject(serialized)

 

[jose luis gudino]

1) I have created the "Blob" type field
2) function

Sub almacena_historial_audio(mensaje As mensajeChatenviado)
    Dim fecha_histo = DateTime.Now/1000 As Long
    Dim codigo = "C"&Starter.ID_chat As String
    '------------------------------------------
    Dim ser As B4XSerializator
    Dim serialized() As Byte = ser.ConvertObjectToBytes(mensaje.men_list_audio)
    '------------------------------------------
    mensaje.hora_timestamp = DateTime.Now
    mensaje.mensaje = mensaje.mensaje.Replace(",",".")
    ExecuteRemoteQuery($"INSERT INTO `vamos_chat`.`chat_app_local_movil`
                                     (`codigo`, `mensaje`,`fecha`,`audio`)
                                      VALUES
                                       ('${codigo}', '${mensaje}', '${fecha_histo}','${serialized}')"$,"")$

End Sub

3) this is the content of the list

4) Content of the variable serialized() As Byte

5) Write database

but when extracting from the database, it returns this
(The variable where the list is stored is called "audio" and is of type "Blob")

any suggestion. Thank you

 

[DonManfred]

2) function

Use Parametzized Queries.

Best is to post an example project showing the problem. Do not use Screenshots to post Code. Use codetags and post the code.

 

[Daestrum]

Have you missed the last part of @DonManfred tip

Dim newlist As List =  ser.ConvertBytesToObject(serialized)

serialised should be a byte array until you convert it back to an object.

 

[jose luis gudino]

Have you missed the last part of @DonManfred tip

Dim newlist As List =  ser.ConvertBytesToObject(serialized)

serialised should be a byte array until you convert it back to an object.

Hi , yes I did it, but the content of the "blob" field is [B@b88ddcd therefore when serialized, it returns an incorrect value in the list.
I am preparing a project to upload

 

[jose luis gudino]

hello.
I attached a project for analysis.
When you press the button it records the audio then plays it
serializes it, uploads it to the database
then download it and deserialize it

the libraries used are

thanks a lot

 

[DonManfred]

serializes it, uploads it to the database

And in case of any error you just ignore it and then read the audio from the Database?

I tried your app, record something. When writing to the database i get an error.

Error_com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'audio' at row 1

I´m not ignoring it and search for the problem.
I connected to your Database and check the table.
You did define chat_app_local_movil.audio as BLOB; means you can store up to 65535 Bytes to it. My 1st test was more already.
I changed the BLOB to a MEDIUMBLOB (16777215 bytes) and record something more.
I also get a networkonmainthread exception so i used DisableStrictMode. This is not recommended but i leave it for now.
I changed the sql query to be parametrized and more easy readable.
The recording are now successfully written to the database and even the playback did work.

Sub Service_Start (StartingIntent As Intent)
    DisableStrictMode
    Service.StopAutomaticForeground 'Starter service can start in the foreground state in some edge cases.
End Sub

Sub Service_TaskRemoved
    
End Sub

'Return true to allow the OS default exceptions handler to handle the uncaught exception.
Sub Application_Error (Error As Exception, StackTrace As String) As Boolean
    Return True
End Sub

Sub Service_Destroy

End Sub

Sub DisableStrictMode
    Dim jo As JavaObject
    jo.InitializeStatic("android.os.Build.VERSION")
    If jo.GetField("SDK_INT") > 9 Then
        Dim policy As JavaObject
        policy = policy.InitializeNewInstance("android.os.StrictMode.ThreadPolicy.Builder", Null)
        policy = policy.RunMethodJO("permitAll", Null).RunMethodJO("build", Null)
        Dim sm As JavaObject
        sm.InitializeStatic("android.os.StrictMode").RunMethod("setThreadPolicy", Array(policy))
    End If
End Sub

Sub Connect As ResumableSub
    mysql.InitializeAsync("mysql", driver, jdbcUrl, Username, password)
    Wait For MySQL_Ready (Success As Boolean)
    If Success = False Then
        Log("Check unfiltered logs for JDBC errors.")
    End If
    Return Success
End Sub

Sub CloseConnection
    mysql.Close
End Sub


Sub UPLOAD_audio As ResumableSub
    Wait For (Connect) Complete (Success As Boolean)
    If Success Then
        Try
            '------------------------------------------
            Dim ser As B4XSerializator
            Dim serialized() As Byte = ser.ConvertObjectToBytes(Main.buffers)
            '------------------------------------------
            mysql.ExecNonQuery2($"REPLACE INTO vamos_chat.chat_app_local_movil(`id`,`audio`) VALUES (?,?)"$, Array(1,serialized))
        Catch
            Log($"Error_${LastException}"$)
        End Try
        CloseConnection
    End If
    Return True
End Sub

Sub DONWLOAD_audio As ResumableSub
    Wait For (Connect) Complete (Success As Boolean)
    If Success Then
        Try
            Dim sf As Object = mysql.ExecQueryAsync("mysql","SELECT chat_app_local_movil.mensaje,chat_app_local_movil.audio FROM chat_app_local_movil;", Null)
                                
            Wait For (sf) mysql_QueryComplete (Success As Boolean, Crsr As JdbcResultSet)
            If Success Then
                Do While Crsr.NextRow
                    'Log($"list: ${Crsr.GetBlob("audio")}"$)
                    Dim ser As B4XSerializator
                    Dim newlist As List =  ser.ConvertBytesToObject(Crsr.GetBlob("audio"))
                    Return newlist
                Loop
                Crsr.Close
            End If
        Catch
            Success = False
            Log(LastException)
        End Try
        CloseConnection
    End If
    Return Success
End Sub

 

[jose luis gudino]

Thanks, all the changes work great.
but I still can't recover the "list" from the database

Log($"list: ${Crsr.GetBlob("audio")}"$)
Dim ser As B4XSerializator
Dim newlist As List =  ser.ConvertBytesToObject(Crsr.GetBlob("audio"))
Return newlist

 

[jose luis gudino]

And in case of any error you just ignore it and then read the audio from the Database?

I tried your app, record something. When writing to the database i get an error.

I´m not ignoring it and search for the problem.
I connected to your Database and check the table.
You did define chat_app_local_movil.audio as BLOB; means you can store up to 65535 Bytes to it. My 1st test was more already.
I changed the BLOB to a MEDIUMBLOB (16777215 bytes) and record something more.
I also get a networkonmainthread exception so i used DisableStrictMode. This is not recommended but i leave it for now.
I changed the sql query to be parametrized and more easy readable.
The recording are now successfully written to the database and even the playback did work.

Sub Service_Start (StartingIntent As Intent)
    DisableStrictMode
    Service.StopAutomaticForeground 'Starter service can start in the foreground state in some edge cases.
End Sub

Sub Service_TaskRemoved
  
End Sub

'Return true to allow the OS default exceptions handler to handle the uncaught exception.
Sub Application_Error (Error As Exception, StackTrace As String) As Boolean
    Return True
End Sub

Sub Service_Destroy

End Sub

Sub DisableStrictMode
    Dim jo As JavaObject
    jo.InitializeStatic("android.os.Build.VERSION")
    If jo.GetField("SDK_INT") > 9 Then
        Dim policy As JavaObject
        policy = policy.InitializeNewInstance("android.os.StrictMode.ThreadPolicy.Builder", Null)
        policy = policy.RunMethodJO("permitAll", Null).RunMethodJO("build", Null)
        Dim sm As JavaObject
        sm.InitializeStatic("android.os.StrictMode").RunMethod("setThreadPolicy", Array(policy))
    End If
End Sub

Sub Connect As ResumableSub
    mysql.InitializeAsync("mysql", driver, jdbcUrl, Username, password)
    Wait For MySQL_Ready (Success As Boolean)
    If Success = False Then
        Log("Check unfiltered logs for JDBC errors.")
    End If
    Return Success
End Sub

Sub CloseConnection
    mysql.Close
End Sub


Sub UPLOAD_audio As ResumableSub
    Wait For (Connect) Complete (Success As Boolean)
    If Success Then
        Try
            '------------------------------------------
            Dim ser As B4XSerializator
            Dim serialized() As Byte = ser.ConvertObjectToBytes(Main.buffers)
            '------------------------------------------
            mysql.ExecNonQuery2($"REPLACE INTO vamos_chat.chat_app_local_movil(`id`,`audio`) VALUES (?,?)"$, Array(1,serialized))
        Catch
            Log($"Error_${LastException}"$)
        End Try
        CloseConnection
    End If
    Return True
End Sub

Sub DONWLOAD_audio As ResumableSub
    Wait For (Connect) Complete (Success As Boolean)
    If Success Then
        Try
            Dim sf As Object = mysql.ExecQueryAsync("mysql","SELECT chat_app_local_movil.mensaje,chat_app_local_movil.audio FROM chat_app_local_movil;", Null)
                              
            Wait For (sf) mysql_QueryComplete (Success As Boolean, Crsr As JdbcResultSet)
            If Success Then
                Do While Crsr.NextRow
                    'Log($"list: ${Crsr.GetBlob("audio")}"$)
                    Dim ser As B4XSerializator
                    Dim newlist As List =  ser.ConvertBytesToObject(Crsr.GetBlob("audio"))
                    Return newlist
                Loop
                Crsr.Close
            End If
        Catch
            Success = False
            Log(LastException)
        End Try
        CloseConnection
    End If
    Return Success
End Sub

Thank you for your help, I have made a small donation.

 

[jose luis gudino]

apparently it didn't work because of the way I was connecting to the database, I was connecting it with the JdbcSQL library and I was doing it with HttpJob
Neither of them works, it has to be with jRDC2
this way it works correctly
thanks for your help everyone