Attachment #135120 for bug #230515

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 230515
Collapse All | Expand All




import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.SwingUtilities;
import org.netbeans.api.db.explorer.DatabaseConnection;
import org.netbeans.modules.db.dataview.meta.DBColumn;
import org.netbeans.modules.db.dataview.meta.DBConnectionFactory;
import org.netbeans.modules.db.dataview.meta.DBException;

            @Override
            public void run() {
                try {
                    DatabaseConnection dc = dataView.getDatabaseConnection();
                    Connection conn = DBConnectionFactory.getInstance().getConnection(dc);
                    
                    String msg;
                    if (conn == null) {
                        Throwable t = DBConnectionFactory.getInstance()

                        LOGGER.log(Level.INFO, msg, t);
                        throw new SQLException(msg, t);
                    }
                    
                    try {
                        if (conn.getMetaData().supportsResultSetType(
                                ResultSet.TYPE_SCROLL_INSENSITIVE)) {
                            resultSetScrollType = ResultSet.TYPE_SCROLL_INSENSITIVE;
                        } else if (conn.getMetaData().supportsResultSetType(
                                ResultSet.TYPE_SCROLL_SENSITIVE)) {
                            resultSetScrollType = ResultSet.TYPE_SCROLL_SENSITIVE;
                        }
                    } catch (Exception ex) {
                        LOGGER.log(Level.WARNING, "Exception while querying database for scrollable resultset support");
                    }
                    try {
                        supportesMultipleResultSets = conn.getMetaData().supportsMultipleResultSets();
                    } catch (SQLException ex) {
                        LOGGER.log(Level.INFO, "Database driver throws exception when checking for multiple resultset support.");
                    }
                    
                    DBMetaDataFactory dbMeta = new DBMetaDataFactory(conn);
                    
                    String sql = dataView.getSQLString();

                    updateScrollableSupport(conn, dc, sql);

                    if (Thread.interrupted()) {
                        return;
                    }

                    }
                    boolean needReread = false;
                    ResultSet rs = null;
                    int count = dataView.getUpdateCount();

                    while(true) {
                        if (resultSet) {
                            rs = stmt.getResultSet();


                            if (!needReread) {
                                loadDataFrom(pageContext, rs, true);
                            }
                        } else {
                            // @todo: Do somethink intelligent with the updatecounts
                            count = stmt.getUpdateCount();
                            if (count >= 0) {
                            } else {
                            }
                        }
                        if (supportesMultipleResultSets) {
                            resultSet = stmt.getMoreResults();
                            // @todo: Do somethink intelligent with the updatecounts
                            count = stmt.getUpdateCount();
                            if(resultSet == false && count == -1) {
                                break;
                            }
                        } else {
                            break;
                        }
                    }

                    if (needReread) {

                        resultSet = executeSQLStatement(stmt, sql);
                        
                        count = dataView.getUpdateCount();
                        
                        int res = -1;
                        
                        while(true) {
                            if (resultSet) {
                                res++;
                                rs = stmt.getResultSet();

                                if (!needReread) {
                                    loadDataFrom(pageContext, rs, true);
                                }
                            } else {
                                // @todo: Do somethink intelligent with the updatecounts
                                count = stmt.getUpdateCount();
                                if (count >= 0) {
                                } else {
                                }
                            }
                            if (supportesMultipleResultSets) {
                                resultSet = stmt.getMoreResults();
                                // @todo: Do somethink intelligent with the updatecounts
                                count = stmt.getUpdateCount();
                                if(resultSet == false && count == -1) {
                                    break;
                                }
                            } else {
                                break;
                            }
                        } while (resultSet || count != -1);
                    }
                    }
                    DataViewUtils.closeResources(rs);
                } catch (SQLException sqlEx) {
                    this.ex = sqlEx;

                stmt = prepareSQLStatement(conn, sql, getTotal);

                // Execute the query and retrieve all resultsets
                // using: http://www.xyzws.com/Javafaq/how-to-use-methods-getresultset-or-getupdatecount-to-retrieve-the-results/175
                try {
                    if (Thread.interrupted()) {
                        return;


                    ResultSet rs = null;
                    int res = -1;
                    int count = 0;

                    int count = dataView.getUpdateCount();

                    while(true) {
                        if (resultSet) {
                            res++;
                            DataViewPageContext pageContext = dataView.getPageContext(
                                    res);
                            rs = stmt.getResultSet();
                            loadDataFrom(pageContext, rs, getTotal);
                        } else {
                            // @todo: Do somethink intelligent with the updatecounts
                            count = stmt.getUpdateCount();
                            if (count >= 0) {
                            } else {
                            }
                        }
                        if(supportesMultipleResultSets) {
                            resultSet = stmt.getMoreResults();
                            // @todo: Do somethink intelligent with the updatecounts
                            count = stmt.getUpdateCount();
                            if(resultSet == false && count == -1) {
                                break;
                            }
                        } else {
                            break;
                        }
                    }

                    DataViewUtils.closeResources(rs);
                } catch (SQLException sqlEx) {

     */
    private static void setFetchSize(Statement stmt, int fetchSize) {
        try {
            stmt.setFetchSize(Integer.MIN_VALUE);
        } catch (SQLException e) {
            // ignore -  used only as a hint to the driver to optimize
            LOGGER.log(Level.INFO, "Unable to set Fetch size", e); // NOI18N

            }
        }
    }
    
   
    /**
     * Determine if DBMS/Driver supports scrollable resultsets to be able to
     * determine complete row count for a given SQL.
     * 
     * @param conn established JDBC connection
     * @param dc connection information
     * @param sql the sql to be executed
     */
    private void updateScrollableSupport(Connection conn, DatabaseConnection dc, String sql) {
        String driverName = dc.getJDBCDriver().getClassName();
        /* Derby fails to support scrollable cursors when invoking 'stored procedures'
           which return resultsets - it fails hard: not throwing a SQLException,
           but terminating the connection - so don't try to use scrollable cursor
           on derby, for "non"-selects */
        if(driverName != null && driverName.startsWith("org.apache.derby")) {
            if(! isSelectStatement(sql)) {
                resultSetScrollType = ResultSet.TYPE_FORWARD_ONLY;
                return;
}
        }
        /* Try to get a "good" scrollable ResultSet and follow the DBs support */
        try {
            if (conn.getMetaData().supportsResultSetType(
                    ResultSet.TYPE_SCROLL_INSENSITIVE)) {
                resultSetScrollType = ResultSet.TYPE_SCROLL_INSENSITIVE;
            } else if (conn.getMetaData().supportsResultSetType(
                    ResultSet.TYPE_SCROLL_SENSITIVE)) {
                resultSetScrollType = ResultSet.TYPE_SCROLL_SENSITIVE;
            }
        } catch (Exception ex) {
            LOGGER.log(Level.WARNING, "Exception while querying database for scrollable resultset support");
        }
    }
}

Return to bug 230515

Lines 61-66 Link Here

(-)db.dataview/src/org/netbeans/modules/db/dataview/output/SQLExecutionHelper.java (-44 / +76 lines)
61	import java.util.logging.Level;	61	import java.util.logging.Level;
62	import java.util.logging.Logger;	62	import java.util.logging.Logger;
63	import javax.swing.SwingUtilities;	63	import javax.swing.SwingUtilities;
		64	import org.netbeans.api.db.explorer.DatabaseConnection;
64	import org.netbeans.modules.db.dataview.meta.DBColumn;	65	import org.netbeans.modules.db.dataview.meta.DBColumn;
65	import org.netbeans.modules.db.dataview.meta.DBConnectionFactory;	66	import org.netbeans.modules.db.dataview.meta.DBConnectionFactory;
66	import org.netbeans.modules.db.dataview.meta.DBException;	67	import org.netbeans.modules.db.dataview.meta.DBException;
Lines 116-123 Link Here
116	@Override	117	@Override
117	public void run() {	118	public void run() {
118	try {	119	try {
119	Connection conn = DBConnectionFactory.getInstance()	120	DatabaseConnection dc = dataView.getDatabaseConnection();
120	.getConnection(dataView.getDatabaseConnection());	121	Connection conn = DBConnectionFactory.getInstance().getConnection(dc);
		122
121	String msg;	123	String msg;
122	if (conn == null) {	124	if (conn == null) {
123	Throwable t = DBConnectionFactory.getInstance()	125	Throwable t = DBConnectionFactory.getInstance()
Lines 135-159 Link Here
135	LOGGER.log(Level.INFO, msg, t);	137	LOGGER.log(Level.INFO, msg, t);
136	throw new SQLException(msg, t);	138	throw new SQLException(msg, t);
137	}	139	}
		140
138	try {	141	try {
139	if (conn.getMetaData().supportsResultSetType(
140	ResultSet.TYPE_SCROLL_INSENSITIVE)) {
141	resultSetScrollType = ResultSet.TYPE_SCROLL_INSENSITIVE;
142	} else if (conn.getMetaData().supportsResultSetType(
143	ResultSet.TYPE_SCROLL_SENSITIVE)) {
144	resultSetScrollType = ResultSet.TYPE_SCROLL_SENSITIVE;
145	}
146	} catch (Exception ex) {
147	LOGGER.log(Level.WARNING, "Exception while querying database for scrollable resultset support");
148	}
149	try {
150	supportesMultipleResultSets = conn.getMetaData().supportsMultipleResultSets();	142	supportesMultipleResultSets = conn.getMetaData().supportsMultipleResultSets();
151	} catch (SQLException ex) {	143	} catch (SQLException ex) {
152	LOGGER.log(Level.INFO, "Database driver throws exception when checking for multiple resultset support.");	144	LOGGER.log(Level.INFO, "Database driver throws exception when checking for multiple resultset support.");
153	}	145	}
		146
154	DBMetaDataFactory dbMeta = new DBMetaDataFactory(conn);	147	DBMetaDataFactory dbMeta = new DBMetaDataFactory(conn);
		148
155	String sql = dataView.getSQLString();	149	String sql = dataView.getSQLString();
156		150
		151	updateScrollableSupport(conn, dc, sql);
		152
157	if (Thread.interrupted()) {	153	if (Thread.interrupted()) {
158	return;	154	return;
159	}	155	}
Lines 186-194 Link Here
186	}	182	}
187	boolean needReread = false;	183	boolean needReread = false;
188	ResultSet rs = null;	184	ResultSet rs = null;
189	int count = 0;	185	int count = dataView.getUpdateCount();
190		186
191	do {	187	while(true) {
192	if (resultSet) {	188	if (resultSet) {
193	rs = stmt.getResultSet();	189	rs = stmt.getResultSet();
194		190
Lines 202-226 Link Here
202	if (!needReread) {	198	if (!needReread) {
203	loadDataFrom(pageContext, rs, true);	199	loadDataFrom(pageContext, rs, true);
204	}	200	}
205	} else {
206	// @todo: Do somethink intelligent with the updatecounts
207	count = stmt.getUpdateCount();
208	if (count >= 0) {
209	} else {
210	}	201	}
211	}
212	if (supportesMultipleResultSets) {	202	if (supportesMultipleResultSets) {
213	resultSet = stmt.getMoreResults();	203	resultSet = stmt.getMoreResults();
		204	// @todo: Do somethink intelligent with the updatecounts
		205	count = stmt.getUpdateCount();
		206	if(resultSet == false && count == -1) {
		207	break;
		208	}
214	} else {	209	} else {
215	resultSet = false;	210	break;
216	}	211	}
217	} while (resultSet \|\| count != -1);	212	}
218		213
219	if (needReread) {	214	if (needReread) {
220		215
221	resultSet = executeSQLStatement(stmt, sql);	216	resultSet = executeSQLStatement(stmt, sql);
		217
		218	count = dataView.getUpdateCount();
		219
222	int res = -1;	220	int res = -1;
223	do {	221
		222	while(true) {
224	if (resultSet) {	223	if (resultSet) {
225	res++;	224	res++;
226	rs = stmt.getResultSet();	225	rs = stmt.getResultSet();
Lines 229-248 Link Here
229	if (!needReread) {	228	if (!needReread) {
230	loadDataFrom(pageContext, rs, true);	229	loadDataFrom(pageContext, rs, true);
231	}	230	}
232	} else {
233	// @todo: Do somethink intelligent with the updatecounts
234	count = stmt.getUpdateCount();
235	if (count >= 0) {
236	} else {
237	}	231	}
238	}
239	if (supportesMultipleResultSets) {	232	if (supportesMultipleResultSets) {
240	resultSet = stmt.getMoreResults();	233	resultSet = stmt.getMoreResults();
		234	// @todo: Do somethink intelligent with the updatecounts
		235	count = stmt.getUpdateCount();
		236	if(resultSet == false && count == -1) {
		237	break;
		238	}
241	} else {	239	} else {
242	resultSet = false;	240	break;
243	}	241	}
244	} while (resultSet \|\| count != -1);
245	}	242	}
		243	}
246	DataViewUtils.closeResources(rs);	244	DataViewUtils.closeResources(rs);
247	} catch (SQLException sqlEx) {	245	} catch (SQLException sqlEx) {
248	this.ex = sqlEx;	246	this.ex = sqlEx;
Lines 631-637 Link Here
631	stmt = prepareSQLStatement(conn, sql, getTotal);	629	stmt = prepareSQLStatement(conn, sql, getTotal);
632		630
633	// Execute the query and retrieve all resultsets	631	// Execute the query and retrieve all resultsets
634	// using: http://www.xyzws.com/Javafaq/how-to-use-methods-getresultset-or-getupdatecount-to-retrieve-the-results/175
635	try {	632	try {
636	if (Thread.interrupted()) {	633	if (Thread.interrupted()) {
637	return;	634	return;
Lines 640-667 Link Here
640		637
641	ResultSet rs = null;	638	ResultSet rs = null;
642	int res = -1;	639	int res = -1;
643	int count = 0;
644		640
645	do {	641	int count = dataView.getUpdateCount();
		642
		643	while(true) {
646	if (resultSet) {	644	if (resultSet) {
647	res++;	645	res++;
648	DataViewPageContext pageContext = dataView.getPageContext(	646	DataViewPageContext pageContext = dataView.getPageContext(
649	res);	647	res);
650	rs = stmt.getResultSet();	648	rs = stmt.getResultSet();
651	loadDataFrom(pageContext, rs, getTotal);	649	loadDataFrom(pageContext, rs, getTotal);
652	} else {
653	// @todo: Do somethink intelligent with the updatecounts
654	count = stmt.getUpdateCount();
655	if (count >= 0) {
656	} else {
657	}	650	}
658	}
659	if(supportesMultipleResultSets) {	651	if(supportesMultipleResultSets) {
660	resultSet = stmt.getMoreResults();	652	resultSet = stmt.getMoreResults();
		653	// @todo: Do somethink intelligent with the updatecounts
		654	count = stmt.getUpdateCount();
		655	if(resultSet == false && count == -1) {
		656	break;
		657	}
661	} else {	658	} else {
662	resultSet = false;	659	break;
663	}	660	}
664	} while (resultSet \|\| count != -1);	661	}
665		662
666	DataViewUtils.closeResources(rs);	663	DataViewUtils.closeResources(rs);
667	} catch (SQLException sqlEx) {	664	} catch (SQLException sqlEx) {
Lines 923-929 Link Here
923	*/	920	*/
924	private static void setFetchSize(Statement stmt, int fetchSize) {	921	private static void setFetchSize(Statement stmt, int fetchSize) {
925	try {	922	try {
926	stmt.setFetchSize(fetchSize);	923	stmt.setFetchSize(Integer.MIN_VALUE);
927	} catch (SQLException e) {	924	} catch (SQLException e) {
928	// ignore - used only as a hint to the driver to optimize	925	// ignore - used only as a hint to the driver to optimize
929	LOGGER.log(Level.INFO, "Unable to set Fetch size", e); // NOI18N	926	LOGGER.log(Level.INFO, "Unable to set Fetch size", e); // NOI18N
Lines 934-937 Link Here
934	}	931	}
935	}	932	}
936	}	933	}
		934
		935
		936	/**
		937	* Determine if DBMS/Driver supports scrollable resultsets to be able to
		938	* determine complete row count for a given SQL.
		939	*
		940	* @param conn established JDBC connection
		941	* @param dc connection information
		942	* @param sql the sql to be executed
		943	*/
		944	private void updateScrollableSupport(Connection conn, DatabaseConnection dc, String sql) {
		945	String driverName = dc.getJDBCDriver().getClassName();
		946	/* Derby fails to support scrollable cursors when invoking 'stored procedures'
		947	which return resultsets - it fails hard: not throwing a SQLException,
		948	but terminating the connection - so don't try to use scrollable cursor
		949	on derby, for "non"-selects */
		950	if(driverName != null && driverName.startsWith("org.apache.derby")) {
		951	if(! isSelectStatement(sql)) {
		952	resultSetScrollType = ResultSet.TYPE_FORWARD_ONLY;
		953	return;
937	}	954	}
		955	}
		956	/* Try to get a "good" scrollable ResultSet and follow the DBs support */
		957	try {
		958	if (conn.getMetaData().supportsResultSetType(
		959	ResultSet.TYPE_SCROLL_INSENSITIVE)) {
		960	resultSetScrollType = ResultSet.TYPE_SCROLL_INSENSITIVE;
		961	} else if (conn.getMetaData().supportsResultSetType(
		962	ResultSet.TYPE_SCROLL_SENSITIVE)) {
		963	resultSetScrollType = ResultSet.TYPE_SCROLL_SENSITIVE;
		964	}
		965	} catch (Exception ex) {
		966	LOGGER.log(Level.WARNING, "Exception while querying database for scrollable resultset support");
		967	}
		968	}
		969	}