B4J Question SQL Object - ExecuteNonQuery

[RickV]

I am using a MySQL database, but thats is sort of irrelevant as I am asking about the SQL objects methods.

SQL Object - ExecuteNonQuery

How do I determine after using this iff any records have been changed - programatically, without have to do a search and determine the result.

Dim Qry as string
Dim Sql as SQL

Qry="update sometable set somefgield=1"
SQL.ExecuteNonQuery(Qry)

Back in vb6 days
rslt=0
Sql.execute Qry, rslt
rslt = number of recods affected

I would like to try and use the following logic after the query

If rslt >0 then
......
else
.....
end if

 

[Mahares]

How do I determine after using this iff any records have been changed

In SQLite there is the Chane() function, used immediately after UPDATE, INSERT DELETE:
SELECT Changes()
It determines the number of rows affected. Is this what you are looking for.

 

[RickV]

I am not using sql lite, I am using a driver direct to mysql, and gaining a connection from a pool thread safe.

 

[OliverA]

How do I determine after using this iff any records have been changed - programatically, without have to do a search and determine the result.

You would need to use some JavaObject to
1) Get the Connection object from the SQL object
2) Create a Statement object from the Connection object in step#1
3) Run the Statement's execute method with the query text you are trying to execute
4) Run the Statement's getUpdateCount method to retrieve the # of records produced

Something like this

Dim jConnection as JavaObject = SQL.As(JavaObject).GetField("connection")
Dim jStatement as JavaObject = jConnection.RunMethod("createStatement", Null)
jStatement.RunMethod("execute", Array(Qry))
Dim affectedRecords as int = jStatement.RunMethod("getUpdateCount", Null)
jStatement.RunMethod("close", Null)
Log(affectedRecords)

Note 1: SQL and Qry variable names taken from post #1
Note 2: Untested code (it's only been typed into the forum here)
Note 3: Some of the proper functionality of various JDBC methods are very driver dependent and therefore may not work for all JDBC drivers and the underlying databases
Note 4: There is some ambiguity in the 0 that is returned by getUpdateCount. See: https://stackoverflow.com/questions/17544782/how-to-tell-number-of-rows-changed-from-jdbc-execution
Note 5: Don't use with SQLite, since B4X's SQL library takes care of some locking to allow for multiuser access of SQLite

Sources:
1) https://github.com/AnywhereSoftware...SQL/src/anywheresoftware/b4j/objects/SQL.java
2) https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getUpdateCount()

 

[RickV]

Thank OlivaA, I will play around with the code later today and see how we go ☺

 

[amykonio]

The following sql command:

SELECT ROW_COUNT()
from dual;

executed right after delete, insert, update should return that information.

Andreas.