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:

[CODE lang="b4x" title="Control Code buidling and accessing SQL Insert/Update"]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")

Select ColumnType

Case "text"

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

Case "Int"

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

End Select

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[/CODE]

[CODE lang="b4x" title="Event Codeblock in the Activity/b4x page"]' ## 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[/CODE]
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.