Hello.

Is there someone who might be able to show me how to adjust a insert for a prepared statement sent to a derby embedded db?

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
created table: createInstrAvailableTable
created table: createInstrumentTable
created table: createLocationTable
created table: createPaymentTable
created table: createPayrollTable
created table: createUserTable
created table: createUserGroupTable
created table: createUserGroupMappingTable
in bInsertedTables:
Error at : insertAdminTable
 INSERT INTO admin (admin_uid,admin_fname,admin_password) VALUES ('ag','Garth','a')
    INSERT INTO admin (admin_uid,admin_fname,admin_password) VALUES ('aw','Wayne','a') 
Error at : insertPayeeTable
INSERT INTO payee (payee_uid,payee_fname,payee_password) VALUES ('ps','Suzies mom','p')
    
    INSERT INTO payee
    (payee_uid,payee_fname,payee_password) VALUES ('pj','Johnie dad','p') 
Error at : insertStudentTable
INSERT INTO student (stu_uid,stu_fname,stu_password) VALUES ('sj','Johnny','s')
    INSERT INTO student (stu_uid,stu_fname,stu_password) VALUES ('ss','Suzie','s') 
Error at : insertInstructorTable
INSERT INTO instructor
    (instr_uid,instr_fname,instr_password) VALUES ('ij','Johanne','i')
    INSERT INTO instructor
    (instr_uid,instr_fname,instr_password) VALUES ('im','Mozart','i') 
Error at : insertBookingTable
 
Error at : insertInstrAvailableTable
 
Error at : insertInstrumentTable
 
Error at : insertLocationTable
 
Error at : insertPaymentTable
INSERT INTO payment (pymt_method) VALUES ('cash')
    INSERT INTO payment (pymt_method) VALUES ('credit') 
Error at : insertPayrollTable
 
Error at : insertUserTable
INSERT INTO user VALUES
    ('aw','a'),
    ('sj','s'),
    ('im','i'),
    ('ps','p') 
Error at : insertUserGroupTable
INSERT INTO `usergroup` VALUES ('adm','admin'),('std','student'),('instr','instructor'),('pye','payee') 
Error at : insertUserGroupMappingTable
INSERT INTO `usergroup_mapping` VALUES ('aw','adm'),('sj','std'),('im','instr'),('ps','pye'),('ag','adm')

method to insert

public boolean bInsertedTables(Connection conn) throws FileNotFoundException, IOException {
        conn = getConn();
        System.out.println("in bInsertedTables:");
        boolean bInsertedTables = false;


        for (int i = 0; i < insertSchoolofdbTables.length; i++) {
            PreparedStatement ps = null;
            String query = "";
            String badInsert = "";
            try {
                badInsert = insertSchoolofdbTables[i].trim();
                query = ModelUtils.getXMLResource(insertSchoolofdbTables[i].trim());
                ps = (PreparedStatement) conn.prepareStatement(query);
                ps.execute();
                bInsertedTables = true;
                ps.close();
                System.out.println("created table: " + insertSchoolofdbTables[i]);
            } catch (SQLException ex) {
                System.out.println("Error at : " + badInsert);
                String value = dbProperties.getProperty(badInsert);
                System.out.println(value);
                //ex.printStackTrace();
            }
        }
        return bInsertedTables;
    }

Your problem (if the queries really look like what you've presented there) is the single quotes (') around the table names. Remove those. If you're worried that the tablename might be "generally" invalid, thereby needing to be quoted, then use double quotes (") not single quotes.

thanks for noticing those two tables I missed those "'"quotes I think I removed all table names using single quotes. I have to now figure why INSERT is not exceptable. I have only looked at examples that show using INSERT at the command line. Mine are prepared stmts so I have yet to figure what the difference may be. It might be implying that the tables are not good but in the dir I created I can not tell if there are actually tables there.


note: I deleted most of the errors here because they go on for every INSERT stmt for each table.
additionally I have attached the dir that is created to hold the database. I am not sure how to tell from the folder if the database hold any 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
created table: createInstrAvailableTable
created table: createInstrumentTable
created table: createLocationTable
created table: createPaymentTable
created table: createPayrollTable
created table: createUserTable
created table: createUserGroupTable
created table: createUserGroupMappingTable
in bInsertedTables:
java.sql.SQLSyntaxErrorException: Syntax error: Encountered "INSERT" at line 2, column 5.
Error at : insertAdminTable
 INSERT INTO admin (admin_uid,admin_fname,admin_password) VALUES ('ag','Garth','a')
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
    INSERT INTO admin (admin_uid,admin_fname,admin_password) VALUES ('aw','Wayne','a') 
        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
       
    
        ... 20 more
Caused by: ERROR 42X01: Syntax error: Encountered "INSERT" at line 2, column 5.
        at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
        at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source)
        at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
        at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
        at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
        ... 14 more

    
    INSERT INTO payee
    (payee_uid,payee_fname,payee_password) VALUES ('pj','Johnie dad','p') 
        at model.dao.ConnectDerbyDAO.bInsertedTables(ConnectDerbyDAO.java:208)
        at model.dao.ConnectDerbyDAO.bCreatedTables(ConnectDerbyDAO.java:193)
        at model.dao.ConnectDerbyDAO.createDatabase(ConnectDerbyDAO.java:163)
        at model.dao.ConnectDerbyDAO.<init>(ConnectDerbyDAO.java:94)
        at model.dao.ConnectDerbyDAO.<init>(ConnectDerbyDAO.java:82)
        at model.dao.DerbyDAOFactory.buildDB(DerbyDAOFactory.java:20)
        at view.Main.main(Main.java:37)
Caused by: java.sql.SQLException: Syntax error: Encountered "INSERT" at line 3, column 5.

        at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
    INSERT INTO student (stu_uid,stu_fname,stu_password) VALUES ('ss','Suzie','s') 
        at org.apache.derby.impl.jdbc.ConnectionChild.handleException(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.bInsertedTables(ConnectDerbyDAO.java:208)
        at model.dao.ConnectDerbyDAO.bCreatedTables(ConnectDerbyDAO.java:193)
        at model.dao.ConnectDerbyDAO.createDatabase(ConnectDerbyDAO.java:163)
        at model.dao.ConnectDerbyDAO.<init>(ConnectDerbyDAO.java:94)
        at model.dao.ConnectDerbyDAO.<init>(ConnectDerbyDAO.java:82)
        at model.dao.DerbyDAOFactory.buildDB(DerbyDAOFactory.java:20)
        at view.Main.main(Main.java:37)
Caused by: java.sql.SQLException: Syntax error: Encountered "INSERT" at line 2, column 5.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
        at 
        ... 20 more
Caused by: ERROR 42X01: Syntax error: Encountered "INSERT" at line 2, column 5.
        at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
        at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source)
        at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
        at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
        at 
        ... 14 more
Error at : insertUserTable
java.sql.SQLSyntaxErrorException: Syntax error: Encountered "user" at line 1, column 13.
INSERT INTO user VALUES
    ('aw','a'),
    ('sj','s'),
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
    ('im','i'),
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
    ('ps','p') 
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.ConnectionChild.handleException(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 view.Main.main(Main.java:37)
Caused by: java.sql.SQLException: Syntax error: Encountered "user" at line 1, column 13.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
        ... 20 more
Caused by: ERROR 42X01: Syntax error: Encountered "user" at line 1, column 13.
        at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
        at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source)
        at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
        at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
        at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
        ... 14 more
created table: insertUserGroupTable
created table: insertUserGroupMappingTable

Show the code for this part. Are you trying to execute multiple statements with a single sql string? If so, have you activated the "allow multiple statements" property?

Show the code for this part. Are you trying to execute multiple statements with a single sql string? If so, have you activated the "allow multiple statements" property?

I am pulling a insert statement from resources that is intended to insert data into each table which should have been created but I added ps.close to my bInsertTables() at the ex.printstack() and it might need the property you mentioned. I might need to dbProperties.put that in the connection. not sure.
but to answer your question. I am trying to use a new prepared statement which will contain many INSERT statements for a table
then loop so as to create another for another table.

incidentally, some tables have a call to make a ps but have no data added at this point. this empty ps may be a prob.

thanks

/////////////////////////


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
created table: createInstrAvailableTable
created table: createInstrumentTable
created table: createLocationTable
created table: createPaymentTable
created table: createPayrollTable
created table: createUserTable
created table: createUserGroupTable
created table: createUserGroupMappingTable
in bInsertedTables:
Error at : insertAdminTable
 INSERT INTO admin (admin_uid,admin_fname,admin_password) VALUES ('ag','Garth','a')
Exception in thread "main" java.lang.NullPointerException
    INSERT INTO admin (admin_uid,admin_fname,admin_password) VALUES ('aw','Wayne','a') 
        at model.dao.ConnectDerbyDAO.bInsertedTables(ConnectDerbyDAO.java:224)
        at model.dao.ConnectDerbyDAO.createDatabase(ConnectDerbyDAO.java:165)
        at model.dao.ConnectDerbyDAO.<init>(ConnectDerbyDAO.java:94)
        at model.dao.ConnectDerbyDAO.<init>(ConnectDerbyDAO.java:82)
        at model.dao.DerbyDAOFactory.buildDB(DerbyDAOFactory.java:20)
        at view.Main.main(Main.java:37)
Java Result: 1
BUILD SUCCESSFUL (total time: 7 seconds)
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);
        if(create==true){
            bCreatedTables = bInsertedTables(conn);
            if(bCreatedTables=true){
                System.out.println("Inserted data to tables");
            }
        }
        dbProperties.remove("create");
        return create;
    }
public boolean bCreatedTables(Connection conn) throws FileNotFoundException, IOException {
        conn = getConn();
        System.out.println("in bCreatedTables:");
        boolean bCreatedTables = false;


        for (int i = 0; i < schoolofdbTables.length; i++) {
            PreparedStatement ps = null;
            String query = "";
            String badTable = "";
            try {
                badTable = schoolofdbTables[i].trim();
                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) {
                System.out.println("Error at : " + badTable);
                String value = dbProperties.getProperty(badTable);
                System.out.println(value);
                //ex.printStackTrace();
            }
        }
        
        return bCreatedTables;
    }
public boolean bInsertedTables(Connection conn) throws FileNotFoundException, IOException, SQLException {
        conn = getConn();
        System.out.println("in bInsertedTables:");
        boolean bInsertedTables = false;
        for (int i = 0; i < insertSchoolofdbTables.length; i++) {
            PreparedStatement ps = null;
            String query = "";
            String badInsert = "";
            try {
                badInsert = insertSchoolofdbTables[i].trim();
                query = ModelUtils.getXMLResource(insertSchoolofdbTables[i].trim());
                ps = (PreparedStatement) conn.prepareStatement(query);
                ps.execute();
                bInsertedTables = true;
                ps.close();
                System.out.println("created table: " + insertSchoolofdbTables[i]);
            } catch (SQLException ex) {
                System.out.println("Error at : " + badInsert);
                String value = dbProperties.getProperty(badInsert);
                System.out.println(value);
                bInsertedTables = false;
                ps.close();
                ex.printStackTrace();
            }
        }
        return bInsertedTables;
    }

Edited 6 Years Ago by ceyesuma: error

Something in there is null. Add a printstacktrace to that catch block so you can at least identify which line it's happening on.

Something in there is null. Add a printstacktrace to that catch block so you can at least identify which line it's happening on.

I won't close the first statement.

I won't close the first statement.

It won't close the first statement so it must be wrong. I will have to look at that tonight and figure how to write th CDATA for a Insert and figure a way to tell if the table is even actually there.
Thanks

This question has already been answered. Start a new discussion instead.