B4J Question ExecNonQuery result

[Douglas Farias]

Hi all
how can i get the result of this?

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?

 

[Douglas Farias]

fixed with try and catch

 

[cimperia]

You may want to ensure that your table was really updated :

 SELECT Changes() from yourTable

The value returned is the number of rows affected. Really useful during development.

 

[Douglas Farias]

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

try
ExecNonQuery
catch
log("any error here, errors on db too, if cant update, or conection problems")
end try

 

[jmon]

I think the correct way to do it is with transactions :

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:

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]

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]

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 :

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]

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]

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]

My mistake, I had not realised it was strictly a B4J discusdion

 

[jmon]

My mistake, I had not realised it was strictly a B4J discusdion

no problem! I think sq.TransactionSuccessful closes the transaction itself.