HANA XSJS - InternalError: dberror(Connection.prepareStatement): 608 - exceed maximum number of prepared statements

Question

I have an xsjs sertvice that is filling some tables with data from another table. after sometime running, the service gives the following error: InternalError: dberror(Connection.prepareStatement): 608 - exceed maximum number of prepared statements: the number of prepared statements per connection cannot exceed the max statements

I'm opening a $.db.getConnection() at the beginning and only closing at the end, with a prepareStatement statement on a for loop. (there are several loops like the one bellow for other tables)

var aSQL = "select field from table";
var conn  = $.hdb.getConnection(); var connInsert  = $.db.getConnection();
var rsLevel1 = conn.executeQuery(aSQL);

var s = {};
var loc_descr_group = [];
var row = {};

for (i = 0; i < rsLevel1.length; i++) {
    var entry = rsLevel1[i].field;
    var split = entry.split(",");
    for (var j = 0; j<split.length; j++){
        if (loc_descr_group.indexOf(split[j]) == -1){
            loc_descr_group.push(split[j]);
            var value = split[j].replace(/'/g,"''");

            sSQL = "insert into another_table "
                + " values ('"+value+"')";
            pstmt = connInsert.prepareStatement(sSQL);

            pstmt.execute(); 
            connInsert.commit();

        }
    }
}
connInsert.close();
conn.close();

I couldn't find any information about the max number of prepareStatement used on xsjs. Is there one?

Thank you.

Answer 1

The problem here is not that there is a per-connection limit of prepared statements, but that the code needlessly creates new prepared statements in a loop.

The whole idea of prepared statements is reuse. When running several statements that are structurally the same and differ only in the actual values covered, using prepared statements allow to parse, check and optimise the query structure once and reuse it over and over again.

Instead of creating the prepared statement object for every insert, it's much better to create it once before the nested loop construct. And instead of pasting quoted and comma-delimited values into the SQL string, rather using bind variables can improve both execution speed and security of the insert statement.

Furthermore, there is a COMMIT after each insert. If that is really required, then using an autocommit connection might be the better choice. If it's not required, the COMMIT should only be send once after the loops have finished. This is not just a question of performance (COMMITs are always synchronous - your code waits for it) but also of possibly half inserted records.

Finally, the code uses two different connection methods $.db.getConnection and $.hdb.db.connection to create two separate connection objects. For the given context that is unnecessary and rather confusing. Just using the newer $.hdb.db.connection and a single connection would suffice.