|
From: <wi...@us...> - 2011-08-19 16:47:09
|
Revision: 6367
http://squirrel-sql.svn.sourceforge.net/squirrel-sql/?rev=6367&view=rev
Author: wis775
Date: 2011-08-19 16:47:00 +0000 (Fri, 19 Aug 2011)
Log Message:
-----------
Store result of SQL in file: At default, the MYSQL driver loads the whole result set into the memory. This can be avoid by using the streaming mode. But if this mode is used, then some metadata of the result set could not be loaded.
Modified Paths:
--------------
trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/ColumnDisplayDefinition.java
trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/gui/action/exportData/ResultSetExportData.java
trunk/sql12/plugins/sqlscript/src/main/java/net/sourceforge/squirrel_sql/plugins/sqlscript/table_script/CreateFileOfCurrentSQLCommand.java
Modified: trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/ColumnDisplayDefinition.java
===================================================================
--- trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/ColumnDisplayDefinition.java 2011-08-18 18:46:21 UTC (rev 6366)
+++ trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/datasetviewer/ColumnDisplayDefinition.java 2011-08-19 16:47:00 UTC (rev 6367)
@@ -185,38 +185,79 @@
* @throws SQLException
*/
public ColumnDisplayDefinition(ResultSet rs, int idx, DialectType dialectType) throws SQLException {
- super();
- ResultSetMetaData md = rs.getMetaData();
-
- String columnLabel = md.getColumnLabel(idx);
- String columnName = md.getColumnName(idx);
- int displayWidth = columnLabel.length();
-
- // Sometimes, the table name is null. eg if the select statement contains a union clause.
- String fullTableColumnName =
- new StringBuilder()
- .append(md.getTableName(idx))
- .append(":")
- .append(columnName)
- .toString();
-
- int sqlType = md.getColumnType(idx);
- String sqlTypeName = md.getColumnTypeName(idx);
- boolean isNullable =
- md.isNullable(idx) == ResultSetMetaData.columnNullable;
- int columnSize = md.getColumnDisplaySize(idx);
- int precision = md.getPrecision(idx);
- int scale = md.getScale(idx);
- boolean isSigned = md.isSigned(idx);
- boolean isCurrency = md.isCurrency(idx);
- boolean isAutoIncrement = md.isAutoIncrement(idx);
-
- init(displayWidth, fullTableColumnName, columnName, columnLabel, sqlType,
- sqlTypeName, isNullable, columnSize, precision, scale,
- isSigned, isCurrency, isAutoIncrement, dialectType);
+ this(rs, idx, dialectType, false);
}
+
/**
+ * Constructs a new ColumnDisplayDefinition using ResultSetMetaData from the
+ * specified ResultSet.
+ *
+ * Some JDBC implementors doesn't allow to fetch some metadata, if the resultset is streamed
+ * from the database and not buffered into the memory. MYSQL is such one.
+ *
+ * A MYSQL connection does not allow to reuse the connection, if the stream is not closed.
+ * To fetch some metadata informations ({@link ResultSetMetaData#getColumnDisplaySize(int) and ResultSetMetaData#getPrecision(int)}),
+ * the JDBC driver itself must query the database. In this case, the driver will throw an exception like
+ * <pre>
+ * No statements may be issued when any streaming result sets are open and in use on a given connection.
+ * Ensure that you have called .close() on any active streaming result sets before attempting more queries.
+ * </pre>
+ * By setting <code>streaming = true</code> we will not read the affected metadata for the known JDBC-Driver'S
+ *
+ * @param rs
+ * the ResultSet to use
+ * @param idx
+ * the index of the column to build a display definition for.
+ * @param dialectType
+ * the type of dialect of the current session.
+ * @param streaming flag, if the result-set is in streaming mode.
+ *
+ * @throws SQLException
+ */
+ public ColumnDisplayDefinition(ResultSet rs, int idx, DialectType dialectType, boolean streaming) throws SQLException {
+ super();
+ ResultSetMetaData md = rs.getMetaData();
+
+ String columnLabel = md.getColumnLabel(idx);
+ String columnName = md.getColumnName(idx);
+ int displayWidth = columnLabel.length();
+
+ // Sometimes, the table name is null. eg if the select statement contains a union clause.
+ String fullTableColumnName =
+ new StringBuilder()
+ .append(md.getTableName(idx))
+ .append(":")
+ .append(columnName)
+ .toString();
+
+ int sqlType = md.getColumnType(idx);
+ String sqlTypeName = md.getColumnTypeName(idx);
+ boolean isNullable =
+ md.isNullable(idx) == ResultSetMetaData.columnNullable;
+
+ int columnSize = 0;
+ int precision = 0;
+
+ // only use columnSize and prescision for the MYSQL driver, if streaming is false
+ if(DialectType.MYSQL5 == dialectType && streaming == false){
+ columnSize = md.getColumnDisplaySize(idx);
+ precision= md.getPrecision(idx);
+ }
+
+ int scale = md.getScale(idx);
+ boolean isSigned = md.isSigned(idx);
+ boolean isCurrency = md.isCurrency(idx);
+ boolean isAutoIncrement = md.isAutoIncrement(idx);
+
+ init(displayWidth, fullTableColumnName, columnName, columnLabel, sqlType,
+ sqlTypeName, isNullable, columnSize, precision, scale,
+ isSigned, isCurrency, isAutoIncrement, dialectType);
+ }
+
+
+
+ /**
* Return the number of characters to display.
*
* @return The number of characters to display.
Modified: trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/gui/action/exportData/ResultSetExportData.java
===================================================================
--- trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/gui/action/exportData/ResultSetExportData.java 2011-08-18 18:46:21 UTC (rev 6366)
+++ trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/gui/action/exportData/ResultSetExportData.java 2011-08-19 16:47:00 UTC (rev 6367)
@@ -89,7 +89,7 @@
int columnCount = this.resultSet.getMetaData().getColumnCount();
for (int i = 1; i <= columnCount; i++) {
- colDispDef.add(new ColumnDisplayDefinition(resultSet, i, this.dialect));
+ colDispDef.add(new ColumnDisplayDefinition(resultSet, i, this.dialect, true));
}
}
Modified: trunk/sql12/plugins/sqlscript/src/main/java/net/sourceforge/squirrel_sql/plugins/sqlscript/table_script/CreateFileOfCurrentSQLCommand.java
===================================================================
--- trunk/sql12/plugins/sqlscript/src/main/java/net/sourceforge/squirrel_sql/plugins/sqlscript/table_script/CreateFileOfCurrentSQLCommand.java 2011-08-18 18:46:21 UTC (rev 6366)
+++ trunk/sql12/plugins/sqlscript/src/main/java/net/sourceforge/squirrel_sql/plugins/sqlscript/table_script/CreateFileOfCurrentSQLCommand.java 2011-08-19 16:47:00 UTC (rev 6367)
@@ -20,6 +20,7 @@
import java.awt.Frame;
import java.io.File;
+import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.NumberFormat;
@@ -27,8 +28,6 @@
import javax.swing.JOptionPane;
import javax.swing.SwingUtilities;
-import org.apache.commons.lang.time.StopWatch;
-
import net.sourceforge.squirrel_sql.client.gui.IAbortEventHandler;
import net.sourceforge.squirrel_sql.client.gui.ProgressAbortDialog;
import net.sourceforge.squirrel_sql.client.session.ISession;
@@ -47,6 +46,8 @@
import net.sourceforge.squirrel_sql.fw.util.StringUtilities;
import net.sourceforge.squirrel_sql.plugins.sqlscript.SQLScriptPlugin;
+import org.apache.commons.lang.time.StopWatch;
+
/**
* Command to export the result of the current SQL into a File.
* With this command is the user able to export the result of the current SQL into a file using the {@link TableExportCsvDlg}.
@@ -115,9 +116,10 @@
// TODO maybe, we should use a SQLExecutorTask for taking advantage of some ExecutionListeners like the parameter replacement. But how to get the right Listeners?
if(unmanagedConnection != null){
- stmt = unmanagedConnection.createStatement();
+
+ stmt = createStatementForStreamingResults(unmanagedConnection.getConnection());
}else{
- stmt = getSession().getSQLConnection().createStatement();
+ stmt = createStatementForStreamingResults(getSession().getSQLConnection().getConnection());
}
@@ -175,6 +177,35 @@
}
/**
+ * Create a {@link Statement} that will stream the result instead of loading into the memory.
+ * @param connection the connection to use
+ * @return A Statement, that will stream the result.
+ * @throws SQLException
+ * @see http://javaquirks.blogspot.com/2007/12/mysql-streaming-result-set.html
+ * @see http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-implementation-notes.html
+ */
+ private Statement createStatementForStreamingResults(Connection connection) throws SQLException {
+ Statement stmt;
+ DialectType dialectType =
+ DialectFactory.getDialectType(getSession().getMetaData());
+ if(DialectType.MYSQL5 == dialectType){
+ /*
+ * MYSQL will load the whole result into memory. To avoid this, we must use the streaming mode.
+ *
+ * http://javaquirks.blogspot.com/2007/12/mysql-streaming-result-set.html
+ * http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-implementation-notes.html
+ */
+ stmt = connection.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
+ stmt.setFetchSize(Integer.MIN_VALUE);
+ }else{
+ stmt = connection.createStatement();
+ }
+ return stmt;
+
+ }
+
+
+ /**
* Create a new unmanaged connection, , which is not associated with the current session.
* @return a new unmanaged connection or null, if no connection can be created.
* @throws SQLException
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
