You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Wildman <wl...@nycap.rr.com> on 2009/12/14 22:30:15 UTC
SQLException Table does not exist although just created
I am trying to move a Java database application from mySQL to the embedded
version of the Java Database (nee Derby). I successfully create an instance
of the DriverManager and make a connection. Next, I create several tables.
Next I try to use the SYSCS_UTIL.SYSCS_IMPORT utility to load data from a
CSV file into the tables I have just created. I get the following error:
java.sql.SQLException: Table 'BILL.part_type' does not exist.
...even though I have just created the table 'part_type' a few lines above.
Is this possibly related to the 'BILL' schema? When I create the tables, I
don't specify or create any schema, although I do log in as 'Bill' when I
open the database connection.
A version of my source, edited for brevity, follows.
Thanks!
-Bill
- - - - - - - - - - - - - -
Properties props = new Properties();
props.put("user", "Bill");
props.put("password", "******"); // password masked in this copy
String full_url = URL + DEFAULT_DB + ";create=true";
try {
con = DriverManager.getConnection(full_url, props);
} catch (Exception e2) {
JOptionPane.showMessageDialog(this.gui,
"Sorry, got an exception creating the new database: " +
e2,
"Fatal", JOptionPane.INFORMATION_MESSAGE);
e2.printStackTrace(System.out);
return -1;
}
// drop any existing tables - ignore errors
try {
sendSQL("drop table part_type");
} // etc..
try {
sql = "create table part_type ("
+ "part_type_id INTEGER NOT NULL PRIMARY KEY GENERATED
ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"
+ "partnum varchar(20),"
+ "name varchar(255),"
+ "family varchar(40),"
+ "genus varchar(20),"
+ "image varchar(1)"
+ ")";
sendSQL(sql); // this sends theSQL string to the connection
String infile = getStartDir() + File.separator + "Partsref.csv";
sql = "CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'part_type', '"
+ infile + "', null, null, null, 1)";
System.err.println(sql);
sendSQL(sql); // <-- exception here
- - - - - - - - - - - - - - - - - -
--
View this message in context: http://old.nabble.com/SQLException-Table-does-not-exist-although-just-created-tp26780099p26780099.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: SQLException Table does not exist although just created
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Wildman <wl...@nycap.rr.com> writes:
> Thanks, Rick -- that is indeed quite helpful.
>
> I am a bit unsure of how to specify the table name so that it retains
> lower-case. If I quote the name when creating the table; e.g. "create table
> 'part_type' ( .... )" then I get a SQL error complaining about the quotes.
> Is there another way to specify a table name including casing?
Hi Bill,
You need to use double-quotes for SQL identifiers (single-quotes are
only for string literals):
create table "part_type" ( .... )
--
Knut Anders
Re: SQLException Table does not exist although just created
Posted by Rick Hillegas <Ri...@Sun.COM>.
Thanks for the getting-started program, Bill. I am sure it will be
helpful to other newcomers.
Cheers,
-Rick
Wildman wrote:
> Rick, thanks again for your help! After reading through the Derby docs, I was
> able to make everything work. Newbies such as myself might be interested in
> this little test program that creates a trivial database (using the embedded
> version), creates a table, adds some records, dumps the table to a file,
> clears the records, and then reads the file back into the table. All the I/O
> uses the Derby utilities.
> -Bill
> -----------------------------
> /*
> * Simple test program for Derby DB.
> * @author W. Leue
> * Date: 12/16/2009
> */
> package dbtest;
>
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.ResultSet;
> import java.sql.Statement;
> import java.util.*;
> import java.io.*;
> import java.sql.SQLException;
>
>
> public class Main {
>
> public static final boolean EMBEDDED = true;
> public static final String URL = "jdbc:derby://localhost:1527/";
> public static final String DRIVER =
> "org.apache.derby.jdbc.EmbeddedDriver";
> public static final String DEFAULT_DB = "Test";
> private static final String USER = "MyName"; // substitute your own!
> private static final String PASSWORD = "********"; // substitute your
> own!
> private Connection con = null;
> private String databaseName = DEFAULT_DB;
> private String startDir = System.getProperty("user.home");
>
> /**
> * @param args the command line arguments
> */
> public static void main(String[] args) {
> // TODO code application logic here
> Main myMain = new Main();
> }
>
> public Main() {
> if (makeDB() != 0) {
> System.exit(-1);
> }
> }
>
> // all the work gets done here:
> // create a database, make a table, add some data to the table,
> // print out the data, dump the table to a file, clear out the table,
> // print the table again to show that it is empty, load the data back
> // from the file, and print the table data again.
> public int makeDB() {
> String sql;
>
> // make database connection
> try {
> Class.forName(DRIVER).newInstance();
> } catch (Exception e) {
> System.err.println("Exception creating DriverManager: " + e);
> return -1;
> }
> System.out.println("Success in creating DriverManager instance");
>
> // create a new database and connect to it
> Properties props = new Properties();
> props.put("user", USER);
> props.put("password", PASSWORD);
> String full_url = URL + DEFAULT_DB + ";create=true";
> try {
> con = DriverManager.getConnection(full_url, props);
> } catch (Exception e2) {
> System.err.println("Exception creating new database '"
> + full_url + "': " + e2);
> return -1;
> }
> System.out.println("Success in creating database '"
> + full_url + "'");
>
> // drop any existing tables - ignore errors
> try {
> sendSQL("drop table MYTABLE");
> } catch (SQLException ex) {
> // allow to fail silently
> }
>
> // build the table anew
> try {
> sql = "create table MYTABLE ("
> + "ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS "
> + "AS IDENTITY (START WITH 1, INCREMENT BY 1),"
> + "NAME1 varchar(50),"
> + "NAME2 varchar(50)"
> + ")";
> sendSQL(sql);
> } catch (Exception ex) {
> System.err.println("Exception creating table 'MYTABLE': " + ex);
> return -1;
> }
> System.out.println("Success in creating table 'MYTABLE'");
>
> // add some data to the table
> try {
> sql = "INSERT INTO MYTABLE (NAME1, NAME2) VALUES "
> + "('row1-col1', 'row1-col2')";
> sendSQL(sql);
> sql = "INSERT INTO MYTABLE (NAME1, NAME2) VALUES "
> + "('row2-col1', 'row2-col2')";
> sendSQL(sql);
> sql = "INSERT INTO MYTABLE (NAME1, NAME2) VALUES "
> + "('row3-col1', 'row3-col2')";
> sendSQL(sql);
> } catch (Exception ex) {
> System.err.println("Success in adding data records to table");
> }
>
> // show table contents
> printTable("MYTABLE");
>
> // dump the table data to a file
> String dumpPath = startDir + File.separator + "TestDump.csv";
> File bkup = new File(dumpPath);
> bkup.delete(); // delete any existing file
> sql = "CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null, 'MYTABLE', '"
> + dumpPath + "', null, null, null)";
> try {
> sendSQL(sql);
> } catch (Exception ex) {
> System.err.println("Exception dumping table to file: " + ex);
> ex.printStackTrace(System.err);
> System.err.println("sql was '" + sql + "'");
> return -1;
> }
>
> // clear out the table
> sql = "DELETE FROM MYTABLE";
> try {
> sendSQL(sql);
> } catch (Exception ex) {
> System.err.println("Exception clearing table records: " + ex);
> return -1;
> }
>
> // show table contents
> printTable("MYTABLE");
>
> // now load the table data back in from the file
> // If we use the full table dump as shown above, the dump file will
> // contain the IDENTITY fields (e.g. master key).
> // It is important NOT to try to load those keys from the file, so
> // the IMPORT_DATA utility is used instead of IMPORT_TABLE,
> // and the IDENTITY colums are skipped in both the column name and
> // column index arguments (args 3 & 4).
> sql = "CALL SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'MYTABLE', "
> + "'NAME1, NAME2', '2, 3', '" + dumpPath + "', null, "
> + "null, null, 1)";
> try {
> sendSQL(sql);
> } catch (Exception ex) {
> System.err.println("Exception loading table from file: " + ex);
> System.err.println("sql was '" + sql + "'");
> return -1;
> }
>
> // and print table contents again
> printTable("MYTABLE");
> return 0;
> }
>
> // execute an SQL statement and return the result
> public ResultSet sendSQL(String sql) throws SQLException {
> ResultSet r = null;
> Statement statement = con.createStatement();
> if (statement.execute(sql)) {
> r = statement.getResultSet();
> }
> return r;
> }
>
> // print all the records in the specified table
> public int printTable(String tname) {
> System.out.println("Contents of '" + tname + "':");
> System.out.println("------------------------------------");
> String sql;
> sql = "SELECT * FROM " + tname;
> try {
> ResultSet r = sendSQL(sql);
> if (r == null) {
> System.err.println("Null result from query");
> return -1;
> }
>
> while (r.next()) {
> int id = r.getInt("ID");
> String name1 = r.getString("NAME1");
> String name2 = r.getString("NAME2");
> System.out.println("" + id + ": '" + name1 + "'\t'"
> + name2 + "'");
> }
> System.out.println("------------------------------------");
> } catch (Exception ex) {
> System.err.println("Exception querying table '"
> + tname + "': " + ex);
> System.err.println("sql was '" + sql + "'");
> return -1;
> }
> return 0;
> }
>
> // return the database name
> public String getDatabaseName() {
> return databaseName;
> }
> }
>
>
Re: SQLException Table does not exist although just created
Posted by Wildman <wl...@nycap.rr.com>.
Rick, thanks again for your help! After reading through the Derby docs, I was
able to make everything work. Newbies such as myself might be interested in
this little test program that creates a trivial database (using the embedded
version), creates a table, adds some records, dumps the table to a file,
clears the records, and then reads the file back into the table. All the I/O
uses the Derby utilities.
-Bill
-----------------------------
/*
* Simple test program for Derby DB.
* @author W. Leue
* Date: 12/16/2009
*/
package dbtest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;
import java.io.*;
import java.sql.SQLException;
public class Main {
public static final boolean EMBEDDED = true;
public static final String URL = "jdbc:derby://localhost:1527/";
public static final String DRIVER =
"org.apache.derby.jdbc.EmbeddedDriver";
public static final String DEFAULT_DB = "Test";
private static final String USER = "MyName"; // substitute your own!
private static final String PASSWORD = "********"; // substitute your
own!
private Connection con = null;
private String databaseName = DEFAULT_DB;
private String startDir = System.getProperty("user.home");
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
// TODO code application logic here
Main myMain = new Main();
}
public Main() {
if (makeDB() != 0) {
System.exit(-1);
}
}
// all the work gets done here:
// create a database, make a table, add some data to the table,
// print out the data, dump the table to a file, clear out the table,
// print the table again to show that it is empty, load the data back
// from the file, and print the table data again.
public int makeDB() {
String sql;
// make database connection
try {
Class.forName(DRIVER).newInstance();
} catch (Exception e) {
System.err.println("Exception creating DriverManager: " + e);
return -1;
}
System.out.println("Success in creating DriverManager instance");
// create a new database and connect to it
Properties props = new Properties();
props.put("user", USER);
props.put("password", PASSWORD);
String full_url = URL + DEFAULT_DB + ";create=true";
try {
con = DriverManager.getConnection(full_url, props);
} catch (Exception e2) {
System.err.println("Exception creating new database '"
+ full_url + "': " + e2);
return -1;
}
System.out.println("Success in creating database '"
+ full_url + "'");
// drop any existing tables - ignore errors
try {
sendSQL("drop table MYTABLE");
} catch (SQLException ex) {
// allow to fail silently
}
// build the table anew
try {
sql = "create table MYTABLE ("
+ "ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS "
+ "AS IDENTITY (START WITH 1, INCREMENT BY 1),"
+ "NAME1 varchar(50),"
+ "NAME2 varchar(50)"
+ ")";
sendSQL(sql);
} catch (Exception ex) {
System.err.println("Exception creating table 'MYTABLE': " + ex);
return -1;
}
System.out.println("Success in creating table 'MYTABLE'");
// add some data to the table
try {
sql = "INSERT INTO MYTABLE (NAME1, NAME2) VALUES "
+ "('row1-col1', 'row1-col2')";
sendSQL(sql);
sql = "INSERT INTO MYTABLE (NAME1, NAME2) VALUES "
+ "('row2-col1', 'row2-col2')";
sendSQL(sql);
sql = "INSERT INTO MYTABLE (NAME1, NAME2) VALUES "
+ "('row3-col1', 'row3-col2')";
sendSQL(sql);
} catch (Exception ex) {
System.err.println("Success in adding data records to table");
}
// show table contents
printTable("MYTABLE");
// dump the table data to a file
String dumpPath = startDir + File.separator + "TestDump.csv";
File bkup = new File(dumpPath);
bkup.delete(); // delete any existing file
sql = "CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null, 'MYTABLE', '"
+ dumpPath + "', null, null, null)";
try {
sendSQL(sql);
} catch (Exception ex) {
System.err.println("Exception dumping table to file: " + ex);
ex.printStackTrace(System.err);
System.err.println("sql was '" + sql + "'");
return -1;
}
// clear out the table
sql = "DELETE FROM MYTABLE";
try {
sendSQL(sql);
} catch (Exception ex) {
System.err.println("Exception clearing table records: " + ex);
return -1;
}
// show table contents
printTable("MYTABLE");
// now load the table data back in from the file
// If we use the full table dump as shown above, the dump file will
// contain the IDENTITY fields (e.g. master key).
// It is important NOT to try to load those keys from the file, so
// the IMPORT_DATA utility is used instead of IMPORT_TABLE,
// and the IDENTITY colums are skipped in both the column name and
// column index arguments (args 3 & 4).
sql = "CALL SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'MYTABLE', "
+ "'NAME1, NAME2', '2, 3', '" + dumpPath + "', null, "
+ "null, null, 1)";
try {
sendSQL(sql);
} catch (Exception ex) {
System.err.println("Exception loading table from file: " + ex);
System.err.println("sql was '" + sql + "'");
return -1;
}
// and print table contents again
printTable("MYTABLE");
return 0;
}
// execute an SQL statement and return the result
public ResultSet sendSQL(String sql) throws SQLException {
ResultSet r = null;
Statement statement = con.createStatement();
if (statement.execute(sql)) {
r = statement.getResultSet();
}
return r;
}
// print all the records in the specified table
public int printTable(String tname) {
System.out.println("Contents of '" + tname + "':");
System.out.println("------------------------------------");
String sql;
sql = "SELECT * FROM " + tname;
try {
ResultSet r = sendSQL(sql);
if (r == null) {
System.err.println("Null result from query");
return -1;
}
while (r.next()) {
int id = r.getInt("ID");
String name1 = r.getString("NAME1");
String name2 = r.getString("NAME2");
System.out.println("" + id + ": '" + name1 + "'\t'"
+ name2 + "'");
}
System.out.println("------------------------------------");
} catch (Exception ex) {
System.err.println("Exception querying table '"
+ tname + "': " + ex);
System.err.println("sql was '" + sql + "'");
return -1;
}
return 0;
}
// return the database name
public String getDatabaseName() {
return databaseName;
}
}
--
View this message in context: http://old.nabble.com/SQLException-Table-does-not-exist-although-just-created-tp26780099p26813899.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: SQLException Table does not exist although just created
Posted by Wildman <wl...@nycap.rr.com>.
Rick,
Again, thanks for your very generous and detailed help!
I am attempting to use the same data file that I used with mySQL. It has no
column names at all, just values. It is in CSV format with strings having
embedded spaces and commas surrounded by double quotes.
I guess I had better start reading the Derby documentation seriously -- it
appears to be quite different from mySQL in many ways.
-Bill
--
View this message in context: http://old.nabble.com/SQLException-Table-does-not-exist-although-just-created-tp26780099p26798039.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: SQLException Table does not exist although just created
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Bill,
It's hard to say without seeing the SQL you used to create the tables
and the SQL of the query which is failing. You may have another
capitalization problem here: column names obey the same casing rules as
table names.
Hope this helps,
-Rick
Wildman wrote:
> After having fixed the table name capitalization problem, when I attempt to
> execute the CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE call, I get this error:
>
> java.sql.SQLException: The exception 'java.sql.SQLException: Column
> 'COLUMN2' is either not in any table in the FROM list or appears within a
> join specification and is outside the scope of the join specification or
> appears in a HAVING clause and is not in the GROUP BY list. If this is a
> CREATE or ALTER TABLE statement then 'COLUMN2' is not a column in the
> target table.' was thrown while evaluating an expression.
> at
> org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown
> Source)
>
> This is quite a helpful error message, yes? Can anyone help in interpreting
> it?
>
> Thanks!
> -Bill
>
>
Re: SQLException Table does not exist although just created
Posted by Wildman <wl...@nycap.rr.com>.
After having fixed the table name capitalization problem, when I attempt to
execute the CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE call, I get this error:
java.sql.SQLException: The exception 'java.sql.SQLException: Column
'COLUMN2' is either not in any table in the FROM list or appears within a
join specification and is outside the scope of the join specification or
appears in a HAVING clause and is not in the GROUP BY list. If this is a
CREATE or ALTER TABLE statement then 'COLUMN2' is not a column in the
target table.' was thrown while evaluating an expression.
at
org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown
Source)
This is quite a helpful error message, yes? Can anyone help in interpreting
it?
Thanks!
-Bill
--
View this message in context: http://old.nabble.com/SQLException-Table-does-not-exist-although-just-created-tp26780099p26796232.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: SQLException Table does not exist although just created
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Bill,
Some responses inline..
Wildman wrote:
> Rick,
>
> Again, this is very helpful! One might well ask why single quotes are
> allowed in the SYSCS_UTIL call (and in fact, Apache's examples show single
> quotes in that application) but not in the SQL create table.
This is because the ANSI/ISO syntax makes a distinction between string
literals and SQL identifiers. String literals are enclosed in single
quotes. SQL identifiers are double-quoted if you need them to be
case-sensitive. It helps to keep in mind that SQL is a very old language
which goes back to the days when case-insensitive languages were still
in fashion. Mixing case-insensitive SQL with case-sensitive Java
requires some patience.
> Actually, I
> think the REAL question is, "Why does the SQL standard not include I/O to
> load tables from files" -- this forces every database to implement this
> functionality in its own, idiosyncratic way.
>
I suspect this is because by the time the language was standardized,
each major vendor had already solved this problem in a proprietary way.
There was no good way to harmonize these different approaches.
Hope this helps,
-Rick
> -Bill
>
Re: SQLException Table does not exist although just created
Posted by Wildman <wl...@nycap.rr.com>.
Rick,
Again, this is very helpful! One might well ask why single quotes are
allowed in the SYSCS_UTIL call (and in fact, Apache's examples show single
quotes in that application) but not in the SQL create table. Actually, I
think the REAL question is, "Why does the SQL standard not include I/O to
load tables from files" -- this forces every database to implement this
functionality in its own, idiosyncratic way.
-Bill
--
View this message in context: http://old.nabble.com/SQLException-Table-does-not-exist-although-just-created-tp26780099p26795557.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: SQLException Table does not exist although just created
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Bill,
You need to use double-quotes rather than single-quotes. See the
following section in the Derby Reference Guide:
http://db.apache.org/derby/docs/10.5/ref/ref-single.html#crefsqlj34834
Hope this helps,
-Rick
Wildman wrote:
> Thanks, Rick -- that is indeed quite helpful.
>
> I am a bit unsure of how to specify the table name so that it retains
> lower-case. If I quote the name when creating the table; e.g. "create table
> 'part_type' ( .... )" then I get a SQL error complaining about the quotes.
> Is there another way to specify a table name including casing?
>
> Thanks!
> -Bill
>
>
Re: SQLException Table does not exist although just created
Posted by Wildman <wl...@nycap.rr.com>.
Thanks, Rick -- that is indeed quite helpful.
I am a bit unsure of how to specify the table name so that it retains
lower-case. If I quote the name when creating the table; e.g. "create table
'part_type' ( .... )" then I get a SQL error complaining about the quotes.
Is there another way to specify a table name including casing?
Thanks!
-Bill
--
View this message in context: http://old.nabble.com/SQLException-Table-does-not-exist-although-just-created-tp26780099p26795124.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: SQLException Table does not exist although just created
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Bill,
The schema and table name arguments are case sensitive. In addition,
since you did not use quoted identifiers when you created the table, the
table name is PART_TYPE, not part_type. Try your experiment with an
uppercased table name.
Hope this helps,
-Rick
Wildman wrote:
> I am trying to move a Java database application from mySQL to the embedded
> version of the Java Database (nee Derby). I successfully create an instance
> of the DriverManager and make a connection. Next, I create several tables.
> Next I try to use the SYSCS_UTIL.SYSCS_IMPORT utility to load data from a
> CSV file into the tables I have just created. I get the following error:
>
> java.sql.SQLException: Table 'BILL.part_type' does not exist.
>
> ...even though I have just created the table 'part_type' a few lines above.
>
> Is this possibly related to the 'BILL' schema? When I create the tables, I
> don't specify or create any schema, although I do log in as 'Bill' when I
> open the database connection.
>
> A version of my source, edited for brevity, follows.
>
> Thanks!
> -Bill
> - - - - - - - - - - - - - -
> Properties props = new Properties();
> props.put("user", "Bill");
> props.put("password", "******"); // password masked in this copy
> String full_url = URL + DEFAULT_DB + ";create=true";
> try {
> con = DriverManager.getConnection(full_url, props);
> } catch (Exception e2) {
> JOptionPane.showMessageDialog(this.gui,
> "Sorry, got an exception creating the new database: " +
> e2,
> "Fatal", JOptionPane.INFORMATION_MESSAGE);
> e2.printStackTrace(System.out);
> return -1;
> }
>
> // drop any existing tables - ignore errors
> try {
> sendSQL("drop table part_type");
> } // etc..
> try {
> sql = "create table part_type ("
> + "part_type_id INTEGER NOT NULL PRIMARY KEY GENERATED
> ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"
> + "partnum varchar(20),"
> + "name varchar(255),"
> + "family varchar(40),"
> + "genus varchar(20),"
> + "image varchar(1)"
> + ")";
> sendSQL(sql); // this sends theSQL string to the connection
>
> String infile = getStartDir() + File.separator + "Partsref.csv";
> sql = "CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'part_type', '"
> + infile + "', null, null, null, 1)";
> System.err.println(sql);
> sendSQL(sql); // <-- exception here
> - - - - - - - - - - - - - - - - - -
>
>