db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: SQLException Table does not exist although just created
Date Wed, 16 Dec 2009 17:15:06 GMT
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;
>     }
> }
>
>   


Mime
View raw message