Android Question [DBCommand] CreateCommand ...

Status
Not open for further replies.

T201016

Active Member
Licensed User
Longtime User
#SQL COMMANDS
sql.update_customers=UPDATE customers SET CustomerId = ?, FirstName = ?, LastName = ?, Company = ?, Address = ?, Email = ? WHERE rowid = ?
Module Class --- > ShowDialog():
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

Expert
Licensed User
Longtime User
1)
B4X:
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)
B4X:
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.
 
Upvote 0

T201016

Active Member
Licensed User
Longtime User
Hi, OliverA!
You are right about this single element, I have to implement it in my head :)
Thanks alot
 
Upvote 0

CR95

Active Member
Licensed User
May I reopen this thread as it seems I have a similar issue ?

I get the correct id if I execute
B4X:
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
B4X:
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
B4X:
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
 
Upvote 0
Status
Not open for further replies.
Top