Bob M | 20 Oct 04:01 2013

selecting records in a derby database


I have a derby database and a table with over 4,000 records...............

I wish to:- 

a) get the number of records in the table
b) retrieve the last record
c) update this record
d) add a new record and
e) delete the first (oldest) record

Please could somebody give me examples of the type of code needed in each


Bob M

View this message in context:
Sent from the Apache Derby Users mailing list archive at

Bob M | 13 Oct 20:39 2013

explanation of errors please


I am getting the following two errors................

---- SQL Exception --------
SQL State: 42X01
Error Code: 20000
Message: Syntax error: Encountered"\', 0, 2, \" at line 1, column 43

Now this refers to data in the first record I am trying to add to my table
the record starts like this-
('06-01-2009', 0, 2, 'Tuesday',...............)
where the fields are

---- SQL Exception --------
SQL State: 25001
Error Code: 20000
Message: Cannot close a connection while a transaction is still active

Any help in understanding either of these errors - much appreciated

Bob M

View this message in context:
Sent from the Apache Derby Users mailing list archive at

(Continue reading)

Mike Martin | 12 Oct 17:12 2013

Feedback derby_downloads.html


I just spent about an hour looking for the Eclipse Derby plugins because 
your documentation for finding them links to:
which in turn links to release 

which has no mention whatsoever of Eclipse plugins.

Only after much head scratching and confusion did I manage to find the 
page for release 
( which has 
the Eclipse plugins for 10.1.1.

Thanks, Mike

marcin.niedobecki | 11 Oct 07:46 2013

Bug - Endless loop in get sequece value

hello !

we are facing problem preventing us using Derby. We found that the problem is already known by other users
but still is facing in the latest version of Derby. The problem is described under We planned to
migrate to Derby our software but facing this problem in all our projects with no clue how to work it around. 

2013-10-11 07:33:05,767 [main ] DEBUG cached
instance of singleton bean 'org.springframework.transaction.interceptor.TransactionInterceptor#0'
2013-10-11 07:33:05,907 [main ] DEBUG org.hibernate.SQLvalues next value for SEQ_USER
2013-10-11 07:33:06,204 [main ] DEBUG identifier
generated: BasicHolder[java.lang.Long[-2147483648]]
2013-10-11 07:33:06,204 [main ] DEBUG org.hibernate.SQLvalues next value for SEQ_USER
2013-10-11 07:33:06,204 [main ] DEBUG identifier
generated: BasicHolder[java.lang.Long[-2147483647]]
2013-10-11 07:33:06,204 [main ] DEBUG org.hibernate.SQLvalues next value for SEQ_USER
2013-10-11 07:33:06,204 [main ] DEBUG identifier
generated: BasicHolder[java.lang.Long[-2147483646]]
2013-10-11 07:33:06,204 [main ] DEBUG org.hibernate.SQLvalues next value for SEQ_USER
2013-10-11 07:33:06,204 [main ] DEBUG identifier
generated: BasicHolder[java.lang.Long[-2147483645]]
2013-10-11 07:33:06,219 [main ] DEBUG org.hibernate.SQLvalues next value for SEQ_USER
2013-10-11 07:33:06,219 [main ] DEBUG identifier
generated: BasicHolder[java.lang.Long[-2147483644]]
2013-10-11 07:33:06,219 [main ] DEBUG org.hibernate.SQLvalues next value for SEQ_USER
2013-10-11 07:33:06,219 [main ] DEBUG identifier
generated: BasicHolder[java.lang.Long[-2147483643]]

best regards

(Continue reading)

pelle.ullberg | 9 Oct 14:26 2013

Able to reconnect previously shutdown in-memory derby database


Could someone please explain whats wrong with this little unit test that I
mocked up? I'm using derby version

Basically I create an in-memory derby database, shut it down and then
expected it to not exist anymore. But if the unit test is right, I can
actually reconnect to it.

Best regards

Code is below!

package com.klarna.derby;

import org.apache.derby.jdbc.EmbeddedDriver;
import org.junit.Assert;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.UUID;

public class DerbyUtilsTest {

    private static final Logger LOGGER =
(Continue reading)

Frank Rivera | 1 Oct 17:00 2013

Serious issue with and tomcat-jdbc

Using Derby Embedded

Tomcat 6

using tomcat-jdbc connection pool

The following simple statement fails with the following error "Schema 'ROOT' does not exist" although the schema is clearly stated esq.licreq


Reads and writes are fine. All Alter statements fail.

Without tomcat-jdbc.jar stored in the tomcat/lib folder Alter statements work. A pooled connection problem with tomcat-jdbc doesn't seem like the cause since the underlying driver is in fact Derby.


  Frank Rivera CEO
LogicBit Software - Research Triangle Park, North Carolina
919.238.7024 408.213.8138 888.366.2280

   HoudiniEsq ™ Legal Practice Management Suite        


   Feature Rich web-based Legal Practice Management for today's cutting edge Law Firms
   Any Browser, Any OS, Anytime, Anywhere, Any size firm, SaaS or On-premise     

Simple & Simplicity are not the same thing. "Simple", as it turns out, simply isn't good enough.
NOTICE: The sender does not accept liability for any errors or omissions in the contents of this message which arise as 
a result of e-mail transmission.  This e-mail and any files transmitted with it are confidential and are intended solely for 
the use of the individual or entity to which they are addressed. It is not an offer or acceptance, and it is not intended to 
be all or part of an agreement.  This communication may contain material protected by the attorney-client privilege.  
Original material created by the author may be protected by US Copyright law, 17 U.S.C. § 101 et seq. This communication 
constitutes an electronic communication within the meaning of the Electronic Communications Privacy Act, 18 U.S.C. 
§ 2510, and its disclosure is strictly limited to the recipient intended by the sender of this message.   If you are not the 
intended recipient or the person responsible for delivering the e-mail to the intended recipient, be advised that you have 
received this e-mail in error and that any review, use, dissemination, forwarding, printing, or copying of this e-mail is strictly 
prohibited. If you have received this e-mail in error, please notify the sender immediately by return e-mail and delete this 

Trejkaz | 1 Oct 11:57 2013

Thread-safe shutdown while other threads might try to reopen

Hi all.

We have an application where there are multiple different contexts and
each is isolated from the others (like a webapp). Individual users
close and open databases at will. They're opening their own database
instance rather than sharing an instance directly, in an attempt to
improve the isolation.

When all users have disconnected from the database, the admin expects
to be able to move the database files aside, so we have to shut the
database down when nobody is connected to it.

To try and achieve that, we keep our own static reference count. This
is done somewhat like this (only the real version uses ConcurrentMap,
so there is a lot more boilerplate):

    public class Database {
        private static final Map<String, Integer> connectionCountMap =
            new HashMap<String, Integer>();
        private static final Object connectionCountLock = new Object();

        private final String path;
        private final Connection conn;

        public Database(String path) throws SQLException {
            this.path = path;
            conn = DriverManager.getConnection("jdbc:derby:" + path);

        public void close() throws SQLException {

        public void incConnectionCount(String path) {
            Integer count = connectionCountMap.get(path);
            if (count == null) {
                count = 1;
            connectionCountMap.put(path, count + 1);

        public void decConnectionCount(String path) {
            Integer count = connectionCountMap.get(path);
            int countAfterDec = count - 1;
            if (countAfterDec > 0) {
                connectionCountMap.put(path, countAfterDec);
            } else {
                try {
                    DriverManager.getConnection("jdbc:derby:" + path +
                } catch (SQLException e) { /* expected */ }

What we are finding is that if multiple threads are accessing the same
database like this, sometimes one of them will be in the middle of
opening a connection while another thread is shutting down the
database. I ended up debugging this into Derby and found that the
thread opening the database would get a null database reference. Derby
would then throw an exception saying the database not found.

So the end result is that despite the database files existing on disk,
Derby would report that the database was not found, which is wrong in

Anyway, setting the incorrect exception message aside, it seems like
Derby is thread-hostile in this regard. The docs do say only to
shutdown when you know the JVM won't open the database again. The
problem with this is that you never know that, so you can never shut
it down--so the files never get unlocked.

I know I can just slap a synchronized block around these two methods
to make it bulletproof, but there are two problems with this:
  (1) synchronized is slow and Derby's shutdown is not fast at all...
  (2) I don't know what other apps might be open in the same JVM at the same.

So I wonder if anyone with experience in this sort of thing has any
good tips on how to get around the problem.

Also, we're on Derby 10.8, so if anything has been improved in this
regard in the later releases, that would be good to know - it might
just be enough impetus to switch.


Jerry Lampi | 30 Sep 21:28 2013

Proper configuration for a very busy DB?

We have about 30 clients that connect to our version Derby DB.

The clients are programs that gather data from the operating system of 
their host and then store that data in the DB, including FTP activity.  
Sometimes, the clients get huge flurries of data all at once and Derby 
is unable to handle the influx of requests; inserts, updates, etc.  In 
addition, the clients are written so that if they are unable to talk to 
the DB, they queue up as much data as possible and then write it to the 
DB when the DB becomes available.

This client queuing is a poor design, and places greater stress on the 
DB, as when the 30 clients finally do talk to the DB, they all dump data 
at once.  The clients do not know about one another and therefore do not 
attempt any throttling or cooperation when dumping on the DB.

The net effect of all this is that the DB is too slow to keep up with 
the clients.  As clients try to feed data to the DB, it cannot accept it 
as fast as desired and this results in the clients queueing more data, 
exacerbating the issue.

So the DB is very busy.  The only significant thing we have done thus 
far is change the and increase Java 
heap to 1536m.

Is there a configuration considered optimal for a VERY busy Derby DB?



avast! Antivirus: Outbound message clean.
Virus Database (VPS): 130930-0, 09/30/2013
Tested on: 9/30/2013 2:28:40 PM
avast! - copyright (c) 1988-2013 AVAST Software.

Sergey Shelukhin | 28 Sep 01:52 2013

order of evaluation for filters in the query

Is it a bug that Derby seems to evaluate the cast in "where" before evaluating the join conditions that would make the cast valid, and is there any way to avoid that?

I have tables T, T2 and T3; all of them can be joined together by id, for simplicity let's say one-to-one.
T2 stores an application-specific type name in a column.
T3.value is a varchar column; if T2.t3_type is integral, then these values would also be integral (e.g. string "5").
I am trying to cast T3.value to decimal for integral values for some purpose

"select ... from T inner join T2 on and T2.t3_type = integral inner join T3 on where cast(T3.value as decimal(...)) > 5"

I get: "ERROR 22018: Invalid character string format for type DECIMAL."

When I rjust return all the T3.value-s to be tested ("select T3.value from T inner join T2 on and T2.t3_type = integral inner join T3 on"), I get all number strings, no spaces or anything (like "3", "11", etc.).
Just to make sure, for each value returned, I do select cast(T3.val as decimal(...)) from T3 where T3.value = (that value as string) - they all are returned, casting successfully.

Why, and what, does it fail to cast then? It appears that cast may be attempted before joining that would filter it?
Could this be happening? This sounds like a bug to me.

NOTICE: This message is intended for the use of the individual or entity to which it is addressed and may contain information that is confidential, privileged and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, you are hereby notified that any printing, copying, dissemination, distribution, disclosure or forwarding of this communication is strictly prohibited. If you have received this communication in error, please contact the sender immediately and delete it from your system. Thank You.
Bob M | 23 Sep 09:24 2013

beginner's question

I am working through Derby tutorial and am up to step 2 using 'ij'

I tried the command connect 'jdbc:derby:DBName;create=true';

and got the following error result..........................

Thread[main,5,main] Not FoundException:derby.log <Access is
ERROR XJ028: The URL 'jdbc:derby:DBName;create=true; connect ' is not
properly formed

What does this mean?

Bob M

View this message in context:
Sent from the Apache Derby Users mailing list archive at

Virag Kothari | 14 Sep 00:53 2013

Clob to Blob


Is there a way to cast from clob to blob in derby?
From the table at, there isn't seem a direct or indirect way to do so.