asked on

JDBC Problem: Schema '...' does not exist

Hi Java Experts,

I'm currently learning some JDBC basics and experimenting with Derby db. Everything was going fine, until I tried to create table from the following program:

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

import java.util.Properties;

public class SQLExample03 {
      final String driver = "org.apache.derby.jdbc.EmbeddedDriver";
      final String protocol = "jdbc:derby:";
      final String dbname = "CarsDB";

      public void work() {
            try {
                  Class.forName(driver).newInstance();
                  Properties props = new Properties();
                  props.put("user", "sysdba");
                  props.put("password", "masterkey");
                  Connection c = DriverManager.getConnection(
                        protocol + dbname + ";create=true", props);

                  Statement s = c.createStatement();
                  s.execute("CREATE TABLE CARS(" +
                          " CarID CHAR(4) PRIMARY KEY, " +
                          " Name CHAR(20) NOT NULL DEFAULT 'Unknown', " +
                          " Cost INT NOT NULL CHECK(Cost BETWEEN 1000 AND 100000))");

                  PreparedStatement ps = c.prepareStatement(
                        "INSERT INTO CARS VALUES (?,?,?)");

                  ps.setString(1, "0001");
                  ps.setString(2, "Ferrari");
                  ps.setInt(3, 100000);
                  ps.execute();

                  ps.setString(1, "0002");
                  ps.setString(2, "Audi");
                  ps.setInt(3, 50000);
                  ps.execute();

                  ps.setString(1, "0003");
                  ps.setString(2, "Ford");
                  ps.setInt(3, 80000);
                  ps.execute();

                  ResultSet rs = s.executeQuery("SELECT * FROM Cars");
                  while (rs.next()) {
                        System.out.println(rs.getString(1) + " | " +
                                      rs.getString(2) + " | " +
                                      rs.getInt(3));
                  }

                  rs.close();
                  ps.close();
                  s.close();
                  c.close();

                  boolean gotSQLExc = false;
                  try {
                        DriverManager.getConnection(protocol + ";shutdown=true");
                  } catch (SQLException e) {
                        gotSQLExc = true;
                  }
                  if (!gotSQLExc) {
                        System.out.println("Database did not shut down normally");
                  } else {
                        System.out.println("Database shut down normally");
                  }
            } catch (Throwable e) {
                  System.out.println("Exception thrown: ");
                  if (e instanceof SQLException) {
                        printSQLError((SQLException)e);
                  } else {
                        e.printStackTrace();
                  }
            }
      }

      private static void printSQLError(SQLException e) {
            while (e != null) {
                  System.out.println(e.toString());
                  e = e.getNextException();
            }
      }

      public static void main(String[] args) {
            new SQLExample03().work();
      }
}

When I try to run it, i get this output.

Exception thrown:
java.sql.SQLSyntaxErrorException: Schema 'SYSDBA' does not exist

Very strange thing is that, when I comment lines in this program that create and update table CARS and replace them with for example:

s.execute("CREATE TABLE X(Y INT)");

Run program, and then:
* remove previous line
* uncomment previously commented lines
* run program again,
table CARS is created without any exceptions.

Also when I make simplier tables without key words DEFAULT, CHECK,... I don't get any exceptions.

I have tried for hours, to figure out what is the problem, but it simply doesn't make any sense to me.
Any ideas, what is wrong?

CEHJ

It's conceivable the driver may not support advanced syntax

elfe69

SYSDBA is certainly the name of the user you use to connect to your database, try adding the name of an existing schema in front of your table names

UrosVidojevic

ASKER

@CEHJ:
I first thought that this is the problem,
but as I said, when I modify program to create some simple table first,
and then run program again (without modifications) it works without exceptions.

Mick Barry

check the permissions the user has
check the table does not already exist
and try taking out pieces of your create statement a bit at a time until you determine exactly what it doesn't like. Or go the other way by slowly building up the create that does work until u find what breaks it

CEHJ

Try committing the creation of the table first

Mick Barry

as well as what I posted above try turning auto commit off

UrosVidojevic

ASKER

I, tried, and I get same error again.
Database doesn't exists before first execution of this program, Derby creates it automatically.

// Program 1:
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;

import java.util.Properties;

public class SQLExample03 {
final String driver = "org.apache.derby.jdbc.EmbeddedDriver";
final String protocol = "jdbc:derby:";
final String dbname = "CarsDB";

public void work() {
try {
Class.forName(driver).newInstance();
Properties props = new Properties();
props.put("user", "sysdba");
props.put("password", "masterkey");
Connection c = DriverManager.getConnection(
protocol + dbname + ";create=true", props);

Statement s = c.createStatement();

s.execute("CREATE TABLE X(Y INT)");
s.close();
c.close();

boolean gotSQLExc = false;
try {
DriverManager.getConnection(protocol + ";shutdown=true");
} catch (SQLException e) {
gotSQLExc = true;
}
if (!gotSQLExc) {
System.out.println("Database did not shut down normally");
} else {
System.out.println("Database shut down normally");
}
} catch (Throwable e) {
System.out.println("Exception thrown: ");
if (e instanceof SQLException) {
printSQLError((SQLException)e);
} else {
e.printStackTrace();
}
}
}

private static void printSQLError(SQLException e) {
while (e != null) {
System.out.println(e.toString());
e = e.getNextException();
}
}

public static void main(String[] args) {
new SQLExample03().work();
}
}

// Program 2:
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;

import java.util.Properties;

public class SQLExample03 {
final String driver = "org.apache.derby.jdbc.EmbeddedDriver";
final String protocol = "jdbc:derby:";
final String dbname = "CarsDB";

public void work() {
try {
Class.forName(driver).newInstance();
Properties props = new Properties();
props.put("user", "sysdba");
props.put("password", "masterkey");
Connection c = DriverManager.getConnection(
protocol + dbname + ";create=true", props);

Statement s = c.createStatement();
s.execute("CREATE TABLE CARS(" +
" CarID CHAR(4) PRIMARY KEY, " +
" Name CHAR(20) NOT NULL DEFAULT 'Unknown', " +
" Cost INT NOT NULL CHECK(Cost BETWEEN 1000 AND 100000))");

PreparedStatement ps = c.prepareStatement(
"INSERT INTO CARS VALUES (?,?,?)");

ps.setString(1, "0001");
ps.setString(2, "Ferrari");
ps.setInt(3, 100000);
ps.execute();

ps.setString(1, "0002");
ps.setString(2, "Audi");
ps.setInt(3, 50000);
ps.execute();

ps.setString(1, "0003");
ps.setString(2, "Ford");
ps.setInt(3, 80000);
ps.execute();

ResultSet rs = s.executeQuery("SELECT * FROM Cars");
while (rs.next()) {
System.out.println(rs.getString(1) + " | " +
rs.getString(2) + " | " +
rs.getInt(3));
}

rs.close();
ps.close();
s.close();
c.close();

boolean gotSQLExc = false;
try {
DriverManager.getConnection(protocol + ";shutdown=true");
} catch (SQLException e) {
gotSQLExc = true;
}
if (!gotSQLExc) {
System.out.println("Database did not shut down normally");
} else {
System.out.println("Database shut down normally");
}
} catch (Throwable e) {
System.out.println("Exception thrown: ");
if (e instanceof SQLException) {
printSQLError((SQLException)e);
} else {
e.printStackTrace();
}
}
}

private static void printSQLError(SQLException e) {
while (e != null) {
System.out.println(e.toString());
e = e.getNextException();
}
}

public static void main(String[] args) {
new SQLExample03().work();
}
}

So, at the begining DB doesn't exists.
If I run Program 2, I get exception.

But If I run Program 1 (which don't report any errors, and obviously creates DB), and after that run Program 2, everything is goint fine.

This is what confuse me. Why Program 2 works only when some simplier program like Program 1 creates database first.

(Sorry because my awful english).

Mick Barry

did u try setting autocommit off as I suggested above?

CEHJ

Try committing the create table statement as i mentioned earlier

elfe69

What is the exception and on which line ?

The following code is useless, you can put it in tray:
boolean gotSQLExc = false;
try {
DriverManager.getConnection(protocol + ";shutdown=true");
} catch (SQLException e) {
gotSQLExc = true;
}
if (!gotSQLExc) {
System.out.println("Database did not shut down normally");
} else {
System.out.println("Database shut down normally");
}

elfe69

MySql does not allow you to name your table X (maybe is it a function ?), if you try this statement it works fine: CREATE TABLE A(Y INT);
So change the name of your first table

UrosVidojevic

ASKER

@objects:
I tried turning autocommit off and commiting.

@elfe69:
I work with derby,
and create table... in first program was not a problem, it works fine for me.
problem is more complex create table statement in second program which causes exception.

But as I said, this exception ocurrs only if some simplier table wasn't created before.

elfe69

OK, and what is the exact error message ?

UrosVidojevic

ASKER

if I use printStackTrace():

java.sql.SQLSyntaxErrorException: Schema 'SYSDBA' does not exist
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unkn
own Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source
)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException
(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Un
known Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown So
urce)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown So
urce)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at SQLExample03.work(SQLExample03.java:25)
at SQLExample03.main(SQLExample03.java:92)
Caused by: java.sql.SQLException: Schema 'SYSDBA' does not exist
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknow
n Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransport
AcrossDRDA(Unknown Source)
... 10 more

ASKER CERTIFIED SOLUTION

elfe69

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

UrosVidojevic

ASKER

When I try only that I get same exception,
but if I do same thing with other scripts that use Cars table:

PreparedStatement ps = c.prepareStatement(
"INSERT INTO CarsDB.CARS VALUES (?,?,?)");

AND

ResultSet rs = s.executeQuery("SELECT * FROM CarsDB.Cars");

It works ok!

Thank you,

I still don't understand what is the difference, and why simplier create table... statements work fine without that, but it's ok.
It's most important that it works! :-)