Java Solutions: SQLException

Showing posts with label SQLException. Show all posts

Wednesday, February 3, 2016

Java try-with-resources statement vs. try-finally block

The try-with-resources is a feature added in Java 1.7. A resource is an object implementing the java.lang.AutoCloseable interface. The try-with-resources statement declares a block on one or more resources. The resources automatically close at the end of the block. The resources are always closed regardless of exceptions. If more than one resources are included, the resources are separated by semicolon.

1. An example of using the try-with-resources block.

public void printData(String sql) throws SQLException {
//try-with-resources block
try (PreparedStatement ps = con.prepareStatement(sql)) {
//try-with-resources block
try (ResultSet rs = ps.executeQuery()) {
//code processing the ResultSet.
while (rs.next()) {
System.out.println(rs.getString(1));
}
}
}
}

The above code automatically closes the rs at the end of the inner try-with-resources block and the ps at the end of the outer try-with-resources block.

2. Comparing the above try-with-resources code with the below try-finally code.

public void printData(String sql) throws SQLException {
  PreparedStatement ps = null;
  ResultSet rs = null;

//try-finally block
try {
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
//code processing the ResultSet.
while (rs.next()) {
System.out.println(rs.getString(1));
}
} finally {
if (rs != null) rs.close();
if (ps != null) ps.close();
}
}

Both codes ensure that the rs and ps are closed. However, if the database query code or the result processing code throws a SQLException and then one of the rs.close() and ps.close() methods also throws a SQLException, the SQLException thrown by the printData(String sql) is different using try-with-resources statement from try-finally statement. With the try-with-resources statement, the printData(String sql) throws the SQLException occurred from the database query code or the result processing code. Whereas the printData(String sql) throws the SQLException generated by the close() method if the  try-finally block is used.

3. Examples of including more than one resources.

try (BufferedReader reader = new BufferedReader(new FileReader(file));
BufferedWriter writer = new BufferedWriter(new FileWriter(file2, true))) {
String line;
while (line = reader.readLine() != null) {
writer.write(line);
}
}

public void printData(String sql) throws SQLException {
try (PreparedStatement ps = con.prepareStatement(sql); ResultSet rs = ps.executeQuery()) {
//code processing the ResultSet.
while (rs.next()) {
System.out.println(rs.getString(1));
}
}

}
---------------------------------------------------------------------------------------------------------

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.

Thursday, November 13, 2014

Missing IN or OUT parameter at index:: 1

The exception occurs when number of values assigned to a query does not equal the columns involved in the query.

The following query when executed throws such an exception.

String sql = "INSERT INTO ORDER VALUES(?, ?, ?,?);
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString("Whole Sale");
ps.setDate(CURRENT_DATE);
ps.setFloat(188.23);
ps.setFloat(1.89);
ps.setString("Kevin Smith");

There are 4 columns in the sql, but it is trying to set 5 values to the query.

---------------------------------------------------------------------------------------------------------------------

SQLException: ORA-00984: column not allowed here

The exception occurs when assigning value for inserting or updating records and value given is not properly presented.

The following queries throw such an exception.

String name = "Apple";

1. INSERT INTO FRUIT (NAME, PRICE) values (name, 1.68);

2. UPDATE FRUIT set NAME=name where PRICE=1.68;

To Fix them, do the following.

1. INSERT INTO FRUIT (NAME, PRICE) values ("'"+name+"'", 1.68);

2. UPDATE FRUIT set NAME= "'"+name+"'" where PRICE=1.68;

----------------------------------------------------------------------------------------------------------------

Monday, September 29, 2014

SQL ORA-00979: not a GROUP BY expression

This exception occurs when the SELECT clause contains aggregate functions such as MAX, MIN, SUM, AVG, and COUNT, and there has no GROUP BY clause or the GROUP BY clause does not include all the items in the SELECT clause except the aggregate functions.

For example, the following queries throw such an exception.

1. SELECT NAME, DESCRIPTION, MAX(PRICE) PRICE FROM FRUIT WHERE PRICE < 1.33;

In this case, the query has an aggregate function MAX(PRICE) in the SELECT clause but does not have a GROUP BY clause.

2. SELECT NAME, DESCRIPTION, MAX(PRICE) PRICE FROM FRUIT WHERE PRICE < 1.33 GROUP BY NAME;

In this case, though the GROUP BY clause is there but it dose not contain all the non-aggregate items. The DESCRIPTION is missing from the GROUP BY clause.

To Fix this problem, you may add all the items in the SELECT clause to the GROUP BY clause . However, the GROUP BY clause must have all the non-aggregate items (both NAME and DESCRIPTION in the above queries).

------------------------------------------------------------------------------------------------------------------

Tuesday, August 19, 2014

ORA-00979: not a GROUP BY expression

This error happens when the GROUP BY clause does not include all the fields in the SELECT and ORDER BY clauses.

For example, the following queries throw the "not a GROUP BY expression" error.

1. SELECT name, cost, customerID from ORDER GROUP BY customerID;

2. SELECT name, cost, customerID from ORDER GROUP BY customerID, name, cost ORDER BY orderID;

To fix this error, you may either add all the fields in the SELECT and ORDER BY clauses to the GROUP BY clause or completely remove the GROUP BY clause from the query or remove whatever is not in the GROUP BY clause from the SELECT and the ORDER BY clauses whichever serves you better.

To fix the above two queries, you may do the following modification.

1. SELECT name, cost, customerID from ORDER GROUP BY customerID, name, cost;

2. SELECT name, cost, customerID from ORDER GROUP BY customerID, name, cost, orderID ORDER BY orderID;

-------------------------------------------------------------------------------------------------------------

Wednesday, August 6, 2014

java.sql.SQLException: ORA-00904: "column": invalid identifier

This exception occurs in several situations.

1. When GROUP BY clause is used in a SQL, the column names in the GROUP BY clause have to be real column names in the selected table even though alias are used.

For example, the following SQL gives the "invalid identifier" exception.

SELECT nvl(p1.NAMELAST, ' ') "NAMELAST1", nvl(p1.NAMEFIRST, ' ') "NAMEFIRST1", nvl(p1.NAMEMIDDLE, ' ') "NAMEMIDDLE1" FROM PERSON p1 GROUP BY NAMELAST1, NAMEFIRST1, NAMEMIDDLE1;

ERROR at line 1:
ORA-00904: "NAMEMIDDLE1": invalid identifier

The following 2 SQLs both work fine.

SELECT nvl(p1.NAMELAST, ' ') "p1.NAMELAST", nvl(p1.NAMEFIRST, ' ') "p1.NAMEFIRST", nvl(p1.NAMEMIDDLE, ' ') "p1.NAMEMIDDLE" FROM PERSON p1 GROUP BY p1.NAMELAST, p1.NAMEFIRST, p1.NAMEMIDDLE;

SELECT nvl(p1.NAMELAST, ' ') NAMELAST, nvl(p1.NAMEFIRST, ' ') NAMEFIRST, nvl(p1.NAMEMIDDLE, ' ') NAMEMIDDLE FROM PERSON p1 GROUP BY NAMELAST, NAMEFIRST, NAMEMIDDLE;

2. The column name used is invalid.

A valid column name must meet the following criteria

Cannot be a reserved word such as ALL, DATE, and COMMENT.
Must start with a letter
Consists only alphanumeric characters and these special characters: $, _, #
The column name must be enclosed in double quotation marks if other characters are used.

For example the following SQL may generate the "invalid identifier" exception.

CREATE table person (
personID NUMBER,
NAME VARCHAR(30),
BORN@ DATE
);

3. Mix INNER JOIN and WHERE clauses.

The following SQL will throw such an exception.

SELECT count(*)
FROM PERSON p, SINGER a
INNER JOIN DANCER b on b.ID = p.PERSONID
WHERE a.ID = p.PERSONID;

The following is odd but works.

SELECT count(*)
FROM PERSON p
INNER JOIN DANCER b on b.ID = p.PERSONID,
SINGER a
WHERE a.ID = p.PERSONID;

4. When double quotation marks are used for column names.

SQL by default is case insensitive for column names. However, if the column names are enclosed in double quotation marks and contains lower case characters, you have to use the double quotation marks and the lower case characters whenever the column name is referenced.

For example, the following throws such an "invalid identifier" exception.

CREATE table person (
"person_ID" NUMBER,
NAME VARCHAR(30),
"BORN@" DATE
);

SELECT NAME from PERSON where person_ID = 5;

ERROR at line 1:
ORA-00904: "PERSON_ID": invalid identifier

The following works fine.

SELECT NAME from PERSON where "person_ID" = 5;

However, it is better not to use double quotation marks for column names.

Previous <

-----------------------------------------------------------------------------------------------------

Thursday, July 10, 2014

java.sql.SQLException: Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found, too many open files

This exception occurs when too many Connections are open at the same time. The following code generates such an SQLException.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Test {
String url = "jdbc:oracle:thin:@<host>:<port>";
String driver = "oracle.jdbc.OracleDriver";
String user = "<userId>";
String passwd = "<password>";

public Connection getConnection() throws Exception {
System.out.println("getting Connection");
Connection conn = null;

Class.forName(driver);
conn = DriverManager.getConnection(url, user, passwd);

return conn;
}

public static void main(String[] args) {
Test test = new Test();
Connection conn = null;
Statement stm = null;
String sql = "select distinct NAME from FRUIT";

try {
conn = test.getConnection();

//Exception occurs in this loop
while(true){
conn = test.getConnection();
stm = conn.createStatement();
ResultSet rs = stm.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString(1));
}
rs.close();
stm.close();
}
} catch (Exception e) {
e.printStackTrace();
}

}
}

The while(true) loop above keeps creating Connections and not closing them, therefore, gives such a SQLException.

To fix this problem

Move the "conn = test.getConnection();" out of the while(true) loop and close it after quering database is finished. The code would look like this.

conn = test.getConnection();
while(true){
stm = conn.createStatement();
ResultSet rs = stm.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString(1));
}
rs.close();
stm.close();
}
conn.close();

If you have to create the Connection inside the loop and are not able to move it out, you, then, have to close it each time when it's job is finished.

while(true){
conn = test.getConnection();
stm = conn.createStatement();
ResultSet rs = stm.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString(1));
}
rs.close();
stm.close();
conn.close();
}

----------------------------------------------------------------------------------------------------------

java.sql.SQLException: ORA-01000: maximum open cursors exceeded / too many open cursors

This exception is caused by leaving too many open Statements and ResultSets. For example the following code throws such an SQLException.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Test {
String url = "jdbc:oracle:thin:@<host>:<port>";
String driver = "oracle.jdbc.OracleDriver";
String user = "<userId>";
String passwd = "<password>";

public Connection getConnection() throws Exception {
System.out.println("getting Connection");
Connection conn = null;

Class.forName(driver);
conn = DriverManager.getConnection(url, user, passwd);

return conn;
}

public static void main(String[] args) {
Test test = new Test();
Connection conn = null;
Statement stm = null;
String sql = "select distinct NAME from FRUIT";

try {
conn = test.getConnection();

//Exception occurs in this loop
while(true){
stm = conn.createStatement();
ResultSet rs = stm.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString(1));
}
}
conn.close();
} catch (Exception e) {
e.printStackTrace();
}

}
}

The while(true) loop in the above code keeps creating new Statement and ResultSet without closing them, therefore generates such an SQLException.

To Fix the Problem, add the following code at the end of the while(true) loop.

rs.close();
stm.close();

So, the while(true) loop looks like this.

while(true){
stm = conn.createStatement();
ResultSet rs = stm.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString(1));
}
rs.close();
stm.close();
}

-------------------------------------------------------------------------------------------------------------

Wednesday, July 2, 2014

java.sql.SQLException: ORA-01791: not a SELECTed expression - resolved

When the unique or distinct key word is used in the SELECT clause and at the same time an ORDER BY clause is attached to the end of a query, it sometimes returns the SQLException that says "not a selected expression" , remove either the ORDER BY clause or the unique/distinct key word from the query stops the exception.

The following two queries throw such a SQLException.

1. SELECT unique personId, nvl (firstname, ' '), nvl (lastname, ' '), ssn
FROM person ORDER BY lastname, firstname;

2. SELECT unique personId, firstname, lastname, ssn
FROM person ORDER BY lastname, firstname, middlename;

The real cause of the exception is that for the ORDER BY to work properly when a unique/distinct key word is used, it has to be able to clearly identify the columns put in the ORDER BY clause from the SELECT clause. If a column in the ORDER BY clause is missing or unable to be identified, the exception is generated.

In the above first query, both the lastname and firstname in the ORDER BY are not clearly specified in the SELECT clause. In the above second query, the middlename in the ORDER BY clause is missing from the SELECT clause.

If you don't mind of duplicate or incorrectly ordered results, you may either remove the unique/distinct key word or the column causing the problem from the ORDER BY clause. Otherwise, you need to modify your query to make it work.

The following modifications will fix the above two queries,

1. SELECT unique personId, nvl (firstname, ' ') firstname, nvl (lastname, ' ') lastname, ssn
FROM person ORDER BY lastname, firstname;

OR

SELECT unique personId, nvl (firstname, ' '), nvl (lastname, ' '), ssn
FROM person ORDER BY nvl (lastname, ' '), nvl (firstname, ' ');

2. SELECT personId, firstname, lastname, ssn FROM (
SELECT unique personId, firstname, lastname, middlename, ssn
FROM person ORDER BY lastname, firstname, middlename);

-------------------------------------------------------------------------------------------------------------

Thursday, June 5, 2014

java.sql.SQLException: Fail to convert to internal representation

Stack Trace:
java.sql.SQLException: Fail to convert to internal representation
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.CharCommonAccessor.getLong(CharCommonAccessor.java:239)
at oracle.jdbc.driver.OracleResultSetImpl.getLong(OracleResultSetImpl.java:558)

The above exception occurs when a field data is retrieved from the ResultSet using a wrong data type that does not match the data type in the database, for example retrieving a varchar field in the database table from the ResultSet using getInt() or retrieving a integer field with getLong(), etc.

If you are using a counter such as

int counter = 1;
while (rs.next()) {
rs.getString(counter++);
rs.getInt(counter++);

...........
}

Check your code to make sure that the order of the fields in the select clause of your sql statement is in the same order as you retrieving the data from the result set (though this is not required for processing a result set). The data type of the get method has to match the data type of the field in the database.

-----------------------------------------------------------------------------------------------------------

Friday, April 11, 2014

ERROR 42Y07: java.sql.SQLException: Schema 'abc' does not exist

A. This exception throws when the schema does not exist in the database. A schema is a structure to group a set of tables, views and other data and operations.

To fix it, create the schema by executing this SQL statement:
Create SCHEMA <schema name>;

In Oracle, the above SQL does not actually create a schema. A schema is created when a user is created.
Create USER <username> IDENTIFIED BY <password>;

To create table and view within the schema in Oracle:
Create SCHEMA AUTHORIZATION <username>
<create table statement>
<create view statement>
<grant statement>;

B. This type of exception may also throw when you are referring a table that is not in your default/current schema. In this case, you need to add the schema to the entity you are referring such as <schema name>.<table name>.<column name>

References:
1. CREATE SCHEMA statement
2. ERROR 42Y07: Schema 'SchemaName' does not exist.

--------------------------------------------------------------------------------------------------------------

Tuesday, March 4, 2014

java.sql.SQLException: Invalid operation for forward only resultset : isLast

The isLast(), last(), beforeFirst(), and other methods in the ResultSet class that use the absolute index are available only after the ResultSet is set to ResultSet.TYPE_SCROLL_INSENSITIVE. Otherwise it throws "Invalid operation for forward only resultset : <method name>" exception.

To fix this type of exception, do one of the followings.

1. Statement statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

ResultSet rs = statement.executeQuery(sql);

2. PreparedStatement statement = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

  ResultSet rs = statement.executeQuery();

------------------------------------------------------------------------------------------------------------

ORA-00923: FROM keyword not found where expected

The program cannot identify the "FROM" key word from a SELECT sql statement. For example when StringBuilder is used to construct the sql and a space is missing between the word FROM and the word before or after it, the "ORA-00923: FROM keyword not found where expected" exception will be thrown.

1. A space is missing between the word FROM and the word before or after it

StringBuilder sqlBuilder = new StringBuilder("SELECT name, type");
sqlBuilder.append("FROM fruit ");
sqlBuilder.append("WHERE name in ('APPLE', 'PEACH'));

The space is missing between the "type" and the "FROM".

2. Mis-spelling the word FROM

StringBuilder sqlBuilder = new StringBuilder("SELECT name, type ");
sqlBuilder.append("FORM fruit ");
sqlBuilder.append("WHERE name in ('APPLE', 'PEACH'));

The "FROM" is written as "FORM".

3. The word "FROM" is missing

StringBuilder sqlBuilder = new StringBuilder("SELECT name, type ");
sqlBuilder.append(" fruit ");
sqlBuilder.append("WHERE name in ('APPLE', 'PEACH'));

4. The word "FROM" is placed in the wrong place

StringBuilder sqlBuilder = new StringBuilder("SELECT name, type, ((price-cost) as profit ");
sqlBuilder.append(" fruit ");
sqlBuilder.append("WHERE name in ('APPLE', 'PEACH'));

The extra parenthesis before price makes the "FROM" key word unable to be identified.

-----------------------------------------------------------------------------------------------------------------

Friday, February 28, 2014

java.sql.SQLException: ORA-01722: invalid number

When the following sql is used to query a database, it throws "java.sql.SQLException: ORA-01722: invalid number" exception.

String name = "Apple";

String sql = new StringBuilder("SELECT * FROM fruit ");
sql.append("WHERE name = ").append(name).toString();

To fix the exception, do one of the followings.

1. Indicate that name is a string in the sql by putting single quotes around it

String sql = new StringBuilder("SELECT * FROM fruit ");
sql.append("WHERE name = '").append(name).append("'").toString();

2. Use java.sql.Statement to set the value

Get your database java.sql.Connection object. Then, change the sql to the following.

String sql = new StringBuilder("SELECT * FROM fruit ");
sql.append("WHERE name = ?").toString();

Use the PreparedStatement to set the value.
java.sql.PreparedStatement statement = conn.prepareStatement(sql);
statement.setString(1, name);

----------------------------------------------------------------------------------------------------------------

Monday, January 20, 2014

java.sql.SQLException: Io exception: The Network Adapter could not establish the connection

This exception can be caused by many problems such as wrong URL, wrong username/password, or the database server is not reachable from your computer. However, if you can access the database with sqlplus or other tools and checked the username/password carefully, it is most likely caused by using a wrong URL.

The basic structure of database URL is like this.

jdbc:<vendor>:<driver type>@<database host ip address>:<port>:<database SID>

For example, an Oracle database URL could look like this: jdbc:oracle:thin:@11.08.0.123:1521:orcl

The database SID can be retrieved with the following query.

select sys_context('userenv','instance_name') from dual;

-------------------------------------------------------------------------------------------------------------

Thursday, January 16, 2014

java.sql.SQLException: Invalid column index

The exception may be caused by using 0 or a number larger than the number of columns in your sql as the column index. While in Java the convention of a collection count starts from 0, it starts from 1 in database query.

A particular situation is using alias in query with union. The alias instead of the real column name should be used in the order by clause. Otherwise, it will throw the exception.

Example 1: Select

String sql = "SELECT name, id, address from employee";

ResultSet rs = statement.executeQuery(sql);

while (rs.next()) {
//exception because 0 if an invalid index.
String c0 = rs.getString(0);
String c1 = rs.getString(1);
long id = rs.getLong(2);
String address = rs.getString(3);
//Exception because the sql has only 3 columns
long deptId = rs.getLong(4);
}

Example 2: Insert

String sql = "INSERT INTO employee (name, id, department, empdate) values (?, ?, ?, ?)";

PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "John Young");
ps.setLong(2, 90000);
ps.setLong(3, 1);
ps.setInt(4, 2017200);
//Exception because the sql has only 4 columns
ps.setString(5, "Excellent in public speaking.");

Example 3: Union

Select storeName as "store name", storeAddress, storePhone from store where storeName like '%Mart%'
UNION
Select storeName as "store name", storeAddress, storePhone from store where storePhone like '505%'
//Exception because it cannot identify the storeName
order by storeName;

The working query is
Select storeName as "store name", storeAddress, storePhone from store where storeName like '%Mart%'
UNION
Select storeName as "store name", storeAddress, storePhone from store where storePhone like '505%'
order by "store name";

-------------------------------------------------------------------------------------------------------------