Wish jSQL ExecNonQuery3 using RETURN_GENERATED_KEYS

[alwaysbusy]

At least on a Microsoft SQL server, with the current jSQL library you're unable to retrieve the ID of the newly inserted record. ("SELECT SCOPE_IDENTITY() AS ID" always returns null)

I've been able to solve this by creating a new lib ABSQLExt but maybe this could be integrated somehow in the jSQL library because it looks like kind of a hack now

Usage:

Dim ABSQL As ABSQLExt
res = ABSQL.ExecNonQuery3(SQL, Query, Args)

ABSQLExt lib code:

package com.ab.absqlext;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.locks.ReentrantLock;

import anywheresoftware.b4a.BA.Author;
import anywheresoftware.b4a.BA.DependsOn;
import anywheresoftware.b4a.BA.DesignerName;
import anywheresoftware.b4a.BA.ShortName;
import anywheresoftware.b4a.BA.Version;
import anywheresoftware.b4a.objects.collections.List;
import anywheresoftware.b4j.objects.SQL;

@DesignerName("Build 20160203")   
@Version(1.00F)   
@Author("Alain Bailleul")   
@ShortName("ABSQLExt")   
@DependsOn(values={"jServer", "jSQL"})
public class ABSQLExt {
   public static final int THREAD_LOCK_TIMEOUT = 60000;
  private ReentrantLock sqliteLock;
   
   private void startLock() {
    if (this.sqliteLock != null) {
       try {
         if (!this.sqliteLock.tryLock(60000L, TimeUnit.MILLISECONDS)) {
           System.err.println("Thread is waiting for more than 60 seconds for the previous transaction to complete...");
           Thread.dumpStack();
           this.sqliteLock.lock();
         }
       } catch (InterruptedException e) {
    e.printStackTrace();
       }
    }
   }

   private void releaseLock() {
     if (this.sqliteLock != null) {
        this.sqliteLock.unlock();
     }
   }
   
   public String ExecNonQuery3(SQL sql, String Statement, List Args) throws SQLException {
     String res="";
     PreparedStatement ps = sql.connection.prepareStatement(Statement, PreparedStatement.RETURN_GENERATED_KEYS);
     int affectedRows=0;
     try {
       int numArgs = !Args.IsInitialized() ? 0 : Args.getSize();
      for (int i = 0; i < numArgs; i++) {
         ps.setObject(i + 1, Args.Get(i));
      }
       
      startLock();
       
      affectedRows = ps.executeUpdate();
     } finally {
      try {
      if (affectedRows == 0) {
      throw new SQLException("no rows affected.");
      }

      try (ResultSet generatedKeys = ps.getGeneratedKeys()) {
      if (generatedKeys.next()) {
      res = "" + generatedKeys.getLong(1);
      }
      else {
      throw new SQLException("failed.");
      }
      }
         ps.close();
      }
      finally {
         releaseLock();
      }
     }
     return res;
   }   
}

 

[alwaysbusy]

Code updated. Saved the record 2 times.

 

[Roberto P.]

hello Alain
I can not run your example of the new release 1.8 because the library lacks. It exists or must it?

thank you

 

[alwaysbusy]

which library is not there? None of the demos use the ABSQLExt library described in this post (this is an internal library from our company, not a public one)

 

[Roberto P.]

the reference to the JasperReports library is present in the example you've sent us with version 1.08.
With reference which I can replace?
thanks you

 

[alwaysbusy]

Got it! You can just comment out those two lines talking about ABSQLExt. This demo is not runnable (you can't access our inhouse database). You should use it in one of your own apps and create your reports with JasperReports. It just shows the code in Main needed to open such a report.

 

[Roberto P.]

ok. clear.