Wish [Wish] SetNonQuery2Statement, AddNonQuery2Args, ExecNonQuery2Batch

[OliverA]

The current AddNonQueryToBatch/ExecNonQueryBatch is very flexible by allowing various non-query statements (update, instert, delete, etc.) to be batched together. But often, it is just one type of non-query statement that needs to be executed multiple times. Even the example given for AddNonQueryToBatch is such a case:

For i = 1 To 1000
  sql.AddNonQueryToBatch("INSERT INTO table1 VALUES (?)", Array(Rnd(0, 100000)))
Next
Dim SenderFilter As Object = sql.ExecNonQueryBatch("SQL")
Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
Log("NonQuery: " & Success)

Even though it is the same statement for 1000 iterations, the underlying library code (https://github.com/AnywhereSoftware/B4J_SQL/blob/master/src/anywheresoftware/b4j/objects/SQL.java), adds the arguments of each AddNonQueryToBatch to a list (with the above code creating a list of 1000 entries). The ExecNonQueryBatch then iterates over that list (https://github.com/AnywhereSoftware...rc/anywheresoftware/b4j/objects/SQL.java#L251) and calls another method. That method creates a prepared statement, sets any parameters, and executes the prepared statement. So for the above loop, 1000 prepared statements are created and executed.

With the three new methods, the following can be accomplished.
SetNonQuery2Statment - this methods takes a non-query SQL statement (parameterized or not) and creates a prepared statement. (Edit: non-parameterized query statements probably don't make any sense with this)
SetNonQuery2Args - this method can be called multiple times to set the arguments for each call of the prepared statement with SetNonQuery2Statement
ExecNonQuery2Batch - this methods loops over the list of arguments and calls the prepared statements execute method with each set of arguments.

The above code would then be

sql.SetNonQuery2Statement("INSERT INTO table1 VALUES (?)")
For i = 1 To 1000
  sql.AddNonQuery2Args(Array(Rnd(0, 100000)))
Next
Dim SenderFilter As Object = sql.ExecNonQuery2Batch("SQL")
Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
Log("NonQuery: " & Success)

Technically this could be implemented with JavaObject(s), but it would be cool to have it part of the SQL library (and therefore I'm placing this wish).

Good grief, how do you edit the title? (found it!)

 

[Erel]

https://www.b4x.com/android/forum/threads/updates-to-internal-libaries.48274/#post-583678

The updated Java code is:

    public Object ExecNonQueryBatch(final BA ba, final String EventName) {
       final ArrayList<Object[]> myList = nonQueryStatementsList;
       nonQueryStatementsList = new ArrayList<Object[]>();
       final SQL ret = SQL.cloneMe(this);
       BA.submitRunnable(new Runnable() {

           @Override
           public void run() {
               synchronized (connection) {
                   try {
                       BeginTransaction();
                       HashMap<String, PreparedStatement> cachedPS = new HashMap<String, PreparedStatement>();
                       try {
                           
                           for (Object[] o: myList) {
                               String Statement = (String)o[0];
                               List Args = (List)o[1];
                               PreparedStatement ps = cachedPS.get(Statement);
                               if (ps == null) {
                                   ps = connection.prepareStatement(Statement);
                                   cachedPS.put(Statement, ps);
                               }
                               int numArgs = Args.IsInitialized() == false ? 0 : Args.getSize();
                               for (int i = 0; i < numArgs; i++) {
                                   ps.setObject(i + 1, Args.Get(i));
                               }
                               ps.execute();

                           }
                       } finally {
                           for (PreparedStatement ps : cachedPS.values()) {
                               try {
                                   ps.close();
                               } catch (Exception pse) {
                                   pse.printStackTrace();
                               }
                           }
                       }
                       TransactionSuccessful();
                       ba.raiseEventFromDifferentThread(ret, null, 0, EventName.toLowerCase(BA.cul) + "_nonquerycomplete",
                               true, new Object[] {true});
                   } catch (Exception e) {
                       e.printStackTrace();
                       try {
                           Rollback();
                       } catch (SQLException e1) {
                           e1.printStackTrace();
                       }
                       ba.setLastException(e);
                       ba.raiseEventFromDifferentThread(ret, null, 0, EventName.toLowerCase(BA.cul) + "_nonquerycomplete",
                               true, new Object[] {false});
                   }
               }
           }

       }, null, 0);
       return ret;
   }

 

[OliverA]

This is even better than my original proposal, since no code change is needed to reap the benefits of using the same prepared statement for a given query that is used multiple times. Hats off to @Erel.