Android Question SQLITE BLOB BY code

[Guenter Becker]

Hello Experts, hope youre all well,

I like to transfer the data of an b4x page input mask via MAP to a datanavigation class which is just under development by me. Inside the class I check if the transfered values are correct or not (not null etc.). If yes I build the SELECT/INSERT/UPDATE Sql as a string variable and execute it with SQL.ExecNonQuery. I know how to build the Sql string if the values are text or numeric but how to do it if the target database field is a BLOB (Images).

Anyone here who is exeperienced for this? Can you help me what to do? Short code snipped would be useful.

Thank you and best regards in advance and stay well
Guenter

 

[toby]

Sub InsertBlob
    Dim Buffer() As Byte = File.ReadBytes(File.DirAssets, "smiley.gif")
    'write the image to the database
    SQL1.ExecNonQuery2("INSERT INTO table2 VALUES('smiley', ?)", Array As Object(Buffer))
End Sub

 

[Guenter Becker]

Thank you that's looking good.
Going to try if it is possibler to use a map insetad of the array.
Problem with the solution above is that I build the sql by code therefore the usage of NonQuery2 is not possible because by code I can not append and array in the second part of the formula. Any Idea to use NonExec1?

Stay well, best regards from Germany Guenter

 

[drgottjr]

it's the same as execnonquery2 but spelled out and without the "?"'s: insert into table2 values('smiley', jksljf-9eugh93lb98484882$%&*(#@DFJTJJEJJEJEJEJEJOTJJ99059dd9jhhkjhkhlq!@^$$RHHDFEDJjckflfkjflkjvlherhg;cv;kljf;sdrfj). good luck with that.

your reason for not using nonquery2 doesn't make any sense. we all build sql "by code". perhaps you could explain a little more. the guy who invented b4a, one of the main experts on the forum, and a well known member all indicated above that it was the way to go. it would be interesting to learn why you think it's not.

 

[Guenter Becker]

I am just developing an Datanavigator control. The control may be used with the designer and deals with several buttons to move between the records and to activate data insert, update and delete. The values to be stored in the database are comming from input fields (by example EditText) in the layout of an activity or b4x page.
For example:

Sub UpdateRecord_Click

    Try

        '1 ## get values from the input mask by event in the activity/page

        '  ## store the values in DBNValueMap(Fieldname, Value)

        If SubExists(mCallBack, mEventName & "_GetValues") = True Then

        ' get col name,type and value

            CallSub2(mCallBack,mEventName & "_GetValues",DBNDataSet

        End If

        '2 ## check all column values

        Dim tempColumnInfo As ResultSet

        Dim Error As Boolean = False

        Dim checkUnique As Boolean =True

        Dim ColumnType As String

        Dim SQLstring As String

        Dim ColumnNames As String

        Dim ColumnValues As String

       

        ToastMessageShow("Prüfe eingegebene Werte....",True)

       

        For Each ColumnName As String In DBNValueMap.Keys

            ' get the column info

            tempColumnInfo = getColumnInfo(ColumnName)

            tempColumnInfo.Position = 0

            ' check if null values allowed and if input field has a value

            If tempColumnInfo.Getstring("col_not_null") = True Then

                If DBNValueMap.Get(ColumnName) = Null Then

                    xui.Msgbox2Async( _

                        "Keine Eingabe in dem einem Pflichtfeld!" & CRLF & _

                        "Funktion wird abgebrochen.","Eingabefehler " & ColumnName,"OK","","",IcoAttention)

                        Error = True

                    Exit

                End If

            End If

            ' check if the value must be unique

            If DBNNewRecord = False Then

                ' for update check if unique check is nessessary

                ColumnType = tempColumnInfo.Getstring("col_type")

                Select ColumnType

                    Case "text"

                        If DBNDataSet.GetString(ColumnName) = DBNValueMap.Get(ColumnName) Then checkUnique = False

                    Case "Int"

                        If DBNDataSet.GetInt(ColumnName) = DBNValueMap.Get(ColumnName) Then checkUnique = False

                End Select

            End If

            If checkUnique = True And DBNValueMap.ContainsKey(ColumnName & "_unique") = True Then

                If checkIsUnique(ColumnName) = True Then

                    xui.Msgbox2Async( _

                        "Eingabe bereits gespeichert!" & CRLF & _

                        "Der Wert muss eindeutig sein," & CRLF & _

                        "Funktion wird abgebrochen.","Eingabefehler " & ColumnName,"OK","","",IcoAttention)

                    Error = True

                    Exit

                End If      

            End If

        Next

       

        '3 ## if new record perform insert

        If Error = False And DBNNewRecord = True Then

            ToastMessageShow("Füge Daten hinzu....",True)

            ' bulid colum name part

            ColumnNames = "("

            For Each ColumnName As String In DBNValueMap.Keys

   

                ColumnNames = ColumnNames & ColumnName & ","

            Next

            ' strip last comma off

            ColumnNames = ColumnNames.SubString2(0,ColumnNames.Length-1) & ")"

            ' build values part

            ColumnValues = "("

            For Each ColumnName As String In DBNValueMap.Keys

                ColumnType = tempColumnInfo.Getstring("col_type")

              Select ColumnType

                    Case "text"

                        ColumnValues = ColumnValues & "'" & DBNValueMap.Get(ColumnName)& "',"

                    Case "Int"

                        ColumnValues = ColumnValues & DBNValueMap.Get(ColumnName)& ","

                End Select

            Next

            ' strip last comma off

            ColumnValues = ColumnValues.SubString2(0,ColumnNames.Length-1) & ")"

            ' perform dtatabase insert

            DBNSQL.ExecNonQuery("INSERT INTO " & DBNTableName & ColumnNames & " VALUES" & ColumnValues)

           

            ToastMessageShow("Daten erfolgreich hinzugefügt.",True)

           

            ' set record pointer to last record

            DBNCurrentIndex = DBNDataSet.rowcount-1

           

            ' fire event

            If SubExists(mCallBack, mEventName & "_CurrentIndexChanged") = True Then

                CallSub2(mCallBack,mEventName & "_CurrentIndexChanged",DBNDataSet)

            End If

        '4 ## perform update

        Else if Error = False And DBNNewRecord = False Then

            ToastMessageShow("Speichere die Änderungen....",True)

            ' bulid colum name part

            For Each ColumnName As String In DBNValueMap.Keys

                ColumnType = tempColumnInfo.Getstring("col_type")

       [COLOR=rgb(65, 168, 95)]         Select ColumnType

                    Case "text"

                        ColumnValues = ColumnValues & ColumnName & "= '" & DBNValueMap.Get(ColumnName)& "',"

                    Case "Int"

                        ColumnValues = ColumnValues & ColumnName & "=" & DBNValueMap.Get(ColumnName)& ","

                End Select[/COLOR]

            Next

            ' strip last comma off

            ColumnValues = ColumnValues.SubString2(0,ColumnNames.Length-1) & ")"

            ' get rowid

            SQLstring = "UPDATE " & DBNTableName & "SET " & ColumnValues & "WHERE rowid=" & DBNCurrentRowid

            DBNSQL.ExecNonQuery(SQLstring)

           

            ToastMessageShow("Änderungen erfolgreich gespeichert.",True)  

        End If

        ' reset new record flag

        DBNNewRecord = False

        ' release resultset

        tempColumnInfo.close

    Catch

        If DBNNewRecord = True Then

            ToastMessageShow("Neue Daten konnten nicht gespeichert werden!",True)

        Else

            ToastMessageShow("Änderungen konnten nicht gespeichert werden!",True)

        End If

        xui1.Msgbox2Async(LastException,"Fehler: DBN002","OK","","",IcoError)

    End Try

End Sub

' ## DBNav managed database, save button clicked
Sub DBNav1_GetValues(DS As ResultSet)
    DBNav1.DBNValueMap.put("Title", EditTextTitel.text)
    DBNav1.DBNValueMap.put("Title_unique", EditTextTitel.text) 'report column info -is unique-
    DBNav1.DBNValueMap.put("Text_1", EditTextTitel.text)
End Sub

As you see what I need is a solution for a BLOB field as part oft the select /case statement to build the sql string part for the data column.

By the way I know that this code can be optimized but this is a part of the work after having the code basically workinmg for all kind of sql datatypes.

Greetings Guenter

 

[drgottjr]

nothing has changed (i did look at all 257 lines). oddly, there is no mention of a blob, so i'm guessing you're thinking about adding such a column to the db. (i say that because the 2 select/case blocks you show only allow for "text" and "int" data types. no hint of a blob.) again, nothing changes.

what you did at line 163 could easily be accomplished with execnonquery2, as recomended. the addition of a blob column will not change that. you could also do it with execnonquery, as you have already done. unclear why you ask if the insert could be done with execnonquery when, in fact, you already use it.

still no explanation that i could see as to why you cannot use execnonquery2.

everyone uses text input views to capture data from the user to insert/update a db. nothing unusual about your project in that regard.

where's the blob? what's the schema?

 

[Guenter Becker]

Ok may be my mistake due to unclear description.

First yes you are on the right page all my questiones are targeting the work to enhance the select statements to all possible sql datatype to build the sql string.
I think our misunderständig is our different viewpoint. You are absolut correct in using executenonquery or executenonquery2! I do it either.

If you have an acticvity where you know how many input fields and their types are neccessary to build the sql its quite easy to build the sql string fixed for that sepcial activity.
But mention I told you that my task is to develop a control to be used by different activities with different layouts and therefore in the controls code I cannot build a specific fixed sql string because it may be one activity has 2 input fields and another activity has three or more and so on. The control has to cover all this recomendations from the different activities!

With executenonquery I use a single sqlstring being dynamicaly build depending on the changing situation of the activity using the control. If I would use executenonquery2 I could also build dynamically the first sql part but how to deal with the second? executenonquery2("UPDATE Table (Col1,Col2,Col3) SET(?,?,?)",..) thats easy but the second part .... ,Array as () that is my problem because I must build it also dynamically based on the among of fields and datatypes.

For several weeks I am now looking for a solution and may be I am moving in a circle not to seeing the "tree in the wood".
Sorry for grammar mistakes but I am German and I hope to make myself clear to you.
Best greetings Guenter

 

[DonManfred]

Even with ExecNonQuery2 you can create it Dynamically without Problem!
1. Create a sql-string with the questionmarks.
2. Instead of Array as Object(.....) you can just put a LIST here which can be created dynamically based on the different values needed.
ExecNonQuery2 (sqlstring, yourlist)

 

[Guenter Becker]

Even with ExecNonQuery2 you can create it Dynamically without Problem!
1. Create a sql-string with the questionmarks.
2. Instead of Array as Object(.....) you can just put a LIST here which can be created dynamically based on the different values needed.
ExecNonQuery2 (sqlstring, yourlist)

That is the way tried it by using a List and this is the solution. Thank you.