B4J Question Array as Object( )

T201016

Active Member
Licensed User
Longtime User
I usually use the function below, and I don't really understand why it DOESN'T ALWAYS work correctly when
my setting is this: SQL.ExecNonQuery2(sb.ToString, (args)) - then all entries to the database are correct.
However, I will change the code to: SQL.ExecNonQuery2(sb.ToString, Array As Object(args))
The content of each field is correct, but the changes are not saved to the database, i.e. the previous values remain.

So far I thought that using an array with objects was safer, precisely because of the diversity of data (integer, string, ...)
- what's worse is that in both cases I don't get an error message?!


Code: Copy
' updates multiple fields in a record
' in the Fields map the keys are the column names
Public Sub UpdateRecord2(SQL As SQL, TableName As String, Fields As Map, WhereFieldEquals As Map) As ResumableSub
    If WhereFieldEquals.Size = 0 Then
        #IF Not(DBUTILS_NOLOGS)
        Log("WhereFieldEquals map empty!")
        #End If
        Return False
    End If
    If Fields.Size = 0 Then
        #IF Not(DBUTILS_NOLOGS)
        Log("Fields empty")
        #End If
        Return False
    End If
    Dim sb As StringBuilder
    sb.Initialize
    sb.Append("UPDATE ").Append(EscapeField(TableName)).Append(" SET ")
    [B]Dim args As List[/B]
    args.Initialize
    For Each col As String In Fields.Keys
        sb.Append(EscapeField(col)).Append(" = ?")
        sb.Append(",")
        args.Add(Fields.Get(col))
    Next
    sb.Remove(sb.Length - 1, sb.Length)
    sb.Append(" WHERE ")
    For Each col As String In WhereFieldEquals.Keys
        sb.Append(EscapeField(col)).Append(" = ?")
        sb.Append(" AND ")
        args.Add(WhereFieldEquals.Get(col))
    Next
    sb.Remove(sb.Length - " AND ".Length, sb.Length)
    #IF Not(DBUTILS_NOLOGS)
    Log("UpdateRecord: " & sb.ToString)
    Log("Size: "&args.Size)
    Log("Name: "&args.Get(0))
    Log("ID:   "&args.Get(16))
    #End If
    SQL.BeginTransaction
    Try
        #IF Not(DBUTILS_NOLOGS)
        Log(args.Get(0))
        #End If
        SQL.ExecNonQuery2(sb.ToString, (args)) '---------- OK!
        SQL.TransactionSuccessful
        Return True
    Catch
        #IF Not(DBUTILS_NOLOGS)
        Log(LastException.Message)
        #End If
        #If B4i OR B4J
        SQL.Rollback
        #End If
    End Try
    Return False
End Sub
 
You are not using Wait For, so why do you set the sub to return As ResumableSub?
Change it to Boolean.
 
Upvote 0
Err, it's correct.

Maybe try adding a Log to check whether your SQL query is correct. Maybe the WHERE conditions not met.
You gave me an idea in thread #4 and I discovered a bug that caused the record ID to receive a negative value, specifically -1. Which resulted in the record not being updated. Thanks, everything should go smoothly :)
 
Upvote 0
Back
Top