Android Question Slow loading MySQL image with jRDC2

[Anderson Linhares]

I'm using jRDC2 and everything is working. I record in the MySQL database the image taken from the camera of the device (the image field is LONGBLOB, because I tried to use BLOB and it makes a mistake when recording), but when I use a SELECT to get the image next to the other fields, it takes 14 seconds to finish loading. When the image field is empty (nullo) it takes nothing. I use the MySQL server and the B4A application on the local network.
Any solution?

 

[jahswant]

Post your code. Hard to debug without code ?

 

[Anderson Linhares]

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    'Activity.LoadLayout("Layout1")
    Activity.LoadLayout("atendimento")
    'Carrega dados do agendamento e abre o painel solicitado   
    Dim req As DBRequestManager = CreateRequest
    Dim cmd As DBCommand = CreateCommand("select_agenda",Array(agenda.mCod_Lancamento))
    Wait For (req.ExecuteQuery(cmd, 0, Null)) JobDone(j As HttpJob)
   
    If j.Success Then
       
        req.HandleJobAsync(j, "req")
        Wait For (req) req_Result(res As DBResult)
           
        If res.Rows.Size > 0 Then
       
            Dim row() As Object = res.Rows.Get(0)
           
            DateTime.TimeFormat = "HH:mm"
            LblCL_Nick.Text = row(res.Columns.Get("CL_Nick"))
            LblAG_Hora_Agenda.Text = row(res.Columns.Get("AG_Hora_Agenda"))
            LblPC_Nome.Text = row(res.Columns.Get("PC_Nome"))
           
            mData_Atendimento = Starter.G_Data_Atendimento.SubString2(6,10) & "/" & Starter.G_Data_Atendimento.SubString2(3,5) & "/" & Starter.G_Data_Atendimento.SubString2(0,2)
            mHora_Atendimento = DateTime.Time(DateTime.Now)
            mCod_Cliente = row(res.Columns.Get("AG_Cod_Cliente"))
            mCod_Procedimento = row(res.Columns.Get("AG_Cod_Procedimento"))
            mTipo_Procedimento = row(res.Columns.Get("PC_Tipo"))
            mValor_Procedimento = row(res.Columns.Get("PC_Valor"))
           
            If mTipo_Procedimento = "Cílios" Then
                ScvAtendimento.Panel.LoadLayout("cilios")
                ScvAtendimento.Panel.Height = PnlAtendimento.Height
   
                CarregaCamposCilios
               
            else If mTipo_Procedimento = "Epilação" Then
                ScvAtendimento.Panel.LoadLayout("epilacao")
                ScvAtendimento.Panel.Height = PnlAtendimento.Height
               
                CarregaCamposEpilacao
               
            else If mTipo_Procedimento = "Sobrancelhas" Then
                ScvAtendimento.Panel.LoadLayout("sobrancelhas")
                ScvAtendimento.Panel.Height = PnlAtendimento.Height
               
                CarregaCamposSobrancelhas
               
            Else
               
                Activity.Finish
               
            End If
           
            If  row(res.Columns.Get("CL_Foto")) <> Null Then
                ImgCL_Foto.Bitmap = CreateRequest.BytesToImage(row(res.Columns.Get("CL_Foto")))
            End If
           
        End If
       
    Else
       
        Log("ERROR: " & j.ErrorMessage)
       
    End If
   
    j.Release
   
End Sub

***************************
File: config.properties

sql.select_agenda=Select agenda.*,cliente.CL_Nick,cliente.CL_Foto,procedimento.PC_Nome,procedimento.PC_Tipo,procedimento.PC_Valor From agenda Left Join Cliente On Agenda.AG_Cod_Cliente = Cliente.CL_Cod_Cliente Left Join procedimento On agenda.AG_Cod_Procedimento = procedimento.PC_Cod_Procedimento Where agenda.AG_Cod_Lancamento = ?

****************************

 

[Erel]

The blob size is probably very large. Maybe you need to decrease the image size.

 

[KMatle]

What I do:

- mostly you don't need the original Image, so reduce it's size to e.g. 800x600 with 80% (JPEG) = 100-300 KB per image
- take the search how to do that
- I only use LONG or MEDIUMTEXT as a Format (more than enough)
- I convert the reduced image to a Base64 string which add's about 100 KB to the size
- benefit: very easy to handle (when you exchange data between platforms)

Another option

- usually you do not store large data in a db (only the index)
- the image itsself can be stored as a file on the server (encrypted if you like)

 

[Anderson Linhares]

Could you guide me in how to proceed to make this reduction of the image?
Here is the code for how I am capturing the image from the camera and writing to the database:

Sub PrepareSurface As ResumableSub
    SetState(False, busystate, VideoMode)
    'sizes can be modified here
    If VideoMode Then
        cam.PreviewSize.Initialize(640, 480)
        'Using a temporary file to store the video.
        Wait For (cam.PrepareSurfaceForVideo(MyTaskIndex, VideoFileDir, "temp-" & VideoFileName)) Complete (Success As Boolean)
    Else
        cam.PreviewSize.Initialize(544, 416)
        Wait For (cam.PrepareSurface(MyTaskIndex)) Complete (Success As Boolean)
    End If
    If Success Then cam.StartPreview(MyTaskIndex, VideoMode)
    SetState(Success, busystate, VideoMode)
    Return Success
End Sub

Sub TakePicture
    Try
        SetState(openstate, True, VideoMode)
        Wait For(cam.FocusAndTakePicture(MyTaskIndex)) Complete (Data() As Byte)
        SetState(openstate, False, VideoMode)
        Dim bmp As Bitmap = cam.DataToBitmap(Data)
        If frontCamera Then
            bmp = bmp.Rotate(270)
        Else
            bmp = bmp.Rotate(90)
        End If
      
        Log("Picture taken: " & bmp)
        pnlBackground.SetVisibleAnimated(100, True)
        pnlPicture.SetBackgroundImage(bmp.Resize(pnlPicture.Width, pnlPicture.Height, True)).Gravity = Gravity.CENTER
        Sleep(4000)
        pnlBackground.SetVisibleAnimated(500, False)
      
        atendimento.mFoto = bmp
      
    Catch
        HandleError(LastException)
    End Try
  
End Sub

Sub Activity_Resume
    If ImgMT_Imagem.IsInitialized And mFoto.IsInitialized And mTipo_Foto = "Procedimento" Then
        ImgMT_Imagem.Bitmap = mFoto
    End If
    If ImgCL_Foto.IsInitialized And mFoto.IsInitialized And mTipo_Foto = "Cadastro" Then
      
        ImgCL_Foto.Bitmap = mFoto
      
        Dim mImagem() As Byte
        If ImgCL_Foto.Bitmap = Null Then
            mImagem = Null
        Else
            mImagem = CreateRequest.ImageToBytes(ImgCL_Foto.Bitmap)
        End If
          
        Dim cmd1 As DBCommand = CreateCommand("update_cliente", Array(mImagem, mCod_Cliente))
        Dim j1 As HttpJob = CreateRequest.ExecuteBatch(Array(cmd1), Null)
        Wait For(j1) JobDone(j1 As HttpJob)
        If j1.Success Then
            ToastMessageShow("Foto Atualizada !!!",False)
        Else
            ToastMessageShow("Erro ao Gravar Foto !!!",False)
        End If
        j1.Release
    End If
    mTipo_Foto = ""
  
End Sub

Should I change the BLOB field to LONG? How will I convert and save the image in MySQL and also how will I display it when loading from the database?

 

[Erel]

What is the output of

Log("length: " & mImagem.Length)

?

 

[Anderson Linhares]

Start success: true
(Rect)(0, 0, 5328, 3000)
Picture taken: (Bitmap): 1080 x 1920, scale = 1,00
** Activity (cameraaparelho) Pause, UserClosed = true **
Ignoring event: camera_previewcapturecomplete
** Activity (atendimento) Resume **
length: 663441

 

[Erel]

This means that each image is about 1MB. What are your requirements about the picture size?

You can for example change your code to:

 mImagem = CreateRequest.ImageToBytes(ImgCL_Foto.Bitmap.Resize(360, 640, True)

It will make the data size much smaller.

 

[Anderson Linhares]

I applied Resize (360, 640, True) to the correct line and it looks like it's working correctly, I'll do new tests with other images.
Thank you very much.