SQL Exception: Table does not exist when accessed using SOAP service

Question

I am using Derby database in embedded mode. I have written a CreateDB class with main() function that creates/inserts/select in a table. Works fine.(I have necessary jar files in project build path:) )

But, when i use same database and table to select rows in my Soap webservice, it gives me error that table doesn't exist. here is the code: For CreateDB (Works Perfectly fine)

package com.myShop.www.ShipmentService;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class CreateDB {
public static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
public static final String JDBC_URL = "jdbc:derby:Shipment_DB;create=true"; 
public static void main(String[] args) throws SQLException, ClassNotFoundException {
    Class.forName(DRIVER);
    Connection connection = DriverManager.getConnection(JDBC_URL);
    String SQLString = "CREATE TABLE SHPMTTYPAV_TBL1(" +
            "SHPMTID INT NOT NULL, " +
            "NAME VARCHAR(30) NOT NULL, " +
            "DESCR VARCHAR(45) NOT NULL, " +
            "MINCOST DOUBLE NOT NULL, " +
            "COSTPEROUNCE DOUBLE NOT NULL, " +
            "ESTDLVYDAYS INT NOT NULL, " +
            "PRIMARY KEY(SHPMTID))";

    connection.createStatement().execute((SQLString));
    String insertSQL = "INSERT INTO SHPMTTYPAV_TBL1 VALUES(5, 'Priority Mail', 'Any item can be shipped', 5, .02, 1)";
    connection.createStatement().execute(insertSQL);

    // invoke sql query
    String selectSQL = "SELECT * From SHPMTTYPAV_TBL1";

    Statement prepStmt = connection.createStatement();

    ResultSet rs = prepStmt.executeQuery(selectSQL);
    while (rs.next()){
        System.out.println(rs.getInt("SHPMTID") + rs.getString("NAME") + rs.getString("DESCR") + rs.getDouble("MINCOST") + " " + rs.getDouble("COSTPEROUNCE")); 
    }
}
}

Code in SOAP service Implementation class: - Error of Table not found

public class ShipmentServiceBindingImpl implements ShipmentServicePortType{
public static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
public static final String JDBC_URL = "jdbc:derby:Shipment_DB;create=true";
public com.myShop.www.ShipmentService.ItemsInOrderResponseType[] getShpmtTps_Csts_DlvyDts(com.myShop.www.ShipmentService.ItemsInOrderType[] itemsInOrder) throws java.rmi.RemoteException, ClassNotFoundException, SQLException {
    Class.forName(DRIVER);
    Connection connection = DriverManager.getConnection(JDBC_URL);

    String selectSQL = "SELECT * From SHPMTTYPAV_TBL1";

    Statement prepStmt = connection.createStatement();
    ResultSet rs = prepStmt.executeQuery(selectSQL);

    int len = itemsInOrder.length;
    ItemsInOrderResponseType[] res = new ItemsInOrderResponseType[len];


    for(int i = 0; i < len; i++){
        rs.next();
        res[i] = new ItemsInOrderResponseType();
        res[i].setShpmtID(rs.getInt("SHPMTID"));
        res[i].setShpmtName(rs.getString("NAME"));
        res[i].setCost(rs.getDouble("COSTPEROUNCE"));
        res[i].setEstDlvyDays(rs.getDouble("MINCOST"));
    }
    if (rs != null) rs.close();
    if (prepStmt != null) prepStmt.close();
    if (connection != null) connection.close();
    return res;
}

}

score 1 · Answer 1 · answered Oct 11 '14 at 18:31

1

Try setting derby.system.home to the same value in both cases. If unset Derby uses the current directory which is likely different in the two cases.

answered Oct 11 '14 at 18:31

Dyre

547
1
3
2

Here are several other common reasons for "table does not exist": http://stackoverflow.com/questions/22996818/is-it-necessary-to-create-tables-each-time-you-connect-the-derby-database/23051822#23051822 – Bryan Pendleton Oct 12 '14 at 14:56

SQL Exception: Table does not exist when accessed using SOAP service

1 Answers1