B4J Question ExecNonQuery2 and Smart String Literals

[LDeeJay]

Hi all,

I need to update records in a database with ExecNonQuery2. But the values in the array need to be enclosed in quotes.
My query:

sql.ExecNonQuery2("UPDATE parts SET PartID = ? WHERE PartID = ?",Array("'" & row(1) & "'", "'" & row(0) & "'")) '<-- this doesn't work

The query should look like:

UPDATE parts SET PartID = 'newvalue' WHERE PartID = 'oldvalue'

I can't figure out how to combine the sql query with the smart string literals.

sql.ExecNonQuery2($"UPDATE parts SET PartID ='?' WHERE PartID = '?'"$,Array(row(1),row(0))) '<-- this doesn't work either
sql.ExecNonQuery2("UPDATE parts SET PartID = ? WHERE PartID = ?",Array($"'${row(1)}'"$,$"'${row(0)}'"$)) '<-- nor does this

When I log the query in all cases I see

UPDATE parts SET PartID = ? WHERE PartID = ?

so the variables are not communicated (something to do with ExecNonQuery2 and quotes?).

I can work around it by building the query as a string first and then using ExecNonQuery instead of ExecNonQuery2 but I can't stand it I can't get to grips with the Smart String Literals (apparently I'm not smart enough ;-) )

Any nudge in the right direction is much appreciated.

B4J Version: 9.5
Java Version: 11
#AdditionalJar: sqlite-jdbc-3.7.2
DBUtils Version: 2.11

 

[Erel]

sql.ExecNonQuery2($"UPDATE parts SET PartID =? WHERE PartID = ?"$,Array(row(1),row(0)))

Everything else is completely wrong.

 

[OliverA]

Parameterized queries take care of the quoting. The proper setup is as simple as @Erel shows you. No self-quoting necessary.

 

[LDeeJay]

sql.ExecNonQuery2($"UPDATE parts SET PartID =? WHERE PartID = ?"$,Array(row(1),row(0)))

Everything else is completely wrong.

It does work.....
But the log still shows

UPDATE parts SET PartID = ? WHERE PartID = ?

Slightly confusing....

 

[roerGarcia]

The parameters ? and ? they are replaced by the values included in the sql call internally. --> Array(row(1),row(0)))

Maybe the manual or help explains it better.

 

[LDeeJay]

Never mind.... the log shows exactly what it's asked to show...
Just one of these Monday mornings..... ?

 

[73Challenger]

+1 for parameterized queries as @Erel has described above. I prefer separate the steps. There may be better ways to do it, this is just my way. For me, it's easier and more maintainable to break up each step into a function.

In the code snippet below, I've pared down the number of columns for brevity. In reality I have 25+ columns. There is no way I could maintain the code building it like you have done above with 25 columns (sure, maybe I need to normalize my tables a little...that is another discussion). Much easier to maintain and troubleshoot....in my opinion (you know what they say about opinions ). You *Could* change/validate your data in the data array builder (add tics/null checks etc.) but I'm guessing this is not best practice. The data should be validated when it goes into the Type object (GameRow in this case). Before the call to update the database.

Additional benefit to this method is, it's much easier to change this structure if you add remove columns from the data table. Sure you have to change it in 3 places, but also, you don't have to spend 1/2 a day finding the syntax errors in a massively long function call when you make a change.

The way I implement parameterized queries it is as follows. It requires....
1. A Type object that is the same structure as your data row (Type GameRow in the code snippit below)
2. A SQL String builder (GetUpdateGameRecordString in the snippit below) to get the update string
3. A data array builder (GetUpdateGameRecordArray in the snippit below) to build the data array
****Then just call ExecNonQuery2 with the objects

Hope this helps.

' This example uses a datatype object for row mapping
Type GameRow(rowId As Int, playResult as String, playScore as Int)

'Accept new values, build type object and update the db
Private Sub UpdateRow(rowId as Int, playResult as String, playScore as Int)

    Dim tempRow as GameRow
    tempRow.Initialize
    tempRow.rowId = rowId
    tempRow.playResult = playResult
    tempRow.playScore = playScore

    UpdateGameRecord(tempRow, "GameTable")
 
End Sub

Private Sub UpdateGameRecord(gr As GameRow, tblName As String)
 
    Dim sqlString As String = GetUpdateGameRecordString(tblName)
    Dim dataArray() As String = GetUpdateGameRecordArray(gr)
 
    B4XPages.MainPage.SQLGame.BeginTransaction
    Try
        'Execute the sql statements.
        B4XPages.MainPage.SQLInstance.ExecNonQuery2(sqlString, dataArray)
        B4XPages.MainPage.SQLInstance.TransactionSuccessful
    Catch
        log(LastException.Message)
    End Try
    B4XPages.MainPage.SQLGame.EndTransaction

End Sub

Private Sub GetUpdateGameRecordString(tblName As String) As String
    Private sb As StringBuilder
    sb.Initialize
 
    sb.Append("UPDATE ")
    sb.Append(tblName)
    sb.Append(" Set ")
    sb.Append("playResult = ?,")
    sb.Append("playScore = ? ")
    sb.Append("WHERE rowId = ?")
 
    Return sb.ToString
 
End Sub

Private Sub GetUpdateGameRecordArray(gr As GameRow) As String()
 
    Dim tempArray(3) As String
 
    tempArray(0) = gr.playResult ' You could tweak/validate data here (add tics, null checks etc.) but probably bad practice.
    tempArray(1) = gr.playScore  ' Data should be good when it goes into the GameRow type object, not on the the database update call.
    tempArray(2) = gr.rowId
 
    Return tempArray
 
End Sub