prepareStatement should not be used outside an EJBServer

[Segi]

I have a function that creates a manual database query to execute a SQL Server stored procedure. It was working fine until just recently. I have not changed anything with eBase.

Code: Select all

function getChangeData(partnum) {

var subSql="Exec ChangeTracker @Partnum='" + partnum + "'";

var subRs,subStmt;

var subCon=system.getDatabaseConnection("VAULT");
     
 try {
     subStmt=subCon.prepareStatement(subSql);
     subRs=subStmt.executeQuery();

     while (subRs.next()) {
          // Logic goes here
     }
} catch (e) {
     // Dump the SQL for debugging purposes
     print("An error occurred reading the database with the query " + subSql);
     print(e);

     if(subRs) subRs.close();
     if(subStmt) subStmt.close();
     if(subCon) subCon.close(); 
                    
     return null;
} finally {
     if(subRs) subRs.close();
     if(subStmt) subStmt.close();
     if(subCon) subCon.close();
}

// data is returned here
}

The error message is:

Code: Select all

An error occurred reading the database with the query Exec ChangeTracker JavaException: java.sql.SQLException: StandardXAConnectionHandle:prepareStatement should not be used outside an EJBServer
java.lang.NullPointerException

What does prepareStatement should not be used outside an EJBServer mean and how can this be fixed ?


EDIT: I edited the db connection settings for the database connection VAULT so that now it shows:
min:5
max:300 (probably overkill)
checkLevel:2
validationQuery:select 1
preparedStatementCache:0

I forgot to mention that the function above called getChangeData() is called by another function which loops through a large set of data and calls getChangeData() many times, each time providing a different part number. After adjusting the DB connection values, the code executes for a while (Over an hour and a half) and now gives me this error

JavaException: com.ebasetech.xi.exceptions.FormRuntimeException: Error getting connection using Database Connection VAULT, SQLException in StandardPoolDataSource:getConnection exception: java.sql.SQLException: SQLException in StandardPoolDataSource:getConnection no connection available java.lang.NullPointerException

Does this mean that its running out of active connections ? As you can see in the code, I do close the connection object in the finally statement of getChangeData() to free up the connection object

[Steve]

Hi Segi,

I cannot see a lot wrong with your statement apart from following;

Code: Select all

    if(subRs) subRs.close(); 
     if(subStmt) subStmt.close(); 
     if(subCon) subCon.close(); 

Inside the catch statement should be removed. The finally is ALWAYS execute regardless of an error. This might cause you problems, but unlikely.

Maybe as a suggestion for performance and the number of connections created is to create the Connection outside this function and add the connection as a parameter:

e.g

Code: Select all

function getData()
{
  var con;
  
   try
  {
          con =system.getDatabaseConnection("VAULT"); 
          //loop through table?
          var rows = tables.mytable.rowIterator();
          while(rows.next())
          {
                var partNum = tables.mytable.partnum.value;
                var result = getChangeData(partNum, con);
          }
   }
   catch(e)
  {
     //log error
  }
  finally
  {
      if(con) con.close();
  }
}


function getChangeData(partnum, subCon) { 

var subSql="Exec ChangeTracker @Partnum='" + partnum + "'"; 

var subRs,subStmt; 


      
 try { 
     subStmt=subCon.prepareStatement(subSql); 
     subRs=subStmt.executeQuery(); 

     while (subRs.next()) { 
          // Logic goes here 
     } 
} catch (e) { 
     // Dump the SQL for debugging purposes 
     print("An error occurred reading the database with the query " + subSql); 
     print(e); 
                    
     return null; 
} finally { 
     if(subRs) subRs.close(); 
     if(subStmt) subStmt.close(); 
} 

If you need 300 connections then there is a serious connection leak. I would have thought that 30 would be generous.

The EJB error I think is a red herring. I think you might have a connection leak elsewhere?

[Segi]

Steve,

What you said about closing the connection in catch AND finally statements isn't accurate because of the if condition if(subStmt) which will only execute it once

I've modified my code to pass a connection object as a parameter and will let you know if it fixes my issue.

Strangely, I got the error "java.sql.SQLException: StandardXAConnectionHandle:prepareStatement should not be used outside an EJBServer" when trying to open an email resource that is used to send the report after it has finished generating (This is done by the function that calls getChangeData). I fixed this particular issue by reimporting the email resource from a backup and can now open the email resource correctly.