List of usage examples for java.sql ResultSetMetaData getColumnTypeName
String getColumnTypeName(int column) throws SQLException;
From source file:org.exist.xquery.modules.oracle.ExecuteFunction.java
@Override public Sequence eval(Sequence[] args, Sequence contextSequence) throws XPathException { if (args.length == 5 || args.length == 6) { // was a connection and PL/SQL statement specified? if (args[0].isEmpty() || args[1].isEmpty()) { return (Sequence.EMPTY_SEQUENCE); }//from w w w .j a va 2s .com // get the Connection long connectionUID = ((IntegerValue) args[0].itemAt(0)).getLong(); Connection connection = SQLModule.retrieveConnection(context, connectionUID); if (connection == null) { return (Sequence.EMPTY_SEQUENCE); } // get the PL/SQL statement String plSql = args[1].getStringValue(); // get the input parameters (if any) Element parameters = null; if (!args[2].isEmpty()) { parameters = (Element) args[2].itemAt(0); } // was a result set position specified? int resultSetPos = 0; if (!args[3].isEmpty()) { resultSetPos = ((IntegerValue) args[3].itemAt(0)).getInt(); } boolean haveReturnCode = false; int plSqlSuccess = 1; // default value of 1 for success if (args.length == 6) { // a return code is expected so what is the value indicating success? plSqlSuccess = ((IntegerValue) args[5].itemAt(0)).getInt(); haveReturnCode = true; } CallableStatement statement = null; ResultSet resultSet = null; try { MemTreeBuilder builder = context.getDocumentBuilder(); int iRow = 0; statement = connection.prepareCall(plSql); if (haveReturnCode) { statement.registerOutParameter(1, Types.NUMERIC); } if (resultSetPos != 0) { statement.registerOutParameter(resultSetPos, OracleTypes.CURSOR); } if (!args[2].isEmpty()) { setParametersOnPreparedStatement(statement, parameters); } statement.execute(); if (haveReturnCode) { int returnCode = statement.getInt(1); if (returnCode != plSqlSuccess) { LOG.error(plSql + " failed [" + returnCode + "]"); return (Sequence.EMPTY_SEQUENCE); } } if (resultSetPos != 0) { // iterate through the result set building an XML document builder.startDocument(); builder.startElement(new QName("result", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); builder.addAttribute(new QName("count", null, null), String.valueOf(-1)); resultSet = (ResultSet) statement.getObject(resultSetPos); ResultSetMetaData rsmd = resultSet.getMetaData(); int iColumns = rsmd.getColumnCount(); while (resultSet.next()) { builder.startElement(new QName("row", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); builder.addAttribute(new QName("index", null, null), String.valueOf(resultSet.getRow())); // get each tuple in the row for (int i = 0; i < iColumns; i++) { String columnName = rsmd.getColumnLabel(i + 1); if (columnName != null) { String colValue = resultSet.getString(i + 1); String colElement = "field"; if (((BooleanValue) args[4].itemAt(0)).effectiveBooleanValue() && columnName.length() > 0) { // use column names as the XML node /** * Spaces in column names are replaced with * underscore's */ colElement = SQLUtils.escapeXmlAttr(columnName.replace(' ', '_')); } builder.startElement( new QName(colElement, OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); if (!((BooleanValue) args[4].itemAt(0)).effectiveBooleanValue() || columnName.length() <= 0) { String name; if (columnName.length() > 0) { name = SQLUtils.escapeXmlAttr(columnName); } else { name = "Column: " + String.valueOf(i + 1); } builder.addAttribute(new QName("name", null, null), name); } builder.addAttribute( new QName("type", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), rsmd.getColumnTypeName(i + 1)); builder.addAttribute(new QName("type", Namespaces.SCHEMA_NS, "xs"), Type.getTypeName(SQLUtils.sqlTypeToXMLType(rsmd.getColumnType(i + 1)))); if (resultSet.wasNull()) { // Add a null indicator attribute if the value was SQL Null builder.addAttribute( new QName("null", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), "true"); } if (colValue != null) { builder.characters(SQLUtils.escapeXmlText(colValue)); } builder.endElement(); } } builder.endElement(); iRow++; } builder.endElement(); // Change the root element count attribute to have the correct value NodeValue node = (NodeValue) builder.getDocument().getDocumentElement(); Node count = node.getNode().getAttributes().getNamedItem("count"); if (count != null) { count.setNodeValue(String.valueOf(iRow)); } builder.endDocument(); // return the XML result set return (node); } else { // there was no result set so just return an empty sequence return (Sequence.EMPTY_SEQUENCE); } } catch (SQLException sqle) { LOG.error("oracle:execute() Caught SQLException \"" + sqle.getMessage() + "\" for PL/SQL: \"" + plSql + "\"", sqle); //return details about the SQLException MemTreeBuilder builder = context.getDocumentBuilder(); builder.startDocument(); builder.startElement(new QName("exception", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); boolean recoverable = false; if (sqle instanceof SQLRecoverableException) { recoverable = true; } builder.addAttribute(new QName("recoverable", null, null), String.valueOf(recoverable)); builder.startElement(new QName("state", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); String sqlState = sqle.getSQLState(); if (sqlState != null) { builder.characters(sqle.getSQLState()); } else { builder.characters("null"); } builder.endElement(); builder.startElement(new QName("message", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); builder.characters(sqle.getMessage()); builder.endElement(); builder.startElement(new QName("stack-trace", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); ByteArrayOutputStream bufStackTrace = new ByteArrayOutputStream(); sqle.printStackTrace(new PrintStream(bufStackTrace)); builder.characters(new String(bufStackTrace.toByteArray())); builder.endElement(); builder.startElement(new QName("oracle", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); builder.characters(SQLUtils.escapeXmlText(plSql)); builder.endElement(); int line = getLine(); int column = getColumn(); builder.startElement(new QName("xquery", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); builder.addAttribute(new QName("line", null, null), String.valueOf(line)); builder.addAttribute(new QName("column", null, null), String.valueOf(column)); builder.endElement(); builder.endElement(); builder.endDocument(); return (NodeValue) builder.getDocument().getDocumentElement(); } finally { release(connection, statement, resultSet); } } else { throw new XPathException("Invalid number of arguments [" + args.length + "]"); } }
From source file:org.jboss.bqt.client.xml.XMLQueryVisitationStrategy.java
/** * new ----/*from w w w . j a va 2 s . c om*/ * @param select * @param rmdata * @param parent * @return Element * @throws JDOMException */ private Element produceMsg(Select select, ResultSetMetaData rmdata, Element parent) throws JDOMException { // ----------------------------------- // Create the Select element ... // ----------------------------------- Element selectElement = new Element(TagNames.Elements.SELECT); // --------------------------------- // Create the DISTINCT attribute ... // --------------------------------- boolean distinct = select.isDistinct(); if (distinct) { Attribute distinctAttribute = new Attribute(TagNames.Attributes.DISTINCT, "true"); //$NON-NLS-1$ selectElement.setAttribute(distinctAttribute); } // else default is false so no need // ---------------------------------- // Create the STAR attribute ... // ---------------------------------- if (select.isStar()) { Attribute starAttribute = new Attribute(TagNames.Attributes.STAR, "true"); //$NON-NLS-1$ selectElement.setAttribute(starAttribute); } // -------------------------------- // Create the DATANODE elements ... // -------------------------------- int col = 0; Iterator iter = select.getSymbols().iterator(); while (iter.hasNext()) { Element dataElement = new Element(TagNames.Elements.DATA_ELEMENT); ElementSymbol symbol = (ElementSymbol) iter.next(); String elementName = symbol.getName(); Attribute dataType = null; try { dataType = new Attribute(TagNames.Attributes.TYPE, rmdata.getColumnTypeName(++col)); } catch (SQLException e) { // } dataElement.setAttribute(dataType); dataElement.setText(elementName); selectElement.addContent(dataElement); } if (parent != null) { selectElement = parent.addContent(selectElement); } return selectElement; }
From source file:org.apache.nifi.processors.standard.util.JdbcCommon.java
/** * Creates an Avro schema from a result set. If the table/record name is known a priori and provided, use that as a * fallback for the record name if it cannot be retrieved from the result set, and finally fall back to a default value. * * @param rs The result set to convert to Avro * @param recordName The a priori record name to use if it cannot be determined from the result set. * @return A Schema object representing the result set converted to an Avro record * @throws SQLException if any error occurs during conversion *//* w w w .j av a 2 s .c om*/ public static Schema createSchema(final ResultSet rs, String recordName, boolean convertNames) throws SQLException { final ResultSetMetaData meta = rs.getMetaData(); final int nrOfColumns = meta.getColumnCount(); String tableName = StringUtils.isEmpty(recordName) ? "NiFi_ExecuteSQL_Record" : recordName; if (nrOfColumns > 0) { String tableNameFromMeta = meta.getTableName(1); if (!StringUtils.isBlank(tableNameFromMeta)) { tableName = tableNameFromMeta; } } if (convertNames) { tableName = normalizeNameForAvro(tableName); } final FieldAssembler<Schema> builder = SchemaBuilder.record(tableName).namespace("any.data").fields(); /** * Some missing Avro types - Decimal, Date types. May need some additional work. */ for (int i = 1; i <= nrOfColumns; i++) { /** * as per jdbc 4 specs, getColumnLabel will have the alias for the column, if not it will have the column name. * so it may be a better option to check for columnlabel first and if in case it is null is someimplementation, * check for alias. Postgres is the one that has the null column names for calculated fields. */ String nameOrLabel = StringUtils.isNotEmpty(meta.getColumnLabel(i)) ? meta.getColumnLabel(i) : meta.getColumnName(i); String columnName = convertNames ? normalizeNameForAvro(nameOrLabel) : nameOrLabel; switch (meta.getColumnType(i)) { case CHAR: case LONGNVARCHAR: case LONGVARCHAR: case NCHAR: case NVARCHAR: case VARCHAR: case CLOB: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion() .noDefault(); break; case BIT: case BOOLEAN: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().booleanType().endUnion() .noDefault(); break; case INTEGER: if (meta.isSigned(i) || (meta.getPrecision(i) > 0 && meta.getPrecision(i) <= MAX_DIGITS_IN_INT)) { builder.name(columnName).type().unionOf().nullBuilder().endNull().and().intType().endUnion() .noDefault(); } else { builder.name(columnName).type().unionOf().nullBuilder().endNull().and().longType().endUnion() .noDefault(); } break; case SMALLINT: case TINYINT: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().intType().endUnion() .noDefault(); break; case BIGINT: // Check the precision of the BIGINT. Some databases allow arbitrary precision (> 19), but Avro won't handle that. // If the precision > 19 (or is negative), use a string for the type, otherwise use a long. The object(s) will be converted // to strings as necessary int precision = meta.getPrecision(i); if (precision < 0 || precision > MAX_DIGITS_IN_BIGINT) { builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion() .noDefault(); } else { builder.name(columnName).type().unionOf().nullBuilder().endNull().and().longType().endUnion() .noDefault(); } break; // java.sql.RowId is interface, is seems to be database // implementation specific, let's convert to String case ROWID: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion() .noDefault(); break; case FLOAT: case REAL: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().floatType().endUnion() .noDefault(); break; case DOUBLE: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().doubleType().endUnion() .noDefault(); break; // Did not find direct suitable type, need to be clarified!!!! case DECIMAL: case NUMERIC: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion() .noDefault(); break; // Did not find direct suitable type, need to be clarified!!!! case DATE: case TIME: case TIMESTAMP: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion() .noDefault(); break; case BINARY: case VARBINARY: case LONGVARBINARY: case ARRAY: case BLOB: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().bytesType().endUnion() .noDefault(); break; default: throw new IllegalArgumentException("createSchema: Unknown SQL type " + meta.getColumnType(i) + " / " + meta.getColumnTypeName(i) + " (table: " + tableName + ", column: " + columnName + ") cannot be converted to Avro type"); } } return builder.endRecord(); }
From source file:org.openmrs.module.sync.api.db.hibernate.HibernateSyncDAO.java
public void exportChildDB(String uuidForChild, OutputStream os) throws DAOException { PrintStream out = new PrintStream(os); Set<String> tablesToSkip = new HashSet<String>(); {/* ww w . j a v a 2 s .c o m*/ tablesToSkip.add("hl7_in_archive"); tablesToSkip.add("hl7_in_queue"); tablesToSkip.add("hl7_in_error"); tablesToSkip.add("formentry_archive"); tablesToSkip.add("formentry_queue"); tablesToSkip.add("formentry_error"); tablesToSkip.add("sync_class"); tablesToSkip.add("sync_import"); tablesToSkip.add("sync_record"); tablesToSkip.add("sync_server"); tablesToSkip.add("sync_server_class"); tablesToSkip.add("sync_server_record"); // TODO: figure out which other tables to skip // tablesToSkip.add("obs"); // tablesToSkip.add("concept"); // tablesToSkip.add("patient"); } List<String> tablesToDump = new ArrayList<String>(); Session session = sessionFactory.getCurrentSession(); String schema = (String) session.createSQLQuery("SELECT schema()").uniqueResult(); log.warn("schema: " + schema); // Get all tables that we'll need to dump { Query query = session.createSQLQuery( "SELECT tabs.table_name FROM INFORMATION_SCHEMA.TABLES tabs WHERE tabs.table_schema = '" + schema + "'"); for (Object tn : query.list()) { String tableName = (String) tn; if (!tablesToSkip.contains(tableName.toLowerCase())) tablesToDump.add(tableName); } } log.warn("tables to dump: " + tablesToDump); String thisServerGuid = getGlobalProperty(SyncConstants.PROPERTY_SERVER_UUID); // Write the DDL Header as mysqldump does { out.println("-- ------------------------------------------------------"); out.println("-- Database dump to create an openmrs child server"); out.println("-- Schema: " + schema); out.println("-- Parent GUID: " + thisServerGuid); out.println("-- Parent version: " + OpenmrsConstants.OPENMRS_VERSION); out.println("-- ------------------------------------------------------"); out.println(""); out.println("/*!40101 SET CHARACTER_SET_CLIENT=utf8 */;"); out.println("/*!40101 SET NAMES utf8 */;"); out.println("/*!40103 SET TIME_ZONE='+00:00' */;"); out.println("/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;"); out.println("/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;"); out.println("/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;"); out.println("/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;"); out.println("/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;"); out.println("/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;"); out.println("/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;"); out.println("/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;"); out.println(""); } try { // JDBC way of doing this // Connection conn = // DriverManager.getConnection("jdbc:mysql://localhost/" + schema, // "test", "test"); Connection conn = sessionFactory.getCurrentSession().connection(); try { Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); // Get the create database statement ResultSet rs = st.executeQuery("SHOW CREATE DATABASE " + schema); for (String tableName : tablesToDump) { out.println(); out.println("--"); out.println("-- Table structure for table `" + tableName + "`"); out.println("--"); out.println("DROP TABLE IF EXISTS `" + tableName + "`;"); out.println("SET @saved_cs_client = @@character_set_client;"); out.println("SET character_set_client = utf8;"); rs = st.executeQuery("SHOW CREATE TABLE " + tableName); while (rs.next()) { out.println(rs.getString("Create Table") + ";"); } out.println("SET character_set_client = @saved_cs_client;"); out.println(); { out.println("-- Dumping data for table `" + tableName + "`"); out.println("LOCK TABLES `" + tableName + "` WRITE;"); out.println("/*!40000 ALTER TABLE `" + tableName + "` DISABLE KEYS */;"); boolean first = true; rs = st.executeQuery("select * from " + tableName); ResultSetMetaData md = rs.getMetaData(); int numColumns = md.getColumnCount(); int rowNum = 0; boolean insert = false; while (rs.next()) { if (rowNum == 0) { insert = true; out.print("INSERT INTO `" + tableName + "` VALUES "); } ++rowNum; if (first) { first = false; } else { out.print(", "); } if (rowNum % 20 == 0) { out.println(); } out.print("("); for (int i = 1; i <= numColumns; ++i) { if (i != 1) { out.print(","); } if (rs.getObject(i) == null) { out.print("NULL"); } else { switch (md.getColumnType(i)) { case Types.VARCHAR: case Types.CHAR: case Types.LONGVARCHAR: out.print("'"); out.print( rs.getString(i).replaceAll("\n", "\\\\n").replaceAll("'", "\\\\'")); out.print("'"); break; case Types.BIGINT: case Types.DECIMAL: case Types.NUMERIC: out.print(rs.getBigDecimal(i)); break; case Types.BIT: out.print(rs.getBoolean(i)); break; case Types.INTEGER: case Types.SMALLINT: case Types.TINYINT: out.print(rs.getInt(i)); break; case Types.REAL: case Types.FLOAT: case Types.DOUBLE: out.print(rs.getDouble(i)); break; case Types.BLOB: case Types.VARBINARY: case Types.LONGVARBINARY: Blob blob = rs.getBlob(i); out.print("'"); InputStream in = blob.getBinaryStream(); while (true) { int b = in.read(); if (b < 0) { break; } char c = (char) b; if (c == '\'') { out.print("\'"); } else { out.print(c); } } out.print("'"); break; case Types.CLOB: out.print("'"); out.print( rs.getString(i).replaceAll("\n", "\\\\n").replaceAll("'", "\\\\'")); out.print("'"); break; case Types.DATE: out.print("'" + rs.getDate(i) + "'"); break; case Types.TIMESTAMP: out.print("'" + rs.getTimestamp(i) + "'"); break; default: throw new RuntimeException("TODO: handle type code " + md.getColumnType(i) + " (name " + md.getColumnTypeName(i) + ")"); } } } out.print(")"); } if (insert) { out.println(";"); insert = false; } out.println("/*!40000 ALTER TABLE `" + tableName + "` ENABLE KEYS */;"); out.println("UNLOCK TABLES;"); out.println(); } } } finally { conn.close(); } // Now we mark this as a child out.println("-- Now mark this as a child database"); if (uuidForChild == null) uuidForChild = SyncUtil.generateUuid(); out.println("update global_property set property_value = '" + uuidForChild + "' where property = '" + SyncConstants.PROPERTY_SERVER_UUID + "';"); // Write the footer of the DDL script { out.println("/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;"); out.println("/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;"); out.println("/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;"); out.println("/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;"); out.println("/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;"); out.println("/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;"); out.println("/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;"); out.println("/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;"); } out.flush(); out.close(); } catch (IOException ex) { log.error("IOException", ex); } catch (SQLException ex) { log.error("SQLException", ex); } }
From source file:us.daveread.basicquery.BasicQuery.java
/** * Populates model with the query results. The query executed is the * currently selected query in the combo-box. * /*from w w w . ja v a 2 s.co m*/ * @param rawSqlStatement * The SQL statement to execute * @param model * The model to populate with the results * @param tripleFile * The location to write the results to as triples. */ private void execute(String rawSqlStatement, ListTableModel<Object> model, File tripleFile) { String sqlStatement = rawSqlStatement; Statement stmt = null; ResultSet result = null; ResultSetMetaData meta = null; List<Object> rowData = null; int retValue = 0; SQLWarning warning = null; int[] myType; Object value; String typeName; String colName; String metaName; boolean hasResults = false; boolean hasBLOB = false; Date connAsk = null; Date connGot = null; Date stmtGot = null; Date queryStart = null; Date queryReady = null; Date queryRSFetched = null; Date queryRSProcessed = null; long rows = 0; int cols = 0; boolean hasParams = false; final List<StatementParameter> allParams = new ArrayList<StatementParameter>(); List<Object> outParams = null; modeOfCurrentTable = whichModeValue(); mapOfCurrentTables = new HashMap<String, String>(); // Try to prevent incorrect selection of query type by checking // beginning of SQL statement for obvious stuff // First check "Select" and Describe query types if (!isOkayQueryType(getQuery().getSql())) { // If the query type is wrong, and the user doesn't override then // Get Out Of Here! return; } // If there were BLOB columns included in the last query the connection // will have been left open. Since we are executing a new query we // can close that old connection now. if (conn != null) { try { conn.close(); } catch (Throwable any) { if (LOGGER.isDebugEnabled()) { LOGGER.debug("Error (expected) closing connection", any); } } } conn = null; try { messageOut(Resources.getString("msgExecuteQuery", asQuery.isSelected() ? Resources.getString("msgQuery") : asDescribe.isSelected() ? Resources.getString("msgDescribe") : Resources.getString("msgUpdate"), sqlStatement), STYLE_BOLD); if (poolConnect.isSelected()) { messageOut(Resources.getString("msgPoolStats") + " ", STYLE_SUBTLE, false); if (getDBPool() != null) { messageOut(Resources.getString("msgPoolStatsCount", getDBPool().getNumActive() + "", getDBPool().getNumIdle() + "")); LOGGER.debug("Retrieved existing DB connection pool"); } else { LOGGER.debug("No existing DB pool"); messageOut(Resources.getString("msgPoolNone")); } } if (getDBPool() == null || /* conn == null */ !((String) connectString.getEditor().getItem()).equals(lastConnection) || !userId.getText().equals(lastUserId) || !new String(password.getPassword()).equals(lastPassword)) { removeDBPool(); lastConnection = (String) connectString.getEditor().getItem(); lastUserId = userId.getText(); lastPassword = new String(password.getPassword()); if (poolConnect.isSelected()) { setupDBPool(lastConnection, lastUserId, lastPassword); } messageOut(Resources.getString("msgConnCreated", lastConnection, lastUserId), STYLE_SUBTLE); } connAsk = new java.util.Date(); if (poolConnect.isSelected()) { conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:" + DBPOOL_NAME); LOGGER.debug("Got pooled connection"); messageOut(Resources.getString("msgGotPoolConn"), STYLE_GREEN); } else { conn = DriverManager.getConnection(lastConnection, lastUserId, lastPassword); LOGGER.debug("Got non-pooled connection"); messageOut(Resources.getString("msgGotDirectConn"), STYLE_GREEN); } if (hasParams = sqlStatement.indexOf("$PARAM[") > -1) { sqlStatement = makeParams(sqlStatement, allParams); } connGot = new java.util.Date(); conn.setAutoCommit(autoCommit.isSelected()); conn.setReadOnly(readOnly.isSelected()); if (!hasParams) { stmt = conn.createStatement(); } else { stmt = conn.prepareCall(sqlStatement); setupCall((CallableStatement) stmt, allParams); } stmtGot = new java.util.Date(); try { if (!maxRows.getSelectedItem().equals(Resources.getString("proNoLimit"))) { stmt.setMaxRows(Integer.parseInt((String) maxRows.getSelectedItem())); messageOut("\n" + Resources.getString("msgMaxRows", stmt.getMaxRows() + ""), STYLE_SUBTLE); } } catch (Exception any) { LOGGER.warn("Unable to set maximum rows", any); messageOut(Resources.getString("errFailSetMaxRows", (String) maxRows.getSelectedItem(), any.getMessage()), STYLE_YELLOW); } if (asQuery.isSelected() || asDescribe.isSelected()) { queryStart = new java.util.Date(); if (!hasParams) { int updateCount; // Execute the query synchronously stmt.execute(sqlStatement); messageOut(Resources.getString("msgQueryExecutedByDB"), STYLE_GREEN); // Process the query results and/or report status if ((updateCount = stmt.getUpdateCount()) > -1) { do { LOGGER.debug("Looking for results [update=" + updateCount + "]"); stmt.getMoreResults(); } while ((updateCount = stmt.getUpdateCount()) > -1); } result = stmt.getResultSet(); } else { result = ((PreparedStatement) stmt).executeQuery(); } queryReady = new java.util.Date(); meta = result.getMetaData(); cols = meta.getColumnCount(); } else { queryStart = new java.util.Date(); if (!hasParams) { retValue = stmt.executeUpdate(sqlStatement); } else { retValue = ((PreparedStatement) stmt).executeUpdate(); } queryReady = new java.util.Date(); } if (asQuery.isSelected()) { for (int col = 0; col < cols; ++col) { colName = meta.getColumnName(col + 1); if (colName == null || colName.trim().length() == 0) { colName = Resources.getString("msgUnnamedColumn", meta.getColumnLabel(col + 1)); } if (configDisplayColumnDataType.isSelected()) { metaName = meta.getColumnTypeName(col + 1) + " " + meta.getColumnDisplaySize(col + 1) + " ("; // have had oracle tables report large precision values // for BLOB fields that caused exception to be thrown // by getPrecision() since the value was beyond int try { metaName += meta.getPrecision(col + 1); } catch (Exception any) { metaName += "?"; LOGGER.warn("Unable to get column precision", any); } metaName += "."; metaName += meta.getScale(col + 1); metaName += ")"; colName += " [" + metaName + "]"; } model.addColumn(colName); // Keep collection of tables used for Insert and Update Menu // Selections try { mapOfCurrentTables.put(meta.getTableName(col + 1), null); } catch (Exception any) { // Probably unimplemented method - Sybase driver LOGGER.warn("Failed to obtain table name from metadata", any); messageOut(Resources.getString("errFailReqTableName", any.getMessage()), STYLE_SUBTLE); } } rowData = new ArrayList<Object>(); myType = new int[cols]; for (int col = 0; col < cols; ++col) { typeName = meta.getColumnTypeName(col + 1).toUpperCase(); if (typeName.equals("NUMBER")) { if (meta.getScale(col + 1) > 0) { myType[col] = COLUMN_DATA_TYPE_DOUBLE; // DOUBLE } else if (meta.getPrecision(col + 1) <= MAX_DIGITS_FOR_INT) { myType[col] = COLUMN_DATA_TYPE_INT; // INTEGER } else { myType[col] = COLUMN_DATA_TYPE_LONG; // LONG } } else if (typeName.equals("LONG")) { myType[col] = COLUMN_DATA_TYPE_LONG; } else if (typeName.equals("DATETIME")) { myType[col] = COLUMN_DATA_TYPE_DATETIME; // Date/Time } else if (typeName.equals("DATE")) { myType[col] = COLUMN_DATA_TYPE_DATE; // Date/Time } else if (typeName.equals("BLOB")) { myType[col] = COLUMN_DATA_TYPE_BLOB; hasBLOB = true; } else { myType[col] = 0; // Default - String } } if (tripleFile != null) { try { final RdbToRdf exporter = new RdbToRdf(tripleFile.getAbsolutePath(), getQuery().getSql(), result); exporter.run(); rows = exporter.getLatestNumberOfRowsExported(); messageOut(""); messageOut(Resources.getString("msgEndExportToFile"), STYLE_BOLD); } catch (Throwable throwable) { messageOut(Resources.getString("errFailDataSave", throwable.toString()), STYLE_RED); LOGGER.error("Failed to save data to triples file: " + tripleFile.getAbsolutePath(), throwable); } } else if (fileLogResults.isSelected()) { writeDataAsCSV(sqlStatement, model, DBRESULTS_NAME, result, myType, false); } else { while (result.next()) { ++rows; rowData = new ArrayList<Object>(); for (int col = 0; col < cols; ++col) { value = getResultField(result, col + 1, myType[col]); rowData.add(value); } model.addRowFast(rowData); hasResults = true; } model.updateCompleted(); } queryRSProcessed = new java.util.Date(); } else if (asDescribe.isSelected()) { String colLabel; meta = result.getMetaData(); myType = new int[DESC_TABLE_COLUMN_COUNT]; for (int col = 0; col < DESC_TABLE_COLUMN_COUNT; ++col) { switch (col) { case DESC_TABLE_NAME_COLUMN: // Col Name colLabel = Resources.getString("proColumnName"); myType[col] = COLUMN_DATA_TYPE_STRING; break; case DESC_TABLE_TYPE_COLUMN: // Col Type colLabel = Resources.getString("proColumnType"); myType[col] = COLUMN_DATA_TYPE_STRING; break; case DESC_TABLE_LENGTH_COLUMN: // Col Length colLabel = Resources.getString("proColumnLength"); myType[col] = COLUMN_DATA_TYPE_INT; break; case DESC_TABLE_PRECISION_COLUMN: // Col precision colLabel = Resources.getString("proColPrecision"); myType[col] = COLUMN_DATA_TYPE_INT; break; case DESC_TABLE_SCALE_COLUMN: // Col scale colLabel = Resources.getString("proColScale"); myType[col] = COLUMN_DATA_TYPE_INT; break; case DESC_TABLE_NULLS_OK_COLUMN: // Nulls Okay? colLabel = Resources.getString("proColNullsAllowed"); myType[col] = COLUMN_DATA_TYPE_STRING; break; default: // oops colLabel = Resources.getString("proColUndefined"); break; } if (configDisplayColumnDataType.isSelected()) { colLabel += " ["; colLabel += myType[col] == 0 ? Resources.getString("proColCharType") : Resources.getString("proColNumeric"); colLabel += "]"; } model.addColumn(colLabel); } rowData = new ArrayList<Object>(); for (int col = 0; col < cols; ++col) { rowData = new ArrayList<Object>(); for (int row = 0; row < DESC_TABLE_COLUMN_COUNT; ++row) { switch (row) { case DESC_TABLE_NAME_COLUMN: // Name colName = meta.getColumnName(col + 1); if (colName == null || colName.trim().length() == 0) { colName = Resources.getString("msgUnnamedColumn", meta.getColumnLabel(col + 1)); } value = colName; break; case DESC_TABLE_TYPE_COLUMN: // Type value = meta.getColumnTypeName(col + 1) + " (" + meta.getColumnType(col + 1) + ")"; break; case DESC_TABLE_LENGTH_COLUMN: // Length value = new Integer(meta.getColumnDisplaySize(col + 1)); break; case DESC_TABLE_PRECISION_COLUMN: // Precision try { value = new Integer(meta.getPrecision(col + 1)); } catch (Exception any) { value = "?"; LOGGER.warn("Unable to obtain column precision", any); } break; case DESC_TABLE_SCALE_COLUMN: // Scale value = new Integer(meta.getScale(col + 1)); break; case DESC_TABLE_NULLS_OK_COLUMN: // Nulls Okay? value = meta.isNullable(col + 1) == ResultSetMetaData.columnNullable ? Resources.getString("proYes") : meta.isNullable(col + 1) == ResultSetMetaData.columnNoNulls ? Resources.getString("proNo") : Resources.getString("proUnknown"); break; default: value = null; break; } rowData.add(value); // Keep collection of tables used for Insert and Update Menu // Selections try { mapOfCurrentTables.put(meta.getTableName(col + 1), null); } catch (Exception any) { // Probably unimplemented method - Sybase driver LOGGER.warn("Failed to obtain table name from metadata", any); messageOut(Resources.getString("errFailReqTableName", any.getMessage()), STYLE_SUBTLE); } } model.addRow(rowData); } while (result.next()) { rows++; for (int col = 0; col < cols; ++col) { result.getObject(col + 1); } } queryRSFetched = new java.util.Date(); } else { messageOut("\n" + Resources.getString("msgReturnValue") + " " + retValue, STYLE_BOLD, false); rows = stmt.getUpdateCount(); } messageOut("\n" + Resources.getString("msgRows") + " ", STYLE_NORMAL, false); if (rows == stmt.getMaxRows() && rows > 0) { messageOut("" + rows, STYLE_YELLOW); } else { messageOut("" + rows, STYLE_BOLD); } messageOut(""); } catch (SQLException sql) { LOGGER.error("Error executing SQL", sql); messageOut(Resources.getString("errFailSQL", sql.getClass().getName(), sql.getMessage()), STYLE_RED); userMessage(Resources.getString("errFailSQLText", sql.getMessage()), Resources.getString("errFailSQLTitle"), JOptionPane.ERROR_MESSAGE); while ((sql = sql.getNextException()) != null) { LOGGER.error("Next Exception", sql); } modeOfCurrentTable = -1; } catch (Throwable any) { LOGGER.error("Error executing SQL", any); messageOut(Resources.getString("errFailSQL", any.getClass().getName(), any.getMessage()), STYLE_RED); userMessage(Resources.getString("errFailSQLText", any.getMessage()), Resources.getString("errFailSQLTitle"), JOptionPane.ERROR_MESSAGE); modeOfCurrentTable = -1; } finally { fileSaveBLOBs.setEnabled(hasBLOB); setExportAvailable((hasResults && model.getRowCount() > 0) || tripleFile != null); queryMakeInsert.setEnabled( modeOfCurrentTable == Query.MODE_DESCRIBE || modeOfCurrentTable == Query.MODE_QUERY); if (hasParams) { outParams = getOutParams((CallableStatement) stmt, allParams); } LOGGER.debug("Check for more results"); try { int resultCount = 0; while (stmt.getMoreResults()) { int updateCount; ++resultCount; updateCount = stmt.getUpdateCount(); LOGGER.debug("More results [" + resultCount + "][updateCount=" + updateCount + "]"); } } catch (SQLException sql) { LOGGER.error("Failed checking for more results", sql); messageOut(Resources.getString("errFailAddlResults", sql.getClass().getName(), sql.getMessage())); } LOGGER.debug("No more results"); if (result != null) { try { result.close(); LOGGER.info("Resultset closed"); } catch (Throwable any) { LOGGER.error("Unable to close resultset", any); } } if (stmt != null) { try { warning = stmt.getWarnings(); while (warning != null) { LOGGER.warn("Stmt Warning: " + warning.toString()); messageOut(Resources.getString("errStmtWarning", warning.toString()), STYLE_YELLOW); warning = warning.getNextWarning(); } } catch (Throwable any) { LOGGER.warn("Error retrieving statement SQL warnings", any); } try { stmt.close(); LOGGER.debug("Statement closed"); } catch (Throwable any) { LOGGER.error("Unable to close statement", any); } } if (conn != null) { try { warning = conn.getWarnings(); while (warning != null) { LOGGER.warn("Connt Warning: " + warning.toString()); messageOut(Resources.getString("errConnWarning", warning.toString()), STYLE_YELLOW); warning = warning.getNextWarning(); } } catch (Throwable any) { LOGGER.warn("Error retrieving connection SQL warnings", any); } } // Close the connection if there are no BLOBs. // If the user decides to save a BLOB we will need to DB connection // to remain open, hence we only close here if there are no BLOBs if (!hasBLOB && conn != null) { try { conn.close(); conn = null; LOGGER.debug("DB Connection closed"); } catch (Throwable any) { LOGGER.error("Unable to close DB connection", any); } } reportStats(sqlStatement, connAsk, connGot, stmtGot, queryStart, queryReady, queryRSFetched, queryRSProcessed, rows, cols, asDescribe.isSelected() ? model : null, outParams); // reportResults(SQL, model); } }