Derby database

Structure

Database → Schema → Tables/Objects

• Database: In Derby, the "database" is basically the connection. There’s only one database per connection (each connection is tied to a single database). A database is essentially a directory on disk.

• Schema: Derby supports schemas, similar to PostgreSQL and SQL Server. In Derby, a schema is a namespace within a database. A schema contains tables, views, indexes, triggers, and procedures. Derby defaults schema = current user name (uppercased), for example if user = "sa" ⇒ schema is "SA". If connecting without credentials (no user) the default schema "APP" will be used

Telosys typical configuration for a Derby database

  - id: derby
    name: my Derby server database
    type: Derby 
    # JDBC connection
    driver: org.apache.derby.jdbc.ClientDriver 
    url: jdbc:derby://localhost:1527/telosysdb;create=true
    user: foo
    password: xxxxx
    # Metadata parameters
    schema: '!'
    # catalog: '!'
    tableNamePattern: '%'
    tableTypes: TABLE

• "catalog" is not required

• "schema" can be a schema name or '!' for all schemas

• if authentication is not enabled then "user" and "password" can have any value

JDBC driver

Download: https://db.apache.org/derby/derby_downloads.html

JAR files required to connect:

• derbytools.jar ( org.apache.derby.jdbc.ClientDriver )

• derbyclient.jar ( org.apache.derby.client.ClientAutoloadedDriver )

• derbyshared.jar ( org.apache.derby.shared.common.info.ProductVersionHolder )

Server Launch

Just launch bin/startNetworkServer ( default port = 1527 )

Derby case conversion rules

Applies to both table names and column names.

• Unquoted identifiers

  • Always converted to UPPERCASE

  • They are not case-sensitive in SQL requests

• Quoted identifiers

  • Case is preserved exactly as written

  • They are case-sensitive in SQL requests

Default schema = username in uppercase.

Derby types of use

Derby is written in Java, so it's possible to connect via JDBC with all JVM languages:

• Java

• Kotlin

• Scala

• Groovy

And also with some other languages:

• Python with JayDeBeApi that allows to connect from Python code to databases using Java JDBC

• C# / .NET via ODBC with a DB2-compatible driver or via JDBC with IKVM.NET (though that’s less common)

• Node.js via JDBC bridges (e.g., node-jdbc) with Derby’s JDBC driver