List of usage examples for java.sql SQLException getSQLState
public String getSQLState()
SQLException object. From source file:Main.java
public static void main(String[] args) throws Exception { try {// w ww . jav a 2 s .c o m Connection conn = getConnection(); Statement st = conn.createStatement(); st.executeUpdate("create table survey (id int,myDate DATE );"); String INSERT_RECORD = "insert into survey(id, myDate) values(?, ?)"; PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD); pstmt.setString(1, "1"); java.sql.Date sqlDate = new java.sql.Date(new java.util.Date().getTime()); pstmt.setDate(2, sqlDate); pstmt.executeUpdate(); ResultSet rs = st.executeQuery("SELECT * FROM survey"); rs.close(); st.close(); conn.close(); } catch (SQLException e) { while (e != null) { String errorMessage = e.getMessage(); System.err.println("sql error message:" + errorMessage); // This vendor-independent string contains a code. String sqlState = e.getSQLState(); System.err.println("sql state:" + sqlState); int errorCode = e.getErrorCode(); System.err.println("error code:" + errorCode); // String driverName = conn.getMetaData().getDriverName(); // System.err.println("driver name:"+driverName); // processDetailError(drivername, errorCode); e = e.getNextException(); } } }
From source file:CreateRef.java
public static void main(String args[]) { String url = "jdbc:mySubprotocol:myDataSource"; Connection con;//from w w w.j a v a 2s . co m Statement stmt; try { Class.forName("myDriver.ClassName"); } catch (java.lang.ClassNotFoundException e) { System.err.print("ClassNotFoundException: "); System.err.println(e.getMessage()); } try { String createManagers = "CREATE TABLE MANAGERS OF MANAGER " + "(OID REF(MANAGER) VALUES ARE SYSTEM GENERATED)"; String insertManager1 = "INSERT INTO MANAGERS " + "(MGR_ID, LAST_NAME, FIRST_NAME, PHONE) VALUES " + "(000001, 'MONTOYA', 'ALFREDO', '8317225600')"; String insertManager2 = "INSERT INTO MANAGERS " + "(MGR_ID, LAST_NAME, FIRST_NAME, PHONE) VALUES " + "(000002, 'HASKINS', 'MARGARET', '4084355600')"; String insertManager3 = "INSERT INTO MANAGERS " + "(MGR_ID, LAST_NAME, FIRST_NAME, PHONE) VALUES " + "(000003, 'CHEN', 'HELEN', '4153785600')"; con = DriverManager.getConnection(url, "myLogin", "myPassword"); stmt = con.createStatement(); stmt.executeUpdate(createManagers); con.setAutoCommit(false); stmt.addBatch(insertManager1); stmt.addBatch(insertManager2); stmt.addBatch(insertManager3); int[] updateCounts = stmt.executeBatch(); con.commit(); System.out.println("Update count for: "); for (int i = 0; i < updateCounts.length; i++) { System.out.print(" command " + (i + 1) + " = "); System.out.println(updateCounts[i]); } stmt.close(); con.close(); } catch (BatchUpdateException b) { System.err.println("-----BatchUpdateException-----"); System.err.println("Message: " + b.getMessage()); System.err.println("SQLState: " + b.getSQLState()); System.err.println("Vendor: " + b.getErrorCode()); System.err.print("Update counts for successful commands: "); int[] rowsUpdated = b.getUpdateCounts(); for (int i = 0; i < rowsUpdated.length; i++) { System.err.print(rowsUpdated[i] + " "); } System.err.println(""); } catch (SQLException ex) { System.err.println("------SQLException------"); System.err.println("Error message: " + ex.getMessage()); System.err.println("SQLState: " + ex.getSQLState()); System.err.println("Vendor: " + ex.getErrorCode()); } }
From source file:InsertStores.java
public static void main(String args[]) { String url = "jdbc:mySubprotocol:myDataSource"; Connection con;/*from w ww . jav a 2 s.c o m*/ Statement stmt; try { Class.forName("myDriver.ClassName"); } catch (java.lang.ClassNotFoundException e) { System.err.print("ClassNotFoundException: "); System.err.println(e.getMessage()); } try { con = DriverManager.getConnection(url, "myLogin", "myPassword"); stmt = con.createStatement(); con.setAutoCommit(false); String insertStore1 = "INSERT INTO STORES VALUES (" + "100001, " + "ADDRESS(888, 'Main_Street', 'Rancho_Alegre', " + "'CA', '94049'), " + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', " + "'Colombian_Decaf', 'French_Roast_Decaf'), " + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000001))"; stmt.addBatch(insertStore1); String insertStore2 = "INSERT INTO STORES VALUES (" + "100002, " + "ADDRESS(1560, 'Alder', 'Ochos_Pinos', " + "'CA', '94049'), " + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', " + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), " + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000001))"; stmt.addBatch(insertStore2); String insertStore3 = "INSERT INTO STORES VALUES (" + "100003, " + "ADDRESS(4344, 'First_Street', 'Verona', " + "'CA', '94545'), " + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', " + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), " + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000002))"; stmt.addBatch(insertStore3); String insertStore4 = "INSERT INTO STORES VALUES (" + "100004, " + "ADDRESS(321, 'Sandy_Way', 'La_Playa', " + "'CA', '94544'), " + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', " + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), " + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000002))"; stmt.addBatch(insertStore4); String insertStore5 = "INSERT INTO STORES VALUES (" + "100005, " + "ADDRESS(1000, 'Clover_Road', 'Happyville', " + "'CA', '90566'), " + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', " + "'Colombian_Decaf', 'French_Roast_Decaf'), " + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000003))"; stmt.addBatch(insertStore5); int[] updateCounts = stmt.executeBatch(); ResultSet rs = stmt.executeQuery("SELECT * FROM STORES"); System.out.println("Table STORES after insertion:"); System.out.println("STORE_NO LOCATION COF_TYPE MGR"); while (rs.next()) { int storeNo = rs.getInt("STORE_NO"); Struct location = (Struct) rs.getObject("LOCATION"); Object[] locAttrs = location.getAttributes(); Array coffeeTypes = rs.getArray("COF_TYPE"); String[] cofTypes = (String[]) coffeeTypes.getArray(); Ref managerRef = rs.getRef("MGR"); PreparedStatement pstmt = con.prepareStatement("SELECT MANAGER FROM MANAGERS WHERE OID = ?"); pstmt.setRef(1, managerRef); ResultSet rs2 = pstmt.executeQuery(); rs2.next(); Struct manager = (Struct) rs2.getObject("MANAGER"); Object[] manAttrs = manager.getAttributes(); System.out.print(storeNo + " "); System.out.print(locAttrs[0] + " " + locAttrs[1] + " " + locAttrs[2] + ", " + locAttrs[3] + " " + locAttrs[4] + " "); for (int i = 0; i < cofTypes.length; i++) System.out.print(cofTypes[i] + " "); System.out.println(manAttrs[1] + ", " + manAttrs[2]); rs2.close(); pstmt.close(); } rs.close(); stmt.close(); con.close(); } catch (BatchUpdateException b) { System.err.println("-----BatchUpdateException-----"); System.err.println("SQLState: " + b.getSQLState()); System.err.println("Message: " + b.getMessage()); System.err.println("Vendor: " + b.getErrorCode()); System.err.print("Update counts: "); int[] updateCounts = b.getUpdateCounts(); for (int i = 0; i < updateCounts.length; i++) { System.err.print(updateCounts[i] + " "); } System.err.println(""); } catch (SQLException ex) { System.err.println("SQLException: " + ex.getMessage()); System.err.println("SQLState: " + ex.getSQLState()); System.err.println("Message: " + ex.getMessage()); System.err.println("Vendor: " + ex.getErrorCode()); } }
From source file:ExecuteSQL.java
public static void main(String[] args) { Connection conn = null; // Our JDBC connection to the database server try {/*from ww w . java 2 s.c o m*/ String driver = null, url = null, user = "", password = ""; // Parse all the command-line arguments for (int n = 0; n < args.length; n++) { if (args[n].equals("-d")) driver = args[++n]; else if (args[n].equals("-u")) user = args[++n]; else if (args[n].equals("-p")) password = args[++n]; else if (url == null) url = args[n]; else throw new IllegalArgumentException("Unknown argument."); } // The only required argument is the database URL. if (url == null) throw new IllegalArgumentException("No database specified"); // If the user specified the classname for the DB driver, load // that class dynamically. This gives the driver the opportunity // to register itself with the DriverManager. if (driver != null) Class.forName(driver); // Now open a connection the specified database, using the // user-specified username and password, if any. The driver // manager will try all of the DB drivers it knows about to try to // parse the URL and connect to the DB server. conn = DriverManager.getConnection(url, user, password); // Now create the statement object we'll use to talk to the DB Statement s = conn.createStatement(); // Get a stream to read from the console BufferedReader in = new BufferedReader(new InputStreamReader(System.in)); // Loop forever, reading the user's queries and executing them while (true) { System.out.print("sql> "); // prompt the user System.out.flush(); // make the prompt appear now. String sql = in.readLine(); // get a line of input from user // Quit when the user types "quit". if ((sql == null) || sql.equals("quit")) break; // Ignore blank lines if (sql.length() == 0) continue; // Now, execute the user's line of SQL and display results. try { // We don't know if this is a query or some kind of // update, so we use execute() instead of executeQuery() // or executeUpdate() If the return value is true, it was // a query, else an update. boolean status = s.execute(sql); // Some complex SQL queries can return more than one set // of results, so loop until there are no more results do { if (status) { // it was a query and returns a ResultSet ResultSet rs = s.getResultSet(); // Get results printResultsTable(rs, System.out); // Display them } else { // If the SQL command that was executed was some // kind of update rather than a query, then it // doesn't return a ResultSet. Instead, we just // print the number of rows that were affected. int numUpdates = s.getUpdateCount(); System.out.println("Ok. " + numUpdates + " rows affected."); } // Now go see if there are even more results, and // continue the results display loop if there are. status = s.getMoreResults(); } while (status || s.getUpdateCount() != -1); } // If a SQLException is thrown, display an error message. // Note that SQLExceptions can have a general message and a // DB-specific message returned by getSQLState() catch (SQLException e) { System.err.println("SQLException: " + e.getMessage() + ":" + e.getSQLState()); } // Each time through this loop, check to see if there were any // warnings. Note that there can be a whole chain of warnings. finally { // print out any warnings that occurred SQLWarning w; for (w = conn.getWarnings(); w != null; w = w.getNextWarning()) System.err.println("WARNING: " + w.getMessage() + ":" + w.getSQLState()); } } } // Handle exceptions that occur during argument parsing, database // connection setup, etc. For SQLExceptions, print the details. catch (Exception e) { System.err.println(e); if (e instanceof SQLException) System.err.println("SQL State: " + ((SQLException) e).getSQLState()); System.err.println( "Usage: java ExecuteSQL [-d <driver>] " + "[-u <user>] [-p <password>] <database URL>"); } // Be sure to always close the database connection when we exit, // whether we exit because the user types 'quit' or because of an // exception thrown while setting things up. Closing this connection // also implicitly closes any open statements and result sets // associated with it. finally { try { conn.close(); } catch (Exception e) { } } }
From source file:org.trafodion.rest.util.JdbcT4Util.java
public static void main(String args[]) { Options options = new Options(); options.addOption("h", "help", false, "print this message"); options.addOption("q", "queryText", true, "SQL query text to execute"); HelpFormatter formatter = new HelpFormatter(); CommandLine commandLine;// ww w . ja v a 2 s . co m String queryText = null; StringBuilder sb = new StringBuilder(); try { commandLine = new GnuParser().parse(options, args); if (commandLine.hasOption("help")) { formatter.printHelp("JdbcT4Util", options); System.exit(0); } if (commandLine.hasOption("queryText")) { queryText = commandLine.getOptionValue("q"); } if (queryText == null) { formatter.printHelp("JdbcT4Util", options); System.exit(-1); } } catch (Exception e) { formatter.printHelp("JdbcT4Util", options); System.exit(-1); } try { JdbcT4Util jdbcT4Util = new JdbcT4Util(); TrafT4Connection connection = (TrafT4Connection) jdbcT4Util.getConnection(); //Regular Statement Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery(queryText); JSONArray js = new JSONArray(); js = convertResultSetToJSON(rs); if (LOG.isErrorEnabled()) LOG.error(js.toString()); rs.close(); stmt.close(); //PreparedStatement with explain TrafT4PreparedStatement pStmt = (TrafT4PreparedStatement) connection.prepareStatement(queryText, "SQL_CURSOR_DEMO"); rs = pStmt.executeQuery("SELECT * FROM TABLE(explain(null, 'SQL_CURSOR_DEMO'))"); js = new JSONArray(); js = convertResultSetToJSON(rs); if (LOG.isErrorEnabled()) LOG.error(js.toString()); rs.close(); stmt.close(); System.exit(0); } catch (SQLException e) { SQLException nextException = e; do { sb.append(nextException.getMessage()); sb.append("\nSQLState " + nextException.getSQLState()); sb.append("\nError Code " + nextException.getErrorCode()); } while ((nextException = nextException.getNextException()) != null); if (LOG.isErrorEnabled()) LOG.error("SQLException [" + sb.toString() + "]"); System.exit(1); } catch (Exception e) { e.printStackTrace(); sb.append(e.getMessage()); if (LOG.isDebugEnabled()) LOG.debug("Exception [" + sb.toString() + "]"); System.exit(1); } }
From source file:ProxyAuthTest.java
public static void main(String[] args) throws Exception { if (args.length < 4) { System.out.println("Usage ProxyAuthTest <host> <port> <server_principal> <proxy_user> [testTab]"); System.exit(1);/*from www . ja v a2s.c o m*/ } File currentResultFile = null; String[] beeLineArgs = {}; Class.forName(driverName); String host = args[0]; String port = args[1]; String serverPrincipal = args[2]; String proxyUser = args[3]; String url = null; if (args.length > 4) { tabName = args[4]; } generateData(); generateSQL(null); try { /* * Connect via kerberos and get delegation token */ url = "jdbc:hive2://" + host + ":" + port + "/default;principal=" + serverPrincipal; con = DriverManager.getConnection(url); System.out.println("Connected successfully to " + url); // get delegation token for the given proxy user String token = ((HiveConnection) con).getDelegationToken(proxyUser, serverPrincipal); if ("true".equals(System.getProperty("proxyAuth.debug", "false"))) { System.out.println("Got token: " + token); } con.close(); // so that beeline won't kill the JVM System.setProperty(BEELINE_EXIT, "true"); // connect using principal via Beeline with inputStream url = "jdbc:hive2://" + host + ":" + port + "/default;principal=" + serverPrincipal; currentResultFile = generateSQL(null); beeLineArgs = new String[] { "-u", url, "-n", "foo", "-p", "bar" }; System.out.println("Connection with kerberos, user/password via args, using input rediction"); BeeLine.mainWithInputRedirection(beeLineArgs, inpStream); compareResults(currentResultFile); // connect using principal via Beeline with inputStream url = "jdbc:hive2://" + host + ":" + port + "/default;principal=" + serverPrincipal; currentResultFile = generateSQL(null); beeLineArgs = new String[] { "-u", url, "-n", "foo", "-p", "bar", "-f", scriptFileName }; System.out.println("Connection with kerberos, user/password via args, using input script"); BeeLine.main(beeLineArgs); compareResults(currentResultFile); // connect using principal via Beeline with inputStream url = "jdbc:hive2://" + host + ":" + port + "/default;principal=" + serverPrincipal; currentResultFile = generateSQL(url + " foo bar "); beeLineArgs = new String[] { "-u", url, "-f", scriptFileName }; System.out.println("Connection with kerberos, user/password via connect, using input script"); BeeLine.main(beeLineArgs); compareResults(currentResultFile); // connect using principal via Beeline with inputStream url = "jdbc:hive2://" + host + ":" + port + "/default;principal=" + serverPrincipal; currentResultFile = generateSQL(url + " foo bar "); beeLineArgs = new String[] { "-u", url, "-f", scriptFileName }; System.out.println("Connection with kerberos, user/password via connect, using input redirect"); BeeLine.mainWithInputRedirection(beeLineArgs, inpStream); compareResults(currentResultFile); /* * Connect using the delegation token passed via configuration object */ System.out.println("Store token into ugi and try"); storeTokenInJobConf(token); url = "jdbc:hive2://" + host + ":" + port + "/default;auth=delegationToken"; con = DriverManager.getConnection(url); System.out.println("Connecting to " + url); runTest(); con.close(); // connect using token via Beeline with inputStream url = "jdbc:hive2://" + host + ":" + port + "/default"; currentResultFile = generateSQL(null); beeLineArgs = new String[] { "-u", url, "-n", "foo", "-p", "bar", "-a", "delegationToken" }; System.out.println("Connection with token, user/password via args, using input redirection"); BeeLine.mainWithInputRedirection(beeLineArgs, inpStream); compareResults(currentResultFile); // connect using token via Beeline using script url = "jdbc:hive2://" + host + ":" + port + "/default"; currentResultFile = generateSQL(null); beeLineArgs = new String[] { "-u", url, "-n", "foo", "-p", "bar", "-a", "delegationToken", "-f", scriptFileName }; System.out.println("Connection with token, user/password via args, using input script"); BeeLine.main(beeLineArgs); compareResults(currentResultFile); // connect using token via Beeline using script url = "jdbc:hive2://" + host + ":" + port + "/default"; currentResultFile = generateSQL(url + " foo bar "); beeLineArgs = new String[] { "-a", "delegationToken", "-f", scriptFileName }; System.out.println("Connection with token, user/password via connect, using input script"); BeeLine.main(beeLineArgs); compareResults(currentResultFile); // connect using token via Beeline using script url = "jdbc:hive2://" + host + ":" + port + "/default"; currentResultFile = generateSQL(url + " foo bar "); System.out.println("Connection with token, user/password via connect, using input script"); beeLineArgs = new String[] { "-f", scriptFileName, "-a", "delegationToken" }; BeeLine.main(beeLineArgs); compareResults(currentResultFile); /* * Connect via kerberos with trusted proxy user */ url = "jdbc:hive2://" + host + ":" + port + "/default;principal=" + serverPrincipal + ";hive.server2.proxy.user=" + proxyUser; con = DriverManager.getConnection(url); System.out.println("Connected successfully to " + url); runTest(); ((HiveConnection) con).cancelDelegationToken(token); con.close(); } catch (SQLException e) { System.out.println("*** SQLException: " + e.getMessage() + " : " + e.getSQLState()); e.printStackTrace(); } /* verify the connection fails after canceling the token */ try { url = "jdbc:hive2://" + host + ":" + port + "/default;auth=delegationToken"; con = DriverManager.getConnection(url); throw new Exception("connection should have failed after token cancelation"); } catch (SQLException e) { // Expected to fail due to canceled token } }
From source file:SimpleProgramToAccessOracleDatabase.java
public static void main(String[] args) throws SQLException { Connection conn = null; // connection object Statement stmt = null; // statement object ResultSet rs = null; // result set object try {/*from w w w . j a v a 2 s . c o m*/ conn = getConnection(); // without Connection, can not do much // create a statement: This object will be used for executing // a static SQL statement and returning the results it produces. stmt = conn.createStatement(); // start a transaction conn.setAutoCommit(false); // create a table called cats_tricks stmt.executeUpdate("CREATE TABLE cats_tricks " + "(name VARCHAR2(30), trick VARCHAR2(30))"); // insert two new records to the cats_tricks table stmt.executeUpdate("INSERT INTO cats_tricks VALUES('mono', 'r')"); stmt.executeUpdate("INSERT INTO cats_tricks VALUES('mono', 'j')"); // commit the transaction conn.commit(); // set auto commit to true (from now on every single // statement will be treated as a single transaction conn.setAutoCommit(true); // get all of the the records from the cats_tricks table rs = stmt.executeQuery("SELECT name, trick FROM cats_tricks"); // iterate the result set and get one row at a time while (rs.next()) { String name = rs.getString(1); // 1st column in query String trick = rs.getString(2); // 2nd column in query System.out.println("name=" + name); System.out.println("trick=" + trick); System.out.println("=========="); } } catch (ClassNotFoundException ce) { // if the driver class not found, then we will be here System.out.println(ce.getMessage()); } catch (SQLException e) { // something went wrong, we are handling the exception here if (conn != null) { conn.rollback(); conn.setAutoCommit(true); } System.out.println("--- SQLException caught ---"); // iterate and get all of the errors as much as possible. while (e != null) { System.out.println("Message : " + e.getMessage()); System.out.println("SQLState : " + e.getSQLState()); System.out.println("ErrorCode : " + e.getErrorCode()); System.out.println("---"); e = e.getNextException(); } } finally { // close db resources try { rs.close(); stmt.close(); conn.close(); } catch (Exception e) { } } }
From source file:com.nabla.wapp.server.database.SQLState.java
public static SQLState valueOf(final SQLException e) { return valueOf(e.getSQLState(), e.getErrorCode()); }
From source file:org.apache.tajo.catalog.store.DerbyStore.java
public static void shutdown() { Connection conn = null;/*from w ww .j a v a2s .co m*/ // shutdown embedded database. try { // the shutdown=true attribute shuts down Derby. conn = DriverManager.getConnection("jdbc:derby:;shutdown=true"); } catch (SQLException se) { if ((se.getErrorCode() == 50000) && (se.getSQLState().equals("XJ015"))) { // tajo got the expected exception LOG.info("Derby shutdown complete normally."); } else { LOG.info("Derby shutdown complete abnormally. - message: " + se.getMessage()); } } finally { CatalogUtil.closeQuietly(conn); } LOG.info("Shutdown database"); }
From source file:nl.strohalm.cyclos.utils.ExceptionHelper.java
private static boolean isLockingException(final Throwable t, final boolean recurse) { if (t instanceof LockingException || t instanceof LockAcquisitionException || t instanceof PessimisticLockException) { return true; }//w ww . jav a 2s . co m if (t instanceof SQLException) { final SQLException e = (SQLException) t; return e.getErrorCode() == ER_LOCK_WAIT_TIMEOUT || ST_LOCK.equals(e.getSQLState()); } if (recurse) { for (final Throwable thr : ExceptionUtils.getThrowables(t)) { if (isLockingException(thr, false)) { return true; } } } return false; }