Android Question Record audio and then write it as string to MSMariaDB...

[Magma]

Hi there...

I am trying to record audio and then write it to db as string... but i have a problem i can;t do that... i dont know why - may be is a limitation... ?

I am using MSMariaDB for connector... + audio library (AudioStreamer)

So I can record the audio - i can play it... here the code of button:

Sub Button1_Click

'start records
If nowrecord=False Then
    Button1.textColor=Colors.red
    nowrecord=True
    Log("recording")
    myaudio.clear
    astream.StartRecording
Else
    Log("writing to db")
    astream.StopRecording
    'astream.StartPlaying
    Dim s As String
    Dim su As StringUtils
   For Each b() As Byte In myaudio
    '  astream.Write(b) 'with that playing /hear the audio
            s = s & su.EncodeBase64(b) 'making some valid string for writing at db
   Next
    'astream.Write(Null) 'stops playing
'write to db

Log(s.Length) 'When i have more than 10 seconds... goes at 800KB !


    db.ExecuteASync("INSERT INTO audios SET forid='0', fromid='" & myid & "', audio1='" & s & "', edate='" &  DateTime.Date(DateTime.Now) &"', etime='" & DateTime.Time(DateTime.Now) & "'","")
    nowrecord=False
    Button1.textColor=Colors.Green
End If   
End Sub

Seems that going to write it... but nothing do... why ? if i just have a piece ex. length of 2.5K writes it ok... is a limitation of statement?... or what ?

What can i do... anyone do something like that ?

 

[tigrot]

I have read the maximum logical record size of a Mysql DB(Maria DB as well?) is 2^16. I don't think this the best way to store audio. Create a file on the server and write it's name in DB. I do for images. I store image locally then using a FTP I transfer to server, only when line is available

 

[Magma]

Create a file on the server and write it's name in DB. I do for images.

How you ll do that.. actually how you uploading the file ? And what';s the best way...

 

[Magma]

b4J server... ?

https://www.b4x.com/android/forum/t...p-to-your-b4j-server-over-the-internet.37201/

with that ?

 

[Erel]

This code is very inefficient:

For Each b() As Byte In myaudio
    '  astream.Write(b) 'with that playing /hear the audio
            s = s & su.EncodeBase64(b) 'making some valid string for writing at db
   Next

You should use StringBuilder instead.

It will create the string successfully. I'm not familiar with the db library so don't know whether you are hitting any limitation there. Note that you can probably use jSQL instead.

 

[OliverA]

What column type is "audio1" in your database? Varchar has a limit of 65K, but if it is declared with UTF-8, it's down to 21844 bytes. On top of that, divide that by four and multiply by three to account for base64 overhead and you get a max of 16383 bytes with UTF-8, and about 49151 bytes without UTF-8.

Sources:
MariaDB documentation
Stackoverflow artice on Base64 size calculation

 

[OliverA]

Ok, you may have a totally different problem. You have posted about storing audio recordings before and seemed to indicate that you use the LONGTEXT column type which can store up to 4GB of text. So my above answer my be mute. What you are most likely running into is that your SQL statement becomes too long since you are embedding the value of your music file into the SQL statement. The maximum length that an SQL statement can be can be found in the MariaDB documentation and has something to do with the "max_allowed_packet" size. Depending on which version of MariaDB is running on the server, this size ranges from 1MB to 16MB. If it is the lower limit, a 800K audio file can easily blow this limit (due to base64 overhead). The answer to this then would be to

1) Use parameterized SQL queries. Since you must be using a 3rd part SQL library (Android does not support MySQL/MariaDB out of the box) you have to look at it and see if it provides methods for handling parameterized SQL queries.

2) Modify the configuration of your database server to accept larger SQL queries.

Please note this is just another (un)educated guess, since I have no means to reproduce your application/server setup and I'm only going off of the symptoms that you are experiencing.

 

[Magma]

Thank you all for your answers....

1. I ll go with the solution of tigrot... using b4j server and uploading/downloading files to it... so with a record of id/file i will search at directory...
2. Erel for educaction purposes can you give an example of Stringbuilder... ??
3. OliverA Totally right... by the way if have multiple audio recordings the same time my db will BREAK!

Thanks again

 

[KMatle]

Here an example how to convert a file's content into a Base64 string:

Dim FileBuffer(0) As Byte
    FileBuffer=Bit.InputStreamToBytes(File.OpenInput(FilePath,FileName))

    Dim B64String As String
    Dim su As StringUtils
    B64String=su.EncodeBase64(FileBuffer)

LONGTEXT is a good choice.

Usually you won't store "big data" in a DB, only the index. So the data would be stored in the server's file system and the index in your db. I store up to 500KB in my DB directly.

 

[OliverA]

an example of Stringbuilder

Modifying a portion of your code in post #1

Dim s As String
Dim su As StringUtils
Dim sb As StringBuilder
For Each b() As Byte In myaudio
   sb.Append(su.EncodeBase64(b)) 'making some valid string for writing at db
Next

Log(sb.Length) 'When i have more than 10 seconds... goes at 800KB !
'Just showing how to get string out of StringBuilder object
s = sb.ToString

As per documentation, "strings are immutable in Basic4android". Therefore, in your original code, a new string was created every time you did the append operation with '&'. For multiple append operations, that can be very inefficient and StringBuilder solves that issue.

by the way if have multiple audio recordings the same time my db will BREAK

I'm not arguing for or against Longtext/Blob storage in a DB, but why would the DB break? Longtext/blob storage is not a new issue for DB's. The DB may not be as efficient in storing Longtext/Blob (and later retrieving such objects), but Longtext/Blob storage should not break the DB.