Attachment #135585 for bug #231030

This Bugzilla instance is a read-only archive of historic NetBeans bug reports. To report a bug in NetBeans please follow the project's instructions for reporting issues.

View | Details | Raw Unified | Return to bug 231030
Collapse All | Expand All

Lines 29-35 Link Here

(-)a/db.dataview/nbproject/project.xml (-1 / +1 lines)
29	<compile-dependency/>	29	<compile-dependency/>
30	<run-dependency>	30	<run-dependency>
31	<release-version>1</release-version>	31	<release-version>1</release-version>
32	<specification-version>1.25.0.5</specification-version>	32	<specification-version>1.56</specification-version>
33	</run-dependency>	33	</run-dependency>
34	</dependency>	34	</dependency>
35	<dependency>	35	<dependency>

Lines 86-91 Link Here

(-)a/db.dataview/src/org/netbeans/modules/db/dataview/output/SQLExecutionHelper.java (-58 / +209 lines)
86	private final DataView dataView;	86	private final DataView dataView;
87	// the RequestProcessor used for executing statements.	87	// the RequestProcessor used for executing statements.
88	private final RequestProcessor rp = new RequestProcessor("SQLStatementExecution", 20, true); // NOI18N	88	private final RequestProcessor rp = new RequestProcessor("SQLStatementExecution", 20, true); // NOI18N
		89	private static final String LIMIT_CLAUSE = "LIMIT "; // NOI18N
		90	public static final String OFFSET_CLAUSE = "OFFSET "; // NOI18N
		91	private boolean limitSupported = false;
		92	private boolean useScrollableCursors = false;
89	private int resultSetScrollType = ResultSet.TYPE_FORWARD_ONLY;	93	private int resultSetScrollType = ResultSet.TYPE_FORWARD_ONLY;
90	private boolean supportesMultipleResultSets = false;	94	private boolean supportesMultipleResultSets = false;
91		95
Lines 94-100 Link Here
94	}	98	}
95		99
96	void initialDataLoad() throws SQLException {	100	void initialDataLoad() throws SQLException {
97	assert (! SwingUtilities.isEventDispatchThread()) : "Must be called of the EDT!";	101	assert (! SwingUtilities.isEventDispatchThread()) : "Must be called off the EDT!";
98		102
99	/**	103	/**
100	* Wrap initializing the SQL result into a runnable. This makes it	104	* Wrap initializing the SQL result into a runnable. This makes it
Lines 118-149 Link Here
118	try {	122	try {
119	DatabaseConnection dc = dataView.getDatabaseConnection();	123	DatabaseConnection dc = dataView.getDatabaseConnection();
120	Connection conn = DBConnectionFactory.getInstance().getConnection(dc);	124	Connection conn = DBConnectionFactory.getInstance().getConnection(dc);
121		125	checkNonNullConnection(conn);
122	String msg;	126	checkSupportForMultipleResultSets(conn);
123	if (conn == null) {
124	Throwable t = DBConnectionFactory.getInstance()
125	.getLastException();
126	if (t != null) {
127	msg = t.getMessage();
128	} else {
129	msg = NbBundle.getMessage(SQLExecutionHelper.class,
130	"MSG_connection_failure", //NOI18N
131	dataView.getDatabaseConnection());
132	}
133	NotifyDescriptor nd = new NotifyDescriptor.Message(msg,
134	NotifyDescriptor.ERROR_MESSAGE);
135	DialogDisplayer.getDefault().notifyLater(nd);
136	LOGGER.log(Level.INFO, msg, t);
137	throw new SQLException(msg, t);
138	}
139	try {
140	supportesMultipleResultSets = conn.getMetaData().supportsMultipleResultSets();
141	} catch (SQLException ex) {
142	LOGGER.log(Level.INFO, "Database driver throws exception " //NOI18N
143	+ "when checking for multiple resultset support."); //NOI18N
144	}
145	DBMetaDataFactory dbMeta = new DBMetaDataFactory(conn);	127	DBMetaDataFactory dbMeta = new DBMetaDataFactory(conn);
		128	limitSupported = dbMeta.supportsLimit();
146	String sql = dataView.getSQLString();	129	String sql = dataView.getSQLString();
		130	boolean isSelect = isSelectStatement(sql);
147		131
148	updateScrollableSupport(conn, dc, sql);	132	updateScrollableSupport(conn, dc, sql);
149		133
Lines 156-166 Link Here
156	return;	140	return;
157	}	141	}
158	// Read multiple Resultsets	142	// Read multiple Resultsets
159	boolean resultSet = executeSQLStatement(stmt, sql);	143	boolean isResultSet = executeSQLStatement(stmt, sql);
160		144
161	// @todo: This needs clearing up => in light of request for	145	// @todo: This needs clearing up => in light of request for
162	// the ability to disable autocommit, this need to go	146	// the ability to disable autocommit, this need to go
163	if (dataView.getUpdateCount() != -1) {	147	if (!isResultSet \|\| dataView.getUpdateCount() != -1) {
164	if (!conn.getAutoCommit()) {	148	if (!conn.getAutoCommit()) {
165	conn.commit();	149	conn.commit();
166	}	150	}
Lines 169-205 Link Here
169	if (Thread.interrupted()) {	153	if (Thread.interrupted()) {
170	return;	154	return;
171	}	155	}
172	// @todo: This needs clearing up => in light of request for
173	// the ability to disable autocommit, this need to go
174	if (!resultSet) {
175	if (!conn.getAutoCommit()) {
176	conn.commit();
177	}
178	return;
179	}
180	boolean needReread = false;	156	boolean needReread = false;
181	ResultSet rs = null;	157	ResultSet rs = null;
182		158
183	while (true) {	159	while (true) {
184	if (resultSet) {	160	if (isResultSet) {
185	rs = stmt.getResultSet();	161	rs = stmt.getResultSet();
186		162
187	Collection<DBTable> tables = dbMeta.generateDBTables(	163	Collection<DBTable> tables = dbMeta.generateDBTables(
188	rs, sql, isSelectStatement(sql));	164	rs, sql, isSelect);
189	DataViewDBTable dvTable = new DataViewDBTable(tables);	165	DataViewDBTable dvTable = new DataViewDBTable(tables);
190	DataViewPageContext pageContext = dataView.addPageContext(	166	DataViewPageContext pageContext = dataView.addPageContext(
191	dvTable);	167	dvTable);
192	needReread \|= resultSetNeedsReloading(dvTable);	168	needReread \|= resultSetNeedsReloading(dvTable);
193		169
194	if (!needReread) {	170	if (!needReread) {
195	loadDataFrom(pageContext, rs, true);	171	loadDataFrom(pageContext, rs, useScrollableCursors);
196	}	172	}
197	}	173	}
198	if (supportesMultipleResultSets) {	174	if (supportesMultipleResultSets) {
199	resultSet = stmt.getMoreResults();	175	isResultSet = stmt.getMoreResults();
200	// @todo: Do somethink intelligent with the updatecounts	176	// @todo: Do somethink intelligent with the updatecounts
201	int updateCount = stmt.getUpdateCount();	177	int updateCount = stmt.getUpdateCount();
202	if (resultSet == false && updateCount == -1) {	178	if (isResultSet == false && updateCount == -1) {
203	break;	179	break;
204	}	180	}
205	} else {	181	} else {
Lines 208-228 Link Here
208	}	184	}
209		185
210	if (needReread) {	186	if (needReread) {
211	resultSet = executeSQLStatement(stmt, sql);	187	isResultSet = executeSQLStatement(stmt, sql);
212	int res = -1;	188	int res = -1;
213	while (true) {	189	while (true) {
214	if (resultSet) {	190	if (isResultSet) {
215	res++;	191	res++;
216	rs = stmt.getResultSet();	192	rs = stmt.getResultSet();
217	DataViewPageContext pageContext = dataView.getPageContext(	193	DataViewPageContext pageContext = dataView.getPageContext(
218	res);	194	res);
219	loadDataFrom(pageContext, rs, true);	195	loadDataFrom(pageContext, rs, useScrollableCursors);
220	}	196	}
221	if (supportesMultipleResultSets) {	197	if (supportesMultipleResultSets) {
222	resultSet = stmt.getMoreResults();	198	isResultSet = stmt.getMoreResults();
223	// @todo: Do somethink intelligent with the updatecounts	199	// @todo: Do somethink intelligent with the updatecounts
224	int updateCount = stmt.getUpdateCount();	200	int updateCount = stmt.getUpdateCount();
225	if (resultSet == false && updateCount == -1) {	201	if (isResultSet == false && updateCount == -1) {
226	break;	202	break;
227	}	203	}
228	} else {	204	} else {
Lines 230-235 Link Here
230	}	206	}
231	}	207	}
232	}	208	}
		209	// If total count was not retrieved using scrollable cursors,
		210	// compute it now.
		211	if (!useScrollableCursors && dataView.getPageContexts().size() > 0) {
		212	getTotalCount(isSelect, sql, stmt, dataView.getPageContext(0));
		213	}
233	DataViewUtils.closeResources(rs);	214	DataViewUtils.closeResources(rs);
234	} catch (SQLException sqlEx) {	215	} catch (SQLException sqlEx) {
235	this.ex = sqlEx;	216	this.ex = sqlEx;
Lines 256-261 Link Here
256	}	237	}
257	return true;	238	return true;
258	}	239	}
		240
		241	/**
		242	* Check that the connection is not null. If it is null, try to find
		243	* cause of the failure and throw an exception.
		244	*/
		245	private void checkNonNullConnection(Connection conn) throws
		246	SQLException {
		247	if (conn == null) {
		248	String msg;
		249	Throwable t = DBConnectionFactory.getInstance()
		250	.getLastException();
		251	if (t != null) {
		252	msg = t.getMessage();
		253	} else {
		254	msg = NbBundle.getMessage(SQLExecutionHelper.class,
		255	"MSG_connection_failure", //NOI18N
		256	dataView.getDatabaseConnection());
		257	}
		258	NotifyDescriptor nd = new NotifyDescriptor.Message(msg,
		259	NotifyDescriptor.ERROR_MESSAGE);
		260	DialogDisplayer.getDefault().notifyLater(nd);
		261	LOGGER.log(Level.INFO, msg, t);
		262	throw new SQLException(msg, t);
		263	}
		264	}
		265
		266	private void checkSupportForMultipleResultSets(Connection conn) {
		267	try {
		268	supportesMultipleResultSets = conn.getMetaData().supportsMultipleResultSets();
		269	} catch (SQLException ex) {
		270	LOGGER.log(Level.INFO, "Database driver throws exception " //NOI18N
		271	+ "when checking for multiple resultset support."); //NOI18N
		272	}
		273	}
259	}	274	}
260	Loader l = new Loader();	275	Loader l = new Loader();
261	Future<?> f = rp.submit(l);	276	Future<?> f = rp.submit(l);
Lines 635-641 Link Here
635	DataViewPageContext pageContext = dataView.getPageContext(	650	DataViewPageContext pageContext = dataView.getPageContext(
636	res);	651	res);
637	rs = stmt.getResultSet();	652	rs = stmt.getResultSet();
638	loadDataFrom(pageContext, rs, getTotal);	653	loadDataFrom(pageContext, rs, getTotal && useScrollableCursors);
639	}	654	}
640	if (supportesMultipleResultSets) {	655	if (supportesMultipleResultSets) {
641	resultSet = stmt.getMoreResults();	656	resultSet = stmt.getMoreResults();
Lines 649-654 Link Here
649	}	664	}
650	}	665	}
651		666
		667	// Get total count using the old-fashioned method.
		668	if (!useScrollableCursors && getTotal && dataView.getPageContexts().size() > 0) {
		669	getTotalCount(isSelectStatement(sql), sql, stmt, dataView.getPageContext(0));
		670	}
652	DataViewUtils.closeResources(rs);	671	DataViewUtils.closeResources(rs);
653	} catch (SQLException sqlEx) {	672	} catch (SQLException sqlEx) {
654	String title = NbBundle.getMessage(SQLExecutionHelper.class, "MSG_error");	673	String title = NbBundle.getMessage(SQLExecutionHelper.class, "MSG_error");
Lines 708-715 Link Here
708	boolean hasNext = false;	727	boolean hasNext = false;
709	boolean needSlowSkip = true;	728	boolean needSlowSkip = true;
710		729
711	if (rs.getType() == ResultSet.TYPE_SCROLL_INSENSITIVE	730	if (useScrollableCursors
712	\|\| rs.getType() == ResultSet.TYPE_SCROLL_SENSITIVE) {	731	&& (rs.getType() == ResultSet.TYPE_SCROLL_INSENSITIVE
		732	\|\| rs.getType() == ResultSet.TYPE_SCROLL_SENSITIVE)) {
713	try {	733	try {
714	hasNext = rs.absolute(startFrom);	734	hasNext = rs.absolute(startFrom);
715	needSlowSkip = false;	735	needSlowSkip = false;
Lines 787-795 Link Here
787	private Statement prepareSQLStatement(Connection conn, String sql, boolean needTotal) throws SQLException {	807	private Statement prepareSQLStatement(Connection conn, String sql, boolean needTotal) throws SQLException {
788	Statement stmt = null;	808	Statement stmt = null;
789	if (sql.startsWith("{")) { // NOI18N	809	if (sql.startsWith("{")) { // NOI18N
790	stmt = conn.prepareCall(sql, resultSetScrollType, ResultSet.CONCUR_READ_ONLY);	810	stmt = useScrollableCursors
		811	? conn.prepareCall(sql, resultSetScrollType, ResultSet.CONCUR_READ_ONLY)
		812	: conn.prepareCall(sql);
791	} else if (isSelectStatement(sql)) {	813	} else if (isSelectStatement(sql)) {
792	stmt = conn.createStatement(resultSetScrollType, ResultSet.CONCUR_READ_ONLY);	814	stmt = useScrollableCursors
		815	? conn.createStatement(resultSetScrollType, ResultSet.CONCUR_READ_ONLY)
		816	: conn.createStatement();
793		817
794	// set a reasonable fetchsize	818	// set a reasonable fetchsize
795	setFetchSize(stmt, 50);	819	setFetchSize(stmt, 50);
Lines 819-825 Link Here
819	}	843	}
820	}	844	}
821	} else {	845	} else {
822	stmt = conn.createStatement(resultSetScrollType, ResultSet.CONCUR_READ_ONLY);	846	stmt = useScrollableCursors
		847	? conn.createStatement(resultSetScrollType, ResultSet.CONCUR_READ_ONLY)
		848	: conn.createStatement();
823	}	849	}
824	return stmt;	850	return stmt;
825	}	851	}
Lines 834-840 Link Here
834	isResultSet = ((PreparedStatement) stmt).execute();	860	isResultSet = ((PreparedStatement) stmt).execute();
835	} else {	861	} else {
836	try {	862	try {
837	isResultSet = stmt.execute(sql);	863	DataViewPageContext pc = dataView.getPageContexts().size() > 0
		864	? dataView.getPageContext(0) : null;
		865	isResultSet = stmt.execute(appendLimitIfRequired(pc, sql));
838	} catch (NullPointerException ex) {	866	} catch (NullPointerException ex) {
839	LOGGER.log(Level.SEVERE, "Failed to execute SQL Statement [{0}], cause: {1}", new Object[] {sql, ex});	867	LOGGER.log(Level.SEVERE, "Failed to execute SQL Statement [{0}], cause: {1}", new Object[] {sql, ex});
840	throw new SQLException(ex);	868	throw new SQLException(ex);
Lines 867-876 Link Here
867	return isResultSet;	895	return isResultSet;
868	}	896	}
869		897
		898	private void getTotalCount(boolean isSelect, String sql, Statement stmt,
		899	DataViewPageContext pageContext) {
		900	if (!isSelect) {
		901	setTotalCount(null, pageContext);
		902	return;
		903	}
		904
		905	// Case for LIMIT n OFFSET m
		906	if (isLimitUsedInSelect(sql)) {
		907	try {
		908	String lmtStr = sql.toUpperCase().split(LIMIT_CLAUSE)[1].trim();
		909	int rCnt = Integer.parseInt(lmtStr.split(" ")[0]);
		910	pageContext.setTotalRows(rCnt);
		911	return;
		912	} catch (NumberFormatException nex) {
		913	LOGGER.log(Level.FINE, null, nex);
		914	}
		915	}
		916
		917	// SELECT COUNT(*) FROM (sqlquery) alias
		918	ResultSet cntResultSet = null;
		919	try {
		920	cntResultSet = stmt.executeQuery(
		921	SQLStatementGenerator.getCountAsSubQuery(sql));
		922	setTotalCount(cntResultSet, pageContext);
		923	return;
		924	} catch (SQLException e) {
		925	} finally {
		926	DataViewUtils.closeResources(cntResultSet);
		927	}
		928
		929	// Try spliting the query by FROM and use "SELECT COUNT(*) FROM" + "2nd part sql"
		930	if (!isGroupByUsedInSelect(sql)) {
		931	cntResultSet = null;
		932	try {
		933	cntResultSet = stmt.executeQuery(
		934	SQLStatementGenerator.getCountSQLQuery(sql));
		935	setTotalCount(cntResultSet, pageContext);
		936	return;
		937	} catch (SQLException e) {
		938	} finally {
		939	DataViewUtils.closeResources(cntResultSet);
		940	}
		941	}
		942
		943	// In worse case, get the count from resultset
		944	cntResultSet = null;
		945	int totalRows = 0;
		946	try {
		947	// reset fetch size
		948	int fetchSize = pageContext.getPageSize();
		949	try {
		950	fetchSize = stmt.getFetchSize();
		951	stmt.setFetchSize(20000);
		952	} catch (SQLException sqe) {
		953	// ignore
		954	}
		955
		956	cntResultSet = stmt.executeQuery(sql);
		957	while (cntResultSet.next()) {
		958	totalRows++;
		959	}
		960	pageContext.setTotalRows(totalRows);
		961
962	// set to old value
963	try {
964	stmt.setFetchSize(fetchSize);
965	} catch (SQLException sqe) {
966	// ignore
967	}
968	return;
969	} catch (SQLException e) {
970	LOGGER.log(Level.FINE, null, e);
971	} finally {
972	DataViewUtils.closeResources(cntResultSet);
973	}
974
975	// Unable to compute the total rows
976	setTotalCount(null, pageContext);
977	}
978
870	private boolean isSelectStatement(String queryString) {	979	private boolean isSelectStatement(String queryString) {
871	return queryString.trim().toUpperCase().startsWith("SELECT") && queryString.trim().toUpperCase().indexOf("INTO") == -1; // NOI18N	980	return queryString.trim().toUpperCase().startsWith("SELECT") && queryString.trim().toUpperCase().indexOf("INTO") == -1; // NOI18N
872	}	981	}
873		982
		983	private boolean isLimitUsedInSelect(String sql) {
		984	return sql.toUpperCase().indexOf(LIMIT_CLAUSE) != -1;
		985	}
		986
		987	private boolean isGroupByUsedInSelect(String sql) {
		988	return sql.toUpperCase().indexOf(" GROUP BY ") != -1 \|\| sql.toUpperCase().indexOf(
		989	" COUNT(*) ") != -1; // NOI18N
		990	}
		991
		992	void setTotalCount(ResultSet countresultSet, DataViewPageContext pageContext) {
		993	try {
		994	if (countresultSet == null) {
		995	pageContext.setTotalRows(-1);
		996	pageContext.setTotalRows(-1);
		997	} else {
		998	if (countresultSet.next()) {
		999	int count = countresultSet.getInt(1);
		1000	pageContext.setTotalRows(count);
		1001	pageContext.setTotalRows(count);
		1002	}
		1003	}
		1004	} catch (SQLException ex) {
		1005	LOGGER.log(Level.SEVERE, "Could not get total row count ", ex); // NOI18N
		1006	}
		1007	}
		1008
		1009	private String appendLimitIfRequired(DataViewPageContext pageContext,
		1010	String sql) {
		1011	if (useScrollableCursors) {
		1012	return sql;
		1013	} else if (limitSupported && isSelectStatement(sql)
		1014	&& !isLimitUsedInSelect(sql)) {
		1015
		1016	int pageSize = pageContext == null ? dataView.getPageSize()
		1017	: pageContext.getPageSize();
		1018	int currentPos = pageContext == null ? 1
		1019	: pageContext.getCurrentPos();
		1020	return sql + ' ' + LIMIT_CLAUSE + pageSize
		1021	+ ' ' + OFFSET_CLAUSE + (currentPos - 1);
		1022	} else {
		1023	return sql;
		1024	}
		1025	}
		1026
874	static String millisecondsToSeconds(long ms) {	1027	static String millisecondsToSeconds(long ms) {
875	NumberFormat fmt = NumberFormat.getInstance();	1028	NumberFormat fmt = NumberFormat.getInstance();
876	fmt.setMaximumFractionDigits(3);	1029	fmt.setMaximumFractionDigits(3);
Lines 931-936 Link Here
931	*/	1084	*/
932	private void updateScrollableSupport(Connection conn, DatabaseConnection dc,	1085	private void updateScrollableSupport(Connection conn, DatabaseConnection dc,
933	String sql) {	1086	String sql) {
		1087	useScrollableCursors = dc.isUseScrollableCursors();
		1088	if (!useScrollableCursors) {
		1089	return;
		1090	}
934	String driverName = dc.getDriverClass();	1091	String driverName = dc.getDriverClass();
935	/* Derby fails to support scrollable cursors when invoking 'stored procedures'	1092	/* Derby fails to support scrollable cursors when invoking 'stored procedures'
936	which return resultsets - it fails hard: not throwing a SQLException,	1093	which return resultsets - it fails hard: not throwing a SQLException,
Lines 941-952 Link Here
941	resultSetScrollType = ResultSet.TYPE_FORWARD_ONLY;	1098	resultSetScrollType = ResultSet.TYPE_FORWARD_ONLY;
942	return;	1099	return;
943	}	1100	}
944	} else if (driverName != null
945	&& driverName.startsWith("com.informix.jdbc.IfxDriver")) { //NOI18N
946	// Informix failes scrollable result sets if blob columns are part
947	// of the resultset -> disable ...
948	resultSetScrollType = ResultSet.TYPE_FORWARD_ONLY;
949	return;
950	}	1101	}
951	/* Try to get a "good" scrollable ResultSet and follow the DBs support */	1102	/* Try to get a "good" scrollable ResultSet and follow the DBs support */
952	try {	1103	try {




        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);

Lines 45-51 Link Here

(-)a/db/nbproject/project.properties (-1 / +1 lines)
45	javadoc.arch=${basedir}/arch.xml	45	javadoc.arch=${basedir}/arch.xml
46	javadoc.apichanges=${basedir}/apichanges.xml	46	javadoc.apichanges=${basedir}/apichanges.xml
47		47
48	spec.version.base=1.55.0	48	spec.version.base=1.56
49		49
50	extra.module.files=modules/ext/ddl.jar	50	extra.module.files=modules/ext/ddl.jar
51		51




    }

    /**
     * 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

Lines 148-153 Link Here

(-)a/db/src/org/netbeans/api/db/explorer/node/Bundle.properties (+2 lines)
148	ConnectionPropertiesDescription=Connection properties	148	ConnectionPropertiesDescription=Connection properties
149	SeparateSystemTables=Show system tables separately	149	SeparateSystemTables=Show system tables separately
150	SeparateSystemTablesDescription=Use special node for system tables	150	SeparateSystemTablesDescription=Use special node for system tables
		151	UseScrollableCursors=Use scrollable cursors
		152	UseScrollableCursorsDescription=Use JDBC support for scrollable cursors. This makes the execution faster, but some drivers can become unstable.
151		153
152	# Booleans	154	# Booleans
153		155

Lines 166-171 Link Here

(-)a/db/src/org/netbeans/modules/db/explorer/DatabaseConnection.java (+25 lines)
166		166
167	private volatile boolean separateSystemTables = false;	167	private volatile boolean separateSystemTables = false;
168		168
		169	private Boolean useScrollableCursors = null; // null = driver default
		170
169	/**	171	/**
170	* The API DatabaseConnection (delegates to this instance)	172	* The API DatabaseConnection (delegates to this instance)
171	*/	173	*/
Lines 1347-1350 Link Here
1347	this.separateSystemTables = separateSystemTables;	1349	this.separateSystemTables = separateSystemTables;
1348	propertySupport.firePropertyChange("separateSystemTables", oldVal, separateSystemTables); //NOI18N	1350	propertySupport.firePropertyChange("separateSystemTables", oldVal, separateSystemTables); //NOI18N
1349	}	1351	}
		1352
		1353	/**
		1354	* Decide whether scrollable cursors should be used by the connection.
		1355	*/
		1356	private boolean isUseScrollableCursorsByDefault() {
		1357	return drv != null
		1358	&& (drv.startsWith("org.apache.derby") //NOI18N
		1359	\|\| drv.startsWith("com.mysql") //NOI18N
		1360	\|\| drv.startsWith("oracle") //NOI18N
		1361	\|\| drv.startsWith("org.postgresql")); //NOI18N
		1362	}
		1363
		1364	public boolean isUseScrollableCursors() {
		1365	return useScrollableCursors == null
		1366	? isUseScrollableCursorsByDefault()
		1367	: useScrollableCursors;
		1368	}
		1369
		1370	public void setUseScrollableCursors(boolean useScrollableCursors) {
		1371	boolean oldVal = isUseScrollableCursors();
		1372	this.useScrollableCursors = useScrollableCursors;
		1373	propertySupport.firePropertyChange("useScrollableCursors", oldVal, useScrollableCursors); //NOI18N
		1374	}
1350	}	1375	}

Lines 89-94 Link Here

(-)a/db/src/org/netbeans/modules/db/explorer/node/ConnectionNode.java (+7 lines)
89	private static final String CONNECTIONPROPERTIESDESC = "ConnectionPropertiesDescription"; //NOI18N	89	private static final String CONNECTIONPROPERTIESDESC = "ConnectionPropertiesDescription"; //NOI18N
90	private static final String SEPARATESYSTEMTABLES = "SeparateSystemTables"; //NOI18N	90	private static final String SEPARATESYSTEMTABLES = "SeparateSystemTables"; //NOI18N
91	private static final String SEPARATESYSTEMTABLESDESC = "SeparateSystemTablesDescription"; //NOI18N	91	private static final String SEPARATESYSTEMTABLESDESC = "SeparateSystemTablesDescription"; //NOI18N
		92	private static final String USESCROLLABLECURSORS = "UseScrollableCursors"; //NOI18N
		93	private static final String USESCROLLABLECURSORSDESC = "UseScrollableCursorsDescription"; //NOI18N
92	private static final String FOLDER = "Connection"; // NOI18N	94	private static final String FOLDER = "Connection"; // NOI18N
93	private static final RequestProcessor RP = new RequestProcessor(ConnectionNode.class.getName());	95	private static final RequestProcessor RP = new RequestProcessor(ConnectionNode.class.getName());
94		96
Lines 175-180 Link Here
175	&& val instanceof Boolean) {	177	&& val instanceof Boolean) {
176	connection.setSeparateSystemTables((Boolean) val);	178	connection.setSeparateSystemTables((Boolean) val);
177	refreshNode = false;	179	refreshNode = false;
		180	} else if (nps.getName().equals(USESCROLLABLECURSORS)
		181	&& val instanceof Boolean) {
		182	connection.setUseScrollableCursors((Boolean) val);
		183	refreshNode = false;
178	}	184	}
179		185
180	super.setPropertyValue(nps, val);	186	super.setPropertyValue(nps, val);
Lines 197-202 Link Here
197	addProperty(REMEMBERPW, REMEMBERPWDESC,	203	addProperty(REMEMBERPW, REMEMBERPWDESC,
198	Boolean.class, !connected, connection.rememberPassword());	204	Boolean.class, !connected, connection.rememberPassword());
199	addProperty(SEPARATESYSTEMTABLES, SEPARATESYSTEMTABLESDESC, Boolean.class, true, connection.isSeparateSystemTables());	205	addProperty(SEPARATESYSTEMTABLES, SEPARATESYSTEMTABLESDESC, Boolean.class, true, connection.isSeparateSystemTables());
		206	addProperty(USESCROLLABLECURSORS, USESCROLLABLECURSORSDESC, Boolean.class, true, connection.isUseScrollableCursors());
200	addProperty(CONNECTIONPROPERTIES, CONNECTIONPROPERTIESDESC, Properties.class, !connected, connection.getConnectionProperties());	207	addProperty(CONNECTIONPROPERTIES, CONNECTIONPROPERTIESDESC, Properties.class, !connected, connection.getConnectionProperties());
201	Property<?> ps = getSheet().get(Sheet.PROPERTIES).get(CONNECTIONPROPERTIES);	208	Property<?> ps = getSheet().get(Sheet.PROPERTIES).get(CONNECTIONPROPERTIES);
202	ps.setValue("canEditAsText", Boolean.FALSE); //NOI18N	209	ps.setValue("canEditAsText", Boolean.FALSE); //NOI18N




-->

<!--- The root connection element. -->
<!ELEMENT connection (driver-class,driver-name,database-url,schema,user,password?,use-scrollable-cursors?,separate-system-tables?)>

<!--- The driver class. -->
<!ELEMENT driver-class EMPTY>

<!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 

Lines 624-629 Link Here

(-)a/db/src/org/netbeans/modules/db/resources/mf-layer.xml (+8 lines)
624	<file name="DTD_Database_Connection_1_1" url="connection-1_1.dtd">	624	<file name="DTD_Database_Connection_1_1" url="connection-1_1.dtd">
625	<attr name="hint.originalPublicID" stringvalue="-//NetBeans//DTD Database Connection 1.1//EN"/>	625	<attr name="hint.originalPublicID" stringvalue="-//NetBeans//DTD Database Connection 1.1//EN"/>
626	</file>	626	</file>
		627	<file name="DTD_Database_Connection_1_2" url="connection-1_2.dtd">
		628	<attr name="hint.originalPublicID" stringvalue="-//NetBeans//DTD Database Connection 1.2//EN"/>
		629	</file>
627	</folder>	630	</folder>
628	</folder>	631	</folder>
629		632
Lines 649-654 Link Here
649	<attr name="instanceOf" stringvalue="org.openide.loaders.Environment$Provider"/>	652	<attr name="instanceOf" stringvalue="org.openide.loaders.Environment$Provider"/>
650	<attr name="instanceCreate" methodvalue="org.netbeans.modules.db.explorer.DatabaseConnectionConvertor.createProvider"/>	653	<attr name="instanceCreate" methodvalue="org.netbeans.modules.db.explorer.DatabaseConnectionConvertor.createProvider"/>
651	</file>	654	</file>
		655	<file name="DTD_Database_Connection_1_2.instance">
		656	<attr name="instanceClass" stringvalue="org.netbeans.modules.db.explorer.DatabaseConnectionConvertor"/>
		657	<attr name="instanceOf" stringvalue="org.openide.loaders.Environment$Provider"/>
		658	<attr name="instanceCreate" methodvalue="org.netbeans.modules.db.explorer.DatabaseConnectionConvertor.createProvider"/>
		659	</file>
652	</folder>	660	</folder>
653	</folder>	661	</folder>
654	</folder>	662	</folder>

Lines 1-9 Link Here

(-)a/db/test/unit/src/org/netbeans/modules/db/explorer/bar-connection.xml (-1 / +2 lines)
1	<?xml version='1.0'?>	1	<?xml version='1.0'?>
2	<!DOCTYPE connection PUBLIC '-//NetBeans//DTD Database Connection 1.1//EN' 'http://www.netbeans.org/dtds/connection-1_1.dtd'>	2	<!DOCTYPE connection PUBLIC '-//NetBeans//DTD Database Connection 1.2//EN' 'http://www.netbeans.org/dtds/connection-1_2.dtd'>
3	<connection>	3	<connection>
4	<driver-class value='org.bar.BarDriver'/>	4	<driver-class value='org.bar.BarDriver'/>
5	<driver-name value='bar_driver'/>	5	<driver-name value='bar_driver'/>
6	<database-url value='jdbc:bar:localhost'/>	6	<database-url value='jdbc:bar:localhost'/>
7	<schema value='schema'/>	7	<schema value='schema'/>
8	<user value='user'/>	8	<user value='user'/>
		9	<use-scrollable-cursors value='false'/>
9	</connection>	10	</connection>

Lines 1-9 Link Here

(-)a/db/test/unit/src/org/netbeans/modules/db/explorer/null-pwd-connection.xml (-1 / +2 lines)
1	<?xml version='1.0'?>	1	<?xml version='1.0'?>
2	<!DOCTYPE connection PUBLIC '-//NetBeans//DTD Database Connection 1.1//EN' 'http://www.netbeans.org/dtds/connection-1_1.dtd'>	2	<!DOCTYPE connection PUBLIC '-//NetBeans//DTD Database Connection 1.2//EN' 'http://www.netbeans.org/dtds/connection-1_2.dtd'>
3	<connection>	3	<connection>
4	<driver-class value='org.bar.BarDriver'/>	4	<driver-class value='org.bar.BarDriver'/>
5	<driver-name value='bar_driver'/>	5	<driver-name value='bar_driver'/>
6	<database-url value='jdbc:bar:localhost'/>	6	<database-url value='jdbc:bar:localhost'/>
7	<schema value='schema'/>	7	<schema value='schema'/>
8	<user value='user'/>	8	<user value='user'/>
		9	<use-scrollable-cursors value='false'/>
9	</connection>	10	</connection>

Return to bug 231030

Lines 287-292 Link Here

(-)a/db/src/org/netbeans/api/db/explorer/DatabaseConnection.java (+11 lines)
287	}	287	}
288		288
289	/**	289	/**
		290	* Check whether usage of scrollable cursors is recommended for this
		291	* connection.
		292	*
		293	* @return True if scrollable cursors can be used, false if scrollable
		294	* cursors are not supported by driver or database of this connection.
		295	*/
		296	public boolean isUseScrollableCursors() {
		297	return delegate.isUseScrollableCursors();
		298	}
		299
		300	/**
290	* Returns the {@link java.sql.Connection} instance which encapsulates	301	* Returns the {@link java.sql.Connection} instance which encapsulates
291	* the physical connection to the database if this database connection	302	* the physical connection to the database if this database connection
292	* is connected. Note that "connected" here means "connected using the	303	* is connected. Note that "connected" here means "connected using the

Lines 335-340 Link Here

(-)a/db.dataview/src/org/netbeans/modules/db/dataview/output/SQLStatementGenerator.java (+16 lines)
335	return sql.toString();	335	return sql.toString();
336	}	336	}
337		337
		338	static String getCountSQLQuery(String queryString) {
		339	// User may type "FROM" in either lower, upper or mixed case
		340	String[] splitByFrom = queryString.toUpperCase().split("FROM"); // NOI18N
		341	queryString = queryString.substring(splitByFrom[0].length());
		342
		343	String[] splitByOrderBy = queryString.toUpperCase().split("ORDER BY"); // NOI18N
		344	queryString = queryString.substring(0, splitByOrderBy[0].length());
		345	return "SELECT COUNT(*) " + queryString; // NOI18N
		346	}
		347
		348	static String getCountAsSubQuery(String queryString) {
		349	String[] splitByOrderBy = queryString.toUpperCase().split("ORDER BY"); // NOI18N
		350	queryString = queryString.substring(0, splitByOrderBy[0].length());
		351	return "SELECT COUNT(*) FROM (" + queryString + ") C2668"; // NOI18N
		352	}
		353
338	private boolean addSeparator(boolean and, StringBuilder sql, String sep) {	354	private boolean addSeparator(boolean and, StringBuilder sql, String sep) {
339	if (and) {	355	if (and) {
340	sql.append(sep);	356	sql.append(sep);

Lines 253-258 Link Here

(-)a/db/src/org/netbeans/modules/db/explorer/DatabaseConnectionConvertor.java (-1 / +9 lines)
253	dbconn.addImportantCatalog(importantDatabase);	253	dbconn.addImportantCatalog(importantDatabase);
254	}	254	}
255	dbconn.setSeparateSystemTables(handler.separateSystemTables);	255	dbconn.setSeparateSystemTables(handler.separateSystemTables);
		256	if (handler.useScrollableCursors != null) {
		257	dbconn.setUseScrollableCursors(handler.useScrollableCursors);
		258	}
256	LOGGER.fine("Created DatabaseConnection[" + dbconn.toString() + "] from file: " + handler.connectionFileName);	259	LOGGER.fine("Created DatabaseConnection[" + dbconn.toString() + "] from file: " + handler.connectionFileName);
257		260
258	return dbconn;	261	return dbconn;
Lines 392-398 Link Here
392		395
393	void write(PrintWriter pw, String name) throws IOException {	396	void write(PrintWriter pw, String name) throws IOException {
394	pw.println("<?xml version='1.0'?>"); //NOI18N	397	pw.println("<?xml version='1.0'?>"); //NOI18N
395	pw.println("<!DOCTYPE connection PUBLIC '-//NetBeans//DTD Database Connection 1.1//EN' 'http://www.netbeans.org/dtds/connection-1_1.dtd'>"); //NOI18N	398	pw.println("<!DOCTYPE connection PUBLIC '-//NetBeans//DTD Database Connection 1.2//EN' 'http://www.netbeans.org/dtds/connection-1_2.dtd'>"); //NOI18N
396	pw.println("<connection>"); //NOI18N	399	pw.println("<connection>"); //NOI18N
397	pw.println(" <driver-class value='" + XMLUtil.toAttributeValue(instance.getDriver()) + "'/>"); //NOI18N	400	pw.println(" <driver-class value='" + XMLUtil.toAttributeValue(instance.getDriver()) + "'/>"); //NOI18N
398	pw.println(" <driver-name value='" + XMLUtil.toAttributeValue(instance.getDriverName()) + "'/>"); // NOI18N	401	pw.println(" <driver-name value='" + XMLUtil.toAttributeValue(instance.getDriverName()) + "'/>"); // NOI18N
Lines 435-440 Link Here
435	if (instance.isSeparateSystemTables()) {	438	if (instance.isSeparateSystemTables()) {
436	pw.println(" <separate-system-tables value='true'/>"); //NOI18N	439	pw.println(" <separate-system-tables value='true'/>"); //NOI18N
437	}	440	}
		441	pw.println(" <use-scrollable-cursors value='" + instance.isUseScrollableCursors() + "'/>"); //NOI18N
438	pw.println("</connection>"); //NOI18N	442	pw.println("</connection>"); //NOI18N
439	}	443	}
440	}	444	}
Lines 455-460 Link Here
455	private static final String ELEMENT_IMPORTANT_CATALOG = "important-catalog"; //NOI18N	459	private static final String ELEMENT_IMPORTANT_CATALOG = "important-catalog"; //NOI18N
456	private static final String ELEMENT_CONNECTION_PROPERTY = "connection-property"; // NOI18N	460	private static final String ELEMENT_CONNECTION_PROPERTY = "connection-property"; // NOI18N
457	private static final String ELEMENT_SEPARATE_SYS_TABLES = "separate-system-tables"; //NOI18N	461	private static final String ELEMENT_SEPARATE_SYS_TABLES = "separate-system-tables"; //NOI18N
		462	private static final String ELEMENT_USE_SCROLLABLE_CURSORS = "use-scrollable-cursors"; //NOI18N
458	private static final String ELEMENT_CONNECTION_PROPERTY_NAME = "name"; // NOI18N	463	private static final String ELEMENT_CONNECTION_PROPERTY_NAME = "name"; // NOI18N
459	private static final String ELEMENT_CONNECTION_PROPERTY_VALUE = "value"; // NOI18N	464	private static final String ELEMENT_CONNECTION_PROPERTY_VALUE = "value"; // NOI18N
460	private static final String ATTR_PROPERTY_VALUE = "value"; // NOI18N	465	private static final String ATTR_PROPERTY_VALUE = "value"; // NOI18N
Lines 473-478 Link Here
473	String displayName;	478	String displayName;
474	Properties connectionProperties;	479	Properties connectionProperties;
475	boolean separateSystemTables = false;	480	boolean separateSystemTables = false;
		481	Boolean useScrollableCursors = null;
476	List<String> importantSchemas = new ArrayList<String>();	482	List<String> importantSchemas = new ArrayList<String>();
477	List<String> importantCatalogs = new ArrayList<String>();	483	List<String> importantCatalogs = new ArrayList<String>();
478		484
Lines 542-547 Link Here
542	importantCatalogs.add(value);	548	importantCatalogs.add(value);
543	} else if (ELEMENT_SEPARATE_SYS_TABLES.equals(qName)) {	549	} else if (ELEMENT_SEPARATE_SYS_TABLES.equals(qName)) {
544	separateSystemTables = Boolean.parseBoolean(value);	550	separateSystemTables = Boolean.parseBoolean(value);
		551	} else if (ELEMENT_USE_SCROLLABLE_CURSORS.equals(qName)) {
		552	useScrollableCursors = Boolean.parseBoolean(value);
545	}	553	}
546	}	554	}
547		555

Lines 19-25 Link Here

(-)a/db/src/org/netbeans/modules/db/resources/connection-1_1.dtd (-1 / +14 lines)
19	-->	19	-->
20		20
21	<!--- The root connection element. -->	21	<!--- The root connection element. -->
22	<!ELEMENT connection (driver-class,driver-name,database-url,schema,user,password?)>	22	<!ELEMENT connection (driver-class,driver-name,database-url,schema,user,password?,use-scrollable-cursors?,separate-system-tables?)>
23		23
24	<!--- The driver class. -->	24	<!--- The driver class. -->
25	<!ELEMENT driver-class EMPTY>	25	<!ELEMENT driver-class EMPTY>
Lines 50-55 Link Here
50	<!ATTLIST user	50	<!ATTLIST user
51	value CDATA #REQUIRED	51	value CDATA #REQUIRED
52	>	52	>
		53
		54	<!--- Should be system tables shown separately? -->
		55	<!ELEMENT separate-system-tables EMPTY>
		56	<!ATTLIST separate-system-tables
		57	value (true \| false) #REQUIRED
		58	>
		59
		60	<!--- Allow JDBC support for scrollable cursors? -->
		61	<!ELEMENT use-scrollable-cursors EMPTY>
		62	<!ATTLIST use-scrollable-cursors
		63	value (true \| false) #REQUIRED
		64	>
		65
53	<!--- The database password (hashed). -->	66	<!--- The database password (hashed). -->
54	<!ELEMENT password EMPTY>	67	<!ELEMENT password EMPTY>
55	<!ATTLIST password	68	<!ATTLIST password