B4J Question MySQL retrieve attributes from ResultSet

labcold

Member
Licensed User
I am using mysql-connector-java-5.1.48-bin [B4J 8.90] to connect to a remote MySQL instance.
I am using the REPLACE INTO command to update a table which works fine.
I currently do this using
B4X:
Dim queryString As String = $"REPLACE INTO active VALUES ('${cmap.Get("imei")}','${cmap.Get("ident")}','${cmap.Get("userid")}','${pmap.Get("batv")}','${pmap.Get("bchg")}',CURRENT_TIMESTAMP,'0.0');"$
SQL1.ExecNonQuery(queryString)


However, I need to know the number of updated rows as part of this query. So I updated the call to:
B4X:
dim rs as ResultSet = SQL1.ExecQuery(queryString)
which also works correctly.
If I log the resultset it correctly identifies the number of updates.
(JDBC42ResultSet) Result set representing update count of 2
Also the debugger allows me to view the attribute in the result set.
updatecountfrom IDE.jpg

However there does not appear to be a method to extract the updateCount field.
Could someone help me as to how to extract that updateCount value ??
 

labcold

Member
Licensed User
Thanks for the reply.
Sorry if I haven't explained well, however, that doesn't address the issue I am trying to solve.

According to the MySQL documentation:
The REPLACE statement returns a count to indicate the number of rows affected. This is the sum of the rows deleted and inserted. If the count is 1 for a single-row REPLACE, a row was inserted and no rows were deleted. If the count is greater than 1, one or more old rows were deleted before the new row was inserted. It is possible for a single row to replace more than one old row if the table contains multiple unique indexes and the new row duplicates values for different old rows in different unique indexes.

The affected-rows count makes it easy to determine whether REPLACE only added a row or whether it also replaced any rows: Check whether the count is 1 (added) or greater (replaced).

If you are using the C API, the affected-rows count can be obtained using the mysql_affected_rows() function.

the REPLACE is essentially a NonQuery but in effect is a QuerySingleResult being an integer returned value.
I am trying to find a way to retrieve the return value as defined above.
You cannot use the resultset as you suggest as it has no data
java.sql.SQLException: ResultSet is from UPDATE. No Data.
it does however have the return value - which is shown in the debugger and the IDE when the value is examined. By using ExecQuery instead of ExecNonQuery I get:
(JDBC42ResultSet) Result set representing update count of 2
1623812412420.png

So I believe that this is a valid mechanism and, although I agree its not parameterised as such, the function should return a useable value that should be accessible to the coder. So there must be a mechanism to retrieve the updateCount attribute from the returned value as the IDE and the debugger can both access this attribute correctly.
 
Upvote 0

Erel

Administrator
Staff member
Licensed User
Try this:
B4X:
Dim rs As ResultSet = SQL1.ExecNonQuery2("REPLACE INTO active VALUES ?,?, ?... ,CURRENT_TIMESTAMP,'0.0'", Array(cmap.Get("imei"), ... ))
Dim jo As JavaObject
jo = jo.InitializeStatic("anywheresoftware.b4j.objects.SQL$ResultSetWrapper").GetField("closePS")
Dim Statement As JavaObject = jo.RunMethod("get", Array(rs))
Dim UpdateCount As Int = Statement.RunMethod("getUpdateCount", Null)
 
Upvote 0

labcold

Member
Licensed User
Thank you Erel that works perfectly.
Would it be complicated to add the updateCount as a returnable value in the resultset type so that you could always get rs.updateCount as a value?
perhaps I could put it in the requested features if its feasible?
Again thanks for your support and the great products :D
 
Upvote 0
Top