Android Question [DBCommand] CreateCommand ...

[T201016]

#SQL COMMANDS
sql.update_customers=UPDATE customers SET CustomerId = ?, FirstName = ?, LastName = ?, Company = ?, Address = ?, Email = ? WHERE rowid = ?

Private Sub ShowDialog(Item As Map, RowId As Long, Action As Int)
    ...
   
    Dim params As List
    params.Initialize
   
    'keys based on the template json file
    params.AddAll(Array As String(id, Item.Get("FirstName"), Item.Get("LastName"), Item.Get("Company"), Item.Get("Address"), Item.Get("Email")))
   
    Dim req As DBRequestManager = CreateRequest
    Dim cmd As DBCommand = CreateCommand("update_customers", Array As Object(params.Get(0), params.Get(1), params.Get(2), params.Get(3), params.Get(4), params.Get(5), RowId))
    Dim j As HttpJob = CreateRequest.ExecuteBatch(Array(cmd), Null)
    Wait For (req.ExecuteCommand(cmd,Null)) JobDone(j As HttpJob)
    If j.Success Then
        TMS.TMessage1_Show("Transaction Successful")
    Else
        Log("ERROR (7): " & j.ErrorMessage)
        TMS.TMessage3_Show( j.ErrorMessage)
    End If
    j.Release
   
    ...
End Sub

Sub CreateCommand(Name As String, Parameters() As Object) As DBCommand
    Dim cmd As DBCommand
    cmd.Initialize
    cmd.Name = Name
    If Parameters <> Null Then cmd.Parameters = Parameters
    Return cmd
End Sub

Hi,
Well, I encountered an unexpected situation that when using the code:
Dim cmd As DBCommand = CreateCommand ("update_customers", Array As Object (params))
the data was not updated in the database at all.

Only after changing the code to:
Dim cmd As DBCommand = CreateCommand ("update_customers", Array As Object (params.Get (0), params.Get (1), params.Get (2), params.Get (3), params.Get (4), params.Get (5), RowId))

all data changes were correctly saved in the database.

Is there a better solution for substituting parameters for "params"?

Thank you in advance for your suggestion ...

 

[OliverA]

1)

Dim cmd As DBCommand = CreateCommand("update_customers", Array(id, Item.Get("FirstName"), Item.Get("LastName"), Item.Get("Company"), Item.Get("Address"), Item.Get("Email")))

or more verbose
2)

Dim params() As Object = Array(id, Item.Get("FirstName"), Item.Get("LastName"), Item.Get("Company"), Item.Get("Address"), Item.Get("Email"))
Dim cmd As DBCommand = CreateCommand("update_customers", params)

Note:
Array As Object (params) where params is a List is essentially a single element Array whose first and only element is a List. The outcome you were experiencing is to be expected.

 

[T201016]

Hi, OliverA!
You are right about this single element, I have to implement it in my head
Thanks alot

 

[CR95]

May I reopen this thread as it seems I have a similar issue ?

I get the correct id if I execute

Dim cmd As DBCommand = CreateCommand("select_artistid", Null)

with this line in the "config.properties" file :

sql.select_artistid=SELECT rowid FROM artiste WHERE ArtisteName = "Adamo";

But is I follow the recommendation of Oliver

Dim params() As Object = Array("Adamo")
Dim cmd As DBCommand = CreateCommand("select_artistid", params)

with

sql.select_artistid=SELECT rowid FROM artiste WHERE ArtisteName = ?;

it returns a bad rowid.
The Log in B4J Server shows it received

Exécute: SELECT rowid FROM artiste WHERE ArtisteName = ?;

My understanding is that the question mark in the command received by B4J Server should be replaced by "Adamo"
Something is wrong in my parameters list in B4A but what ?
Thanks for the help

 

[Erel]

May I reopen this thread as it seems I have a similar issue ?

No. There should only be a question in the very first post.