perdoname_

asked on 4/14/2008

java.sql.SQLException: Table/view already exists ???? [URGENT]

Hello Experts,

I need your assistance about the following errors

java.sql.SQLException: Table/view already exists: JOB_ROLES
      at com.quadcap.sql.AddTable.prepare(AddTable.java:97)
      at com.quadcap.sql.Session.doStep(Session.java:377)
      at com.quadcap.sql.StmtCreateTable.execute(StmtCreateTable.java:98)
      at com.quadcap.sql.Session.doStatement(Session.java:264)
      at com.quadcap.jdbc.Statement.execute(Statement.java:229)
      at com.quadcap.jdbc.Statement.executeUpdate(Statement.java:347)
      at DB.createJobRoles(createTable.java:148)
      at createTable.main(createTable.java:51)
java.sql.SQLException: Table/view already exists: EMPLOYEES
      at com.quadcap.sql.AddTable.prepare(AddTable.java:97)
      at com.quadcap.sql.Session.doStep(Session.java:377)
      at com.quadcap.sql.StmtCreateTable.execute(StmtCreateTable.java:98)
      at com.quadcap.sql.Session.doStatement(Session.java:264)
      at com.quadcap.jdbc.Statement.execute(Statement.java:229)
      at com.quadcap.jdbc.Statement.executeUpdate(Statement.java:347)
      at DB.createTables(createTable.java:101)
      at createTable.main(createTable.java:52)

Thanks in advance for any help!

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
 
public class createTable 
{
	public static void main(String[] args) throws SQLException 
	{
		DB db = new DB();
 
		/*
		try {
			Class.forName("com.quadcap.jdbc.JdbcDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		 */
 
		String username;
		String password;
 
		username = "something";
		password = "123456";
		Connection conn = db.dbConnect(username, password);
		//Connection conn = java.sql.DriverManager.getConnection("jdbc:qed:mynewdb", p);
		try{
			Statement st = conn.createStatement();
			//BufferedReader bf = new BufferedReader
			//(new InputStreamReader(System.in));
			//System.out.println("Enter Database name:");
			//String database = bf.readLine();
			//st.executeUpdate("CREATE DATABASE "+database);
		}
		catch (SQLException s){
			System.out.println("SQL statement is not executed!");
		}
		catch (Exception e){
			e.printStackTrace();
		}
 
		//DB db = new DB();
		//conn=db.dbConnect("jdbc:qed:mynewdb", "p");
		db.createJobRoles(conn);
		db.createTables(conn);
 
	}
}
 
class DB
{
	public DB() {}
 
	public Connection dbConnect(String db_connect_string,
			String db_userid)
	{
		try
		{
			Class.forName("com.quadcap.jdbc.JdbcDriver");
			java.util.Properties p = new java.util.Properties();
			p.setProperty("create", "true");
			java.sql.Connection conn =
				java.sql.DriverManager.getConnection("jdbc:qed:mynewdb", p);
			return conn;
 
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return null;
		}
	} 
 
 
	//Employee table
	public void createTables(Connection conn)
	{
 
		String query;
		Statement stmt = null;
 
		try
		{
 
			query="create table employees " +
			"(name       varchar(32), " +
			"id          varchar(50), " +
			"telephoneNo varchar(50), " +
			"email       varchar(50), " +
			"JOB_ID      integer,     " +
			"foreign key (JOB_ID) references Job_roles (JOB_ID))";
 
			stmt = conn.createStatement();
			stmt.executeUpdate(query);
 
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('John Doe', '1', '5555999944', 'Something@mail.com', 1)");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Neil Mayhem', '2', '555588888', 'sth@mail.com', 2)");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Alice Lovelace', '3', '6666999944', 'alice@mail.com', 3)");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Jay Something', '4', '666669999', 'mail@mail.com',4 )");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Terry Something', '5', '622211111', 'mail20@mail.com', 1)");
 
 
			stmt.close();
			conn.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	} //createTables
 
 
 
	public void createJobRoles(Connection conn)
	{
 
		String query;
		Statement stmt = null;
 
		try
		{
 
			query="create table Job_roles " +
			"(JOB_ID INTEGER NOT NULL, " +
			"job_title varchar(32), " +
			"salary_class varchar(50), " +
			"job_description varchar(50), " +
			"primary key(JOB_ID))";
 
			stmt = conn.createStatement();
			stmt.executeUpdate(query);
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (1, 'Garden Worker', '4', 'Cultivates and cares for plants')");
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (2, 'Security Guard', '4', 'Takes care of security')");
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (3, 'Stage Hypnotist', '2', 'Hypnotizes people on stage')");
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (4, 'Amusement Park Guide', '3', 'Guides people through an amusement park')");
 
 
			stmt.close();
			conn.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
 
 
	public void printFields()
	{
		String query;
		Statement stmt;
		try {
			query = "SELECT employees.name, Job_roles.salary_class FROM employees INNER JOIN Job_roles on employees.JOB_ID = Job_roles.JOB_ID";
 
			Connection conn = null;
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				//print query
				System.out.printf("Salary of %s is %s\n", rs.getString(1), rs.getObject(2).toString());
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
 
	}
 
 
	public void addColumn()
	{
		String query;
		Statement stmt;
		Connection conn = null;
 
 
		try {
			query= "ALTER TABLE employees ADD JOB_ID";
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
 
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}
 
 
 
	public void EmployeeNameSearch(String[] args) throws SQLException
	{
		List names =  Arrays.asList(args);
 
		List not = new ArrayList();
 
		//Collections.sort(names); 
 
		PreparedStatement ps = null;
 
		BufferedReader stdin = new BufferedReader(new InputStreamReader(System.in));
		while (true) {
			try {
				String option = stdin.readLine();
			} catch (IOException e1) {
				e1.printStackTrace();
			}
 
			for (Object name : names) {
				try {
					ps.setString(1, name.toString());
				} catch (SQLException e) {
					e.printStackTrace();
				}
				ResultSet rs = null;
				try {
					rs = ps.executeQuery();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				if (rs.next()) {
					// name in dba
				}
				else
				{
					System.out.println(name + "not in db!");
					not.add(name);
 
				}
			}
		}
	}
 
};

Open in new window

JavaProgramming Languages-Other

Last Comment

perdoname_

8/22/2022 - Mon

Mick Barry

4/14/2008

Your create table commands should be:

CREATE TABLE IF NOT EXISTS ....

perdoname_

4/14/2008

ASKER

Okay code changed [please have a look to the code]

But now it returns:

2008-04-15 04:17:09.032: [main] antlr.MismatchedTokenException
2008-04-15 04:17:09.032: [main] line 1:24: expecting '(', found 'IF'
      at antlr.Parser.match(Parser.java:195)
      at com.quadcap.sql.SQLParser.createTableStmt(SQLParser.java:677)
      at com.quadcap.sql.SQLParser.statement(SQLParser.java:242)
      at com.quadcap.jdbc.Statement.execute(Statement.java:225)
      at com.quadcap.jdbc.Statement.executeUpdate(Statement.java:347)
      at DB.createJobRoles(createTable.java:148)
      at createTable.main(createTable.java:51)
2008-04-15 04:17:09.034: [main] SQL: CREATE TABLE Job_roles IF NOT EXISTS (JOB_ID INTEGER NOT NULL, job_title varchar(32), salary_class varchar(50), job_description varchar(50), primary key(JOB_ID))
java.sql.SQLException: line 1:24: expecting '(', found 'IF'
      at com.quadcap.jdbc.Statement.execute(Statement.java:258)
      at com.quadcap.jdbc.Statement.executeUpdate(Statement.java:347)
      at DB.createJobRoles(createTable.java:148)
      at createTable.main(createTable.java:51)
2008-04-15 04:17:09.039: [main] antlr.MismatchedTokenException
2008-04-15 04:17:09.039: [main] line 1:24: expecting '(', found 'IF'
      at antlr.Parser.match(Parser.java:195)
      at com.quadcap.sql.SQLParser.createTableStmt(SQLParser.java:677)
      at com.quadcap.sql.SQLParser.statement(SQLParser.java:242)
      at com.quadcap.jdbc.Statement.execute(Statement.java:225)
      at com.quadcap.jdbc.Statement.executeUpdate(Statement.java:347)
      at DB.createTables(createTable.java:101)
      at createTable.main(createTable.java:52)
2008-04-15 04:17:09.046: [main] SQL: CREATE TABLE employees IF NOT EXISTS (name varchar(32), id varchar(50), telephoneNo varchar(50), email varchar(50), JOB_ID integer, foreign key (JOB_ID) references Job_roles (JOB_ID))
java.sql.SQLException: line 1:24: expecting '(', found 'IF'
      at com.quadcap.jdbc.Statement.execute(Statement.java:258)
      at com.quadcap.jdbc.Statement.executeUpdate(Statement.java:347)
      at DB.createTables(createTable.java:101)
      at createTable.main(createTable.java:52)

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
 
public class createTable 
{
	public static void main(String[] args) throws SQLException 
	{
		DB db = new DB();
 
		/*
		try {
			Class.forName("com.quadcap.jdbc.JdbcDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		 */
 
		String username;
		String password;
 
		username = "something";
		password = "123456";
		Connection conn = db.dbConnect(username, password);
		//Connection conn = java.sql.DriverManager.getConnection("jdbc:qed:mynewdb", p);
		try{
			Statement st = conn.createStatement();
			//BufferedReader bf = new BufferedReader
			//(new InputStreamReader(System.in));
			//System.out.println("Enter Database name:");
			//String database = bf.readLine();
			//st.executeUpdate("CREATE DATABASE "+database);
		}
		catch (SQLException s){
			System.out.println("SQL statement is not executed!");
		}
		catch (Exception e){
			e.printStackTrace();
		}
 
		//DB db = new DB();
		//conn=db.dbConnect("jdbc:qed:mynewdb", "p");
		db.createJobRoles(conn);
		db.createTables(conn);
 
	}
}
 
class DB
{
	public DB() {}
 
	public Connection dbConnect(String db_connect_string,
			String db_userid)
	{
		try
		{
			Class.forName("com.quadcap.jdbc.JdbcDriver");
			java.util.Properties p = new java.util.Properties();
			p.setProperty("create", "true");
			java.sql.Connection conn =
				java.sql.DriverManager.getConnection("jdbc:qed:mynewdb", p);
			return conn;
 
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return null;
		}
	} 
 
 
	//Employee table
	public void createTables(Connection conn) throws SQLException
	{
 
		String query;
		Statement stmt = null;
 
		try
		{
 
			query="CREATE TABLE employees IF NOT EXISTS " +
			"(name       varchar(32), " +
			"id          varchar(50), " +
			"telephoneNo varchar(50), " +
			"email       varchar(50), " +
			"JOB_ID      integer,     " +
			"foreign key (JOB_ID) references Job_roles (JOB_ID))";
 
			stmt = conn.createStatement();
			stmt.executeUpdate(query);
 
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('John Doe', '1', '5555999944', 'Something@mail.com', 1)");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Neil Mayhem', '2', '555588888', 'sth@mail.com', 2)");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Alice Lovelace', '3', '6666999944', 'alice@mail.com', 3)");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Jay Something', '4', '666669999', 'mail@mail.com',4 )");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Terry Something', '5', '622211111', 'mail20@mail.com', 1)");
 
 
			stmt.close();
			conn.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	} //createTables
 
 
 
	public void createJobRoles(Connection conn) throws SQLException
	{
 
		String query;
		Statement stmt = null;
 
		try
		{
 
			query="CREATE TABLE Job_roles IF NOT EXISTS " +
			"(JOB_ID INTEGER NOT NULL, " +
			"job_title varchar(32), " +
			"salary_class varchar(50), " +
			"job_description varchar(50), " +
			"primary key(JOB_ID))";
 
			stmt = conn.createStatement();
			stmt.executeUpdate(query);
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (1, 'Garden Worker', '4', 'Cultivates and cares for plants')");
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (2, 'Security Guard', '4', 'Takes care of security')");
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (3, 'Stage Hypnotist', '2', 'Hypnotizes people on stage')");
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (4, 'Amusement Park Guide', '3', 'Guides people through an amusement park')");
 
 
			stmt.close();
			conn.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
 
 
	public void printFields()
	{
		String query;
		Statement stmt;
		try {
			query = "SELECT employees.name, Job_roles.salary_class FROM employees INNER JOIN Job_roles on employees.JOB_ID = Job_roles.JOB_ID";
 
			Connection conn = null;
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				//print query
				System.out.printf("Salary of %s is %s\n", rs.getString(1), rs.getObject(2).toString());
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
 
	}
 
 
	public void addColumn()
	{
		String query;
		Statement stmt;
		Connection conn = null;
 
 
		try {
			query= "ALTER TABLE employees ADD JOB_ID";
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
 
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}
 
 
 
	public void EmployeeNameSearch(String[] args) throws SQLException
	{
		List names =  Arrays.asList(args);
 
		List not = new ArrayList();
 
		//Collections.sort(names); 
 
		PreparedStatement ps = null;
 
		BufferedReader stdin = new BufferedReader(new InputStreamReader(System.in));
		while (true) {
			try {
				String option = stdin.readLine();
			} catch (IOException e1) {
				e1.printStackTrace();
			}
 
			for (Object name : names) {
				try {
					ps.setString(1, name.toString());
				} catch (SQLException e) {
					e.printStackTrace();
				}
				ResultSet rs = null;
				try {
					rs = ps.executeQuery();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				if (rs.next()) {
					// name in dba
				}
				else
				{
					System.out.println(name + "not in db!");
					not.add(name);
 
				}
			}
		}
	}
 
};

Open in new window

Mick Barry

4/14/2008

the table name comes *after* EXISTS

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

perdoname_

4/14/2008

ASKER

I did so but still returns errors:

2008-04-15 04:26:07.691: [main] antlr.MismatchedTokenException
2008-04-15 04:26:07.692: [main] line 1:17: expecting '(', found 'NOT'
      at antlr.Parser.match(Parser.java:195)
      at com.quadcap.sql.SQLParser.createTableStmt(SQLParser.java:677)
      at com.quadcap.sql.SQLParser.statement(SQLParser.java:242)
      at com.quadcap.jdbc.Statement.execute(Statement.java:225)
      at com.quadcap.jdbc.Statement.executeUpdate(Statement.java:347)
      at DB.createJobRoles(createTable.java:148)
      at createTable.main(createTable.java:51)
2008-04-15 04:26:07.693: [main] SQL: CREATE TABLE IF NOT EXISTS Job_roles (JOB_ID INTEGER NOT NULL, job_title varchar(32), salary_class varchar(50), job_description varchar(50), primary key(JOB_ID))
java.sql.SQLException: line 1:17: expecting '(', found 'NOT'
      at com.quadcap.jdbc.Statement.execute(Statement.java:258)
      at com.quadcap.jdbc.Statement.executeUpdate(Statement.java:347)
      at DB.createJobRoles(createTable.java:148)
      at createTable.main(createTable.java:51)
2008-04-15 04:26:07.696: [main] antlr.MismatchedTokenException
2008-04-15 04:26:07.696: [main] line 1:17: expecting '(', found 'NOT'
      at antlr.Parser.match(Parser.java:195)
      at com.quadcap.sql.SQLParser.createTableStmt(SQLParser.java:677)
      at com.quadcap.sql.SQLParser.statement(SQLParser.java:242)
      at com.quadcap.jdbc.Statement.execute(Statement.java:225)
      at com.quadcap.jdbc.Statement.executeUpdate(Statement.java:347)
      at DB.createTables(createTable.java:101)
      at createTable.main(createTable.java:52)
2008-04-15 04:26:07.700: [main] SQL: CREATE TABLE IF NOT EXISTS employees (name varchar(32), id varchar(50), telephoneNo varchar(50), email varchar(50), JOB_ID integer, foreign key (JOB_ID) references Job_roles (JOB_ID))
java.sql.SQLException: line 1:17: expecting '(', found 'NOT'
      at com.quadcap.jdbc.Statement.execute(Statement.java:258)
      at com.quadcap.jdbc.Statement.executeUpdate(Statement.java:347)
      at DB.createTables(createTable.java:101)
      at createTable.main(createTable.java:52)

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
 
public class createTable 
{
	public static void main(String[] args) throws SQLException 
	{
		DB db = new DB();
 
		/*
		try {
			Class.forName("com.quadcap.jdbc.JdbcDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		 */
 
		String username;
		String password;
 
		username = "something";
		password = "123456";
		Connection conn = db.dbConnect(username, password);
		//Connection conn = java.sql.DriverManager.getConnection("jdbc:qed:mynewdb", p);
		try{
			Statement st = conn.createStatement();
			//BufferedReader bf = new BufferedReader
			//(new InputStreamReader(System.in));
			//System.out.println("Enter Database name:");
			//String database = bf.readLine();
			//st.executeUpdate("CREATE DATABASE "+database);
		}
		catch (SQLException s){
			System.out.println("SQL statement is not executed!");
		}
		catch (Exception e){
			e.printStackTrace();
		}
 
		//DB db = new DB();
		//conn=db.dbConnect("jdbc:qed:mynewdb", "p");
		db.createJobRoles(conn);
		db.createTables(conn);
 
	}
}
 
class DB
{
	public DB() {}
 
	public Connection dbConnect(String db_connect_string,
			String db_userid)
	{
		try
		{
			Class.forName("com.quadcap.jdbc.JdbcDriver");
			java.util.Properties p = new java.util.Properties();
			p.setProperty("create", "true");
			java.sql.Connection conn =
				java.sql.DriverManager.getConnection("jdbc:qed:mynewdb", p);
			return conn;
 
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return null;
		}
	} 
 
 
	//Employee table
	public void createTables(Connection conn) throws SQLException
	{
 
		String query;
		Statement stmt = null;
 
		try
		{
 
			query="CREATE TABLE IF NOT EXISTS employees" +
			"(name       varchar(32), " +
			"id          varchar(50), " +
			"telephoneNo varchar(50), " +
			"email       varchar(50), " +
			"JOB_ID      integer,     " +
			"foreign key (JOB_ID) references Job_roles (JOB_ID))";
 
			stmt = conn.createStatement();
			stmt.executeUpdate(query);
 
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('John Doe', '1', '5555999944', 'Something@mail.com', 1)");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Neil Mayhem', '2', '555588888', 'sth@mail.com', 2)");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Alice Lovelace', '3', '6666999944', 'alice@mail.com', 3)");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Jay Something', '4', '666669999', 'mail@mail.com',4 )");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Terry Something', '5', '622211111', 'mail20@mail.com', 1)");
 
 
			stmt.close();
			conn.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	} //createTables
 
 
 
	public void createJobRoles(Connection conn) throws SQLException
	{
 
		String query;
		Statement stmt = null;
 
		try
		{
 
			query="CREATE TABLE IF NOT EXISTS Job_roles" +
			"(JOB_ID INTEGER NOT NULL, " +
			"job_title varchar(32), " +
			"salary_class varchar(50), " +
			"job_description varchar(50), " +
			"primary key(JOB_ID))";
 
			stmt = conn.createStatement();
			stmt.executeUpdate(query);
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (1, 'Garden Worker', '4', 'Cultivates and cares for plants')");
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (2, 'Security Guard', '4', 'Takes care of security')");
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (3, 'Stage Hypnotist', '2', 'Hypnotizes people on stage')");
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (4, 'Amusement Park Guide', '3', 'Guides people through an amusement park')");
 
 
			stmt.close();
			conn.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
 
 
	public void printFields()
	{
		String query;
		Statement stmt;
		try {
			query = "SELECT employees.name, Job_roles.salary_class FROM employees INNER JOIN Job_roles on employees.JOB_ID = Job_roles.JOB_ID";
 
			Connection conn = null;
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				//print query
				System.out.printf("Salary of %s is %s\n", rs.getString(1), rs.getObject(2).toString());
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
 
	}
 
 
	public void addColumn()
	{
		String query;
		Statement stmt;
		Connection conn = null;
 
 
		try {
			query= "ALTER TABLE employees ADD JOB_ID";
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
 
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}
 
 
 
	public void EmployeeNameSearch(String[] args) throws SQLException
	{
		List names =  Arrays.asList(args);
 
		List not = new ArrayList();
 
		//Collections.sort(names); 
 
		PreparedStatement ps = null;
 
		BufferedReader stdin = new BufferedReader(new InputStreamReader(System.in));
		while (true) {
			try {
				String option = stdin.readLine();
			} catch (IOException e1) {
				e1.printStackTrace();
			}
 
			for (Object name : names) {
				try {
					ps.setString(1, name.toString());
				} catch (SQLException e) {
					e.printStackTrace();
				}
				ResultSet rs = null;
				try {
					rs = ps.executeQuery();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				if (rs.next()) {
					// name in dba
				}
				else
				{
					System.out.println(name + "not in db!");
					not.add(name);
 
				}
			}
		}
	}
 
};

Open in new window

Mick Barry

4/14/2008

looks like they don't support that syntax.
you could try firtst dropping the table
Other option is to delete the database file before you create the connection.

perdoname_

4/14/2008

ASKER

Do you mean to add that
String query;
query = "DROP DATABASE mynewdb";
Connection conn;
Statement stmt = conn.createStatement();
stmt.executeUpdate(query); in the

in the:
      public Connection dbConnect(String db_connect_string,
                  String db_userid)

???

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

ASKER CERTIFIED SOLUTION

Mick Barry

4/14/2008

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

perdoname_

4/14/2008

ASKER

I did so [please have a look to the code]

and now returns :
Exception in thread "main" java.sql.SQLException: Can't drop table with RESTRICT because of referencing constraint: __ec_EMPLOYEES_ImportedKeyConstraint_0, table EMPLOYEES
      at com.quadcap.sql.StmtDropTable.execute(StmtDropTable.java:107)
      at com.quadcap.sql.Session.doStatement(Session.java:264)
      at com.quadcap.jdbc.Statement.execute(Statement.java:229)
      at com.quadcap.jdbc.Statement.executeUpdate(Statement.java:347)
      at DB.createJobRoles(createTable.java:148)
      at createTable.main(createTable.java:51)

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
 
public class createTable 
{
	public static void main(String[] args) throws SQLException 
	{
		DB db = new DB();
 
		/*
		try {
			Class.forName("com.quadcap.jdbc.JdbcDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		 */
 
		String username;
		String password;
 
		username = "something";
		password = "123456";
		Connection conn = db.dbConnect(username, password);
		//Connection conn = java.sql.DriverManager.getConnection("jdbc:qed:mynewdb", p);
		try{
			Statement st = conn.createStatement();
			//BufferedReader bf = new BufferedReader
			//(new InputStreamReader(System.in));
			//System.out.println("Enter Database name:");
			//String database = bf.readLine();
			//st.executeUpdate("CREATE DATABASE "+database);
		}
		catch (SQLException s){
			System.out.println("SQL statement is not executed!");
		}
		catch (Exception e){
			e.printStackTrace();
		}
 
		//DB db = new DB();
		//conn=db.dbConnect("jdbc:qed:mynewdb", "p");
		db.createJobRoles(conn);
		db.createTables(conn);
 
	}
}
 
class DB
{
	public DB() {}
 
	public Connection dbConnect(String db_connect_string,
			String db_userid) throws SQLException
			{
 
		try
		{
			Class.forName("com.quadcap.jdbc.JdbcDriver");
			java.util.Properties p = new java.util.Properties();
			p.setProperty("create", "true");
			java.sql.Connection conn =
				java.sql.DriverManager.getConnection("jdbc:qed:mynewdb", p);
			return conn;
 
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return null;
		}
			} 
 
 
	//Employee table
	public void createTables(Connection conn) throws SQLException
	{
 
		String query;
		Statement stmt = null;
		Statement stmtdrop;
		String queryDrop;
		
		queryDrop = "DROP TABLE employees";
		stmtdrop = conn.createStatement();
		stmtdrop.executeUpdate(queryDrop);
		try
		{
 
			query="CREATE TABLE employees" +
			"(name       varchar(32), " +
			"id          varchar(50), " +
			"telephoneNo varchar(50), " +
			"email       varchar(50), " +
			"JOB_ID      integer,     " +
			"foreign key (JOB_ID) references Job_roles (JOB_ID))";
 
			stmt = conn.createStatement();
			stmt.executeUpdate(query);
 
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('John Doe', '1', '5555999944', 'Something@mail.com', 1)");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Neil Mayhem', '2', '555588888', 'sth@mail.com', 2)");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Alice Lovelace', '3', '6666999944', 'alice@mail.com', 3)");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Jay Something', '4', '666669999', 'mail@mail.com',4 )");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Terry Something', '5', '622211111', 'mail20@mail.com', 1)");
 
 
			stmt.close();
			conn.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	} //createTables
 
 
 
	public void createJobRoles(Connection conn) throws SQLException
	{
 
		String query;
		Statement stmt = null;
 
		Statement stmtdrop;
		String queryDrop;
		
		queryDrop = "DROP TABLE Job_roles";
		stmtdrop = conn.createStatement();
		stmtdrop.executeUpdate(queryDrop);
		try
		{
 
			query="CREATE TABLE Job_roles" +
			"(JOB_ID INTEGER NOT NULL, " +
			"job_title varchar(32), " +
			"salary_class varchar(50), " +
			"job_description varchar(50), " +
			"primary key(JOB_ID))";
 
			stmt = conn.createStatement();
			stmt.executeUpdate(query);
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (1, 'Garden Worker', '4', 'Cultivates and cares for plants')");
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (2, 'Security Guard', '4', 'Takes care of security')");
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (3, 'Stage Hypnotist', '2', 'Hypnotizes people on stage')");
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (4, 'Amusement Park Guide', '3', 'Guides people through an amusement park')");
 
 
			stmt.close();
			conn.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
 
 
	public void printFields()
	{
		String query;
		Statement stmt;
		try {
			query = "SELECT employees.name, Job_roles.salary_class FROM employees INNER JOIN Job_roles on employees.JOB_ID = Job_roles.JOB_ID";
 
			Connection conn = null;
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				//print query
				System.out.printf("Salary of %s is %s\n", rs.getString(1), rs.getObject(2).toString());
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
 
	}
 
 
	public void addColumn()
	{
		String query;
		Statement stmt;
		Connection conn = null;
 
 
		try {
			query= "ALTER TABLE employees ADD JOB_ID";
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
 
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}
 
 
 
	public void EmployeeNameSearch(String[] args) throws SQLException
	{
		List names =  Arrays.asList(args);
 
		List not = new ArrayList();
 
		//Collections.sort(names); 
 
		PreparedStatement ps = null;
 
		BufferedReader stdin = new BufferedReader(new InputStreamReader(System.in));
		while (true) {
			try {
				String option = stdin.readLine();
			} catch (IOException e1) {
				e1.printStackTrace();
			}
 
			for (Object name : names) {
				try {
					ps.setString(1, name.toString());
				} catch (SQLException e) {
					e.printStackTrace();
				}
				ResultSet rs = null;
				try {
					rs = ps.executeQuery();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				if (rs.next()) {
					// name in dba
				}
				else
				{
					System.out.println(name + "not in db!");
					not.add(name);
 
				}
			}
		}
	}
 
};

Open in new window