java.sql.SQLSyntaxErrorException: The class 'TempSP' does not exist or is inaccessible

Question

I am trying to create stored procedure in derby database using java.

public class TestDrive {

public static void main (String[] args) throws SQLException, ClassNotFoundException{
    Connection conn = null;
    CallableStatement stmt = null;
    try {
        try{
        Class.forName("org.apache.derby.jdbc.ClientDriver").;
    } catch(ClassNotFoundException e){
        System.out.println("Can't load the database driver");
        return;
    }
        conn = DriverManager.getConnection("jdbc:derby://localhost:1527/Connections_DB", "Connections_DB", "KaayRey");
        stmt = conn.prepareCall("CALL NameOFstoreProcedure4( ?, ? )");
        stmt.setString(1, "Kitu");
        stmt.setString(2, "KaayChalay");
        stmt.execu`enter code here`te();
    } finally {
        if (stmt != null) {
            stmt.close();
        }
        if (conn != null) {
            conn.close();
        }
    }
}
}   //END OF CLASS TestDrive.java

TempSP.java Code:-

public class TempSP {
public static void NameOFSP(String uname, String upass)throws SQLException {
   PreparedStatement stmt = null;
   ResultSet res = null;
   String message = null ;
    try{
        try{`enter code here`
        Class.forName("org.apache.derby.jdbc.ClientDriver");
    } catch(ClassNotFoundException e){
        System.out.println("Can't load the database driver");
        return;
    }
    String dbURL = "jdbc:derby://localhost:1527/Connections_DB";
    Connection conn = DriverManager.getConnection(dbURL, "Connections_DB", "KaayRey");      
     Stri`enter code here`ng sql = "INSERT INTO USERS2(USER_NAME, USER_PASSWORD) VALUES(?, ?)";
     stmt = conn.prepareStatement(sql);
     stmt.setString(1, uname);
     stmt.setString(2, upass);
         int row = stmt.executeUpdate(sql);
    }catch (SQLException ex) {
        message = "ERROR 1 : " + ex.getMessage();
        ex.printStackTrace();
    } 
}
} // END OD TempSP.java

But when I run run my TestDrive.java file it will gives me following error:-

"Exception in thread "main" java.sql.SQLSyntaxErrorException: The class 'TempSP' does not exist or is inaccessible. This can happen if the class is not public.
    at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
    at org.apache.derby.client.am.Connection.prepareCall(Unknown Source)
    at FriendsConnectionPack.TestDrive.main(TestDrive.java:34)
Caused by: org.apache.derby.client.am.SqlException: The class 'TempSP' does not exist or is inaccessible. This can happen if the class is not public.
    at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.readPrepare(Unknown Source)
    at org.apache.derby.client.net.StatementReply.readPrepare(Unknown Source)
    at org.apache.derby.client.net.NetStatement.readPrepare_(Unknown Source)
    at org.apache.derby.client.am.Statement.readPrepare(Unknown Source)
    at org.apache.derby.client.am.PreparedStatement.readPrepareDescribeInput(Unknown Source)
    at org.apache.derby.client.am.PreparedStatement.flowPrepareDescribeInputOutput(Unknown Source)
    at org.apache.derby.client.am.PreparedStatement.prepare(Unknown Source)
    at org.apache.derby.client.am.Connection.prepareCallX(Unknown Source)
    ... 2 more
Caused by: org.apache.derby.client.am.SqlException: Java exception: 'TempSP: java.lang.ClassNotFoundException'.
    ... 13 more
Java Result: 1
BUILD SUCCESSFUL (total time: 1 second)"

Derby Stored Procedure Code As Follows:-

CREATE PROCEDURE NameOFstoreProcedure5(User_Name VARCHAR(50), User_Password VARCHAR(20))
PARAMETER STYLE JAVA READS SQL DATA LANGUAGE JAVA EXTERNAL NAME 
'FriendsConnectionPack.TempSP.NameOFSP'

Answer 1

I encountered the same problem and the solution was setting the stored procedure's code in a jar file and then using the derby's systems procedures to register the jar and setting the derby's class path as shown next

To add a jar to Derby:

CALL sqlj.install_jar('PATH TO JAR FILE', 'DATABASE.IDENTIFIER FOR THE JAR', 0)

To add the jar to derby's internal classpath

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
'derby.database.classpath',
'colonSeparatedJarFiles')

Hope this helps someone