Hello
Can someone get me started in the effert to fix this SQL statement to be excepted to create a table in a derby embeded db?

public boolean createTables(Connection conn) throws FileNotFoundException, IOException {
        System.out.println("in createTables:");
        boolean createdTables = false;
       Statement stmt=null;
        try {
            String thisTable=ModelUtils.getXMLResource("createAdminTable");
            System.out.println("thisTable sql String: /n"+thisTable);
            stmt = (Statement) conn.createStatement();
            //System.out.println("the statement: /n"+stmt);
            stmt.execute(thisTable);
            
            createdTables=true;
        } catch (SQLException ex) {
            Logger.getLogger(ConnectDerbyDAO.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("sql is wrong \n");
            ex.printStackTrace();
        }finally{
           
        }
/*
        for (int i = 0; i < schoolofdbTables.length; i++) {
            PreparedStatement ps = null;
            try {
                //System.out.println("array:table: " + schoolofdbTables[i]);
                //System.out.println("table string: " + ModelUtils.getXMLResource(schoolofdbTables[i]));
                ps = (PreparedStatement) conn.prepareStatement(ModelUtils.getXMLResource(schoolofdbTables[i].trim()));
             System.out.println("table preparedStatement : " + ps);
                ps.execute();
                createdTables = true;
                ps.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
 * 
 */
        return createdTables;
    }

error output

run:
driver name from xml: org.apache.derby.jdbc.EmbeddedDriver
in createTables:
thisTable sql String: /n CREATE TABLE admin (
    admin_uid VARCHAR(11) PRIMARY KEY,
    admin_password VARCHAR(11),
    admin_lname VARCHAR(20),
    admin_mname VARCHAR(20),
    admin_fname VARCHAR(20),
    admin_gender VARCHAR(1),
    admin_age INT,
    admin_start_date DATE,
    admin_end_date DATE,
    admin_address VARCHAR(20),
    admin_state VARCHAR(2),
    admin_zip INT,
    admin_area_code INT,
    admin_phone INT,
    admin_pay_rate INT )
Exception in thread "main" java.lang.NullPointerException
        at model.dao.ConnectDerbyDAO.createTables(ConnectDerbyDAO.java:158)
        at model.dao.ConnectDerbyDAO.createDatabase(ConnectDerbyDAO.java:145)
        at model.dao.ConnectDerbyDAO.<init>(ConnectDerbyDAO.java:80)
        at model.dao.ConnectDerbyDAO.<init>(ConnectDerbyDAO.java:68)
        at model.dao.DerbyDAOFactory.buildDB(DerbyDAOFactory.java:20)
        at view.Main.main(Main.java:37)
Java Result: 1
BUILD SUCCESSFUL (total time: 0 seconds)

error encluded to show errors in sql

run:
driver name from xml: org.apache.derby.jdbc.EmbeddedDriver
in createTables:
Exception in thread "main" java.lang.NullPointerException
thisTable sql String: /n CREATE TABLE admin (
        at model.dao.ConnectDerbyDAO.createTables(ConnectDerbyDAO.java:158)
    admin_uid VARCHAR(11) PRIMARY KEY,
        at model.dao.ConnectDerbyDAO.createDatabase(ConnectDerbyDAO.java:145)
    admin_password VARCHAR(11),
        at model.dao.ConnectDerbyDAO.<init>(ConnectDerbyDAO.java:80)
    admin_lname VARCHAR(20),
        at model.dao.ConnectDerbyDAO.<init>(ConnectDerbyDAO.java:68)
    admin_mname VARCHAR(20),
        at model.dao.DerbyDAOFactory.buildDB(DerbyDAOFactory.java:20)
        at view.Main.main(Main.java:37)
    admin_fname VARCHAR(20),
    admin_gender VARCHAR(1),
    admin_age INT,
    admin_start_date DATE,
    admin_end_date DATE,
    admin_address VARCHAR(20),
    admin_state VARCHAR(2),
    admin_zip INT,
    admin_area_code INT,
    admin_phone INT,
    admin_pay_rate INT )
Java Result: 1
BUILD SUCCESSFUL (total time: 0 seconds)

Thank you.

At that line something is null:
at model.dao.ConnectDerbyDAO.createTables(ConnectDerbyDAO.java:158)

My guess is the connection. So try to go to that line and print the objects you are using.
Also you need to close the connection, statement in the finally block.

At that line something is null:
at model.dao.ConnectDerbyDAO.createTables(ConnectDerbyDAO.java:158)

My guess is the connection. So try to go to that line and print the objects you are using.
Also you need to close the connection, statement in the finally block.

it appears that you are right about no connection. I will have to examine the entire class and research the way derby takes param's
to fix

conn=DriverManager.getConnection(dbURL,dbProperties);

it has the dbURL but I understood some of the documentation for derby that I believe stated that the second param was to supply a list of sorts for derby to search for necessary param.

dbProperties is a new properties() that is used to .put boolean variables to control method interation.

Is it correct to assume I can create this dbProperties.properties and add and utilize the properties brought in from xml and have a both in dbProperties?

public Properties loadDBProperties() throws FileNotFoundException, IOException {
        dbProperties = new Properties();
        dbProperties = ModelUtils.loadXMLResources();
        return dbProperties;
    }

ModelUtils.java method

public static synchronized Properties loadXMLResources() throws IOException {
        FileInputStream fis = new FileInputStream(XMLRESOURCES);       
        xmlResource = new Properties();
        xmlResource.loadFromXML(fis);
       
        fis.close();
        return xmlResource;
    }

I am not familiar with Derby, so I would like someone else to help if they can.

What I could suggest is this:

Usually I use this method: conn=DriverManager.getConnection(dbURL,username, password); Try to use that, and print the values of the variables you are using to see if the dbURL is correct. Maybe the name of the property in the Properties class is not the one the Derby expects.

Usually I use this method:
conn=DriverManager.getConnection(dbURL,username, password);.

In this post or a previous post it was suggested to add "create=true"
and you suggest hard coding the username and password as a param to connect.

I think these are both necessary now.
I am working on loading my dbProperties.properties because I realize I chaged the key name and derby is looking for specific keys in my dbProperties file ex:("shutdown=true,create=true,bExists=true,user,password etc.

I think derby is able to look through my dbProperties file and find these key's it needs but I am using loadFromXML() so I have to make sure it is able to use ".put" if I make it a new Properties()

public Properties loadDBProprties(){
dbProperties=new Properties()
dbProperties=ModelUtils.loadXMLResources();
return dbProperties;
conn=DriverManager.getConnection(dbURL,dbProperties);

The example I am working with loads properties:

 private Properties loadDBProperties() {
        InputStream dbPropInputStream = null;
        dbPropInputStream = AddressDao.class.getResourceAsStream("Configuration.properties");
        dbProperties = new Properties();
        try {
            dbProperties.load(dbPropInputStream);
        } catch (IOException ex) {
            ex.printStackTrace();
        }
        return dbProperties;
    }

# Sample ResourceBundle properties file
user=addressuser
password=addressuser
derby.driver=org.apache.derby.jdbc.EmbeddedDriver
derby.url=jdbc:derby:
db.table=ADDRESS
db.schema=APP

I have these key = value properties in my xml whidh is loading
but I am not sure what the user and password values should be.

In this post or a previous post it was suggested to add "create=true"
and you suggest hard coding the username and password as a param to connect.

.

I was able to add to my dbProperties.properties .

I am not sure if "sa" and "manager" are random examples or specific
to derby.

Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

Properties p = new Properties();

p.put("user", "sa");

p.put("password", "manager");


Connection conn = DriverManager.getConnection(

    "jdbc:derby:mynewDB", p);

I removed anything pertaining to user or password from my properties as I believe that it will be needed for the user to access the db.

I think it is connected now.

would any of this output suggest it is connected?

I am too new to this to comprehend the obvious.

driver name from xml: org.apache.derby.jdbc.EmbeddedDriver
in bCreatedTables:
thisTable sql String: /n CREATE TABLE admin (
    admin_uid VARCHAR(11) PRIMARY KEY,
    admin_password VARCHAR(11),
    admin_lname VARCHAR(20),
    admin_mname VARCHAR(20),
    admin_fname VARCHAR(20),
    admin_gender VARCHAR(1),
    admin_age INT,
    admin_start_date DATE,
    admin_end_date DATE,
    admin_address VARCHAR(20),
    admin_state VARCHAR(2),
    admin_zip INT,
    admin_area_code INT,
    admin_phone INT,
    admin_pay_rate INT )
the statement: /norg.apache.derby.impl.jdbc.EmbedStatement40@1b446d1
key='create' after createTables(): true
public boolean createDatabase() throws FileNotFoundException, IOException, SQLException {
        boolean create = false;
        dbProperties.put("create", "true");
        String dbURL = getDbURL();
        conn = DriverManager.getConnection(dbURL, dbProperties);
        create = bCreatedTables(conn);
        // dbProperties.remove("create");
        System.out.println("key='create' after createTables(): " + dbProperties.getProperty("create"));
        return create;
    }

    public boolean bCreatedTables(Connection conn) throws FileNotFoundException, IOException {
        conn = getConn();
        System.out.println("in bCreatedTables:");
        boolean bCreatedTables = false;
        Statement stmt = null;
        try {
            String thisTable = ModelUtils.getXMLResource("createAdminTable");
            System.out.println("thisTable sql String: /n" + thisTable);
            stmt = (Statement) conn.createStatement();
            System.out.println("the statement: /n" + stmt);
            stmt.execute(thisTable);

            bCreatedTables = true;
        } catch (SQLException ex) {
            Logger.getLogger(ConnectDerbyDAO.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("sql is wrong \n");
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(ConnectDerbyDAO.class.getName()).log(Level.SEVERE, null, ex);
                    System.out.println("conn !=null");
                }
            } else {
                System.out.println("conn==null");
            }
        }

I don't know if I understood correctly or you already know it. You are supposed to enter the username, password of your own database. Are you saying that the sa, manager are just examples from some code snippet?!

I removed anything pertaining to user or password from my properties as I believe that it will be needed for the user to access the db.

I think it is connected now.

would any of this output suggest it is connected?

You wrote the code and you want it to do something. Why don't you check the database and see if the query did what you want. Create the table for example.

You wrote the code and you want it to do something. Why don't you check the database and see if the query did what you want. Create the table for example.

I believe it is thanks again for the help and correct me if it is not actually working.
granted it still has issues with some tables.

for (int i = 0; i < schoolofdbTables.length; i++) {
            PreparedStatement ps = null;
            try {
                //System.out.println("array:table: " + schoolofdbTables[i]);
                //System.out.println("table string: " + ModelUtils.getXMLResource(schoolofdbTables[i]));
                ps = (PreparedStatement) conn.prepareStatement(ModelUtils.getXMLResource(schoolofdbTables[i].trim()));
                System.out.println("table preparedStatement : " + ps);
                ps.execute();
                bCreatedTables = true;
                ps.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            System.out.println("created table: "+schoolofdbTables[i]);
        }
run:
driver name from xml: org.apache.derby.jdbc.EmbeddedDriver
in bCreatedTables:
table preparedStatement : 629f8094-0128-7e58-6a66-ffff88feed96
created table: createAdminTable
table preparedStatement : 2ba7c09b-0128-7e58-6a66-ffff88feed96
created table: createPayeeTable
table preparedStatement : 8c9400a1-0128-7e58-6a66-ffff88feed96
created table: createStudentTable
table preparedStatement : ed8940a7-0128-7e58-6a66-ffff88feed96
created table: createInstructorTable
table preparedStatement : ce8780ad-0128-7e58-6a66-ffff88feed96
created table: createBookingTable
java.sql.SQLException: SQL text pointer is null.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.ConnectionChild.newSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
        at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
        at model.dao.ConnectDerbyDAO.bCreatedTables(ConnectDerbyDAO.java:193)
        at model.dao.ConnectDerbyDAO.createDatabase(ConnectDerbyDAO.java:149)
created table: createInstrAvailableTable
        at model.dao.ConnectDerbyDAO.<init>(ConnectDerbyDAO.java:80)
        at model.dao.ConnectDerbyDAO.<init>(ConnectDerbyDAO.java:68)
        at model.dao.DerbyDAOFactory.buildDB(DerbyDAOFactory.java:20)
        at view.Main.main(Main.java:37)
Caused by: java.sql.SQLException: SQL text pointer is null.
table preparedStatement : 8f3600b1-0128-7e58-6a66-ffff88feed96
        at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
        ... 19 more
created table: createInstrumentTable
table preparedStatement : f04340b7-0128-7e58-6a66-ffff88feed96
created table: createLocationTable
table preparedStatement : d15980bd-0128-7e58-6a66-ffff88feed96
created table: createPaymentTable
java.sql.SQLException: SQL text pointer is null.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.ConnectionChild.newSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
        at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
        at model.dao.ConnectDerbyDAO.bCreatedTables(ConnectDerbyDAO.java:193)
        at model.dao.ConnectDerbyDAO.createDatabase(ConnectDerbyDAO.java:149)
        at model.dao.ConnectDerbyDAO.<init>(ConnectDerbyDAO.java:80)
        at model.dao.ConnectDerbyDAO.<init>(ConnectDerbyDAO.java:68)
        at model.dao.DerbyDAOFactory.buildDB(DerbyDAOFactory.java:20)
        at view.Main.main(Main.java:37)
Caused by: java.sql.SQLException: SQL text pointer is null.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
        ... 19 more
created table: createPayRollTable
table preparedStatement : b278c0c3-0128-7e58-6a66-ffff88feed96
created table: createUserTable
table preparedStatement : 13a100c9-0128-7e58-6a66-ffff88feed96
created table: createUserGroupTable
table preparedStatement : 74d240cf-0128-7e58-6a66-ffff88feed96
created table: createUserGroupMappingTable
key='create' after createTables(): true
Exception in thread "main" java.lang.NullPointerException
        at view.KeyFactory.createKey(KeyFactory.java:52)
        at view.SetObjects.initPanelComponents(SetObjects.java:72)
        at view.SchoolJDesktopPane.createNewGuide(SchoolJDesktopPane.java:137)
        at view.SchoolJDesktopPane.login(SchoolJDesktopPane.java:232)
        at view.SchoolJDesktopPane.<init>(SchoolJDesktopPane.java:118)
        at view.Main.createJDesktopPane(Main.java:43)
        at view.Main.main(Main.java:38)
Java Result: 1
BUILD SUCCESSFUL (total time: 17 seconds)

I would suggest to make these changes:

for (int i = 0; i < schoolofdbTables.length; i++) {
            PreparedStatement ps = null;
            String query = "";
            try {
               query = ModelUtils.getXMLResource(schoolofdbTables[i].trim());
                ps = (PreparedStatement) conn.prepareStatement(query);
                ps.execute();
                bCreatedTables = true;
                ps.close();
               System.out.println("created table: "+schoolofdbTables[i]);
            } catch (SQLException ex) {
                ex.printStackTrace();
System.out.println("Table was not created: "+schoolofdbTables[i]);
System.out.println("Error at:"+schoolofdbTables[i]+":"+query);
            }
        }

With that way you can see which query went wrong. With your code the System.out.println("created table: "+schoolofdbTables[i]); was always executed because it is outside the try-catch.

Also the errors tell you the line of the file that occured. Check it out. And it is good to print the query run. Try the code again and see the queries that went wrong.

java.sql.SQLException: SQL text pointer is null.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.newSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at model.dao.ConnectDerbyDAO.bCreatedTables(ConnectDerbyDAO.java:193)
at model.dao.ConnectDerbyDAO.createDatabase(ConnectDerbyDAO.java:149)

That worked good I have to look at the cdata for the bad tables.
run:
driver name from xml: org.apache.derby.jdbc.EmbeddedDriver
in bCreatedTables:
created table: createAdminTable
created table: createPayeeTable
created table: createStudentTable
created table: createInstructorTable
created table: createBookingTable
Error at : createInstrAvailableTable
null
created table: createInstrumentTable
created table: createLocationTable
created table: createPaymentTable
Error at : createPayRollTable
null
created table: createUserTable
created table: createUserGroupTable
created table: createUserGroupMappingTable
Thank you

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.