Android Question Load Database into the ListView

[Matteo Granatiero]

I have a database made up of the table: Clothes made up of about 30 records. Each record contains an image (blob).
I would like to load in the listview only the records that contain the word "Jeans" (under column number 2); inside the listview.
This is the code I use to insert in the database :

Private Query As String

'we check if all fields are filled
    If edtBrand.Text = "" Or edtPrezzo.Text = "" Then
        edtBrand.Text = "Nessuno"
         edtPrezzo.Text="Nessuno"
        Return
    End If
   
    'convert the image file to a bytes array
        Dim InputStream1 As InputStream
        InputStream1 = File.OpenInput(File.DirRootExternal, "BetaImage.jpg")
        Dim OutputStream1 As OutputStream
        OutputStream1.InitializeToBytesArray(1000)
        File.Copy2(InputStream1, OutputStream1)
        Dim Buffer() As Byte
        Buffer = OutputStream1.ToBytesArray
   
        Query ="INSERT INTO Clothes VALUES ('" & univoco & " - " & name & "','" & spnCategoria.SelectedItem & "','" & spnColore.SelectedItem & "','" & spnEvento.SelectedItem & "','" & spnStagione.SelectedItem & "','" & spnTaglia.SelectedItem & "','" & edtBrand.Text & "','" & edtPrezzo.Text & "'," & " ?" & ")"
        SQL1.ExecNonQuery2(Query,Array As Object (Buffer))

 

[DonManfred]

You should use parametrized queries.

 

[Matteo Granatiero]

You should use parametrized queries.

which means I don't understand

 

[DonManfred]

I suggest to learn the language.
https://www.b4x.com/android/help/sql.html#sql_execquery2

Read Klaus booklets. I´m sure there are SqLite Episodes in it...

 

[emexes]

Another approach to storing images is to save them as regular files, and just store the image filename in the database.

Upside is simplicity and ease of debugging: you can browse the images with whatever file manager your OS provides, and you can reasonably load the database into Excel or whatever.

Downsides are: copying a database is now more than just copying one file, and harder to use over a network connection (although with some intelligent management, it could also be quicker, if the image files are stored (ie: cached) locally).

 

[Matteo Granatiero]

SOLVED

Sub carica
    Dim Cursor1 As Cursor
    Dim descrizione As String
    Cursor1 = SQL1.ExecQuery2("SELECT * FROM Vestiti WHERE Categoria = ?", Array As String("Jeans"))
   
For i=0 To Cursor1.RowCount-1
   
        Cursor1.Position=i
       
        Dim Buffer() As Byte
        Buffer = Cursor1.GetBlob("Immagine")
        Dim InputStream1 As InputStream
        InputStream1.InitializeFromBytesArray(Buffer, 0, Buffer.Length)
        Dim Bitmap1 As Bitmap
        Bitmap1.Initialize2(InputStream1)
        InputStream1.Close

        descrizione =Cursor1.GetString("Colore") & CRLF
        descrizione= descrizione & Cursor1.GetString("Evento") & CRLF
        descrizione=descrizione & Cursor1.GetString("Stagione") & CRLF
        descrizione =descrizione & Cursor1.GetString("Taglia") & CRLF
        descrizione=descrizione & Cursor1.GetString("Brand") & CRLF
        descrizione=descrizione & Cursor1.GetString("Prezzo") & CRLF
       
        ListView1.AddTwoLinesAndBitmap("",CRLF & descrizione,Bitmap1)
Next
    Cursor1.Close
   
End Sub