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.
