Derby database tips
Making a database connection in ij
ij is the sql client that comes with Derby. Here are a few sample connection strings:
connect 'jdbc:derby:admin' user 'APP' password ''; connect 'jdbc:derby:C:\zephyr\databases\zeprs' user 'APP' password ''; connect 'jdbc:derby://localhost/C:\zephyr\databases\zeprs'' user 'APP' password ''; connect 'jdbc:derby://localhost//var/www/zeprs_standalone/databases/zeprs' user 'APP' password ' ';
Backups
This dumps the db filesystem - not the DDL.
set connection zeprs;
CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE('C:/zephyr/backup/');
Export data from a single table:
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (null,'SITE','C:/zephyr/site.txt',null,null,null);
Exporting Admin schema
BackupDAO.backupAdminSchema() saves all of the dynasite-related tables in the Admin schema to .csv files according to the following format:
- Columns: comma-delimited
- Characters: double quotation mark (")
It uses the CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE system procedure.
Links to exporting both the Admin schema and certain administrative tables in the App schema (site* and user*) are available in the Data Centre section of Administration.
Import data from a single file into the ADMIN schema:
CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('ADMIN','SITE','C:/zephyr/site.txt',null,null,null,0);
Generate Schema
To generate schema, use the Derby command "dblook". Must close all connection to the db - it does an embedded connection to the db. There is a script in the databases/scripts folder - dblook.bat - that will backup the schema to zeprs.ddl. Example:
dblook -d 'jdbc:derby:C:\zcore\databases\zeprs' -o zeprs.ddl
Run a sql script
ijz C:\zephyr\databases\admin.ddl
Reset an identity column (autogenerated id)
First get the max id for the field:
select MAX(id)+1 from ADMIN.DISTRICT;
Then reset the id using the value returned:
ALTER TABLE ADMIN.DISTRICT ALTER COLUMN id RESTART WITH 6
Substitutions for MySQL commands
LIMIT
A substitute for the LIMIT keyword is setMaxRows. Example: DatabaseUtils.getList(Connection conn, Class clazz, String sql, ArrayList values, Integer maxRows) calls ZEPRSQueryRunner.query(Connection conn, String sql, Object[] params, ResultSetHandler rsh, Integer maxRows).
public Object query(Connection conn, String sql, Object[] params,
ResultSetHandler rsh, Integer maxRows) throws SQLException {
PreparedStatement stmt = null;
ResultSet rs = null;
Object result = null;
try {
stmt = this.prepareStatement(conn, sql);
stmt.setMaxRows(maxRows);
this.fillStatement(stmt, params);
rs = this.wrap(stmt.executeQuery());
result = rsh.handle(rs);
} catch (SQLException e) {
this.rethrow(e, sql, params);
} finally {
try {
close(rs);
} finally {
close(stmt);
}
}
return result;
}
After the list is returned, select the first element:
List items = DatabaseUtils.getList(conn, EncounterData.class, sql, values, 1);
if (items.size() > 0) {
item = (EncounterData) items.get(0);
}
Also check out the ROW_NUMBER function in the Derby documentation: db-derby-10.4.1.3-bin/docs/html/ref/rreffuncrownumber.html
PAGING
In the 10.5 release, paging capabilities have been greatly enhanced by the new result offset and fetch first clauses.
String sql = (String) queries.get(genSqlName) + " ORDER BY id desc OFFSET " + offset + " ROWS FETCH NEXT " + maxRows + " ROWS ONLY";
Paging could be accomplished in earlier releases using the following caveats:
Derby does not currently support ORDER BY in subqueries, so there is currently no way to guarantee the order of rows in the SELECT subquery. An optimizer override can be used to force the optimizer to use an index ordered on the desired column(s) if ordering is a firm requirement.
So, you'd need to make sure that the column you need to have ordered by is indexed with ASC or DESC.
SELECT * FROM (
SELECT
ROW_NUMBER() OVER () AS R,
p.id AS patientId, o.id AS referralId, o.encounter_id AS encounterId
FROM outcome o
INNER JOIN (patient p) ON o.patient_id = p.id
) AS TR
WHERE R <= 100
ORDER BY patientId desc;
View the following JIRA's for more information as these capabilities progress:
Query Optimization
As noted in the Paging section, one may use an optimizer override to force the optimizer to use an index ordered on the desired column(s). In a sql statement, the syntax is
-- DERBY-PROPERTIES index = indexname
Example:
String sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER () AS R," + "id, date_of_record AS field2241, item_id AS field2236, type_of_change AS field2250, " + "change_value AS field2251, expiry_date AS field2244, notes AS field2237, " + "balance AS field2248, last_patient_item_id AS field2249 " + "FROM stock_control -- DERBY-PROPERTIES index = FKSTOCK_CONTROLID \r" + ") AS TR WHERE (R > " + offset + " AND R <= " + (maxRows + offset) + ")";
See Overriding the default optimizer behavior for more information about optimizer over-riding.
Also note the user of the "\r" character (carriage return) in the statement/ There must be a carriage return between the properties statement and the order by or else the system will throw an error. See http://www.mail-archive.com/derby-user@db.apache.org/msg06296.html.
Concatenation
u2.lastName || ', ' || u2.firstName AS lastModifiedByName
Date fields
ALTER TABLE site add column created TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
UUID/GUID's
There is not a function for creating a UUID in Derby - the app must provide the UUID.
Or you can create a jar with a uuid generating method. Here is the class:
package org.cidrz.webapp.dynasite.utils;
public class ZcoreDerbyUuid {
/**
* Used to generate uuid's. Create a jar and pop it into Derby.
* @return
*/
public static String generateUuid() {
return java.util.UUID.randomUUID().toString();
}
}
To install the jar:
CALL SQLJ.install_jar('C:\tcc\databases\zcoreUuid.jar', 'zcoreUuid', 0);
Set the derby classpath to recognise the new class:
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath','APP.zcoreUuid');
Create a function to generate the uuid:
CREATE FUNCTION GENERATE_UUID()
RETURNS VARCHAR(255)
PARAMETER STYLE JAVA
NO SQL LANGUAGE JAVA
EXTERNAL NAME 'org.cidrz.webapp.dynasite.utils.ZcoreDerbyUuid.generateUuid';
To query in ij:
values GENERATE_UUID();
Here's the ALTER TABLE statement for adding a uuid field.
ALTER TABLE admin.form_type add column GLOBAL_IDENTIFIER_NAME CHAR(36);
Using the new function in a sql statement
UPDATE admin.form_field AS t SET uuid = (SELECT GENERATE_UUID() AS newuuid FROM admin.form_field WHERE t.id = admin.form_field.id );
Escape Syntax
Within a character string, to represent a single quotation mark or apostrophe, use two single quotation marks. (In other words, a single quotation mark is the escape character for a single quotation mark.)
VALUES 'Joe''s umbrella'
-- in ij, you don't need to escape the double quotation marks
VALUES 'He said, "hello!"'
see db-derby-10.4.1.3-bin/docs/html/ref/rrefsqlj28468.html
To get the last autogenerated id:
in MySQL - LAST_INSERT_ID()
in Derby - VALUES IDENTITY_VAL_LOCAL()
Strange error message
java.sql.SQLException: A network protocol error was encountered and the connection has been terminated: the requested command encountered an unarchitected and implementation-specific condition for which there was no architected message
Sometimes the Derby log (databases/derby.log) has a useful message. Unfortunately it is still cryptic:
java.lang.NullPointerException at org.apache.derby.impl.sql.execute.IndexChanger.insertAndCheckDups(Unknown Source) ...
But it does shed a little light - IndexChanger.insertAndCheckDups hints that the error has something to do w/ indexes and checking for duplicate values. A column in this table is a Char 36 named uuid - that has a unique index. I did a select on the table to see if there was already an index with this value - et voila - there was.
PDF version