java.sql.SQLException: ORA-00933: SQL command not properly ended

Question

What i want to accomplish, is to be able to write SQL statements and verify the results. The SQL Statements would have variables in them, like :

String sql = "Select zoneid from zone where zonename = myZoneName";

Where myZoneName is generated from count +

Note: I use Apache POI to parse Excel Spreadsheet.

here is the code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.log4j.Logger;
import org.junit.Test;
public class VerifyDBSingleDomain {
 static Logger log = Logger.getLogger(VerifyDBSingleDomain.class.getName());
 String url = "jdbc:oracle:thin:@a.b.c.d:1521:mname";
 @Test
 public void VerifyDBSingleDomainTest() throws SQLException {
 Properties props = new Properties();
 props.setProperty("user", "user");
 props.setProperty("password", "password");
 String sql = "Select zoneid from zone where zonename = 99224356787.tv";
 //String sql = "Select * from zone";
 Connection conn;
 //try {
 conn = DriverManager.getConnection(url, props);
 PreparedStatement preStatement = conn.prepareStatement(sql);
 ResultSet result = preStatement.executeQuery();
 System.out.println(result);
 /*while (result.next()) {
 System.out.println(result.toString());
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }*/
 // }
 //}
 }
}

and i get error:

java.sql.SQLException: ORA-00933: SQL command not properly ended

• gtgaxiola: Not sure but looks like you have to quote your comparison String sql = "Select zoneid from zone where zonename = '99224356787.tv'";
• kamal: @gtgaxiola not sure how you see '' for zone name, i removed it as it was a typo in the question perhaps.
• gtgaxiola: I meant 99224356787.tv is not surrounded by single quotes in your code since SQL comparisons on varchars usually are of the form WHERE field = 'myValue'

Answer 1

You should use single quotes in your WHERE clause assuming myZoneName is a text type:

String sql = "Select zoneid from zone where zonename = '99224356787.tv'";

Use the following to display the zoneid assuming it is an INTEGER type:

while (result.next()) { 
 System.out.println(result.getInt("zoneid");
}

• kamal: Great, now i get oracle.jdbc.driver.OracleResultSetImpl@1506dc4. but that seems to be an Object, HOW do i get the actual Table Contents

Answer 2

if myZoneName is a Varchar type you have to wrap it around quotes like this

 String sql = "Select zoneid from zone where zonename = '99224356787.tv'";

To get the table contents:

ResultSet result = preStatement.executeQuery();
 System.out.println(result);
 while (result.next()) {
 System.out.println(result.getInt("zoneid")); // if zoneid is int in the db table
 }

refer to Java resultSet API for more information :)