Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: domain of lists@nabble.com designates
 216.139.236.158 as permitted sender)
Message-ID: <26813899.post@talk.nabble.com>
Date: Wed, 16 Dec 2009 08:33:43 -0800 (PST)
From: Wildman <wleue1@nycap.rr.com>
To: derby-user@db.apache.org
Subject: Re: SQLException Table does not exist although just created
In-Reply-To: <26798039.post@talk.nabble.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
References: <26780099.post@talk.nabble.com> <4B26D9FA.40202@sun.com>
 <26795124.post@talk.nabble.com> <4B27984E.4010001@sun.com>
 <26795557.post@talk.nabble.com> <26796232.post@talk.nabble.com>
 <4B27BF36.6020005@sun.com> <26798039.post@talk.nabble.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.