Bob M | 23 Oct 05:44 2013
Picon

deleting a record - still a problem

Hi there

The error is that my SELECT can not have "For UPDATE" ????

My current code is
***************************
// retrieve and output date and time of oldest record from the table
rs = s.executeQuery("SELECT * FROM tablename ORDER BY Date ASC, Time ASC
FETCH FIRST ROW ONLY");
rs.next();
String Date2 = rs.getString("Date");
String Time2 = rs.getString("Time");

myConsole.getOut().println("Date/Time: " + Date2 + ", " + Time2);

// and now delete this record.............
s.setCursorName("MYCURSOR");
rs = s.executeQuery("SELECT * FROM tablename ORDER BY Date ASC, Time ASC
FETCH FIRST ROW ONLY FOR UPDATE");
rs.next();

psDelete = conn.prepareStatement("DELETE FROM tablename WHERE CURRENT OF
MYCURSOR");
statements.add(psDelete);

myConsole.getOut().println("Deleted oldest record");

--
View this message in context: http://apache-database.10148.n7.nabble.com/deleting-a-record-still-a-problem-tp134975.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
(Continue reading)

Bob M | 21 Oct 20:53 2013
Picon

Retrieving the 'oldest' record and deleting it

Hi

I have the following code which retrieves the 'oldest' record and prints out 
some data

rs = s.executeQuery("SELECT from tablename ORDER BY Date ASC, Time, ASC
FETCH FIRST ROW ONLY");
rs.next();
String Date = rs.getString("Date");
String Date = rs.getString("Time");
myConsole.getOut().println("Date/Time " + Date ", " + Time);

Now I wish to delete this record and the code is..........

rs = s.executeQuery("DELETE from tablename WHERE ????");

What is the ?????

Bob M

--
View this message in context: http://apache-database.10148.n7.nabble.com/Retrieving-the-oldest-record-and-deleting-it-tp134915.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Bob M | 21 Oct 04:01 2013
Picon

Retrieving newest record from derby table

Hi again

> b) retrieve the last record 
***************************
Assuming you have a column x which imposes the order you're after: 

SELECT MAX(x) FROM t 

to get the max key 

SELECT * FROM t WHERE x=<max key> 
**************************
I have a primary key comprising first two columns Date and Hours e.g.
01-01-2009, 6

And so what do I change the above code suggestion to ?

Bob M 

--
View this message in context: http://apache-database.10148.n7.nabble.com/Retrieving-newest-record-from-derby-table-tp134908.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Bob M | 20 Oct 04:01 2013
Picon

selecting records in a derby database

hi

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
instance

Thanks

Bob M

--
View this message in context: http://apache-database.10148.n7.nabble.com/selecting-records-in-a-derby-database-tp134896.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Bob M | 13 Oct 20:39 2013
Picon

explanation of errors please

Hi

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

(1)
---- 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
VARCHR, INT, INT, VARCHR,.......

(2)
---- 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: http://apache-database.10148.n7.nabble.com/explanation-of-errors-please-tp134695.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

(Continue reading)

Mike Martin | 12 Oct 17:12 2013
Picon
Picon

Feedback derby_downloads.html

Hi

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

http://db.apache.org/derby/derby_downloads.html
which in turn links to release 10.10.1.1 
(http://db.apache.org/derby/releases/release-10.10.1.1.cgi)

which has no mention whatsoever of Eclipse plugins.

Only after much head scratching and confusion did I manage to find the 
page for release 10.10.1.0 
(http://db.apache.org/derby/releases/release-10.1.1.0.html) which has 
the Eclipse plugins for 10.1.1.

Thanks, Mike

marcin.niedobecki | 11 Oct 07:46 2013
Picon

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
http://stackoverflow.com/questions/12975279/derby-sequence-loop-in-junit-test. 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 factory.support.DefaultListableBeanFactoryReturning 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 hibernate.id.SequenceGeneratorSequence 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 hibernate.id.SequenceGeneratorSequence 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 hibernate.id.SequenceGeneratorSequence 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 hibernate.id.SequenceGeneratorSequence 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 hibernate.id.SequenceGeneratorSequence 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 hibernate.id.SequenceGeneratorSequence identifier
generated: BasicHolder[java.lang.Long[-2147483643]]

best regards

(Continue reading)

pelle.ullberg | 9 Oct 14:26 2013
Picon

Able to reconnect previously shutdown in-memory derby database

Hi, 

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

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
/Pelle

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 10.10.1.1 and tomcat-jdbc

Using Derby 10.10.1.1 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

ALTER TABLE esq.licreq ADD COLUMN u_domain GENERATED ALWAYS AS (UPPER(domain));

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.



Sincerely,

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

   HoudiniEsq ™ Legal Practice Management Suite http://HoudiniEsq.com        

 

   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 {
            conn.close();
            decConnectionCount(path);
        }

        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 {
                connectionCountMap.remove(path);
                try {
                    DriverManager.getConnection("jdbc:derby:" + path +
                         ";shutdown=true");
                } 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
itself...

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.

TX

Jerry Lampi | 30 Sep 21:28 2013

Proper configuration for a very busy DB?

We have about 30 clients that connect to our version 10.8.2.2 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 derby.storage.pageCacheSize=5000 and increase Java 
heap to 1536m.

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

Thanks,

Jerry

---
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.
http://www.avast.com


Gmane