B4J Question ExecNonQuery result

Douglas Farias

Expert
Licensed User
Hi all
how can i get the result of this?

B4X:
Main.sql1.ExecNonQuery("update servidor.apps set isenable_notification = 'true' where app_name = '"&lstapps.SelectedItem&"'")
i tryed use Main.sql1.ExecQueryAsync but i cant use update with ExecQueryAsync, how to know if this really have update my db with no errors?
 

cimperia

Active Member
Licensed User
You may want to ensure that your table was really updated :
B4X:
 SELECT Changes() from yourTable
The value returned is the number of rows affected. Really useful during development.
 

Douglas Farias

Expert
Licensed User
i know that, but i cant make every update a select, this make my server down with 10.000 users online.
i need update and already get the result.

but already fixed, i can use try and catch
ExecNonQuery
B4X:
try
ExecNonQuery
catch
log("any error here, errors on db too, if cant update, or conection problems")
end try
 

jmon

Well-Known Member
Licensed User
I think the correct way to do it is with transactions :
B4X:
sq.BeginTransaction
Try
    sq.ExecNonQuery2("INSERT INTO table1 VALUES(?, ?, 0);", Array("Sunday", "Monday"))
    sq.TransactionSuccessful
Catch
    If LastException.IsInitialized Then
        Log(LastException.Message)
    End If
End Try
and if you use connectionPool:
B4X:
Dim sq As SQL = Pool.GetConnection
sq.BeginTransaction
Try
    sq.ExecNonQuery2("INSERT INTO table1 VALUES(?, ?, 0);", Array("Sunday", "Monday"))
    sq.TransactionSuccessful
Catch
    If LastException.IsInitialized Then
        Log(LastException.Message)
    End If
End Try
sq.Close
 

cimperia

Active Member
Licensed User
I am not sure about the second code fragment, but in the first it looks to me that EndTransaction is missing and therefore your changes are not committed to the database.
Correct me if I am wrong.

I would too include the beginTransaction in the Try block.
 

jmon

Well-Known Member
Licensed User
no, this is the way you end a transaction, there is no endTransaction.

The second method, with the connectionPool, is in my opinion the best when multiple persons connect directly to a database.

Edit:
you could write example 1 like that with a for loop for example :
B4X:
sq.BeginTransaction
Try
    for i = 1 to 10
        sq.ExecNonQuery2("INSERT INTO table1 VALUES(?, ?, 0);", Array(i, "Monday"))
    next
    sq.TransactionSuccessful
Catch
    If LastException.IsInitialized Then
        Log(LastException.Message)
    End If
End Try
 

cimperia

Active Member
Licensed User
There definitely is an EndTransaction method as described in the SQL doc. and I use it all the time in my own code. What I had not realised is that TransactionSuccessful committed the changes? If you try to begin a transaction while another one is open, it 'll generate an exception.

I agree with your statement about connectionPool.
 

jmon

Well-Known Member
Licensed User
here I'm just talking about the way B4J works, so in B4J there is no endTransaction, at least not that I know.

If you try to begin a transaction while another one is open
with ExecNonQuery2 you can start multiple transactions at the same time, because it's not assync. it's one after the other.
 

cimperia

Active Member
Licensed User
My mistake, I had not realised it was strictly a B4J discusdion:confused:
 

jmon

Well-Known Member
Licensed User
Top