# HG changeset patch
# Parent 54628ce756bb50b51a2da79f7971424fab92039d
# User Jaroslav Havlin <jhavlin@netbeans.org>
#231030: Make usage of scrollable cursors configurable

diff --git a/db.dataview/nbproject/project.xml b/db.dataview/nbproject/project.xml
--- a/db.dataview/nbproject/project.xml
+++ b/db.dataview/nbproject/project.xml
@@ -29,7 +29,7 @@
                     <compile-dependency/>
                     <run-dependency>
                         <release-version>1</release-version>
-                        <specification-version>1.25.0.5</specification-version>
+                        <specification-version>1.56</specification-version>
                     </run-dependency>
                 </dependency>
                 <dependency>
diff --git a/db.dataview/src/org/netbeans/modules/db/dataview/output/SQLExecutionHelper.java b/db.dataview/src/org/netbeans/modules/db/dataview/output/SQLExecutionHelper.java
--- a/db.dataview/src/org/netbeans/modules/db/dataview/output/SQLExecutionHelper.java
+++ b/db.dataview/src/org/netbeans/modules/db/dataview/output/SQLExecutionHelper.java
@@ -86,6 +86,10 @@
     private final DataView dataView;
     // the RequestProcessor used for executing statements.
     private final RequestProcessor rp = new RequestProcessor("SQLStatementExecution", 20, true); // NOI18N
+    private static final String LIMIT_CLAUSE = "LIMIT ";               // NOI18N
+    public static final String OFFSET_CLAUSE = "OFFSET ";              // NOI18N
+    private boolean limitSupported = false;
+    private boolean useScrollableCursors = false;
     private int resultSetScrollType = ResultSet.TYPE_FORWARD_ONLY;
     private boolean supportesMultipleResultSets = false;
 
@@ -94,7 +98,7 @@
     }
 
     void initialDataLoad() throws SQLException {
-        assert (! SwingUtilities.isEventDispatchThread()) : "Must be called of the EDT!";
+        assert (! SwingUtilities.isEventDispatchThread()) : "Must be called off the EDT!";
 
         /**
          * Wrap initializing the SQL result into a runnable. This makes it
@@ -118,32 +122,12 @@
                 try {
                     DatabaseConnection dc = dataView.getDatabaseConnection();
                     Connection conn = DBConnectionFactory.getInstance().getConnection(dc);
-
-                    String msg;
-                    if (conn == null) {
-                        Throwable t = DBConnectionFactory.getInstance()
-                                .getLastException();
-                        if (t != null) {
-                            msg = t.getMessage();
-                        } else {
-                            msg = NbBundle.getMessage(SQLExecutionHelper.class,
-                                    "MSG_connection_failure", //NOI18N
-                                    dataView.getDatabaseConnection());
-                        }
-                        NotifyDescriptor nd = new NotifyDescriptor.Message(msg,
-                                NotifyDescriptor.ERROR_MESSAGE);
-                        DialogDisplayer.getDefault().notifyLater(nd);
-                        LOGGER.log(Level.INFO, msg, t);
-                        throw new SQLException(msg, t);
-                    }
-                    try {
-                        supportesMultipleResultSets = conn.getMetaData().supportsMultipleResultSets();
-                    } catch (SQLException ex) {
-                        LOGGER.log(Level.INFO, "Database driver throws exception "  //NOI18N
-                                + "when checking for multiple resultset support."); //NOI18N
-                    }
+                    checkNonNullConnection(conn);
+                    checkSupportForMultipleResultSets(conn);
                     DBMetaDataFactory dbMeta = new DBMetaDataFactory(conn);
+                    limitSupported = dbMeta.supportsLimit();
                     String sql = dataView.getSQLString();
+                    boolean isSelect = isSelectStatement(sql);
 
                     updateScrollableSupport(conn, dc, sql);
 
@@ -156,11 +140,11 @@
                         return;
                     }
                     // Read multiple Resultsets
-                    boolean resultSet = executeSQLStatement(stmt, sql);
+                    boolean isResultSet = executeSQLStatement(stmt, sql);
 
                     // @todo: This needs clearing up => in light of request for
                     // the ability to disable autocommit, this need to go
-                    if (dataView.getUpdateCount() != -1) {
+                    if (!isResultSet || dataView.getUpdateCount() != -1) {
                         if (!conn.getAutoCommit()) {
                             conn.commit();
                         }
@@ -169,37 +153,29 @@
                     if (Thread.interrupted()) {
                         return;
                     }
-                    // @todo: This needs clearing up => in light of request for
-                    // the ability to disable autocommit, this need to go
-                    if (!resultSet) {
-                        if (!conn.getAutoCommit()) {
-                            conn.commit();
-                        }
-                        return;
-                    }
                     boolean needReread = false;
                     ResultSet rs = null;
 
                     while (true) {
-                        if (resultSet) {
+                        if (isResultSet) {
                             rs = stmt.getResultSet();
 
                             Collection<DBTable> tables = dbMeta.generateDBTables(
-                                    rs, sql, isSelectStatement(sql));
+                                    rs, sql, isSelect);
                             DataViewDBTable dvTable = new DataViewDBTable(tables);
                             DataViewPageContext pageContext = dataView.addPageContext(
                                     dvTable);
                             needReread |= resultSetNeedsReloading(dvTable);
 
                             if (!needReread) {
-                                loadDataFrom(pageContext, rs, true);
+                                loadDataFrom(pageContext, rs, useScrollableCursors);
                             }
                         }
                         if (supportesMultipleResultSets) {
-                            resultSet = stmt.getMoreResults();
+                            isResultSet = stmt.getMoreResults();
                             // @todo: Do somethink intelligent with the updatecounts
                             int updateCount = stmt.getUpdateCount();
-                            if (resultSet == false && updateCount == -1) {
+                            if (isResultSet == false && updateCount == -1) {
                                 break;
                             }
                         } else {
@@ -208,21 +184,21 @@
                     }
 
                     if (needReread) {
-                        resultSet = executeSQLStatement(stmt, sql);
+                        isResultSet = executeSQLStatement(stmt, sql);
                         int res = -1;
                         while (true) {
-                            if (resultSet) {
+                            if (isResultSet) {
                                 res++;
                                 rs = stmt.getResultSet();
                                 DataViewPageContext pageContext = dataView.getPageContext(
                                         res);
-                                loadDataFrom(pageContext, rs, true);
+                                loadDataFrom(pageContext, rs, useScrollableCursors);
                             }
                             if (supportesMultipleResultSets) {
-                                resultSet = stmt.getMoreResults();
+                                isResultSet = stmt.getMoreResults();
                                 // @todo: Do somethink intelligent with the updatecounts
                                 int updateCount = stmt.getUpdateCount();
-                                if (resultSet == false && updateCount == -1) {
+                                if (isResultSet == false && updateCount == -1) {
                                     break;
                                 }
                             } else {
@@ -230,6 +206,11 @@
                             }
                         }
                     }
+                    // If total count was not retrieved using scrollable cursors,
+                    // compute it now.
+                    if (!useScrollableCursors && dataView.getPageContexts().size() > 0) {
+                        getTotalCount(isSelect, sql, stmt, dataView.getPageContext(0));
+                    }
                     DataViewUtils.closeResources(rs);
                 } catch (SQLException sqlEx) {
                     this.ex = sqlEx;
@@ -256,6 +237,40 @@
                 }
                 return true;
             }
+
+            /**
+             * Check that the connection is not null. If it is null, try to find
+             * cause of the failure and throw an exception.
+             */
+            private void checkNonNullConnection(Connection conn) throws
+                    SQLException {
+                if (conn == null) {
+                    String msg;
+                    Throwable t = DBConnectionFactory.getInstance()
+                            .getLastException();
+                    if (t != null) {
+                        msg = t.getMessage();
+                    } else {
+                        msg = NbBundle.getMessage(SQLExecutionHelper.class,
+                                "MSG_connection_failure", //NOI18N
+                                dataView.getDatabaseConnection());
+                    }
+                    NotifyDescriptor nd = new NotifyDescriptor.Message(msg,
+                            NotifyDescriptor.ERROR_MESSAGE);
+                    DialogDisplayer.getDefault().notifyLater(nd);
+                    LOGGER.log(Level.INFO, msg, t);
+                    throw new SQLException(msg, t);
+                }
+            }
+
+            private void checkSupportForMultipleResultSets(Connection conn) {
+                try {
+                    supportesMultipleResultSets = conn.getMetaData().supportsMultipleResultSets();
+                } catch (SQLException ex) {
+                    LOGGER.log(Level.INFO, "Database driver throws exception "  //NOI18N
+                            + "when checking for multiple resultset support."); //NOI18N
+                }
+            }
         }
         Loader l = new Loader();
         Future<?> f = rp.submit(l);
@@ -635,7 +650,7 @@
                             DataViewPageContext pageContext = dataView.getPageContext(
                                     res);
                             rs = stmt.getResultSet();
-                            loadDataFrom(pageContext, rs, getTotal);
+                            loadDataFrom(pageContext, rs, getTotal && useScrollableCursors);
                         }
                         if (supportesMultipleResultSets) {
                             resultSet = stmt.getMoreResults();
@@ -649,6 +664,10 @@
                         }
                     }
 
+                    // Get total count using the old-fashioned method.
+                    if (!useScrollableCursors && getTotal && dataView.getPageContexts().size() > 0) {
+                        getTotalCount(isSelectStatement(sql), sql, stmt, dataView.getPageContext(0));
+                    }
                     DataViewUtils.closeResources(rs);
                 } catch (SQLException sqlEx) {
                     String title = NbBundle.getMessage(SQLExecutionHelper.class, "MSG_error");
@@ -708,8 +727,9 @@
             boolean hasNext = false;
             boolean needSlowSkip = true;
 
-            if (rs.getType() == ResultSet.TYPE_SCROLL_INSENSITIVE
-                    || rs.getType() == ResultSet.TYPE_SCROLL_SENSITIVE) {
+            if (useScrollableCursors
+                    && (rs.getType() == ResultSet.TYPE_SCROLL_INSENSITIVE
+                    || rs.getType() == ResultSet.TYPE_SCROLL_SENSITIVE)) {
                 try {
                     hasNext = rs.absolute(startFrom);
                     needSlowSkip = false;
@@ -787,9 +807,13 @@
     private Statement prepareSQLStatement(Connection conn, String sql, boolean needTotal) throws SQLException {
         Statement stmt = null;
         if (sql.startsWith("{")) { // NOI18N
-            stmt = conn.prepareCall(sql, resultSetScrollType, ResultSet.CONCUR_READ_ONLY);
+            stmt = useScrollableCursors
+                    ? conn.prepareCall(sql, resultSetScrollType, ResultSet.CONCUR_READ_ONLY)
+                    : conn.prepareCall(sql);
         } else if (isSelectStatement(sql)) {
-            stmt = conn.createStatement(resultSetScrollType, ResultSet.CONCUR_READ_ONLY);
+            stmt = useScrollableCursors
+                    ? conn.createStatement(resultSetScrollType, ResultSet.CONCUR_READ_ONLY)
+                    : conn.createStatement();
 
             // set a reasonable fetchsize
             setFetchSize(stmt, 50);
@@ -819,7 +843,9 @@
                 }
             }
         } else {
-            stmt = conn.createStatement(resultSetScrollType, ResultSet.CONCUR_READ_ONLY);
+            stmt = useScrollableCursors
+                    ? conn.createStatement(resultSetScrollType, ResultSet.CONCUR_READ_ONLY)
+                    : conn.createStatement();
         }
         return stmt;
     }
@@ -834,7 +860,9 @@
             isResultSet = ((PreparedStatement) stmt).execute();
         } else {
             try {
-                isResultSet = stmt.execute(sql);
+                DataViewPageContext pc = dataView.getPageContexts().size() > 0
+                        ? dataView.getPageContext(0) : null;
+                isResultSet = stmt.execute(appendLimitIfRequired(pc, sql));
             } catch (NullPointerException ex) {
                 LOGGER.log(Level.SEVERE, "Failed to execute SQL Statement [{0}], cause: {1}", new Object[] {sql, ex});
                 throw new SQLException(ex);
@@ -867,10 +895,135 @@
         return isResultSet;
     }
 
+    private void getTotalCount(boolean isSelect, String sql, Statement stmt,
+            DataViewPageContext pageContext) {
+        if (!isSelect) {
+            setTotalCount(null, pageContext);
+            return;
+        }
+
+        // Case for LIMIT n OFFSET m
+        if (isLimitUsedInSelect(sql)) {
+            try {
+                String lmtStr = sql.toUpperCase().split(LIMIT_CLAUSE)[1].trim();
+                int rCnt = Integer.parseInt(lmtStr.split(" ")[0]);
+                pageContext.setTotalRows(rCnt);
+                return;
+            } catch (NumberFormatException nex) {
+                LOGGER.log(Level.FINE, null, nex);
+            }
+        }
+
+        // SELECT COUNT(*) FROM (sqlquery) alias
+        ResultSet cntResultSet = null;
+        try {
+            cntResultSet = stmt.executeQuery(
+                    SQLStatementGenerator.getCountAsSubQuery(sql));
+            setTotalCount(cntResultSet, pageContext);
+            return;
+        } catch (SQLException e) {
+        } finally {
+            DataViewUtils.closeResources(cntResultSet);
+        }
+
+        // Try spliting the query by FROM and use "SELECT COUNT(*) FROM"  + "2nd part sql"
+        if (!isGroupByUsedInSelect(sql)) {
+            cntResultSet = null;
+            try {
+                cntResultSet = stmt.executeQuery(
+                        SQLStatementGenerator.getCountSQLQuery(sql));
+                setTotalCount(cntResultSet, pageContext);
+                return;
+            } catch (SQLException e) {
+            } finally {
+                DataViewUtils.closeResources(cntResultSet);
+            }
+        }
+
+        // In worse case, get the count from resultset
+        cntResultSet = null;
+        int totalRows = 0;
+        try {
+            // reset fetch size
+            int fetchSize = pageContext.getPageSize();
+            try {
+                fetchSize = stmt.getFetchSize();
+                stmt.setFetchSize(20000);
+            } catch (SQLException sqe) {
+                // ignore
+            }
+
+            cntResultSet = stmt.executeQuery(sql);
+            while (cntResultSet.next()) {
+                totalRows++;
+            }
+            pageContext.setTotalRows(totalRows);
+
+            // set to old value
+            try {
+                stmt.setFetchSize(fetchSize);
+            } catch (SQLException sqe) {
+                // ignore
+            }
+            return;
+        } catch (SQLException e) {
+            LOGGER.log(Level.FINE, null, e);
+        } finally {
+            DataViewUtils.closeResources(cntResultSet);
+        }
+
+        // Unable to compute the total rows
+        setTotalCount(null, pageContext);
+    }
+
     private boolean isSelectStatement(String queryString) {
         return queryString.trim().toUpperCase().startsWith("SELECT") && queryString.trim().toUpperCase().indexOf("INTO") == -1; // NOI18N
     }
 
+    private boolean isLimitUsedInSelect(String sql) {
+        return sql.toUpperCase().indexOf(LIMIT_CLAUSE) != -1;
+    }
+
+    private boolean isGroupByUsedInSelect(String sql) {
+        return sql.toUpperCase().indexOf(" GROUP BY ") != -1 || sql.toUpperCase().indexOf(
+                " COUNT(*) ") != -1; // NOI18N
+    }
+
+    void setTotalCount(ResultSet countresultSet, DataViewPageContext pageContext) {
+        try {
+            if (countresultSet == null) {
+                pageContext.setTotalRows(-1);
+                pageContext.setTotalRows(-1);
+            } else {
+                if (countresultSet.next()) {
+                    int count = countresultSet.getInt(1);
+                    pageContext.setTotalRows(count);
+                    pageContext.setTotalRows(count);
+                }
+            }
+        } catch (SQLException ex) {
+            LOGGER.log(Level.SEVERE, "Could not get total row count ", ex); // NOI18N
+        }
+    }
+
+    private String appendLimitIfRequired(DataViewPageContext pageContext,
+            String sql) {
+        if (useScrollableCursors) {
+            return sql;
+        } else if (limitSupported && isSelectStatement(sql)
+                && !isLimitUsedInSelect(sql)) {
+
+            int pageSize = pageContext == null ? dataView.getPageSize()
+                    : pageContext.getPageSize();
+            int currentPos = pageContext == null ? 1
+                    : pageContext.getCurrentPos();
+            return sql + ' ' + LIMIT_CLAUSE + pageSize
+                    + ' ' + OFFSET_CLAUSE + (currentPos - 1);
+        } else {
+            return sql;
+        }
+    }
+
     static String millisecondsToSeconds(long ms) {
         NumberFormat fmt = NumberFormat.getInstance();
         fmt.setMaximumFractionDigits(3);
@@ -931,6 +1084,10 @@
      */
     private void updateScrollableSupport(Connection conn, DatabaseConnection dc,
             String sql) {
+        useScrollableCursors = dc.isUseScrollableCursors();
+        if (!useScrollableCursors) {
+            return;
+        }
         String driverName = dc.getDriverClass();
         /* Derby fails to support scrollable cursors when invoking 'stored procedures'
          which return resultsets - it fails hard: not throwing a SQLException,
@@ -941,12 +1098,6 @@
                 resultSetScrollType = ResultSet.TYPE_FORWARD_ONLY;
                 return;
             }
-        } else if (driverName != null
-                && driverName.startsWith("com.informix.jdbc.IfxDriver")) { //NOI18N
-            // Informix failes scrollable result sets if blob columns are part
-            // of the resultset -> disable ...
-            resultSetScrollType = ResultSet.TYPE_FORWARD_ONLY;
-            return;
         }
         /* Try to get a "good" scrollable ResultSet and follow the DBs support */
         try {
diff --git a/db.dataview/src/org/netbeans/modules/db/dataview/output/SQLStatementGenerator.java b/db.dataview/src/org/netbeans/modules/db/dataview/output/SQLStatementGenerator.java
--- a/db.dataview/src/org/netbeans/modules/db/dataview/output/SQLStatementGenerator.java
+++ b/db.dataview/src/org/netbeans/modules/db/dataview/output/SQLStatementGenerator.java
@@ -335,6 +335,22 @@
         return sql.toString();
     }
 
+    static String getCountSQLQuery(String queryString) {
+        // User may type "FROM" in either lower, upper or mixed case
+        String[] splitByFrom = queryString.toUpperCase().split("FROM"); // NOI18N
+        queryString = queryString.substring(splitByFrom[0].length());
+
+        String[] splitByOrderBy = queryString.toUpperCase().split("ORDER BY"); // NOI18N
+        queryString = queryString.substring(0, splitByOrderBy[0].length());
+        return "SELECT COUNT(*) " + queryString; // NOI18N
+    }
+
+    static String getCountAsSubQuery(String queryString) {
+        String[] splitByOrderBy = queryString.toUpperCase().split("ORDER BY"); // NOI18N
+        queryString = queryString.substring(0, splitByOrderBy[0].length());
+        return "SELECT COUNT(*) FROM (" + queryString + ") C2668"; // NOI18N
+    }
+
     private boolean addSeparator(boolean and, StringBuilder sql, String sep) {
         if (and) {
             sql.append(sep);
diff --git a/db/nbproject/project.properties b/db/nbproject/project.properties
--- a/db/nbproject/project.properties
+++ b/db/nbproject/project.properties
@@ -45,7 +45,7 @@
 javadoc.arch=${basedir}/arch.xml
 javadoc.apichanges=${basedir}/apichanges.xml
 
-spec.version.base=1.55.0
+spec.version.base=1.56
 
 extra.module.files=modules/ext/ddl.jar
 
diff --git a/db/src/org/netbeans/api/db/explorer/DatabaseConnection.java b/db/src/org/netbeans/api/db/explorer/DatabaseConnection.java
--- a/db/src/org/netbeans/api/db/explorer/DatabaseConnection.java
+++ b/db/src/org/netbeans/api/db/explorer/DatabaseConnection.java
@@ -287,6 +287,17 @@
     }
 
     /**
+     * Check whether usage of scrollable cursors is recommended for this
+     * connection.
+     *
+     * @return True if scrollable cursors can be used, false if scrollable
+     * cursors are not supported by driver or database of this connection.
+     */
+    public boolean isUseScrollableCursors() {
+        return delegate.isUseScrollableCursors();
+    }
+
+    /**
      * Returns the {@link java.sql.Connection} instance which encapsulates 
      * the physical connection to the database if this database connection
      * is connected. Note that "connected" here means "connected using the
diff --git a/db/src/org/netbeans/api/db/explorer/node/Bundle.properties b/db/src/org/netbeans/api/db/explorer/node/Bundle.properties
--- a/db/src/org/netbeans/api/db/explorer/node/Bundle.properties
+++ b/db/src/org/netbeans/api/db/explorer/node/Bundle.properties
@@ -148,6 +148,8 @@
 ConnectionPropertiesDescription=Connection properties
 SeparateSystemTables=Show system tables separately
 SeparateSystemTablesDescription=Use special node for system tables
+UseScrollableCursors=Use scrollable cursors
+UseScrollableCursorsDescription=Use JDBC support for scrollable cursors. This makes the execution faster, but some drivers can become unstable.
 
 # Booleans
 
diff --git a/db/src/org/netbeans/modules/db/explorer/DatabaseConnection.java b/db/src/org/netbeans/modules/db/explorer/DatabaseConnection.java
--- a/db/src/org/netbeans/modules/db/explorer/DatabaseConnection.java
+++ b/db/src/org/netbeans/modules/db/explorer/DatabaseConnection.java
@@ -166,6 +166,8 @@
 
     private volatile boolean separateSystemTables = false;
 
+    private Boolean useScrollableCursors = null; // null = driver default
+
     /**
      * The API DatabaseConnection (delegates to this instance)
      */
@@ -1347,4 +1349,27 @@
         this.separateSystemTables = separateSystemTables;
         propertySupport.firePropertyChange("separateSystemTables", oldVal, separateSystemTables); //NOI18N
     }
+
+    /**
+     * Decide whether scrollable cursors should be used by the connection.
+     */
+    private boolean isUseScrollableCursorsByDefault() {
+        return drv != null
+                && (drv.startsWith("org.apache.derby") //NOI18N
+                || drv.startsWith("com.mysql") //NOI18N
+                || drv.startsWith("oracle") //NOI18N
+                || drv.startsWith("org.postgresql")); //NOI18N
+    }
+
+    public boolean isUseScrollableCursors() {
+        return useScrollableCursors == null
+                ? isUseScrollableCursorsByDefault()
+                : useScrollableCursors;
+    }
+
+    public void setUseScrollableCursors(boolean useScrollableCursors) {
+        boolean oldVal = isUseScrollableCursors();
+        this.useScrollableCursors = useScrollableCursors;
+        propertySupport.firePropertyChange("useScrollableCursors", oldVal, useScrollableCursors); //NOI18N
+    }
 }
diff --git a/db/src/org/netbeans/modules/db/explorer/DatabaseConnectionConvertor.java b/db/src/org/netbeans/modules/db/explorer/DatabaseConnectionConvertor.java
--- a/db/src/org/netbeans/modules/db/explorer/DatabaseConnectionConvertor.java
+++ b/db/src/org/netbeans/modules/db/explorer/DatabaseConnectionConvertor.java
@@ -253,6 +253,9 @@
             dbconn.addImportantCatalog(importantDatabase);
         }
         dbconn.setSeparateSystemTables(handler.separateSystemTables);
+        if (handler.useScrollableCursors != null) {
+            dbconn.setUseScrollableCursors(handler.useScrollableCursors);
+        }
         LOGGER.fine("Created DatabaseConnection[" + dbconn.toString() + "] from file: " + handler.connectionFileName);
 
         return dbconn;
@@ -392,7 +395,7 @@
 
         void write(PrintWriter pw, String name) throws IOException {
             pw.println("<?xml version='1.0'?>"); //NOI18N
-            pw.println("<!DOCTYPE connection PUBLIC '-//NetBeans//DTD Database Connection 1.1//EN' 'http://www.netbeans.org/dtds/connection-1_1.dtd'>"); //NOI18N
+            pw.println("<!DOCTYPE connection PUBLIC '-//NetBeans//DTD Database Connection 1.2//EN' 'http://www.netbeans.org/dtds/connection-1_2.dtd'>"); //NOI18N
             pw.println("<connection>"); //NOI18N
             pw.println("  <driver-class value='" + XMLUtil.toAttributeValue(instance.getDriver()) + "'/>"); //NOI18N
             pw.println("  <driver-name value='" + XMLUtil.toAttributeValue(instance.getDriverName()) + "'/>"); // NOI18N
@@ -435,6 +438,7 @@
             if (instance.isSeparateSystemTables()) {
                 pw.println("  <separate-system-tables value='true'/>"); //NOI18N
             }
+            pw.println("  <use-scrollable-cursors value='" + instance.isUseScrollableCursors() + "'/>"); //NOI18N
             pw.println("</connection>"); //NOI18N
         }        
     }
@@ -455,6 +459,7 @@
         private static final String ELEMENT_IMPORTANT_CATALOG = "important-catalog"; //NOI18N
         private static final String ELEMENT_CONNECTION_PROPERTY = "connection-property"; // NOI18N
         private static final String ELEMENT_SEPARATE_SYS_TABLES = "separate-system-tables"; //NOI18N
+        private static final String ELEMENT_USE_SCROLLABLE_CURSORS = "use-scrollable-cursors"; //NOI18N
         private static final String ELEMENT_CONNECTION_PROPERTY_NAME = "name"; // NOI18N
         private static final String ELEMENT_CONNECTION_PROPERTY_VALUE = "value"; // NOI18N
         private static final String ATTR_PROPERTY_VALUE = "value"; // NOI18N
@@ -473,6 +478,7 @@
         String displayName;
         Properties connectionProperties;
         boolean separateSystemTables = false;
+        Boolean useScrollableCursors = null;
         List<String> importantSchemas = new ArrayList<String>();
         List<String> importantCatalogs = new ArrayList<String>();
         
@@ -542,6 +548,8 @@
                 importantCatalogs.add(value);
             } else if (ELEMENT_SEPARATE_SYS_TABLES.equals(qName)) {
                 separateSystemTables = Boolean.parseBoolean(value);
+            } else if (ELEMENT_USE_SCROLLABLE_CURSORS.equals(qName)) {
+                useScrollableCursors = Boolean.parseBoolean(value);
             }
         }
 
diff --git a/db/src/org/netbeans/modules/db/explorer/node/ConnectionNode.java b/db/src/org/netbeans/modules/db/explorer/node/ConnectionNode.java
--- a/db/src/org/netbeans/modules/db/explorer/node/ConnectionNode.java
+++ b/db/src/org/netbeans/modules/db/explorer/node/ConnectionNode.java
@@ -89,6 +89,8 @@
     private static final String CONNECTIONPROPERTIESDESC = "ConnectionPropertiesDescription"; //NOI18N
     private static final String SEPARATESYSTEMTABLES = "SeparateSystemTables"; //NOI18N
     private static final String SEPARATESYSTEMTABLESDESC = "SeparateSystemTablesDescription"; //NOI18N
+    private static final String USESCROLLABLECURSORS = "UseScrollableCursors"; //NOI18N
+    private static final String USESCROLLABLECURSORSDESC = "UseScrollableCursorsDescription"; //NOI18N
     private static final String FOLDER = "Connection"; // NOI18N
     private static final RequestProcessor RP = new RequestProcessor(ConnectionNode.class.getName());
     
@@ -175,6 +177,10 @@
                 && val instanceof Boolean) {
             connection.setSeparateSystemTables((Boolean) val);
             refreshNode = false;
+        } else if (nps.getName().equals(USESCROLLABLECURSORS)
+                && val instanceof Boolean) {
+            connection.setUseScrollableCursors((Boolean) val);
+            refreshNode = false;
         }
 
         super.setPropertyValue(nps, val);
@@ -197,6 +203,7 @@
             addProperty(REMEMBERPW, REMEMBERPWDESC,
                     Boolean.class, !connected, connection.rememberPassword());
             addProperty(SEPARATESYSTEMTABLES, SEPARATESYSTEMTABLESDESC, Boolean.class, true, connection.isSeparateSystemTables());
+            addProperty(USESCROLLABLECURSORS, USESCROLLABLECURSORSDESC, Boolean.class, true, connection.isUseScrollableCursors());
             addProperty(CONNECTIONPROPERTIES, CONNECTIONPROPERTIESDESC, Properties.class, !connected, connection.getConnectionProperties());
             Property<?> ps = getSheet().get(Sheet.PROPERTIES).get(CONNECTIONPROPERTIES);
             ps.setValue("canEditAsText", Boolean.FALSE);                //NOI18N
diff --git a/db/src/org/netbeans/modules/db/resources/connection-1_1.dtd b/db/src/org/netbeans/modules/db/resources/connection-1_2.dtd
copy from db/src/org/netbeans/modules/db/resources/connection-1_1.dtd
copy to db/src/org/netbeans/modules/db/resources/connection-1_2.dtd
--- a/db/src/org/netbeans/modules/db/resources/connection-1_1.dtd
+++ b/db/src/org/netbeans/modules/db/resources/connection-1_2.dtd
@@ -19,7 +19,7 @@
 -->
 
 <!--- The root connection element. -->
-<!ELEMENT connection (driver-class,driver-name,database-url,schema,user,password?)>
+<!ELEMENT connection (driver-class,driver-name,database-url,schema,user,password?,use-scrollable-cursors?,separate-system-tables?)>
 
 <!--- The driver class. -->
 <!ELEMENT driver-class EMPTY>
@@ -50,6 +50,19 @@
 <!ATTLIST user 
     value CDATA #REQUIRED
 >
+
+<!--- Should be system tables shown separately? -->
+<!ELEMENT separate-system-tables EMPTY>
+<!ATTLIST separate-system-tables
+    value (true | false) #REQUIRED
+>
+
+<!--- Allow JDBC support for scrollable cursors? -->
+<!ELEMENT use-scrollable-cursors EMPTY>
+<!ATTLIST use-scrollable-cursors
+    value (true | false) #REQUIRED
+>
+
 <!--- The database password (hashed). -->
 <!ELEMENT password EMPTY>
 <!ATTLIST password 
diff --git a/db/src/org/netbeans/modules/db/resources/mf-layer.xml b/db/src/org/netbeans/modules/db/resources/mf-layer.xml
--- a/db/src/org/netbeans/modules/db/resources/mf-layer.xml
+++ b/db/src/org/netbeans/modules/db/resources/mf-layer.xml
@@ -624,6 +624,9 @@
                 <file name="DTD_Database_Connection_1_1" url="connection-1_1.dtd">
                     <attr name="hint.originalPublicID" stringvalue="-//NetBeans//DTD Database Connection 1.1//EN"/>
                 </file>
+                <file name="DTD_Database_Connection_1_2" url="connection-1_2.dtd">
+                    <attr name="hint.originalPublicID" stringvalue="-//NetBeans//DTD Database Connection 1.2//EN"/>
+                </file>
             </folder>
         </folder>
         
@@ -649,6 +652,11 @@
                     <attr name="instanceOf" stringvalue="org.openide.loaders.Environment$Provider"/>
                     <attr name="instanceCreate" methodvalue="org.netbeans.modules.db.explorer.DatabaseConnectionConvertor.createProvider"/>
                 </file>
+                <file name="DTD_Database_Connection_1_2.instance">
+                    <attr name="instanceClass" stringvalue="org.netbeans.modules.db.explorer.DatabaseConnectionConvertor"/>
+                    <attr name="instanceOf" stringvalue="org.openide.loaders.Environment$Provider"/>
+                    <attr name="instanceCreate" methodvalue="org.netbeans.modules.db.explorer.DatabaseConnectionConvertor.createProvider"/>
+                </file>
             </folder>
         </folder>
     </folder>
diff --git a/db/test/unit/src/org/netbeans/modules/db/explorer/bar-connection.xml b/db/test/unit/src/org/netbeans/modules/db/explorer/bar-connection.xml
--- a/db/test/unit/src/org/netbeans/modules/db/explorer/bar-connection.xml
+++ b/db/test/unit/src/org/netbeans/modules/db/explorer/bar-connection.xml
@@ -1,9 +1,10 @@
 <?xml version='1.0'?>
-<!DOCTYPE connection PUBLIC '-//NetBeans//DTD Database Connection 1.1//EN' 'http://www.netbeans.org/dtds/connection-1_1.dtd'>
+<!DOCTYPE connection PUBLIC '-//NetBeans//DTD Database Connection 1.2//EN' 'http://www.netbeans.org/dtds/connection-1_2.dtd'>
 <connection>
   <driver-class value='org.bar.BarDriver'/>
   <driver-name value='bar_driver'/>
   <database-url value='jdbc:bar:localhost'/>
   <schema value='schema'/>
   <user value='user'/>
+  <use-scrollable-cursors value='false'/>
 </connection>
diff --git a/db/test/unit/src/org/netbeans/modules/db/explorer/null-pwd-connection.xml b/db/test/unit/src/org/netbeans/modules/db/explorer/null-pwd-connection.xml
--- a/db/test/unit/src/org/netbeans/modules/db/explorer/null-pwd-connection.xml
+++ b/db/test/unit/src/org/netbeans/modules/db/explorer/null-pwd-connection.xml
@@ -1,9 +1,10 @@
 <?xml version='1.0'?>
-<!DOCTYPE connection PUBLIC '-//NetBeans//DTD Database Connection 1.1//EN' 'http://www.netbeans.org/dtds/connection-1_1.dtd'>
+<!DOCTYPE connection PUBLIC '-//NetBeans//DTD Database Connection 1.2//EN' 'http://www.netbeans.org/dtds/connection-1_2.dtd'>
 <connection>
   <driver-class value='org.bar.BarDriver'/>
   <driver-name value='bar_driver'/>
   <database-url value='jdbc:bar:localhost'/>
   <schema value='schema'/>
   <user value='user'/>
+  <use-scrollable-cursors value='false'/>
 </connection>