List of usage examples for java.sql Statement getWarnings
SQLWarning getWarnings() throws SQLException;
Statement object. From source file:io.cloudslang.content.database.services.SQLCommandService.java
public static String executeSqlCommand(final SQLInputs sqlInputs) throws Exception { final ConnectionService connectionService = new ConnectionService(); try (final Connection connection = connectionService.setUpConnection(sqlInputs)) { connection.setReadOnly(false);//from ww w. j a va 2 s . c o m final String dbType = sqlInputs.getDbType(); if (ORACLE_DB_TYPE.equalsIgnoreCase(dbType) && sqlInputs.getSqlCommand().toLowerCase().contains(DBMS_OUTPUT)) { final PreparedStatement preparedStatement = connection.prepareStatement(sqlInputs.getSqlCommand()); preparedStatement.setQueryTimeout(sqlInputs.getTimeout()); OracleDbmsOutput oracleDbmsOutput = new OracleDbmsOutput(connection); preparedStatement.executeQuery(); sqlInputs.setIUpdateCount(preparedStatement.getUpdateCount()); preparedStatement.close(); final String output = oracleDbmsOutput.getOutput(); oracleDbmsOutput.close(); return output; } else { final Statement statement = connection.createStatement(sqlInputs.getResultSetType(), sqlInputs.getResultSetConcurrency()); statement.setQueryTimeout(sqlInputs.getTimeout()); try { statement.execute(sqlInputs.getSqlCommand()); } catch (SQLException e) { if (SYBASE_DB_TYPE.equalsIgnoreCase(dbType)) { //during a dump sybase sends back status as exceptions. if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dump")) { return SQLUtils.processDumpException(e); } else if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("load")) { return SQLUtils.processLoadException(e); } } else { throw e; } } ResultSet rs = statement.getResultSet(); if (rs != null) { ResultSetMetaData rsMtd = rs.getMetaData(); if (rsMtd != null) { sqlInputs.getLRows().clear(); int colCount = rsMtd.getColumnCount(); if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dbcc")) { while (rs.next()) { if (colCount >= 4) { sqlInputs.getLRows().add(rs.getString(4)); } } } else { String delimiter = (StringUtils.isNoneEmpty(sqlInputs.getStrDelim())) ? sqlInputs.getStrDelim() : ","; String strRowHolder; while (rs.next()) { strRowHolder = ""; for (int i = 1; i <= colCount; i++) { if (i > 1) { strRowHolder += delimiter; } strRowHolder += rs.getString(i); } sqlInputs.getLRows().add(strRowHolder); } } rs.close(); } } //For sybase, when dbcc command is executed, the result is shown in warning message else if (dbType.equalsIgnoreCase(SYBASE_DB_TYPE) && sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dbcc")) { SQLWarning warning = statement.getWarnings(); while (warning != null) { sqlInputs.getLRows().add(warning.getMessage()); warning = warning.getNextWarning(); } } sqlInputs.setIUpdateCount(statement.getUpdateCount()); } } return "Command completed successfully"; }
From source file:com.alibaba.wasp.jdbc.TestJdbcStatement.java
@Test public void testStatement() throws SQLException, IOException, InterruptedException { Statement stat = conn.createStatement(); assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, conn.getHoldability()); conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT); assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, conn.getHoldability()); // ignored//from ww w . j av a2 s . c o m stat.setCursorName("x"); // fixed return value assertEquals(stat.getFetchDirection(), ResultSet.FETCH_FORWARD); // ignored stat.setFetchDirection(ResultSet.FETCH_REVERSE); // ignored stat.setMaxFieldSize(100); assertEquals(conf.getInt(FConstants.WASP_JDBC_FETCHSIZE, FConstants.DEFAULT_WASP_JDBC_FETCHSIZE), stat.getFetchSize()); stat.setFetchSize(10); assertEquals(10, stat.getFetchSize()); stat.setFetchSize(0); assertEquals(conf.getInt(FConstants.WASP_JDBC_FETCHSIZE, FConstants.DEFAULT_WASP_JDBC_FETCHSIZE), stat.getFetchSize()); assertEquals(ResultSet.TYPE_FORWARD_ONLY, stat.getResultSetType()); Statement stat2 = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT); assertEquals(ResultSet.TYPE_SCROLL_SENSITIVE, stat2.getResultSetType()); assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, stat2.getResultSetHoldability()); assertEquals(ResultSet.CONCUR_READ_ONLY, stat2.getResultSetConcurrency()); assertEquals(0, stat.getMaxFieldSize()); assertTrue(!((JdbcStatement) stat2).isClosed()); stat2.close(); assertTrue(((JdbcStatement) stat2).isClosed()); ResultSet rs; int count; boolean result; stat.execute("CREATE TABLE TEST {REQUIRED INT64 ID;" + "REQUIRED STRING VALUE; }PRIMARY KEY(ID), " + "ENTITY GROUP ROOT,ENTITY GROUP KEY(ID);"); TEST_UTIL.waitTableEnabled(Bytes.toBytes("TEST"), 5000); ResultInHBasePrinter.printMETA(conf, LOG); ResultInHBasePrinter.printFMETA(conf, LOG); ResultInHBasePrinter.printTable("test", "WASP_ENTITY_TEST", conf, LOG); conn.getTypeMap(); // this method should not throw an exception - if not supported, this // calls are ignored assertEquals(ResultSet.CONCUR_READ_ONLY, stat.getResultSetConcurrency()); // stat.cancel(); stat.setQueryTimeout(10); assertTrue(stat.getQueryTimeout() == 10); stat.setQueryTimeout(0); assertTrue(stat.getQueryTimeout() == 0); // assertThrows(SQLErrorCode.INVALID_VALUE_2, stat).setQueryTimeout(-1); assertTrue(stat.getQueryTimeout() == 0); trace("executeUpdate"); count = stat.executeUpdate("INSERT INTO TEST (ID,VALUE) VALUES (1,'Hello')"); assertEquals(1, count); count = stat.executeUpdate("INSERT INTO TEST (VALUE,ID) VALUES ('JDBC',2)"); assertEquals(1, count); count = stat.executeUpdate("UPDATE TEST SET VALUE='LDBC' WHERE ID=1"); assertEquals(1, count); count = stat.executeUpdate("DELETE FROM TEST WHERE ID=-1"); assertEquals(0, count); count = stat.executeUpdate("DELETE FROM TEST WHERE ID=1"); assertEquals(1, count); count = stat.executeUpdate("DELETE FROM TEST WHERE ID=2"); assertEquals(1, count); result = stat.execute("INSERT INTO TEST(ID,VALUE) VALUES(1,'Hello')"); assertTrue(!result); result = stat.execute("INSERT INTO TEST(VALUE,ID) VALUES('JDBC',2)"); assertTrue(!result); result = stat.execute("UPDATE TEST SET VALUE='LDBC' WHERE ID=2"); assertTrue(!result); result = stat.execute("DELETE FROM TEST WHERE ID=1"); assertTrue(!result); result = stat.execute("DELETE FROM TEST WHERE ID=2"); assertTrue(!result); result = stat.execute("DELETE FROM TEST WHERE ID=3"); assertTrue(!result); // getMoreResults rs = stat.executeQuery("SELECT ID,VALUE FROM TEST WHERE ID=1"); assertFalse(stat.getMoreResults()); assertThrows(SQLErrorCode.OBJECT_CLOSED, rs).next(); assertTrue(stat.getUpdateCount() == -1); count = stat.executeUpdate("DELETE FROM TEST WHERE ID=1"); assertFalse(stat.getMoreResults()); assertTrue(stat.getUpdateCount() == -1); WaspAdmin admin = new WaspAdmin(TEST_UTIL.getConfiguration()); admin.disableTable("TEST"); stat.execute("DROP TABLE TEST"); admin.waitTableNotLocked("TEST".getBytes()); stat.executeUpdate("DROP TABLE IF EXISTS TEST"); assertTrue(stat.getWarnings() == null); stat.clearWarnings(); assertTrue(stat.getWarnings() == null); assertTrue(conn == stat.getConnection()); admin.close(); stat.close(); }
From source file:rems.Global.java
public static void executeGnrlSQL(String genSql) { Statement stmt = null; try {// w w w.j av a2 s . c o m Connection mycon = null; Class.forName("org.postgresql.Driver"); mycon = DriverManager.getConnection(Global.connStr, Global.Uname, Global.Pswd); mycon.setAutoCommit(false); //System.out.println("Opened database successfully"); stmt = mycon.createStatement(); stmt.executeUpdate(genSql); mycon.commit(); Global.updateLogMsg(Global.logMsgID, "\r\n" + stmt.getWarnings().toString() + ": " + mycon.getWarnings().toString() + "\r\n", Global.logTbl, Global.gnrlDateStr, Global.rnUser_ID); stmt.close(); mycon.close(); } catch (Exception ex) { Global.errorLog = genSql + "\r\n" + ex.getMessage(); Global.writeToLog(); } finally { } }
From source file:org.apache.hadoop.hive.jdbc.TestJdbcDriver.java
private void doTestSelectAll(String tableName, int maxRows, int fetchSize) throws Exception { boolean isPartitionTable = tableName.equals(partitionedTableName); Statement stmt = con.createStatement(); if (maxRows >= 0) { stmt.setMaxRows(maxRows);//from w w w . j ava2 s . c o m } if (fetchSize > 0) { stmt.setFetchSize(fetchSize); assertEquals(fetchSize, stmt.getFetchSize()); } // JDBC says that 0 means return all, which is the default int expectedMaxRows = maxRows < 1 ? 0 : maxRows; assertNotNull("Statement is null", stmt); assertEquals("Statement max rows not as expected", expectedMaxRows, stmt.getMaxRows()); assertFalse("Statement should not be closed", stmt.isClosed()); ResultSet res; // run some queries res = stmt.executeQuery("select * from " + tableName); assertNotNull("ResultSet is null", res); assertTrue("getResultSet() not returning expected ResultSet", res == stmt.getResultSet()); assertEquals("get update count not as expected", 0, stmt.getUpdateCount()); int i = 0; ResultSetMetaData meta = res.getMetaData(); int expectedColCount = isPartitionTable ? 3 : 2; assertEquals("Unexpected column count", expectedColCount, meta.getColumnCount()); String colQualifier = ((tableName != null) && !tableName.isEmpty()) ? tableName.toLowerCase() + "." : ""; boolean moreRow = res.next(); while (moreRow) { try { i++; assertEquals(res.getInt(1), res.getInt(colQualifier + "under_col")); assertEquals(res.getString(1), res.getString(colQualifier + "under_col")); assertEquals(res.getString(2), res.getString(colQualifier + "value")); if (isPartitionTable) { assertEquals(res.getString(3), partitionedColumnValue); assertEquals(res.getString(3), res.getString(colQualifier + partitionedColumnName)); } assertFalse("Last result value was not null", res.wasNull()); assertNull("No warnings should be found on ResultSet", res.getWarnings()); res.clearWarnings(); // verifying that method is supported // System.out.println(res.getString(1) + " " + res.getString(2)); assertEquals("getInt and getString don't align for the same result value", String.valueOf(res.getInt(1)), res.getString(1)); assertEquals("Unexpected result found", "val_" + res.getString(1), res.getString(2)); moreRow = res.next(); } catch (SQLException e) { System.out.println(e.toString()); e.printStackTrace(); throw new Exception(e.toString()); } } // supposed to get 500 rows if maxRows isn't set int expectedRowCount = maxRows > 0 ? maxRows : 500; assertEquals("Incorrect number of rows returned", expectedRowCount, i); // should have no more rows assertEquals(false, moreRow); assertNull("No warnings should be found on statement", stmt.getWarnings()); stmt.clearWarnings(); // verifying that method is supported assertNull("No warnings should be found on connection", con.getWarnings()); con.clearWarnings(); // verifying that method is supported stmt.close(); assertTrue("Statement should be closed", stmt.isClosed()); }
From source file:org.apache.hive.jdbc.TestJdbcDriver2.java
private void doTestSelectAll(String tableName, int maxRows, int fetchSize) throws Exception { boolean isPartitionTable = tableName.equals(partitionedTableName); Statement stmt = con.createStatement(); if (maxRows >= 0) { stmt.setMaxRows(maxRows);/* w w w. j a va 2 s . c om*/ } if (fetchSize > 0) { stmt.setFetchSize(fetchSize); assertEquals(fetchSize, stmt.getFetchSize()); } // JDBC says that 0 means return all, which is the default int expectedMaxRows = maxRows < 1 ? 0 : maxRows; assertNotNull("Statement is null", stmt); assertEquals("Statement max rows not as expected", expectedMaxRows, stmt.getMaxRows()); assertFalse("Statement should not be closed", stmt.isClosed()); ResultSet res; // run some queries res = stmt.executeQuery("select * from " + tableName); assertNotNull("ResultSet is null", res); assertTrue("getResultSet() not returning expected ResultSet", res == stmt.getResultSet()); assertEquals("get update count not as expected", -1, stmt.getUpdateCount()); int i = 0; ResultSetMetaData meta = res.getMetaData(); int expectedColCount = isPartitionTable ? 3 : 2; assertEquals("Unexpected column count", expectedColCount, meta.getColumnCount()); boolean moreRow = res.next(); while (moreRow) { try { i++; assertEquals(res.getInt(1), res.getInt(tableName + ".under_col")); assertEquals(res.getInt(1), res.getInt("under_col")); assertEquals(res.getString(1), res.getString(tableName + ".under_col")); assertEquals(res.getString(1), res.getString("under_col")); assertEquals(res.getString(2), res.getString(tableName + ".value")); assertEquals(res.getString(2), res.getString("value")); if (isPartitionTable) { assertEquals(res.getString(3), partitionedColumnValue); assertEquals(res.getString(3), res.getString(partitionedColumnName)); assertEquals(res.getString(3), res.getString(tableName + "." + partitionedColumnName)); } assertFalse("Last result value was not null", res.wasNull()); assertNull("No warnings should be found on ResultSet", res.getWarnings()); res.clearWarnings(); // verifying that method is supported // System.out.println(res.getString(1) + " " + res.getString(2)); assertEquals("getInt and getString don't align for the same result value", String.valueOf(res.getInt(1)), res.getString(1)); assertEquals("Unexpected result found", "val_" + res.getString(1), res.getString(2)); moreRow = res.next(); } catch (SQLException e) { System.out.println(e.toString()); e.printStackTrace(); throw new Exception(e.toString()); } } // supposed to get 500 rows if maxRows isn't set int expectedRowCount = maxRows > 0 ? maxRows : 500; assertEquals("Incorrect number of rows returned", expectedRowCount, i); // should have no more rows assertEquals(false, moreRow); assertNull("No warnings should be found on statement", stmt.getWarnings()); stmt.clearWarnings(); // verifying that method is supported assertNull("No warnings should be found on connection", con.getWarnings()); con.clearWarnings(); // verifying that method is supported stmt.close(); assertTrue("Statement should be closed", stmt.isClosed()); }
From source file:org.mskcc.cbio.portal.dao.MySQLbulkLoader.java
/** * load the temp file maintained by the MySQLbulkLoader into the DMBS. * truncates the temp file, and leaves it open for more insertRecord() operations. * returns number of records inserted./*ww w . ja v a 2s. c om*/ * * TODO: perhaps instead of having each program that uses a DAO that uses bulk loading call 'completeInsert', * get MySQLbulkLoader created by a factory, and have the factory remember to load all the tables from all * the temp files before the program exits. * @return number of records inserted * @throws DaoException * @throws IOException */ private int loadDataFromTempFileIntoDBMS() throws DaoException, IOException { Connection con = null; Statement stmt; PreparedStatement pstmt = null; ResultSet rs = null; try { try { // close the file, flushing all buffers before loading the DBMS tempFileWriter.flush(); tempFileWriter.close(); } catch (IOException e) { throw new DaoException(e); } con = JdbcUtil.getDbConnection(MySQLbulkLoader.class); stmt = con.createStatement(); String command = "LOAD DATA LOCAL INFILE '" + tempFileName + "'" + " INTO TABLE " + tableName; stmt.execute(command); int updateCount = stmt.getUpdateCount(); ProgressMonitor .setCurrentMessage(" --> records inserted into `" + tableName + "` table: " + updateCount); int nLines = FileUtil.getNumLines(tempFileHandle); if (nLines != updateCount) { String otherDetails = ""; if (stmt.getWarnings() != null) { otherDetails = "More error/warning details: " + stmt.getWarnings().getMessage(); } throw new DaoException("DB Error: only " + updateCount + " of the " + nLines + " records were inserted in `" + tableName + "`. " + otherDetails); } else { tempFileHandle.delete(); } // reopen empty temp file -- not necessary, this loader will be removed. //this.tempFileWriter = new BufferedWriter(new FileWriter( this.tempFileHandle, false)); return updateCount; } catch (SQLException e) { throw new DaoException(e); } finally { JdbcUtil.closeAll(MySQLbulkLoader.class, con, pstmt, rs); } }
From source file:org.springframework.batch.item.database.AbstractCursorItemReader.java
/** * Throw a SQLWarningException if we're not ignoring warnings, else log the * warnings (at debug level).//from w ww. j av a 2 s . co m * * @param statement the current statement to obtain the warnings from, if there are any. * @throws SQLException if interaction with provided statement fails. * * @see org.springframework.jdbc.SQLWarningException */ protected void handleWarnings(Statement statement) throws SQLWarningException, SQLException { if (ignoreWarnings) { if (log.isDebugEnabled()) { SQLWarning warningToLog = statement.getWarnings(); while (warningToLog != null) { log.debug("SQLWarning ignored: SQL state '" + warningToLog.getSQLState() + "', error code '" + warningToLog.getErrorCode() + "', message [" + warningToLog.getMessage() + "]"); warningToLog = warningToLog.getNextWarning(); } } } else { SQLWarning warnings = statement.getWarnings(); if (warnings != null) { throw new SQLWarningException("Warning not ignored", warnings); } } }
From source file:org.springframework.jdbc.core.JdbcTemplate.java
public Object execute(final StatementCallback action) { Connection con = DataSourceUtils.getConnection(getDataSource()); Statement stmt = null; try {/*from w w w .j a v a2 s . c om*/ Connection conToUse = con; if (this.nativeJdbcExtractor != null && this.nativeJdbcExtractor.isNativeConnectionNecessaryForNativeStatements()) { conToUse = this.nativeJdbcExtractor.getNativeConnection(con); } stmt = conToUse.createStatement(); DataSourceUtils.applyTransactionTimeout(stmt, getDataSource()); Statement stmtToUse = stmt; if (this.nativeJdbcExtractor != null) { stmtToUse = this.nativeJdbcExtractor.getNativeStatement(stmt); } Object result = action.doInStatement(stmtToUse); SQLWarning warning = stmt.getWarnings(); throwExceptionOnWarningIfNotIgnoringWarnings(warning); return result; } catch (SQLException ex) { throw getExceptionTranslator().translate("executing StatementCallback", getSql(action), ex); } finally { JdbcUtils.closeStatement(stmt); DataSourceUtils.closeConnectionIfNecessary(con, getDataSource()); } }
From source file:org.springframework.jdbc.core.JdbcTemplateTests.java
/** * Test update with static SQL.// ww w. j a v a2s .c o m */ public void testSqlUpdate() throws Exception { final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 4"; int rowsAffected = 33; MockControl ctrlStatement = MockControl.createControl(Statement.class); Statement mockStatement = (Statement) ctrlStatement.getMock(); mockStatement.executeUpdate(sql); ctrlStatement.setReturnValue(rowsAffected); if (debugEnabled) { mockStatement.getWarnings(); ctrlStatement.setReturnValue(null); } mockStatement.close(); ctrlStatement.setVoidCallable(); mockConnection.createStatement(); ctrlConnection.setReturnValue(mockStatement); ctrlStatement.replay(); replay(); JdbcTemplate template = new JdbcTemplate(mockDataSource); int actualRowsAffected = template.update(sql); assertTrue("Actual rows affected is correct", actualRowsAffected == rowsAffected); ctrlStatement.verify(); }
From source file:org.springframework.jdbc.core.JdbcTemplateTests.java
public void testSqlUpdateWithThreadConnection() throws Exception { final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 4"; int rowsAffected = 33; MockControl ctrlStatement = MockControl.createControl(Statement.class); Statement mockStatement = (Statement) ctrlStatement.getMock(); mockStatement.executeUpdate(sql);// w w w. ja va 2s . c o m ctrlStatement.setReturnValue(rowsAffected); if (debugEnabled) { mockStatement.getWarnings(); ctrlStatement.setReturnValue(null); } mockStatement.close(); ctrlStatement.setVoidCallable(); mockConnection.createStatement(); ctrlConnection.setReturnValue(mockStatement); ctrlStatement.replay(); replay(); JdbcTemplate template = new JdbcTemplate(mockDataSource); int actualRowsAffected = template.update(sql); assertTrue("Actual rows affected is correct", actualRowsAffected == rowsAffected); ctrlStatement.verify(); }