Setting class path property for Apache Derby

How does one go about setting the class path property for the Apache server derby. I am trying to debug this error upon creating a procedure and linking it to a Java function as the external name:

The class 'C:\Users\Koi\IdeaProjects\Java Finishing Touches\JDBC\src\main\java\MyFirstDatabaseConnection.testProc' does not exist or is inaccessible. This can happen if the class is not public or the derby.database.classpath property is missing or incorrectly defined.

As for the apache derby procedure this is the external name I defined:

CREATE PROCEDURE 
read_e_names 
() PARAMETER STYLE JAVA READS SQL DATA LANGUAGE JAVA EXTERNAL NAME 'C:\Users\Koi\IdeaProjects\Java Finishing Touches\JDBC\src\main\java\MyFirstDatabaseConnection.testProc';

Solution

In short:

Set CLASSPATH to add the path to your Procedure's Jar file.
Set EXTERNAL NAME to be the Java package name plus the class name plus the method name: for example: com.example.ReadENamesProcedure.readENames

Project Directory

create ST_Derby_Procedure in C:\

C:\ST_Derby_Procedure
├── init.sql
├── apache-derby-demodb
├── derby-my-demo-procedure
│   ├── pom.xml
│   └── src
│       └── main
│           └── java
│               └── com
│                   └── example
│                       ├── MyProcedure.java
│                       └── ReadENamesProcedure.java
└── demo-java-call-procedure
    ├── pom.xml
    └── src
        └── main
            └── java
                └── com
                    └── example
                        ├── CallAddNumbersFunction.java
                        └── CallReadENames.java

Create a database data directory

Open CMD.exe

cd C:\ST_Derby_Procedure

mkdir apache-derby-demodb

derby-my-demo-procedure

C:\ST_Derby_Procedure
├── derby-my-demo-procedure
│   ├── pom.xml
│   └── src
│       └── main
│           └── java
│               └── com
│                   └── example
│                       ├── MyProcedure.java
│                       └── ReadENamesProcedure.java

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://maven.apache.org/POM/4.0.0"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.example</groupId>
    <artifactId>derby-my-demo-procedure</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>derby-my-demo-procedure</name>
    <description>Apache Derby my-demo-procedure</description>
    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
        <maven.compiler.encoding>UTF-8</maven.compiler.encoding>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>  
    </properties>

    <build>
        <finalName>derby-my-demo-procedure</finalName>
    </build>

</project>

MyProcedure.java

package com.example;

import java.sql.SQLException;

public class MyProcedure {

    public static int addNumbers(int a, int b) throws SQLException {
        return a + b;
    }
    
}

ReadENamesProcedure.java

package com.example;

import java.sql.*;

public class ReadENamesProcedure {

    public static void readENames(ResultSet[] results) throws SQLException {

        Connection conn = DriverManager.getConnection("jdbc:default:connection");

        PreparedStatement ps = conn.prepareStatement("SELECT name FROM STUDENTS");
        
        results[0] = ps.executeQuery();
    }
}

Build

cd C:\ST_Derby_Procedure\derby-my-demo-procedure

mvn clean package

output: Our procedure.jar

C:\ST_Derby_Procedure\derby-my-demo-procedure\target\derby-my-demo-procedure.jar

Apace Derby Network Server

For Java 17+

Brower open https://db.apache.org/derby/releases/release-10_16_1_1.cgi
download db-derby-10.16.1.1-bin.zip
Unzip db-derby-10.16.1.1-bin.zip, get directory: db-derby-10.16.1.1-bin
Put directory: db-derby-10.16.1.1-bin into C:\TOOLS (Full path: C:\TOOLS\db-derby-10.16.1.1-bin)

ADD User Environment Variable

ADD DERBY_HOME , SET C:\TOOLS\db-derby-10.16.1.1-bin
EDIT PATH , ADD C:\TOOLS\db-derby-10.16.1.1-bin\bin

Run Derby Network Server

Set the path to your procedure jar to CLASSPATH
Change working directory to database data directory ( C:\ST_Derby_Procedure\apache-derby-demodb)
Open CMD.exe

set CLASSPATH=C:\ST_Derby_Procedure\derby-my-demo-procedure\target\derby-my-demo-procedure.jar

cd C:\ST_Derby_Procedure\apache-derby-demodb

startNetworkServer

Create Database

Open CMD.exe

set CLASSPATH=C:\ST_Derby_Procedure\derby-my-demo-procedure\target\derby-my-demo-procedure.jar

cd C:\ST_Derby_Procedure

ij

If you have not created a database yet, you need to create one:

in ij:

CONNECT 'jdbc:derby://localhost:1527/demodb;create=true' USER 'demouser' PASSWORD 'Passw0rd!';

If you have already created a database:

CONNECT 'jdbc:derby://localhost:1527/demodb' USER 'demouser' PASSWORD 'Passw0rd!';

C:\ST_Derby_Procedure\init.sql

CREATE TABLE STUDENTS (
    id INT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    grade VARCHAR(10),
    email VARCHAR(100),
    enrollment_date DATE
);

INSERT INTO STUDENTS (name, age, grade, email, enrollment_date)
VALUES
('Derby Alice', 20, '1', 'derby.alice@example.com', '2023-01-15'),
('Derby Bob', 22, '1', 'derby.bob@example.com', '2023-02-10'),
('Derby Charlie', 21, '2', 'derby.charlie@example.com', '2022-03-12'),
('Derby David', 23, '2', 'derby.david@example.com', '2022-04-08'),
('Derby Eva', 20, '3', 'derby.eva@example.com', '2021-05-05');

Create Table And Import Data

Open CMD.exe

set CLASSPATH=C:\ST_Derby_Procedure\derby-my-demo-procedure\target\derby-my-demo-procedure.jar

cd C:\ST_Derby_Procedure

ij

Connect to database

In ij , run command:

CONNECT 'jdbc:derby://localhost:1527/demodb' USER 'demouser' PASSWORD 'Passw0rd!';

In ij , Run SQL Command from file : init.sql

RUN 'init.sql';

Create PROCEDURE

In ij , run command:

CREATE PROCEDURE READ_E_NAMES() LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA DYNAMIC RESULT SETS 1 EXTERNAL NAME 'com.example.ReadENamesProcedure.readENames';

Note: In Windows, the instructions must be combined into one line.

Linux: This is equivalent to the above.

CREATE PROCEDURE READ_E_NAMES()
LANGUAGE JAVA
PARAMETER STYLE JAVA
READS SQL DATA
DYNAMIC RESULT SETS 1
EXTERNAL NAME 'com.example.ReadENamesProcedure.readENames';

Note:

The EXTERNAL NAME here is in Java format, which is:

package name.class name.method name
com.example . ReadENamesProcedure . readENames
'com.example.ReadENamesProcedure.readENames'

Test PROCEDURE

In ij , run command:

CALL READ_E_NAMES();

output:

ij> CALL READ_E_NAMES();
NAME
----------------------------------------------------------------------------------------------------
Derby Alice
Derby Bob
Derby Charlie
Derby David
Derby Eva

5 rows selected

Create FUNCTION

In ij , run command:

CREATE FUNCTION ADD_NUMBERS(A INT, B INT) RETURNS INT PARAMETER STYLE JAVA LANGUAGE JAVA NO SQL EXTERNAL NAME 'com.example.MyProcedure.addNumbers';

Note: In Windows, the instructions must be combined into one line.

Linux:

CREATE FUNCTION ADD_NUMBERS(A INT, B INT) RETURNS INT
PARAMETER STYLE JAVA
LANGUAGE JAVA
NO SQL
EXTERNAL NAME 'com.example.MyProcedure.addNumbers';

Test FUNCTION

In ij , run command:

VALUES ADD_NUMBERS(3, 4);

output:

ij> VALUES ADD_NUMBERS(3, 4);
1
-----------
7

1 row selected

demo-java-call-procedure

Use Java to call the above PROCEDURE and FUNCTION.

C:\ST_Derby_Procedure
└── demo-java-call-procedure
    ├── pom.xml
    └── src
        └── main
            └── java
                └── com
                    └── example
                        ├── CallAddNumbersFunction.java
                        └── CallReadENames.java

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://maven.apache.org/POM/4.0.0"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    
    <groupId>com.example</groupId>
    <artifactId>call-myprocedure</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>call-myprocedure</name>
    <description>Call myprocedure</description>
    
    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
        <maven.compiler.encoding>UTF-8</maven.compiler.encoding>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>  
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.apache.derby</groupId>
            <artifactId>derbyclient</artifactId>
            <version>10.16.1.1</version>
            </dependency>
    </dependencies>

    <build>
        <finalName>app</finalName>
    </build>

</project>

CallReadENames.java

package com.example;

import java.sql.*;

public class CallReadENames {
    public static void main(String[] args) {
        String url = "jdbc:derby://localhost:1527/demodb";
        String username = "demouser";
        String password = "Passw0rd!";

        try (Connection conn = DriverManager.getConnection(url,username, password);
             CallableStatement cs = conn.prepareCall("{CALL READ_E_NAMES()}")) {

            boolean hasResults = cs.execute();
            while (hasResults) {
                try (ResultSet rs = cs.getResultSet()) {
                    while (rs.next()) {
                        System.out.println("Name: " + rs.getString(1));
                    }
                }
                hasResults = cs.getMoreResults();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

CallAddNumbersFunction.java

package com.example;

import java.sql.*;

public class CallAddNumbersFunction {
    public static void main(String[] args) {
        String url = "jdbc:derby://localhost:1527/demodb";
        String username = "demouser";
        String password = "Passw0rd!";
                
        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement ps = conn.prepareStatement("VALUES ADD_NUMBERS(?, ?)")) {
            
            ps.setInt(1, 3);
            ps.setInt(2, 4);
            
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    int result = rs.getInt(1);
                    System.out.println("Result: " + result); // output: Result: 7
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Build And Download Dependencies

cd C:\ST_Derby_Procedure\demo-java-call-procedure

mvn clean package

mvn dependency:copy-dependencies -DoutputDirectory=target\libs

Run - Call PROCEDURE

cd C:\ST_Derby_Procedure\demo-java-call-procedure

java -cp "target\libs\*;target\app.jar" com.example.CallReadENames

get output:

C:\ST_Derby_Procedure\demo-java-call-procedure>java -cp "target\libs\*;target\app.jar" com.example.CallReadENames

Name: Derby Alice
Name: Derby Bob
Name: Derby Charlie
Name: Derby David
Name: Derby Eva

Run - Call FUNCTION

cd C:\ST_Derby_Procedure\demo-java-call-procedure

java -cp "target\libs\*;target\app.jar" com.example.CallAddNumbersFunction

get Output

C:\ST_Derby_Procedure\demo-java-call-procedure>java -cp "target\libs\*;target\app.jar" com.example.CallAddNumbersFunction

Result: 7