2. Algorithm
  3. java - Setting class path property for Apache Derby - Stack Overflow

java - Setting class path property for Apache Derby - Stack Overflow

admin2025-04-10  0

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';

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';
  • java
  • sql
  • derby
Share Improve this question asked Mar 23 at 13:25 Ken KiarieKen Kiarie 1411 gold badge1 silver badge8 bronze badges
Add a comment  | 

1 Answer 1

Reset to default 2

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./2001/XMLSchema-instance" xmlns="http://maven.apache./POM/4.0.0"
         xsi:schemaLocation="http://maven.apache./POM/4.0.0 https://maven.apache./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>
        <mavenpiler.source>17</mavenpiler.source>
        <mavenpiler.target>17</mavenpiler.target>
        <mavenpiler.encoding>UTF-8</mavenpiler.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./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', '[email protected]', '2023-01-15'),
('Derby Bob', 22, '1', '[email protected]', '2023-02-10'),
('Derby Charlie', 21, '2', '[email protected]', '2022-03-12'),
('Derby David', 23, '2', '[email protected]', '2022-04-08'),
('Derby Eva', 20, '3', '[email protected]', '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./2001/XMLSchema-instance" xmlns="http://maven.apache./POM/4.0.0"
         xsi:schemaLocation="http://maven.apache./POM/4.0.0 https://maven.apache./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>
        <mavenpiler.source>17</mavenpiler.source>
        <mavenpiler.target>17</mavenpiler.target>
        <mavenpiler.encoding>UTF-8</mavenpiler.encoding>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>  
    </properties>

    <dependencies>
        <dependency>
            <groupId>.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
转载请注明原文地址:http://conceptsofalgorithm.com/Algorithm/1744284901a239666.html

javaSetting class path property for Apache DerbyStack Overflow

最新回复(0)