Re: Network server with different java (or derby) version between client and server

[Bryan Pendleton]

; Caused by: java.security.AccessControlException: access denied
>>> >> ("java.io.FilePermission" "/home/user/db/service.properties" "read")
>>> >> ...
>>> >> ERROR XBM0C: Privilegio mancante per l'operazione 'exists' sul file
>>> >> 'service.properties': access denied ("java.io.FilePermission"
>>> >> "/home/user/db/service.properties" "read")
>>> >>
>>> >>
>>> >> There are several errors like these ones but I have read permission
>>> >> on this
>>> >> file.
>>> >> The user that starts the server is the same that owns the file, the
>>> >> permissions on the file are 664.
>>> >>
>>> >> As I said, same setup but using 10.12.1.1 for the server, I have no
>>> >> problems.
>>> >>
>>> >> Best Regards
>>> >> -fed
>>> >>
>>> >> On Thu, 24 Nov 2022 at 19:52, Rick Hillegas 
>>> >> wrote:
>>> >>
>>> >>> The SQLState indicates that the server was not able to boot the
>>> >>> database. Look in the server-side derby.log to see if there is a
>>> >>> detailed error message describing why the boot failed.
>>> >>>
>>> >>> On 11/23/22 4:42 PM, fed wrote:
>>> >>>> Hi,
>>> >>>>
>>> >>>> Sorry for the late answer but I lost your reply.
>>> >>>>
>>> >>>> Two tests:
>>> >>>>
>>> >>>> I have a database updated to version 10.12.1.1, the server is running
>>> >>> with
>>> >>>> the 10.12.1.1 too and the client is using 10.12.1.1 too, the
>>> >>>> connection
>>> >>> is
>>> >>>> OK, I can use this setup.
>>> >>>>
>>> >>>> But another test:
>>> >>>> still the same database updated to version 10.12.1.1, the server is
>>> >>> running
>>> >>>> 10.15.2.0 so a newer version and the client is using 10.12.1.1: I have
>>> >>>> problems in this case the client can't connect to the database with
>>> >>>> this
>>> >>>> error:
>>> >>>>
>>> >>>> Caused by: org.apache.derby.client.am.SqlException: DERBY SQL error:
>>> >>>> ERRORCODE: 4, SQLSTATE: XJ040, SQLERRMC: Impossibile avviare il
>>> >>>> database '/home/user/some_db_path/' con il caricatore di classi
>>> >>>> jdk.internal.loader.ClassLoaders$AppClassLoader@277050dc. Per i
>>> >>> dettagli,
>>> >>>> vedere l'eccezione successiva.::SQLSTATE: XBM0C
>>> >>>>
>>> >>>> Thanks for the help
>>> >>>>
>>> >>>>
>>> >>>>
>>> >>>> On Sun, 13 Nov 2022 at 15:26, Bryan Pendleton <
>>> >>> bpendleton.de...@gmail.com>
>>> >>>> wrote:
>>> >>>>
>>> >>>>> I'm not aware of client-server version incompatibilities. Have you
>>> >>>>> done any experiments with different versions?
>>> >>>>>
>>> >>>>> thanks,
>>> >>>>>
>>> >>>>> bryan
>>> >>>>>
>>> >>>>> On Thu, Nov 10, 2022 at 4:16 AM fed  wrote:
>>> >>>>>> Hi,
>>> >>>>>>
>>> >>>>>> using derby with network server setup is there any problem if the
>>> >>> server
>>> >>>>> and the client are running on different java versions?
>>> >>>>>> Still on this, considering the database created/updated with the
>>> >>>>>> apache
>>> >>>>> derby version that the client uses, is there any problem if the
>>> >>>>> server
>>> >>> will
>>> >>>>> use a newer version of apache derby?
>>> >>>>>> Thanks for the help
>>> >>>>>>
>>> >>>>>> -fed
>>> >>>
>>> >>>
>>> >
>>>

Re: Network server with different java (or derby) version between client and server

[Bryan Pendleton]

I'm not aware of client-server version incompatibilities. Have you
done any experiments with different versions?

thanks,

bryan

On Thu, Nov 10, 2022 at 4:16 AM fed  wrote:
>
> Hi,
>
> using derby with network server setup is there any problem if the server and 
> the client are running on different java versions?
>
> Still on this, considering the database created/updated with the apache derby 
> version that the client uses, is there any problem if the server will use a 
> newer version of apache derby?
>
> Thanks for the help
>
> -fed

Re: deadlock when running SYSCS_UTIL.SYSCS_COMPRESS_TABLE

[Bryan Pendleton]

A wild guess: if the INSERT transaction doesn't commit immediately,
but rather inserts multiple rows in a group before committing them
all, then maybe the transaction gets too large, and then once there
have been enough row locks taken by inserting the individual rows, the
database is switching from row locking to table locking, and that's
what causes the deadlock.

As an experiment, try committing the INSERT transaction more
frequently, even (as an extreme) committing after every row inserted,
and see if that avoids the deadlock.

Switching the discussion slightly, one technique that people often use
instead of trying to re-use the same table by deleting rows and
compressing in a way that keeps pace with the insert load, is to use
multiple tables, and have the insert switch to an alternate table
after some time, then the cleaning program can delete the old rows and
compress, and then the two tasks and flip back.

So maybe you could enhance QPID's DerbyMessageStore so that it uses a
collection of tables, not just a single table, so that it can arrange
to have the compress work operating on a different table than the
insert work.

bryan

On Thu, Sep 29, 2022 at 11:28 AM Rick Hillegas  wrote:
>
> I'm not an expert on the table compression code, but it does need an overhaul 
> (see https://issues.apache.org/jira/browse/DERBY-3683).
>
> If I'm interpreting the error correctly, the table compressor is blocked 
> trying to escalate its lock on the SYSCONGLOMERATES row from shared to 
> exclusive. But it can't do this because the INSERT transaction holds a shared 
> lock on the SYSCONGLOMERATES row. I don't understand why the INSERT 
> transaction would need to lock a SYSCONGLOMERATES row. The following simple 
> experiment shows that, by itself, a simple INSERT should not lock any rows in 
> SYSCONGLOMERATES:
>
> ij version 10.17
>
> ij> CONNECT 'jdbc:derby:memory:db;create=true';
>
> ij> AUTOCOMMIT OFF;
>
> ij> CREATE TABLE t(a INT);
>
> 0 rows inserted/updated/deleted
>
> ij> CREATE INDEX t_idx ON t(a);
>
> 0 rows inserted/updated/deleted
>
> ij> CREATE TABLE s(a INT);
>
> 0 rows inserted/updated/deleted
>
> ij> INSERT INTO s VALUES (1);
>
> 1 row inserted/updated/deleted
>
> ij> COMMIT;
>
> ij> INSERT INTO t SELECT * FROM s;
>
> 1 row inserted/updated/deleted
>
> ij> SELECT * FROM SYSCS_DIAG.LOCK_TABLE;
>
> XID|TYPE |MODE|TABLENAME  
>  
> |LOCKNAME|STATE|TABLETYPE|LOCK&|INDEXNAME
>
> ---
>
> 186|TABLE|IX  |T  
>  
> |Tablelock   |GRANT|T|2|NULL
>
> 186|ROW  |X   |T  
>  
> |(1,7)   |GRANT|T|1|NULL
>
> 2 rows selected
>
> ij> COMMIT;
>
> ij> SELECT * FROM SYSCS_DIAG.LOCK_TABLE;
>
> XID|TYPE |MODE|TABLENAME  
>  
> |LOCKNAME|STATE|TABLETYPE|LOCK&|INDEXNAME
>
> ---
>
> 0 rows selected
>
>
> Is there anything else going on in the INSERT transaction? Can you share the 
> table DDL with us, including indexes, constraints, auto-generated columns, 
> etc.?
>
> -Rick
>
>
> On 9/28/22 2:12 PM, Nathan Jensen wrote:
>
> Hi, we are using Derby as the backing message store for Qpid-Broker-J.  We
> have a continuous data flow into Qpid so each message gets persisted to the
> Derby message store as data comes in and then it is deleted from the Derby
> message store as data is picked up and processed.  The data flow is pretty
> constant, there are peaks and valleys but basically data is always coming
> in so there are LOTS of inserts and deletes.  Over time we found that the
> Derby message store fills up the disk drive until it runs out of space and
> in general the workaround to that has been to stop Qpid and remove the
> message store (i.e. the Derby directory), which loses data.
>
> My attempted solution to this was to extend Qpid's DerbyMessageStore with a
> DerbyRepackingMessageStore that executes 

Re: Strange sorting behaviour

[Bryan Pendleton]

There is no natural order for rows in SQL. If you care about the order
of the rows in your result, you must always specify an ORDER BY
clause.

Instead of putting the ORDER BY in the definition of the view, put the
ORDER BY on the SELECT statement from the view.

That is, don't do:

 create view v1 as select a,b,c from t1 order by a,b;

instead do:

 create view v1 as select a,b,c from t1;

 select a,b,c from v1 order by a,b;

bryan

On Wed, Apr 27, 2022 at 4:35 AM John English  wrote:
>
> I have a Java method which displays a view as an HTML table. The method
> has parameters to specify the sort column (default is the first column
> ascending), filters to select specific rows, and so on.
>
> Recently I came across an oddity which I can't figure out. Consider the
> following table and view:
>
> create table t1 (
>   a integer,
>   b integer,
>   c integer,
>   d integer
> );
> insert into t1 values
>   (1,2,3,4),
>   (1,4,5,6),
>   (2,3,4,5),
>   (1,3,4,5);
> create view v1 as
>   select a,b,c from t1;
>
> If I select from the view like this:
>
> select * from v1;
>
> this is what I get:
>
> A B C
> -
> 1 2 3
> 1 4 5
> 2 3 4
> 1 3 4
>
> This is the same as the order of insertion. However, as soon as I sort
> on column A, the sort order for column B changes:
>
> select * from v1 order by a;
>
> A B C
> -
> 1 3 4
> 1 4 5
> 1 2 3
> 2 3 4
>
> B is now shown in the reverse of the order of insertion.
>
> If I add an "order by" to the view:
>
> create view v1 as
>   select a,b,c from t1 order by a,b;
>
> I get this:
>
> select * from v1;
>
> A B C
> -
> 1 2 3
> 1 3 4
> 1 4 5
> 2 3 4
>
> select * from v1 order by a;
>
> A B C
> -
> 1 4 5
> 1 3 4
> 1 2 3
> 2 3 4
>
> Again, column B is reversed.
>
> Since my table display method automatically inserts an "order by" as in
> this example, my displayed table always ends up with the second column
> in descending order no matter what I do.
>
> Can anyone explain why this is, and what I can do to fix it so that B
> comes out sorted in the "natural" order (order of insertion, or as
> specified by "order by" in the view)?
>
> TIA,
> --
> John English
>
> --
> This email has been checked for viruses by AVG.
> https://www.avg.com
>

Re: query timeout

[Bryan Pendleton]

 postgresql while it is not while run against derby
> >>> The two jdbc drivers I used :
> >>> 
> >>> org.apache.derby
> >>> derby
> >>> 10.14.2.0
> >>> 
> >>> 
> >>> org.postgresql
> >>> postgresql
> >>> 42.3.4
> >>> 
> >>>
> >>> Am I hitting a derby/derby-jdbc limitation or am I missing some 
> >>> configuration ?
> >>> Thanks in advance for any help you can provide
> >>>
> >>> On Apr 19 2022, at 11:57 pm, Marco Ferretti  
> >>> wrote:
> >>>> Hi Brian,
> >>>> Thanks for your reply and attempt to help.
> >>>>
> >>>> Here's what I do:
> >>>> Within the database (preparation of the test)
> >>>> CALL 
> >>>> SQLJ.replace_jar('file:///home/marco/devel/stored_procedures/target/storedprocedure-1.0.jar',
> >>>>  'APP.STORED_PROCS');
> >>>>
> >>>> CREATE PROCEDURE APP.P_MUI_EXPORT_PIANIFICAZIONE (IN ID_PROMOZIONE 
> >>>> BIGINT , IN ID_COMPRATORE VARCHAR(255), IN CODICE_UTENTE VARCHAR(50) )
> >>>> PARAMETER STYLE JAVA
> >>>> MODIFIES SQL DATA
> >>>> LANGUAGE JAVA
> >>>> EXTERNAL NAME 'com.foo.Procedures.exportPianificazione';
> >>>>
> >>>> Here's the relevant parts of the (dummy) stored procedure:
> >>>> public static void exportPianificazione(long idPromozione, String 
> >>>> idCompratori, String codiceUtente)
> >>>> throws DbPromoException {
> >>>> try (Connection conn = 
> >>>> DriverManager.getConnection("jdbc:default:connection");) {
> >>>> new Utils().dummyExportPianificazione("APP", conn, idPromozione, 
> >>>> idCompratori, codiceUtente);
> >>>> } catch (Exception e) {
> >>>> log.log(Level.SEVERE, "Error writing values in mui_check_testata", e);
> >>>> throw new DbPromoException("Error writing values in mui_check_testata : 
> >>>> " + e.getMessage(), e);
> >>>> }
> >>>> }
> >>>>
> >>>> within the Utils class:
> >>>> public void dummyExportPianificazione(String schema, Connection conn, 
> >>>> long idPromozione, String idCompratori,
> >>>> String codiceUtente) throws SQLException, InterruptedException {
> >>>> String query = "insert into "+schema+".MUI_CHECK_COMPRATORI (ID, 
> >>>> ID_PROMOZIONE, ID_COMPRATORE, ESITO, CODICE_UTENTE_INSERIMENTO, 
> >>>> CODICE_UTENTE_AGGIORNAMENTO, DATA_INSERIMENTO, DATA_AGGIORNAMENTO ) 
> >>>> values ("
> >>>> + "NEXT VALUE FOR MUI_CHECK_COMPRATORI_ID_SEQ, ?, ?, ?, ?, ?, ?, ?)";
> >>>> String delete = "delete from " + schema
> >>>> + ".MUI_CHECK_COMPRATORI where id_promozione = ? and id_compratore = ?";
> >>>> String esito=getEsito(); //random generation of a result
> >>>> String[] compratori = idCompratori.split(",");
> >>>> TimeUnit.SECONDS.wait(10);
> >>>> 
> >>>> }
> >>>>
> >>>>
> >>>>
> >>>> In my application call :
> >>>> ...
> >>>> milliseconds = 1;
> >>>>
> >>>> ...
> >>>> getEm().createStoredProcedureQuery(Constants.SP_EXPORT_PIANIFICAZIONE)
> >>>> .registerStoredProcedureParameter(1, Long.class, ParameterMode.IN)
> >>>> .registerStoredProcedureParameter(2, String.class, ParameterMode.IN)
> >>>> .registerStoredProcedureParameter(3, String.class, 
> >>>> ParameterMode.IN).setParameter(1, myPromo)
> >>>> .setParameter(2, idCompratori).setParameter(3, username)
> >>>> .setHint("javax.persistence.query.timeout", milliseconds)
> >>>> .execute();
> >>>> 
> >>>>
> >>>> Activating the JPA logs (EclipseLink) to level FINEST I can see that it 
> >>>> takes 10 seconds between the call to the stored procedure and the the 
> >>>> next step of my application.
> >>>> I have also checked that the version of EclipseLink that I am using 
> >>>> supports this hint and that the default unit of the hint is millisecons 
> >>>> (but even if it was seconds, it should throw the timeout as 1<10).
> >>>>

Re: query timeout

[Bryan Pendleton]

I think that Marco is trying to *test* how his code handles a timeout
exception, but he doesn't know a way to force a timeout exception to occur
during his test.

I don't know a trivial way to do this, either.

I think you might have to write a more complex test program, for example
you could have two threads, and two transactions, and in the first thread
you could begin a transaction and update a record BUT NOT COMMIT.

Then, in the second thread, you could begin a transaction and call your
stored procedure to run a query against that record, but the query should
block because the record is updated by the first transaction in the first
thread.

Then, after some time expires, you should get the timeout exception in your
stored procedure.

thanks,

bryan


On Tue, Apr 19, 2022 at 9:30 AM Marco Ferretti 
wrote:

> Hi Rick,
>
> thanks for taking the time to reply.
> I am not 100% sure what you mean. My application uses JPA and the Entity
> Manager is getting the connection from the container's datasource: I do
> not have (direct) access to the java.sql.Statement.
> What I (would like to) do  is calling the a stored procedure in this way:
>
> getEm().createStoredProcedureQuery(Constants.SP_EXPORT_PIANIFICAZIONE)
> .registerStoredProcedureParameter(1, Long.class, ParameterMode.IN)
> .registerStoredProcedureParameter(2, String.class, ParameterMode.IN)
> .registerStoredProcedureParameter(3, String.class,
> ParameterMode.IN).setParameter(1, myPromo)
> .setParameter(2, idCompratori).setParameter(3, username)
> .setHint("javax.persistence.query.timeout", milliseconds)
> .execute();
>
> As far as the  JPA API specification (and EclipseLink implementation)
> goes, the  ".setHint("javax.persistence.query.timeout", milliseconds)" should
> be equivalent to what you suggest.
>
> The problem is that I cannot make the stored procedure call to throw a
> QueryTimeoutException even if my dummy procedure (in derby) waits for 10
> seconds and I set the timeout to 1 millisecond.
> I am using, in my test environment, Apache Tomee (java 8) , derby client
> 10.14 and a docker image with a derby network server (
> https://github.com/az82/docker-derby/blob/master/Dockerfile
> )
> .
>
> As far as my knowledge goes, and it's not that far, the reasons I am not
> getting a timeout are :
>
>1. I am not correctly using the hint
>2. I am not correctly setting up the datasource
>3. There is no timeout because the stored procedure terminates within
>the given timeout.
>
> On point 3: in my (dummy) implementation of the stored procedure i do,
> before doing anything else, a TimeUnit.SECONDS.wait(10);
> On point 1 : I am lost. I *think*  I am using the hint correctly (at
> least the API docs say so)
> On point 2: I am setting up the datasource with the minimum configuration
> possible :
>
>  JdbcDriver 
> org.apache.derby.jdbc.ClientDriver JdbcUrl 
> jdbc:derby://database:1527/dbpromo;create=false UserName DBPROMO Password 
> dbpromo 
>
> Could it be that I am failing to activate something on the Derby side ?
>
> Thanks in advance
> Marco
>
> On Apr 19 2022, at 4:57 pm, Rick Hillegas  wrote:
>
> java.sql.Statement.setQueryTimeout(int) should do the trick.
>
> On 4/19/22 3:30 AM, Marco Ferretti wrote:
> > Hi all,
> > I am trying to simulate a query timeout in a stored procedure by simply
> adding a delay in my (test) jar.
> > I then am launching the stored procedure in my java code via JPA and try
> to set a timeout hint by adding
> > .setHint("javax.persistence.query.timeout", milliseconds)
> > but I am not able to register a timeout.
> > I am wondering if there is some derby property or jdbc property I should
> use to activate such behavior. I have tried to google for it but I am
> having extremely bad results... but according to this (
> https://docs.oracle.com/cd/E25178_01/apirefs./e13952/pagehelp/J2EEkodojdbcconfdescriptorDerbyDictionaryBeantitle.html)
> I have to activate it somehow.
> >
> > Does Derby supports query timeout at all? If so, can you please point me
> to some references ?
> > Thanks in advance for any help,
> > Marco
> >
>
> [image: Sent from Mailspring]

Re: Case insensitive ORDER BY?

[Bryan Pendleton]

I think he was saying that doing "ORDER BY UPPER(x)", where x is a
column of type INT, did something strange:

> " for numberical columns I will end up sorting textually: values 1,2,10 will 
> be sorted as 1,10,2."

On Sat, Aug 21, 2021 at 8:34 AM Rick Hillegas  wrote:
>
> Some responses inline...
>
> On 8/21/21 8:03 AM, John English wrote:
> > On 20/08/2021 20:13, Rick Hillegas wrote:
> >> You could solve this problem with a custom character collation. See
> >> https://db.apache.org/derby/docs/10.15/devguide/cdevcollation.html
> >
> > Great!
> >
> >> If you don't need to sort the embedded numbers, then the simplest
> >> solution is to create a database which uses a case-insensitive sort
> >> order. See
> >> https://db.apache.org/derby/docs/10.15/devguide/tdevdvlpcollation.html
> >
> > I need to think a bit about whether I ever need case-sensitivity. I
> > suspect not, but I'll need to go through the tables, and if I can't
> > find any problems, this sounds like it might be the best solution.
> >
> > Assuming this is a viable solution, is there a way to convert a live
> > database from case-sensitive to case-insensitive (from
> > collation=TERRITORY_BASED:TERTIARY to
> > collation=TERRITORY_BASED:PRIMARY, if I understand correctly), which I
> > assume will involve rebuilding all the indexes?
> Unfortunately, you have to create a new database and copy your old data
> into the new database. I would recommend creating a fresh database which
> has the correct, case-insensitive collation. Then copy the old data into
> the new database using the foreign views optional tool. See
> https://db.apache.org/derby/docs/10.15/tools/rtoolsoptforeignviews.html
> >
> >> If you need to sort the embedded numbers too, then you have to supply
> >> a custom collator. See
> >> https://db.apache.org/derby/docs/10.15/devguide/tdevdvlpcustomcollation.html
> >
> > OK, this bit I didn't understand. Sometimes I want to sort on columns
> > of numbers, sometimes dates, sometimes strings. Is that what you mean
> > by needing to "sort the embedded numbers"?
> Or I don't understand your problem. I thought that you needed a string
> like abc2def to sort before abc10def. Sort order should be correct for
> numeric and date/time datatypes. It's just the character typed data
> which sorts incorrectly.
> >
> >> It's hard to imagine that you are the first person who needs the sort
> >> order you have described. Maybe a little googling will discover that
> >> someone has open-sourced a collator which does the right thing. If
> >> you can't find one but you end up writing your own, please consider
> >> open-sourcing it.
> >
> > OK, will do.
> >
> > Many thanks,
>
>

Re: How to determine version of a derby database

[Bryan Pendleton]

Great! Thanks for the follow-up and the example code, very helpful.

On Thu, Feb 18, 2021 at 1:01 PM Lisa Ruby  wrote:
>
> Thank you again Bryan. I just got it working. From what I read about the 
> versioning, it would seem that being able to obtain only the major and minor 
> versions returned from 
> SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('DataDictionaryVersion') should be 
> enough to know if a database upgrade is needed. I'm assuming that the point 
> and fixpack versions would not contain changes extensive enough to require an 
> upgrade.
>
> In case anyone else is reading this and wants to know, here is Java code that 
> works for using 
> SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('DataDictionaryVersion').
> This obviously has no error or Exception handling.
> It prints out the string "ddVersion = 10.11" for me on a database I haven't 
> yet upgraded to 10.15.
>
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.Statement;
> import java.sql.ResultSet;
>
>
> String connectionURL = "jdbc:derby:dbname";
> Connection conn = DriverManager.getConnection(dbConnectionURL)
> String ddVersionQueryStr = "values syscs_util.syscs_get_database_property( 
> 'DataDictionaryVersion' )";
> Statement stmt = conn.createStatement();
> boolean isResultSet = stmt.execute(ddVersionQueryStr);
> ResultSet resultSet = stmt.getResultSet();
> resultSet.next();
> System.out.print("ddVersion = " + resultSet.getString(1) + "\n");
>
> Lisa
>
>
> On 2/18/2021 10:29 AM, Lisa Ruby wrote:
>
> I had not seen this Bryan. Thank you for pointing it out. Seems like it
> would provide the information I'm looking for. I'll look into it.
>
> Lisa
>
> On 2/18/2021 6:48 AM, Bryan Pendleton wrote:
>
> Have you tried this?
> https://db.apache.org/derby/docs/10.15/ref/rrefproperdatadictversion.html
>
> thanks,
>
> bryan
>
> On Wed, Feb 17, 2021 at 4:23 PM Lisa Ruby  wrote:
>
> Probably should have mentioned that I'm using derby version 10.15.2.0
> and AdoptOpenJDK Java 11.0.9.11-hotspot
>
> Lisa
>
> On 2/17/2021 4:08 PM, Lisa Ruby wrote:
>
> Hi,
>
> I am using derby in the embedded mode for a Java Windows desktop
> application. I am trying to find a way that I can determine what version
> my database is. When I use the Java Connection class
> getMetaData().getDatabaseProductVersion() call it always returns the
> version of the derby driver I am running, rather than the version of the
> actual database. Or else it's returning the version of the database
> after a soft upgrade, but not the underlying database version.  Same for
> getMetaData().getDatabaseMajorVersion() and
> getMetaData().getDatabaseMinorVersion(). The reason I want to know the
> version of the database is so I can determine if I need/want to do a
> hard upgrade on the database. Is there any way for me to get the version
> of the database from inside a Java program?
>
> Thank you.
>
> Lisa
>
>
>

Re: How to determine version of a derby database

[Bryan Pendleton]

Have you tried this?
https://db.apache.org/derby/docs/10.15/ref/rrefproperdatadictversion.html

thanks,

bryan

On Wed, Feb 17, 2021 at 4:23 PM Lisa Ruby  wrote:
>
> Probably should have mentioned that I'm using derby version 10.15.2.0
> and AdoptOpenJDK Java 11.0.9.11-hotspot
>
> Lisa
>
> On 2/17/2021 4:08 PM, Lisa Ruby wrote:
> > Hi,
> >
> > I am using derby in the embedded mode for a Java Windows desktop
> > application. I am trying to find a way that I can determine what version
> > my database is. When I use the Java Connection class
> > getMetaData().getDatabaseProductVersion() call it always returns the
> > version of the derby driver I am running, rather than the version of the
> > actual database. Or else it's returning the version of the database
> > after a soft upgrade, but not the underlying database version.  Same for
> > getMetaData().getDatabaseMajorVersion() and
> > getMetaData().getDatabaseMinorVersion(). The reason I want to know the
> > version of the database is so I can determine if I need/want to do a
> > hard upgrade on the database. Is there any way for me to get the version
> > of the database from inside a Java program?
> >
> > Thank you.
> >
> > Lisa
> >
>
>

Re: Out-of-memory errors

[Bryan Pendleton]

I think Rick was suggesting that if you got the right query plan,
using your index as efficiently as possible, then that would be the
best solution? You can find a lot of information about this in
http://db.apache.org/derby/docs/10.15/tuning/

Perhaps your statistics are inaccurate, so Derby is not choosing the
index-based plan? I think there are ways to force Derby to choose that
plan, and there are also ways to ensure that your statistics are
accurate.

Specifically, see:
http://db.apache.org/derby/docs/10.15/tuning/ctunperfstatistics.html

bryan

On Tue, Feb 9, 2021 at 4:04 AM John English  wrote:
>
> On 08/02/2021 18:43, Rick Hillegas wrote:
> > I would recommend throwing more memory at your JVM or adding a
> > descending index to system_log.time.
>
> So, there is already a descending index to system_log.time, and I've
> upped the heap to 2GB. I also added the time taken to service the
> request as an HTML comment at the end of the page.
>
> The disk is an SSD, and the table contains about 261,000 rows. Accessing
> the first 20 rows ordered by time desc, it takes about 13 seconds, which
> seems a bit excessive to me. Ordering by event also takes about the same
> (12690ms as opposed to 13151ms), so the index doesn't seem to have much
> effect.
>
> Not sure what to try next... any ideas?
>
> --
> John English

Re: Derby driver woes in Squirrel

[Bryan Pendleton]

Interesting. Can you let the Squirrel community know about this?

I wonder if this is related to the switch from Java 8 to Java 9 across
the Derby 10.14 to 10.15 boundary?

Can you tell what version Squirrel was pre-packaging, and what version
you used instead?

thanks,

bryan

On Mon, May 18, 2020 at 6:17 AM John English  wrote:
>
> On 18/05/2020 16:10, John English wrote:
> > I'm now at the sacrificing chickens stage. Does anyone have any clue
> > what is going wrong here?
>
> Aha. The derby.jar that comes in the Squirrel distro is a dud. Replacing
> it with a known-good copy saved a chicken's life. Apologies for wasting
> your bandwidth.
>
> --
> John English

Re: Derby newbie - error seen creating 'seconddb' thru ij for network-server Derby

[Bryan Pendleton]

If you're just learning Derby, you don't need to use the Network
Server and Network Client.

Try following the "embedded" path in
http://db.apache.org/derby/docs/10.15/getstart/twwdactivity1_overview.html

It's a lot simpler.

Then when you're more comfortable with Derby you can move up to the
Network Server and its additional administration issues.

bryan

On Thu, Nov 7, 2019 at 11:29 PM Banibrata Dutta
 wrote:
>
> Hi Folk,
>
> Trying to use Derby for the first time. I am not an expert Java developer but 
> dabbling for learning me some. I followed the official Derby instructions for 
> installing the latest Derby on my freshly minted Lubuntu (lighter Ubuntu 
> using LXDE) 64-bit 18.04.3 setup, with OpenJDK11 (default).
>
> Have started Derby in network server mode like this:
> $ java -jar $DERBY_HOME/lib/derbyrun.jar server start
>
> And then trying to using ij to create the database like this:
> $ ij
> ij version 10.15
> ij> CONNECT 'jdbc:derby://localhost:1527/seconddb;create=true';
> ERROR XJ041: DERBY SQL error: ERRORCODE: 4, SQLSTATE: XJ041, SQLERRMC: 
> Failed to create database 'seconddb', see the next exception for 
> details.::SQLSTATE: XBM01::SQLSTATE: XJ001
> ij>
>
> Trying to read around, there are suggestions to look into derby.log file, but 
> I found none so far. What gives ? How to proceed ? Didn't find any general 
> troubleshooting instructions in the docs. Tried the FAQ, but no obvious clues.
>
> cheers,
> B
>

Re: Is the Wiki available

[Bryan Pendleton]

Unfortunately, the wiki has moved to confluence, and the process
wasn't all that clean, and that auto-redirect system isn't pointing to
the right place.

The right place, I think, is
https://cwiki.apache.org/confluence/display/DERBY/FrontPage

I'm not sure how to fix that auto-redirect system, so we need to fix
the bad references I think.

But we should at least start finding the places where we have bad
links to the old wiki and try to replace them with links to the new
wiki.

I'm not sure which page you were remembering, unfortunately.

Was it by any chance
https://cwiki.apache.org/confluence/display/DERBY/LanguageOptimize

or maybe https://cwiki.apache.org/confluence/display/DERBY/PermutationCosting

bryan

On Mon, Nov 4, 2019 at 5:32 PM Brett Bergquist
 wrote:
>
> I tried to use the link on the Community page
>
> http://wiki.apache.org/db-derby
>
> And it tells me the page is
>
> https://cwiki.apache.org/confluence/display/db-derby
>
> And it is a Page Not Found
>
>
> There was a page on there about checking the cardinality statistics for all 
> of the tables that I was interested in.
>

Re: Multiple Derby embedded engine instances - problem

[Bryan Pendleton]

Yes, but not PATH, CLASSPATH. They are different environment variables.

On Fri, Jul 12, 2019 at 9:06 PM Bob M  wrote:
>
> Bryan
>
> Environment Variables
> System Variables
> variable = Path
> value = %DERBY_INSTALL%\lib\derby.jar
>
> Is that what you are meaning?
>
> Bob M
>
>
>
> --
> Sent from: 
> http://apache-database.10148.n7.nabble.com/Apache-Derby-Users-f95095.html

Re: Multiple Derby embedded engine instances - problem

[Bryan Pendleton]

The EmbeddedDriver is located in the derby.jar jar file, which MUST be
on the CLASSPATH of your program.

bryan

On Fri, Jul 12, 2019 at 3:52 PM Bob M  wrote:
>
> Hi Rick
>
> Ignore the above message... I see I had two 'on the hour' attempts before
> the market closes for the weekend
>
> On the first of these hours (after I added the 2 new properties)
>
> In the logfile I use, I got the following message for each of the 5
> different programs running simultaneously
>
> Unable to load the JDBC driver org.apache.derby.jdbc.Embedded Driver
> Please check your ClASSPATH
>
> and the derby log file was as shown abve
>
> On the second hour
>
> my log file showed only the first line and no derby log file can be found
>
> Bob M
>
>
>
> --
> Sent from: 
> http://apache-database.10148.n7.nabble.com/Apache-Derby-Users-f95095.html

Re: AccessControlException with no security manager

[Bryan Pendleton]

Probably best to open an issue in the Derby JIRA, to help keep the
conversation easy to find and track.

Can you share the complete stack trace of the AccessControlException?

Can you share a run of 'sysinfo' in a configuration where this occurs?

I don't suppose you've been able to whittle this down to a Minimum
Reproducible Case yet, have you?

thanks,

bryan

On Thu, Jun 6, 2019 at 6:22 AM Besosa, Michael
 wrote:
>
> We're still interested in ideas about this problem. We are getting occasional 
> AccessControlExceptions in an environment with no SecurityManager installed. 
> This behavior appears to have started when we migrated our application to 
> Java 8. It was never seen prior to that. If there is additional information 
> that would be useful, please let me know.
>
> On Mon, Jun 3, 2019 at 8:17 PM Bryan Pendleton  
> wrote:
>>
>> Ah, good point.
>>
>> Yes, I missed that, and yes I was thinking about the network server.
>>
>> Sorry about that.
>>
>> bryan
>>
>> On Mon, Jun 3, 2019 at 8:37 AM Besosa, Michael
>>  wrote:
>> >
>> > Thinking about it a bit more, I wonder if you missed that this is using 
>> > the embedded engine, not the network server. I know that the network 
>> > server installs a security manager by default, and that there is a system 
>> > property that can be set to disable that behavior. But that's not 
>> > applicable in our situation.
>> >
>> > On Mon, Jun 3, 2019 at 8:52 AM Besosa, Michael 
>> >  wrote:
>> >>
>> >> I don't understand what you mean when you say, "Even if you don't install 
>> >> a Java security manager...you get a security manager anyway." I don't 
>> >> have (haven't installed) a security manager and 
>> >> System.getSecurityManager() returns null. And if I "really, really" don't 
>> >> want a security manager, how can I say so, other than executing 
>> >> System.setSecurityManager(null)?
>> >>
>> >> On Fri, May 31, 2019 at 9:03 PM Bryan Pendleton 
>> >>  wrote:
>> >>>
>> >>> I feel like you're probably hitting DERBY-6648:
>> >>> https://urldefense.proofpoint.com/v2/url?u=https-3A__issues.apache.org_jira_browse_DERBY-2D6648=DwIFaQ=0YLnzTkWOdJlub_y7qAx8Q=Wl0HX9k4fXSgP8TsHwfE2EzboRhWUZIw3D92MjZ0k8A=jAZ8M-UCTv0Ms-FrB1NaYA62mIGrbUhHirW9AxhEyxw=Hutrm_MTSwNDtab2E2bMNCxLo0eDRIZrB1B5x2AL-1k=
>> >>>
>> >>> Even if you don't install a Java security manager (perhaps especially
>> >>> if that is so), you get a security manager anyway.
>> >>>
>> >>> If you really, really don't want a security manager, you can say so.
>> >>>
>> >>> Or, bite the bullet and define a security manager, and start
>> >>> assembling the precise security policy that is right for your
>> >>> particular situation, as in:
>> >>> https://urldefense.proofpoint.com/v2/url?u=https-3A__stackoverflow.com_a_52392015_193453=DwIFaQ=0YLnzTkWOdJlub_y7qAx8Q=Wl0HX9k4fXSgP8TsHwfE2EzboRhWUZIw3D92MjZ0k8A=jAZ8M-UCTv0Ms-FrB1NaYA62mIGrbUhHirW9AxhEyxw=uackNzNN36boV8-m6DGN7VJOPwN4tnfuUwgQBgOmv0I=
>> >>>   ,
>> >>> https://urldefense.proofpoint.com/v2/url?u=https-3A__db.apache.org_derby_docs_10.13_security_csecembeddedperms.html=DwIFaQ=0YLnzTkWOdJlub_y7qAx8Q=Wl0HX9k4fXSgP8TsHwfE2EzboRhWUZIw3D92MjZ0k8A=jAZ8M-UCTv0Ms-FrB1NaYA62mIGrbUhHirW9AxhEyxw=53JhmaGEsK92jA_W0ofwQrvGLuaZ-TLXOiDawh9P3rQ=
>> >>> , etc.
>> >>>
>> >>> bryan
>> >>>
>> >>> On Fri, May 31, 2019 at 11:58 AM Besosa, Michael
>> >>>  wrote:
>> >>> >
>> >>> > We have an application that is using the Derby 10.12.1.1 embedded 
>> >>> > engine and Java 8. When the app executes the SELECT shown below, we 
>> >>> > see an exception with the message 'access denied 
>> >>> > org.apache.derby.security.SystemPermission( "engine", 
>> >>> > "usederbyinternals" ): java.security.AccessControlException'.
>> >>> >
>> >>> > This application does not install a SecurityManager. I'm at a bit of a 
>> >>> > loss about how to fix this.
>> >>> >
>> >>> > The query:
>> >>> >
>> >>> > SELECT
>> >>> > DeliveryEvent.AppointmentStart,
>> >>> > DeliveryEvent.Attempt,
>> >>> > DeliveryEvent.BiometricCheckTypeCode,
>> >>> >

Re: AccessControlException with no security manager

[Bryan Pendleton]

Ah, good point.

Yes, I missed that, and yes I was thinking about the network server.

Sorry about that.

bryan

On Mon, Jun 3, 2019 at 8:37 AM Besosa, Michael
 wrote:
>
> Thinking about it a bit more, I wonder if you missed that this is using the 
> embedded engine, not the network server. I know that the network server 
> installs a security manager by default, and that there is a system property 
> that can be set to disable that behavior. But that's not applicable in our 
> situation.
>
> On Mon, Jun 3, 2019 at 8:52 AM Besosa, Michael  
> wrote:
>>
>> I don't understand what you mean when you say, "Even if you don't install a 
>> Java security manager...you get a security manager anyway." I don't have 
>> (haven't installed) a security manager and System.getSecurityManager() 
>> returns null. And if I "really, really" don't want a security manager, how 
>> can I say so, other than executing System.setSecurityManager(null)?
>>
>> On Fri, May 31, 2019 at 9:03 PM Bryan Pendleton  
>> wrote:
>>>
>>> I feel like you're probably hitting DERBY-6648:
>>> https://urldefense.proofpoint.com/v2/url?u=https-3A__issues.apache.org_jira_browse_DERBY-2D6648=DwIFaQ=0YLnzTkWOdJlub_y7qAx8Q=Wl0HX9k4fXSgP8TsHwfE2EzboRhWUZIw3D92MjZ0k8A=jAZ8M-UCTv0Ms-FrB1NaYA62mIGrbUhHirW9AxhEyxw=Hutrm_MTSwNDtab2E2bMNCxLo0eDRIZrB1B5x2AL-1k=
>>>
>>> Even if you don't install a Java security manager (perhaps especially
>>> if that is so), you get a security manager anyway.
>>>
>>> If you really, really don't want a security manager, you can say so.
>>>
>>> Or, bite the bullet and define a security manager, and start
>>> assembling the precise security policy that is right for your
>>> particular situation, as in:
>>> https://urldefense.proofpoint.com/v2/url?u=https-3A__stackoverflow.com_a_52392015_193453=DwIFaQ=0YLnzTkWOdJlub_y7qAx8Q=Wl0HX9k4fXSgP8TsHwfE2EzboRhWUZIw3D92MjZ0k8A=jAZ8M-UCTv0Ms-FrB1NaYA62mIGrbUhHirW9AxhEyxw=uackNzNN36boV8-m6DGN7VJOPwN4tnfuUwgQBgOmv0I=
>>>   ,
>>> https://urldefense.proofpoint.com/v2/url?u=https-3A__db.apache.org_derby_docs_10.13_security_csecembeddedperms.html=DwIFaQ=0YLnzTkWOdJlub_y7qAx8Q=Wl0HX9k4fXSgP8TsHwfE2EzboRhWUZIw3D92MjZ0k8A=jAZ8M-UCTv0Ms-FrB1NaYA62mIGrbUhHirW9AxhEyxw=53JhmaGEsK92jA_W0ofwQrvGLuaZ-TLXOiDawh9P3rQ=
>>> , etc.
>>>
>>> bryan
>>>
>>> On Fri, May 31, 2019 at 11:58 AM Besosa, Michael
>>>  wrote:
>>> >
>>> > We have an application that is using the Derby 10.12.1.1 embedded engine 
>>> > and Java 8. When the app executes the SELECT shown below, we see an 
>>> > exception with the message 'access denied 
>>> > org.apache.derby.security.SystemPermission( "engine", "usederbyinternals" 
>>> > ): java.security.AccessControlException'.
>>> >
>>> > This application does not install a SecurityManager. I'm at a bit of a 
>>> > loss about how to fix this.
>>> >
>>> > The query:
>>> >
>>> > SELECT
>>> > DeliveryEvent.AppointmentStart,
>>> > DeliveryEvent.Attempt,
>>> > DeliveryEvent.BiometricCheckTypeCode,
>>> > DeliveryEvent.CandidateID,
>>> > DeliveryEvent.DeliveryStatus,
>>> > DeliveryEvent.ExamLanguageID,
>>> > DeliveryEvent.HasNoTestListBlock,
>>> > DeliveryEvent.IsDisconnectedIBTDelivery,
>>> > DeliveryEvent.NextAppointmentID,
>>> > DeliveryEvent.ProcessStateCode,
>>> > DeliveryEvent.ProctorID,
>>> > DeliveryEvent.RegisteredExamVersionID,
>>> > DeliveryEvent.RegistrationID,
>>> > DeliveryEvent.SelectedFormID,
>>> > DeliveryEvent.SelectedExamVersionID,
>>> > DeliveryEvent.TerminationDate,
>>> > DeliveryEvent.TimeLimit,
>>> > DeliveryEvent.TimeLimitAdjust,
>>> > Clients.Client_ID AS ClientID,
>>> > Clients.ClientName AS ClientName,
>>> > ExamSeries.Code AS ExamSeriesCode,
>>> > ExamVersion.DriverID AS DriverID,
>>> > ExamLanguage.Title AS TranslatedTitle,
>>> > (SELECT AppUser.FirstName || ' ' || AppUser.LastName FROM 
>>> > ((AssetAppUser INNER JOIN AppUser ON AssetAppUser.AppUserID = 
>>> > AppUser.ID) INNER JOIN AssetUsage ON AssetAppUser.AssetID = 
>>> > AssetUsage.AssetID) INNER JOIN Asset ON AssetU

Re: AccessControlException with no security manager

[Bryan Pendleton]

I feel like you're probably hitting DERBY-6648:
https://issues.apache.org/jira/browse/DERBY-6648

Even if you don't install a Java security manager (perhaps especially
if that is so), you get a security manager anyway.

If you really, really don't want a security manager, you can say so.

Or, bite the bullet and define a security manager, and start
assembling the precise security policy that is right for your
particular situation, as in:
https://stackoverflow.com/a/52392015/193453 ,
https://db.apache.org/derby/docs/10.13/security/csecembeddedperms.html
, etc.

bryan

On Fri, May 31, 2019 at 11:58 AM Besosa, Michael
 wrote:
>
> We have an application that is using the Derby 10.12.1.1 embedded engine and 
> Java 8. When the app executes the SELECT shown below, we see an exception 
> with the message 'access denied org.apache.derby.security.SystemPermission( 
> "engine", "usederbyinternals" ): java.security.AccessControlException'.
>
> This application does not install a SecurityManager. I'm at a bit of a loss 
> about how to fix this.
>
> The query:
>
> SELECT
> DeliveryEvent.AppointmentStart,
> DeliveryEvent.Attempt,
> DeliveryEvent.BiometricCheckTypeCode,
> DeliveryEvent.CandidateID,
> DeliveryEvent.DeliveryStatus,
> DeliveryEvent.ExamLanguageID,
> DeliveryEvent.HasNoTestListBlock,
> DeliveryEvent.IsDisconnectedIBTDelivery,
> DeliveryEvent.NextAppointmentID,
> DeliveryEvent.ProcessStateCode,
> DeliveryEvent.ProctorID,
> DeliveryEvent.RegisteredExamVersionID,
> DeliveryEvent.RegistrationID,
> DeliveryEvent.SelectedFormID,
> DeliveryEvent.SelectedExamVersionID,
> DeliveryEvent.TerminationDate,
> DeliveryEvent.TimeLimit,
> DeliveryEvent.TimeLimitAdjust,
> Clients.Client_ID AS ClientID,
> Clients.ClientName AS ClientName,
> ExamSeries.Code AS ExamSeriesCode,
> ExamVersion.DriverID AS DriverID,
> ExamLanguage.Title AS TranslatedTitle,
> (SELECT AppUser.FirstName || ' ' || AppUser.LastName FROM 
> ((AssetAppUser INNER JOIN AppUser ON AssetAppUser.AppUserID = AppUser.ID) 
> INNER JOIN AssetUsage ON AssetAppUser.AssetID = AssetUsage.AssetID) INNER 
> JOIN Asset ON AssetUsage.AssetID = Asset.ID) INNER JOIN AssetType ON 
> Asset.AssetTypeID = AssetType.ID) INNER JOIN AssetTypeProperty ON 
> AssetType.ID = AssetTypeProperty.AssetTypeID AND 
> AssetTypeProperty.AssetTypePropertyValue = 'Examiner') INNER JOIN 
> AssetTypePropertyKey ON AssetTypeProperty.AssetTypePropertyKeyID = 
> AssetTypePropertyKey.AssetTypePropertyKeyID AND 
> AssetTypePropertyKey.AssetTypePropertyKeyName = 'AssetSubClass') WHERE 
> (AssetUsage.OrderItemID = DeliveryEvent.RegistrationID) FETCH FIRST 1 ROWS 
> ONLY) AS ExaminerName,
> (SELECT Asset.Name FROM Asset INNER JOIN AssetType ON 
> Asset.AssetTypeID = AssetType.ID) INNER JOIN AssetUsage ON Asset.ID = 
> AssetUsage.AssetID) INNER JOIN AssetTypeProperty ON AssetType.ID = 
> AssetTypeProperty.AssetTypeID AND AssetTypeProperty.AssetTypePropertyValue = 
> 'Vehicle') INNER JOIN AssetTypePropertyKey ON 
> AssetTypeProperty.AssetTypePropertyKeyID = 
> AssetTypePropertyKey.AssetTypePropertyKeyID AND 
> AssetTypePropertyKey.AssetTypePropertyKeyName = 'AssetSubClass') WHERE 
> (AssetUsage.OrderItemID = DeliveryEvent.RegistrationID) AND 
> (AssetType.AssetClassCode IN 
> ('Facility','Workstation','CiscoRack','TaskSchedule')) FETCH FIRST 1 ROWS 
> ONLY) AS VehicleName,
> (SELECT COUNT(*) FROM (Accommodation INNER JOIN AccommodationStatus 
> ON Accommodation.AccommodationStatusID = 
> AccommodationStatus.AccommodationStatusID AND AccommodationStatus.StatusCode 
> = 'Granted') WHERE (Accommodation.OrderItemID = 
> DeliveryEvent.RegistrationID)) AS AccommodationCount,
> (SELECT COUNT(*) FROM DeliveryBlockedReason WHERE 
> (DeliveryBlockedReason.DeliveryEventID = DeliveryEvent.RegistrationID)) AS 
> ReasonsBlockedCount,
> (SELECT DeliveryEventNext.RegistrationID FROM DeliveryEvent AS 
> DeliveryEventNext WHERE (DeliveryEvent.RegistrationID = 
> DeliveryEventNext.NextAppointmentID)) AS FirstDayRegistrationID
> FROM 
> DeliveryEvent
> INNER JOIN ExamLanguage
> ON DeliveryEvent.ExamLanguageID = ExamLanguage.ID)
> INNER JOIN ExamSeriesON ExamLanguage.ExamSeriesID = ExamSeries.ID)
> INNER JOIN Clients
> ON ExamSeries.Client_ID = Clients.Client_ID)
> LEFT JOIN ExamVersion
> ON DeliveryEvent.RegisteredExamVersionID = ExamVersion.ID)
> WHERE
> (DeliveryEvent.RegistrationID = 5)

Re: Derby wiki will be migrating to new URLs this spring

[Bryan Pendleton]

Reminder: I'm working on converting the db-derby moin wiki to the new
Derby confluence space.

Please don't make any changes to either space right now; I will send
out a message when I've successfully migrated the wiki.

For now, I'm having a little trouble with the tools: INFRA-18081 if
you want to follow along.

thanks,

bryan

On Tue, Mar 5, 2019 at 7:35 PM Bryan Pendleton
 wrote:
>
> Hi all,
>
> Quoting from an internal Apache email:
>
>  "As you may be aware moin wiki service has been deprecated for
> some time now - we have not been accepting new moin wikis. "
>
> "Infra has decided to close down the service, and so all projects
> that still have a moin wiki are being asked to migrate away to
> Confluence. We have set a deadline of May 31st, 3 months from now"
>
> The Derby wiki (https://wiki.apache.org/db-derby/) is a MoinMoin wiki.
>
> The purpose of this mail is:
> 1) To let people know that we're going to need to migrate the Derby wiki.
> 2) To find out if anyone can help with this task: there is a migration
> tool that will hopefully do most of the mechanical steps for us, but
> then we need some folks to sign on to the new wiki, make sure that the
> conversion has gone successfully, fix problems that are present after
> the conversion, help us find broken links that need to be changed,
> etc.
> 3) To propose that I will make a first attempt at using the wiki
> conversion tool over the weekend of Mar 22nd, 2019.
>
> Please let us know any feedback or ideas.
>
> thanks,
>
> bryan

Derby wiki will be migrating to new URLs this spring

[Bryan Pendleton]

Hi all,

Quoting from an internal Apache email:

 "As you may be aware moin wiki service has been deprecated for
some time now - we have not been accepting new moin wikis. "

"Infra has decided to close down the service, and so all projects
that still have a moin wiki are being asked to migrate away to
Confluence. We have set a deadline of May 31st, 3 months from now"

The Derby wiki (https://wiki.apache.org/db-derby/) is a MoinMoin wiki.

The purpose of this mail is:
1) To let people know that we're going to need to migrate the Derby wiki.
2) To find out if anyone can help with this task: there is a migration
tool that will hopefully do most of the mechanical steps for us, but
then we need some folks to sign on to the new wiki, make sure that the
conversion has gone successfully, fix problems that are present after
the conversion, help us find broken links that need to be changed,
etc.
3) To propose that I will make a first attempt at using the wiki
conversion tool over the weekend of Mar 22nd, 2019.

Please let us know any feedback or ideas.

thanks,

bryan

Re: selecting a particular record from a derby table

[Bryan Pendleton]

Change that first line to use a PreparedStatement, rather than a
Statement, so it becomes:

PreparedStatement ps = conn.prepareStatement("select * from prototypes
where prototype_id=?");
ps.setInt(1, Candidate_ID);
rs = ps.executeQuery();
On Wed, Dec 12, 2018 at 7:18 PM Bob M  wrote:
>
> I have a table with a first column as ID (integer) and this column is the
> primary key
>
> I do some calculations to decide which record I wish to retrieve
> and end up with the following integer value variable called Candidate_ID
>
> The table also has a column called Occurances (integer) which I wish to
> update by adding 1
> ***
> rs = s.executeQuery("SELECT * FROM PROTOTYPES WHERE Prototype_ID =
> Candidate_ID");
> rs.next();
> s.setCursorName("MYCURSOR");
> rs = s.executeQuery("SELECT Occurances FROM PROTOTYPES WHERE Prototype_ID =
> Candidate_ID_to_be_validated FOR UPDATE");
> rs.next();
> psUpdate = conn.prepareStatement("UPDATE PROTOTYPES SET Occurances = ? WHERE
> CURRENT OF MYCURSOR");
> statements.add(psUpdate);
> psUpdate.setInt(1, Occurances + 1);
> psUpdate.executeUpdate();
> 
> I am getting an error on the first line of code
> SQL State: 42X04
> Error Code: 2
> Message: Column 'Candidate_ID' is either not in any table or
>
> What should I change in the first line ?
>
> Bob M
>
>
>
>
> --
> Sent from: 
> http://apache-database.10148.n7.nabble.com/Apache-Derby-Users-f95095.html

Re: deleting records from a table

[Bryan Pendleton]

Can you delete the records using ij?

bryan

On Mon, Dec 10, 2018 at 5:38 PM Bob M  wrote:
>
> Hi
> I have run my java program and it has added a couple of records to a table
>
> when I try to delete those 2 records from within Netbeans I get the
> following message:-
>
> Error code 2, SQL state 25502
>
> Delete command failed: An SQL data change is not permitted for a read-only
> connection, user or database
>
> What do I need to do to be able to alter data in a table ?
>
> Bob M
>
>
>
> --
> Sent from: 
> http://apache-database.10148.n7.nabble.com/Apache-Derby-Users-f95095.html

Re: Counting a subset of records

[Bryan Pendleton]

If Search_ID is a value, then it either has to be a literal value
enclosed in quotation marks, or a variable value which is substituted
into the statement using a PreparedStatement and a setString() call.

Remember to avoid constructing variable values in SQL statements by
string concatenation, for you are vulnerable to SQL Injection that
way.

bryan

On Tue, Dec 4, 2018 at 6:01 PM Bob M  wrote:
>
> I have a table with a particular column named ID
>
> The records in the table have an integer in the field and there may be
> several records with the same integer value
>
> I am using the following code where Search_ID has just been
> calculated...
>
> ps = conn.prepareStatement("SELECT COUNT(*) from table_name where ID =
> Search_ID");
>
> The program falters on this line and I am unclear why ?
>
> Bob M
>
>
>
> --
> Sent from: 
> http://apache-database.10148.n7.nabble.com/Apache-Derby-Users-f95095.html

Re: Force TLSv1.2 or higher for the server

[Bryan Pendleton]

There was a similar, but not identical, discussion around these topics
four years ago, when the code was changed to remove SSLv3 and SSLv2
support. See DERBY-6764 for the full details.

I think it would certainly be possible to change the code in a similar way
to allow more configurability, but I am not sure of the implications, and if
it is similar to the DERBY-6764 work, a fair amount of testing is required.

According to this article:
https://blogs.oracle.com/java-platform-group/jdk-8-will-use-tls-12-as-default
you might investigate using the deployment.security.TLSvX.Y=false
system property.

Perhaps you could investigate whether the referenced blog article
allows a configuration that suits your needs?

Please let us know what you learn!

thanks,

bryan


On Mon, Jul 9, 2018 at 3:25 AM, Peter  wrote:
> Hello,
>
> I cannot find a way to force the server to just use TLSv1.2. Currently
> it says:
>
> Apache Derby Network Server - 10.13.1.1 - (1765088) Enabled Protocols
> are TLSv1, TLSv1.1, TLSv1.2
>
> even when using
>
> -Dhttps.protocols=TLSv1.2
>
> or similar settings found on the internet. Then I saw in the source:
>
> SSLContext ctx = SSLContext.getInstance("TLS");
>
> https://github.com/apache/derby/blob/f16c46cbdd5be8dd9bdcee935ec1f68970146478/java/org.apache.derby.commons/org/apache/derby/shared/common/drda/NaiveTrustManager.java#L73
>
> that it seems to ignore command line settings. Is it possible to add
> such a property or a different workaround to avoid older TLS versions?
>
> Regards
> Peter
>

Fwd: Got a security exception calling SQLJ.INSTALL_JAR with Derby 10.14

[Bryan Pendleton]

Yes, this is a known result of  DERBY-6987. The default security
policy file for Derby no longer allows unlimited access to your
computer's local filesystem.

Please see these resource for how to adjust your security settings to
explicitly authorize loading your jar into Derby:

http://db.apache.org/derby/releases/release-10.14.2.0.cgi

https://db.apache.org/derby/docs/10.14/security/csecjavasecurity.html

In particular, see the section on "Backups/imports/jars".

thanks,

bryan



-- Forwarded message --
From:  
Date: Sun, Jul 8, 2018 at 11:22 PM
Subject: Got a security exception calling SQLJ.INSTALL_JAR with Derby 10.14
To: bpendleton.de...@gmail.com


Hi Bryan,

I got following exception with derby 10.14. This exception does not
occur with derby 10.13 but does occur now that I have upgrade to
10.14.2.0.

[sql] Failed to execute:  CALL SQLJ.INSTALL_JAR('./demo.jar',
'CSEM.csemderby', 0)
[sql] java.sql.SQLTransactionRollbackException: The exception
'java.security.AccessControlException: access denied
("java.io.FilePermission" "./demo.jar" "read")' was thrown while
evaluating an expression.

I got the following info in derby.log file.

Wed Jul 04 14:25:03 CST 2018 : Security manager installed using the
Basic server security policy.
Wed Jul 04 14:25:03 CST 2018 : Apache Derby Network Server - 10.14.2.0
- (1828579) started and ready to accept connections on port 1527

The application that is starting the JVM that is running Derby Network
Server has not changed. Only the version of Derby has changed.
So could you please give me any advice to solve the issue ？

Any pointers will be greatly appreciated.


Thanks.
Yan


_
Sent from http://apache-database.10148.n7.nabble.com

Re: Question on why I am getting a security exception calling SQLJ.INSTALL_JAR with Derby 10.14.2.0

[Bryan Pendleton]

Hi Brett,

I'm not sure what's going on with the mirrors. The cgi page is working
properly for me now, and it contains the release notes on that page.

http://db.apache.org/derby/releases/release-10.14.2.0.cgi

When you finally got the the right page, did it have the release notes on it?

Meanwhile, w.r.t. the permissions question, I think you are right
that DERBY-6987 is the source of your problems.

Can you change your startup procedures so that you specify a
security policy with the slightly looser permission that your application
requires?

Here's the docs on how to provide your desired security policy:
https://db.apache.org/derby/docs/10.14/security/csecjavasecurity.html

thanks,

bryan


On Mon, Jun 4, 2018 at 8:18 AM, Bergquist, Brett  wrote:
> I am getting this error when trying to install a JAR
>
>
>
>   [sql] Failed to execute:  CALL
> SQLJ.INSTALL_JAR('/opt/canogaview/temp/buil
>
> d/core_pkg/META-INF/customize/csemdb/csem-derby.jar', 'CSEM.csemderby', 0)
>
>   [sql] java.sql.SQLTransactionRollbackException: The exception
> 'java.securi
>
> ty.AccessControlException: access denied ("java.io.FilePermission"
> "/opt/canogav
>
> iew/temp/build/core_pkg/META-INF/customize/csemdb/csem-derby.jar" "read")'
> was t
>
> hrown while evaluating an expression.
>
>   [sql] Failed to execute:   CALL
> SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('de
>
> rby.database.classpath','CSEM.csemderby')
>
>   [sql] java.sql.SQLSyntaxErrorException: The database class path
> contains a
>
> n unknown jar file '"CSEM"."CSEMDERBY"'.
>
>
>
> This error does not occur with Derby 10.13.1.1 but does occur now that I
> have upgrade to 10.14.2.0.
>
>
>
> I believe the this is related to some security change.   Possibly:
>
>
>
> https://issues.apache.org/jira/browse/DERBY-6987
>
>
>
> The application that is starting the JVM that is running Derby Network
> Server has not changed.   Only the version of Derby has changed.
>
>
>
> Any pointers will be greatly appreciated.
>
>
>
> I will be honest here.  Trying to find the change in the release notes is
> darn near impossible since the release notes for the releases are no longer
> on the Derby web site, at least I cannot find them.   Having to download an
> release and expand it to see what has changed is pretty awful.
>
>
>
> So interestingly I just clicked on this link again:
>
>
>
> http://db.apache.org/derby/releases/release-10.14.2.0.cgi
>
>
>
> Now I get a totally different page being returned.   I think this has
> something to do with the mirrors.   Earlier this morning, it brought me to
> page where it shows all of the apache projects and I had to drill down to
> “db/derby” and then was able to download the “bin” zip file for 10.14.2.0.
>
>
>
> So there is possibly some issue with the mirrors sometimes not displaying
> the same content.
>
>
> 
> Canoga Perkins
> 20600 Prairie Street
> Chatsworth, CA 91311
> (818) 718-6300
>
> This e-mail and any attached document(s) is confidential and is intended
> only for the review of the party to whom it is addressed. If you have
> received this transmission in error, please notify the sender immediately
> and discard the original message and any attachment(s).

[ANNOUNCE] CVE-2018-1313: Apache Derby externally-controlled input vulnerability

[Bryan Pendleton]

CVE-2018-1313: Apache Derby externally-controlled input vulnerability

Severity: Important

Vendor:
The Apache Software Foundation

Versions Affected:
Derby 10.3.1.4 to 10.14.1.0

Description:
A specially-crafted network packet can be used to request the Derby
Network Server to boot a database whose location and contents are under
the user's control. If the Derby Network Server is not running with a
Java Security Manager policy file, the attack is successful. If the
server is using a policy file, the policy file must permit the
database location to be read for the attack to work. The default
Derby Network Server policy file distributed with the affected releases
includes a permissive policy as the default Network Server policy, which
allows the attack to work.

Mitigation:
Users should specify an explicit security policy file, as described here:
http://db.apache.org/derby/docs/10.14/security/csecjavasecurity.html

Derby release 10.14.2.0 disallows the specially-crafted network packet,
and also modifies the default Derby Network Server policy file to be
significantly less permissive (the default file access policy is now
limited to the derby.system.home directory and the directory from
which the Derby jar files were loaded). It is still recommended that
production installations of the Derby Network Server should specify
an explicit security policy file.

Credit:
This issue was discovered by Grégory Draperi

[ANNOUNCE] Apache Derby 10.14.2.0

[Bryan Pendleton]

The Apache Derby project is pleased to announce release 10.14.2.0.

Apache Derby is a sub-project of the Apache DB project. Derby is a
pure Java relational database engine which conforms to the ISO/ANSI
SQL and JDBC standards. Derby aims to be easy for developers and
end-users to work with.

Derby 10.14.2.0 can be obtained from the Derby download site:

   http://db.apache.org/derby/derby_downloads.html.

This is a patch release. No new features have been added.

Bug Fixes
The following issues are addressed by Derby release 10.14.2.0. These
issues are not addressed in the preceding 10.14.1.0 release.

DERBY-6987 The default Network Server security policy file could be
trimmed down somewhat.
DERBY-6986 Network Server COMMAND_TESTCONNECTION need not try to open a database

For additional information, please see the release notes:

https://db.apache.org/derby/releases/release-10.14.2.0.cgi

Please try out this new release.

Re: Migrate from MariaDB to Derby using DdlUtils : Ant task failure

[Bryan Pendleton]

I don't think the DdlUtils mailing lists are active anymore. The DB
project retired the DdlUtils mailing lists in the fall of 2016, as
that subproject is entirely inactive now. I think this is the first
DdlUtils question that's appeared on any of the mailing lists in 5
years.

bryan

On Wed, May 2, 2018 at 3:45 PM, Rick Hillegas  wrote:
> I recommend that you post this question to the DdlUtils user list if you
> want to use DdlUtils for this task. I don't think that you will find a lot
> of DdlUtils expertise on the Derby lists. But maybe someone will speak up
> and happily contradict me.
>
> Another approach would be to use the foreignViews optional tool as
> documented here:
> http://db.apache.org/derby/docs/10.14/tools/rtoolsoptforeignviews.html. An
> advantage to this approach is that you don't have to indirect through a
> dumped data file on disk. The foreignViews will siphon the data out of
> MariaDB and drop it directly into the target Derby tables.
>
> Hope this helps,
> -Rick
>
>
> On 5/2/18 1:31 PM, afr0ck wrote:
>
> Hello,
>
> So i had to convert my production DB from MariaDB to Derby. I went through
> the steps at http://db.apache.org/derby/integrate/db_ddlutils.html but i had
> a problem when i run the export Ant task.
> The following text includes the environment variables i set & the failure
> message from Ant alongside with the build.xml file. The database URL is
> correct & works fine in the JVM.
>
> The environment variables :
>
>
> afr0ck@afr0ck:~/Desktop/ESTORE/ddlutils$ echo $CLASSPATH
> /home/afr0ck/Desktop/ESTORE/sqlite-jdbc-3.21.0.jar:/home/afr0ck/Desktop/ESTORE/db-derby-10.14.1.0-lib/lib/derby.jar:/home/afr0ck/Desktop/ESTORE/db-derby-10.14.1.0-lib/lib/derbytools.jar:/home/afr0ck/Desktop/ESTORE/mariadb-java-client-2.2.1.jar
>
> build.xml :
>
>
> 
> 
> 
> 
> 
> 
> 
> 
>  classname="org.apache.ddlutils.task.DatabaseToDdlTask">
> 
> 
> 
>  driverClassName="org.mariadb.jdbc.Driver" username="root" password="afr0ck"
> />
> 
> 
> 
> 
> 
>
> Ant task execution :
>
>
> afr0ck@afr0ck:~/Desktop/ESTORE/ddlutils$ ant -v export-source-db
> Apache Ant(TM) version 1.10.3 compiled on March 24 2018
> Trying the default build file: build.xml
> Buildfile: /home/afr0ck/Desktop/ESTORE/ddlutils/build.xml
> Detected Java version: 1.8 in: /home/afr0ck/jdk1.8.0_144/jre
> Detected OS: Linux
> parsing buildfile /home/afr0ck/Desktop/ESTORE/ddlutils/build.xml with URI =
> file:/home/afr0ck/Desktop/ESTORE/ddlutils/build.xml
> Project base dir set to: /home/afr0ck/Desktop/ESTORE/ddlutils
> parsing buildfile
> jar:file:/usr/local/ant/lib/ant.jar!/org/apache/tools/ant/antlib.xml with
> URI = jar:file:/usr/local/ant/lib/ant.jar!/org/apache/tools/ant/antlib.xml
> from a zip file
> Build sequence for target(s) `export-source-db' is [export-source-db]
> Complete build sequence is [export-source-db, ]
>
> export-source-db:
>
> BUILD FAILED /home/afr0ck/Desktop/ESTORE/ddlutils/build.xml:12: Could not
> read the schema from the specified database: Database type null is not
> supported.
> at
> org.apache.ddlutils.task.PlatformConfiguration.getPlatform(PlatformConfiguration.java:226)
> at
> org.apache.ddlutils.task.DatabaseTaskBase.getPlatform(DatabaseTaskBase.java:288)
> at
> org.apache.ddlutils.task.DatabaseToDdlTask.readModel(DatabaseToDdlTask.java:265)
> at
> org.apache.ddlutils.task.DatabaseTaskBase.execute(DatabaseTaskBase.java:381)
> at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:292)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)
> at org.apache.tools.ant.Task.perform(Task.java:350)
> at org.apache.tools.ant.Target.execute(Target.java:448)
> at org.apache.tools.ant.Target.performTasks(Target.java:469)
> at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1399)
> at org.apache.tools.ant.Project.executeTarget(Project.java:1370)
> at
> org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:41)
> at org.apache.tools.ant.Project.executeTargets(Project.java:1260)
> at org.apache.tools.ant.Main.runBuild(Main.java:849)
> at org.apache.tools.ant.Main.startAnt(Main.java:228)
> at org.apache.tools.ant.launch.Launcher.run(Launcher.java:283)
> at org.apache.tools.ant.launch.Launcher.main(Launcher.java:101)
> Caused by: java.lang.NullPointerException
> at
> org.apache.ddlutils.PlatformFactory.createNewPlatformInstance(PlatformFactory.java:83)
> at
> org.apache.ddlutils.task.PlatformConfiguration.getPlatform(PlatformConfiguration.java:222)
> ... 20 more
>
> Total time: 0 seconds
>
> What could be wrong with the above configuration ?
> 
> Sent from the Apache Derby Users mailing list 

Re: creating a database

[Bryan Pendleton]

You probably didn't really mean to create the database in the root
level of your filesystem. (C:\) It's not uncommon for the root of the
filesystem to have special security restrictions.

Try making a folder in your home directory, and try creating your
database there.

On Sat, Apr 7, 2018 at 8:37 PM, Bob M  wrote:
> Hi
>
> when I try to create a dbase named us_copiosus_USDJPY I get the enclosed
> error message
>
> not sure where to go from here ?
>
> 
>
> Bob M
>
>
>
> --
> Sent from: 
> http://apache-database.10148.n7.nabble.com/Apache-Derby-Users-f95095.html

Re: Installing Derby Database

[Bryan Pendleton]

Which instructions are you following?

And where did you enter 'java org.apache.derby.tools.sysinfo'?


On Sat, Apr 7, 2018 at 3:06 PM, Bob M  wrote:
> Hi
>
> Am trying to install the latest version 10.14.1.0
>
> Following instructions - I get to verifying Derby
>
> I input the command - java org.apache.derby.tools.sysinfo
>
> when I enter this command, there is an extremely brief flash on the screen
> and that is it ?
>
> what am I doing wrong ?
>
> Bob M
>
>
>
> --
> Sent from: 
> http://apache-database.10148.n7.nabble.com/Apache-Derby-Users-f95095.html

Re: Using Squirrel for Derby on Windows

[Bryan Pendleton]

Can you share any more details?

For example, can you cut-and-paste the section
of the CMD.EXE window where you run this batch file,
showing the over-long command that gets issued,
and the precise error that you get?

thanks,

bryan

Re: Very, very large .dat files

[Bryan Pendleton]

You are correct, Derby does not provide table partitioning features
such as those provided by Oracle.

And you are correct, a single Derby table is a single .dat file.

But modern filesystems handle very large files without problems. Is
there a particular reason that you think a very large file will be a
problem for you? Or that you think a larger number of smaller .dat
files would have some benefit?

I believe there are a variety of library/framework approaches to
splitting up a logical table into multiple physical tables. People
often do this with data that is oriented around "time", and becomes of
less interest as it ages. I've seen applications which, e.g., create a
new table each day, named appropriately, and "rotate" these tables
over time so that the old data ages out and is dropped (by dropping
the older no-longer-wanted tables).

Careful use of CREATE VIEW can shield your application from most of
the impact of such techniques.

thanks,

bryan

Re: Connecting to Apache Derby using DB2 unified driver

[Bryan Pendleton]

Interoperability between the DB2 driver and the Derby server
disappeared many years ago.

I don't think Derby 10.8 is anywhere old enough for things to work
properly; I think the last serious testing of those configurations was
back in the 10.2, 10.3 days (though my memory is fuzzy).

However, if you got things to "mostly work", I'm sure the community
would be quite interested to hear the details, and of course if you
wanted to contribute patches to improve the Derby behavior in that
configuration, they would certainly receive attention by the
developers.

bryan

P.S. It's been years since I looked at the DB2 driver. Is it by any
chance open source now?

Re: Can the embedded JDBC driver and the client JDBC driver co-exist?

[Bryan Pendleton]

Excellent news! Thanks for digging down to uncover the source of the
problem, and thanks for sharing your discoveries with us.

I know very little about pax-jdbc, but it seems like useful software; it
would be nice if it cleanly supported both the Embedded and Client-Server
driver configurations of Derby.

Perhaps you could forward your findings on to the pax-jdbc community, to
see if they think it might be possible to enhance their software?

In the meantime, it's great to hear that you have a solution that is
working for you.

bryan

Re: Can the embedded JDBC driver and the client JDBC driver co-exist?

[Bryan Pendleton]

On Sat, Aug 12, 2017 at 9:47 AM, Steinar Bang  wrote:


>
> Ok, I interpret this answer to be that there shouldn't be any problems
> with pulling the embedded driver and the client driver into the same
> java program?
>
>
>
Yes, that's my belief.

You might experience higher-level behavior differences. There have been, in
the past, subtle differences between the way the two drivers behave, so you
might encounter such differences (e.g.,
https://issues.apache.org/jira/browse/DERBY-2453). Those known differences
are cataloged in the Derby JIRA, so you could search there if you need more
details.

But as far as including both JDBC drivers into the same JVM, and opening
multiple connections, some with the one driver and some with the other, I
believe that should work fine.

You can't simultaneously access the identical physical database with
multiple connections this way, of course, because your embedded connections
to a particular physical database on your machine will lock out the Derby
Network Server's attempts to provide connections to that same database (and
vice versa).

So either connect to different databases with these different driver types,
or close your connections (and shut down the embedded engine) prior to
opening client connections to a Derby Network Server serving the same
database.

Anyway, yes, please try to figure out if you have any CLASSPATH conflicts,
and please do let us know the results of your investigations!

thanks,

bryan

Re: Can the embedded JDBC driver and the client JDBC driver co-exist?

[Bryan Pendleton]

I suspect your problems are caused by having two different versions of the
Derby jars intermingled. The behavior of CLASSPATH is complex, especially
for things like the JDBC 4.0 autoload features, and Derby jars are often
packaged and bundled into other software packages, making it easy to have
multiple copies of the Derby code without realizing it.

Sometimes, running with "-verbose:class" will give you a clue about which
Derby code is being loaded from which location on the CLASSPATH.

But, perhaps you are encountering some other problem that isn't clear to me.

Can you isolate your problematic code as a minimum viable test case and
share it?

Ideally, start by taking a very simple test program such as the one from
the Derby tutorial (
http://db.apache.org/derby/docs/10.13/getstart/twwdactivity3_Setup.html)
and then make the minimum necessary changes to it to demonstrate the
problem you're having.

Then share that code with the list.

thanks,

bryan

Re: Page cache sizing

[Bryan Pendleton]

how does derby.storage.pageCacheSize parameter (https://db.apache.org/derby/
> docs/10.13/ref/rrefproper81359.html) work with database that has multiple
> page sizes--tables with default 4096 bytes and tables with long/blob of
> 32768 byte pages?
>
>
Hi Peter,

I'm not 100% sure how this works; I think you should run some experiments.

Here's what I *think* the behavior is:
1) The page cache is sized in 4K pages, so if you set pageCacheSize=1024,
you get 4 meg of page cache memory
2) Tables with 4K pages simply use the cache as you expect.
3) Tables with 32K pages chew up 8 cache "pages" at a time, each 32K chunk
of page cache holding 1 32K page from that large-page table.

Some stuff I found while searching around, which might give you some ideas
for experiments you could run:

http://apache-database.10148.n7.nabble.com/out-of-memory-when-writing-blobs-td100948.html

and

https://issues.apache.org/jira/browse/DERBY-4537

Sorry I'm not of much more help here.

bryan

Re: Updating 2 derby tables error message interpretation

[Bryan Pendleton]

> So my question is: why do I not see the new 54 table2 records that my
> program says have been created?
>
>
Perhaps you didn't COMMIT the 54 records in your test program?

Perhaps your test program is using a different Derby database than you're
looking at with NetBeans?

Perhaps your test program uses an in-memory database, and your records were
committed to the in-memory database, then that database was lost when your
program exited from its in-memory configuration?

Perhaps your connection information is slightly different, so you are using
two different SCHEMA settings in the two configurations, so that you have
two different sets of tables, in two different SCHEMA?

There are many possible ways in which one Derby configuration can be
looking at a different set of database tables than another, all of which
are intentional, but which are easy configurations to find yourself in
accidentally.

thanks,

bryan

Re: Updating 2 derby tables

[Bryan Pendleton]

>
> Exactly what would you wish me to post?
>
> Bob M
>
>
Here are some useful guidelines for how to ask a question in a way that
helps others help you:

https://stackoverflow.com/help/mcve

thanks,

bryan

Re: Updating 2 derby tables

[Bryan Pendleton]

Perhaps you made a change to the code, but forgot to recompile, or forgot
to re-update your JAR files, or made some other simple build mistake.

As a technique for understanding the behavior of your program, commenting
parts of it in and out and re-running the program is a very good technique,
but it is easy to make a simple build mistake and not run the modified
version
of the program which you think you are running.

Another very good technique for understanding the behavior of your program
is to run it under a Java debugger, and then you can stop and start it and
various points, step through it line by line, look at the values of
variables
at various points, etc.

thanks,

bryan


On Sat, May 20, 2017 at 11:12 PM, Bob M  wrote:

> The situation has deteriorated..
>
> I have placed  /*  */ around all code which adds new trade records and
> which
> updates trade records
>
> I was expecting x number of trades to take place and no error messages
>
> Instead I got the following:-
>
> Adding a new trade record Number: 4101
>
> - SQLException -
>  SQL State: 23505
>  Error Code: 2
>  Message: The statement was aborted because it would have caused a
> duplicate
> key value in a unique or primary key constraint or unique index identified
> by 'SQL170412104646890' defined on 'TRADES'.
>
> I checked the whole program for "adding a new trade record" and found only
> a
> single instance - the code is within the /*  */  so how can I continue to
> get a reference to these words in an error code?
>
> Bob M
>
>
>
> --
> View this message in context: http://apache-database.10148.
> n7.nabble.com/Updating-2-derby-tables-tp147386p147412.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>

Re: Connection issues with Windows Server 2016

[Bryan Pendleton]

What sort of error do you get? Provide as much detail as you can.

thanks,

bryan

Re: Security problem with ggregate functions using Java

[Bryan Pendleton]

Hello Nick,

The ij code is in derbytools.jar, I believe. Did you define a security
policy that covers derbytools.jar?

In particular, to allow calling System.getProperties, try something like:

grant codeBase "${derbyTesting.codejar}derbytools.jar" {
  // Access all properties using System.getProperties -
  // ij enumerates the properties in order to open connections
  // for any property set in ij.connection.* and set protocols
  // for any property in ij.protocol.*
  permission java.util.PropertyPermission "*", "read, write";

  // Read all files under ${user.dir}
  permission java.io.FilePermission "${user.dir}${/}-", "read";

  // IjTestCases read, write, and delete ij's output in the extinout dir
  permission java.io.FilePermission "${user.dir}${/}extinout${/}-", "read,
write, delete";

  // ij needs permission to read the sql files in this jar
  permission java.io.FilePermission "${derbyTesting.testjarpath}", "read";

};

thanks,

bryan

Re: BACKUP.HISTORY file

[Bryan Pendleton]

Seems reasonable to log an enhancement request, I think.

In the meantime, Eric, can you modify your system so that it truncates or
removes
the BACKUP.HISTORY file after the successful completion of a backup?

bryan

Re: Unexplained problem

[Bryan Pendleton]

When my test spawns the "server shutdown", the "server start" process exits 
shortly afterward, except that it prints a message on its STDOUT:

Wed Nov 23 09:09:25 EST 2016 : Invalid reply from network server: Insufficient 
data


The *"server start"* process is the one which has this behavior?

Thank you for clarifying that, I didn't understand that detail initially.

This feels almost identical to a similar problem in one of the Derby
regression test suites:


https://issues.apache.org/jira/browse/DERBY-6337?focusedCommentId=13976483=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-13976483

Here's what Knut Anders suggested at the time:

The "insufficient data" error indicates that the ping
command was able to connect to the server, but didn't
get a full response back from it. I suppose that it
might happen if the server shutdown is completed after
the ping command has connected to it and before the
response has been sent back.

I think that, basically, the problem is that the Network Server is
multi-threaded and has some rather complex internal state, and the
simple atomic operation that we think of as "server shutdown" is
actually more complex, and so there can be a situation, where the server
shutdown interrupts/interferes with other server connections that are
still connected at the time.

I'm speculating that the "server start" part of your system has
retained some level of connection to the Derby Network Server, even
though the Network Server is already up-and-running.

Then, subsequently, the "server shutdown" request interrupts the
"server start" connection, not allowing it a chance to fully
send the network messages back to your client.

And so the code on the client sees a partial response from the
server, and tells you that fact.

Unfortunately, this doesn't look like a simple situation to improve.

From what I can tell by studying DERBY-6337, the solution that
the developers chose at the time was to recognize in the test program
that this was a possible result, and to teach the test program to
allow for this awkward response from shutting the server down.

You could certainly log an enhancement request to try to make
this cleaner, although I think if there were an easy way to do
that, it would have been pursued during the DERBY-6337 research.

However, it's interesting to me that it's the "server start" part
of your test which receives this error.

That says to me that the mechanism you're using the *start* the
server is successfully starting the Network Server, but is also
retaining a network connection to the server, which doesn't seem
necessary to me.

If that startup technique were able to start the Network Server,
then "detach" from it, then when the server is subsequently shut
down, it wouldn't have any message to try to send back to that
startup connection (since the connection would no longer exist),
and so the partial message error would not arise.

Lots of speculation and hand-waving here, but perhaps it gives
you some more ideas about further diagnosis you can perform?

thanks,

bryan

Re: Unexplained problem

[Bryan Pendleton]

On 11/21/2016 1:52 PM, Donald McLean wrote:

I'm trying to run a Derby in server mode. When I go to shut it down, I'm 
getting this weird error:

Mon Nov 21 16:19:44 EST 2016 : Invalid reply from network server: Insufficient 
data.

The command line arguments:

/usr/bin/java, -jar, 
/Users/dmclean/Library/java/db-derby-10.12.1.1-bin/lib/derbyrun.jar, server, 
shutdown

Does anyone have any suggestions as to what the problem would be and how to fix 
it?

Thank you,

Donald



I don't think I recall having seen that before. There are some old jobs
in the Derby issues database (e.g., DERBY-6337) that indicate that if the
Network Server is slow to respond, or slow to shut down, then the client
may print some odd messages.

Does this happen *every* time you shut down the Network Server?

What does the end of your derby.log file look like?

thanks,

bryan

[ANNOUNCE] Apache Derby 10.13.1.1 released

[Bryan Pendleton]

The Apache Derby project is pleased to announce feature release 10.13.1.1.

Apache Derby is a subproject of the Apache DB project. Derby is a pure Java
relational database engine which conforms to the ISO/ANSI SQL and JDBC
standards. Derby aims to be easy for developers and end-users to work with.

Derby 10.13.1.1 can be obtained from the Derby download site:

   http://db.apache.org/derby/derby_downloads.html.

Derby 10.13.1.1 contains the following new features:

* Identity column definitions may now contain the CYCLE clause.  The
  CYCLE clause controls what happens when the identity column exhausts
  its range and wraps around.
* The new SYSCS_GET_DATABASE_NAME system function returns the
  canonicalized version of the current database name.
* The new rawDBReader tool can be used in various scenarios to recover
  some or all of the data from a severely corrupted database.
* The new SYSCS_IMPORT_TABLE_BULK and SYSCS_IMPORT_DATA_BULK system
  procedures can be used to process input data files which contain
  one or more header lines at the start of the file.

In addition, Derby 10.13.1.1 contains bug, security, and documentation fixes.

Please try out this new release.

Re: Moving from 10.5.3.0 -> 10.12.1.1 SQLNonTransientConnectionException but 10.5.3.0 ->10.7.1.1 is working perfectly exactly same code, 10.5.3.0 -> 10.8.1.2 fails similar

[Bryan Pendleton]

java.lang.NullPointerException
at 
org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getTriggerActionString(Unknown
 Source)
at 
org.apache.derby.iapi.sql.dictionary.TriggerDescriptor.getActionSPS(Unknown 
Source)


This definitely looks like it could be DERBY-6726

https://issues.apache.org/jira/browse/DERBY-6726

From the notes in that bug report we were unable to reproduce
the problem, and the original reporter noted that

 I rebuilt all the tables and triggers and now the problem doesn't 
happen

It would sure be nice to fix this problem, but if we can't reproduce
it we may need a lot of help from you to try to fix it.

Alternatively, if you have a way to backup your tables, and then drop
and re-load your data, it seems like the problem may go away?

The fact that you seemed to be able to narrow down the introduction
of this problem to Derby 10.8 makes me wonder if DERBY-5121 is related?

At the least, it would be nice if you could put some comments into DERBY-6726
and include as much information as you can about the particulars of your
situation.

thanks,

bryan

Re: Export with column names in first line

[Bryan Pendleton]

is there an opportunity to export a table with column names in first line of 
the exported file?


I don't believe that is currently possible, no.

It seems like a nice enhancement to suggest.

I see some enhancement requests for SYSCS_EXPORT_TABLE logged as:

https://issues.apache.org/jira/browse/DERBY-6850

but yours seems rather a different idea than those.

If you'd like to record your enhancement request, it seems to me
you could either add it to DERBY-6850 or you could log a new suggestion.

See: http://db.apache.org/derby/DerbyBugGuidelines.html for
more details about how Derby tracks suggestions and bug reports.

thanks,

bryan

Re: Puzzling error

[Bryan Pendleton]

I am getting an exception trying to delete a record that I added not long 
before (as part of a unit test)


Sometimes unit test frameworks do really clever things, too clever in fact.

For example, I've seen unit test frameworks for database applications which
have automatically "cleanup" code which completely removes the entire
directory tree which was being used by the test, in order to release
the disk space and prevent clutter from accumulating.

Only, it turned out that the framework removed the folder tree while the
test was still running.

One thing you can do to try to get more information is to make sure
you are getting the *full* exception trace, not the abbeviated one. See:

http://wiki.apache.org/db-derby/UnwindExceptionChain

Another thing you might try is to run your unit tests with Derby
configured so that it has logStatementText=true, and then look at your
derby.log after the test runs to get a better idea about how far your
test ran, and what was going on just before the failure.

bryan

Re: lose ownership on date base after set derby.database.sqlAuthorization to true

[Bryan Pendleton]

On 7/12/2016 7:01 PM, Rick Hillegas wrote:


Effectively when i execute select * from sys.sysschemas where schemaName 
='TESTEUR';
i obtain: 278b81cd-0155-d9ce-d86a-12e9eb35TESTEURAPP



As you note, this indicates that the database is owned by APP.


Hi Rick,

In your opinion, is this a reliable way to ascertain the identity
of the database owner, at least in the scenarios in which you
can successfully connect to the database as *some* user?

If so, I wonder if we should add a suggestion about this to:

https://db.apache.org/derby/docs/10.12/security/cseccsecuredbowner.html

This might make it easier for people to understand how their
database ownership behavior is arising?

thanks,

bryan

Re: lose ownership on date base after set derby.database.sqlAuthorization to true

[Bryan Pendleton]

On 7/11/2016 6:07 AM, Régis Monfilliette wrote:

|Hi,|

|I have lose ownership on database after i have passed the command: |

|CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.sqlAuthorization', 
'true');|

I have restart the data base, and  then connect  on same user( TESTEUR)  i have 
created a table but i can't pass any command ( select or grant) i received the 
message:

Error code -1, SQL state 42506: L'utilisateur 'TESTEUR' n'est pas propriétaire 
de Table/View 'TESTEUR'.'CLIENT'.

Someone can help me?
Thank you.


Perhaps user TESTEUR wasn't actually the database owner?

https://db.apache.org/derby/docs/10.12/security/cseccsecuredbowner.html

Perhapps user APP is actually the database owner?

bryan

Re: Updating several consecutive fields in a record

[Bryan Pendleton]

psUpdate = conn.prepareStatement("UPDATE  SET PROFIT_LOSS = ? WHERE




Now, I wish to update seven consecutive fields in the latest derby record


You can list multiple columns in the UPDATE statement:

UPDATE my-table SET col-1=?, col-2=?, col-3=? WHERE ...

And then use multiple "setInt", "setString", etc. statements
to associate the various column values to be used for the
placeholders.

bryan

Would it be beneficial to merge the derby-user and derby-dev mailing lists?

[Bryan Pendleton]

Hi all,

It is not 100% clear to me why we have separate 'derby-user' and 'derby-dev'
mailing lists. On our web site, http://db.apache.org/derby/derby_mail.html,
we describe the two lists as follows:

derby-user: For discussions about how to use Derby.

derby-dev: For discussions about development tasks, such as bug fixes, 
proposed code changes, etc.

This is a clear distinction, but I'm not sure it is very beneficial
to have such a distinction.

In particular, I worry that it divides the Derby community into
"users" and "developers", and I think it might be more in the spirit
of Open Source to try to eliminate such distinctions, and encourage
everyone to think of themselves as simply a member of the Derby community.

I'd also like to encourage more people to actively contribute to
Derby development. There are not as many active Derby developers as
there once were, but I'm hopeful we can find more people who are
interested in contributing ideas, techniques, documentation, test
cases, and code to Derby.

What is your reaction? Do you find it beneficial to maintain
the two separate mailing lists? Or do you think it would be
an improvement to have only a single mailing list?

thanks,

bryan

Re: Processing a subset of records correctly - again

[Bryan Pendleton]

For all the others - compare prediction (from prev record) with actual )from
current record) and do some maths

I seem to not be getting the predicted value correct?


Hi Bob,

I don't think you've really given us enough information to help you very much.

Perhaps you could provide more details about what you expected your program
to do, and what you observe that it is actually doing, and we could offer
more suggestions.

One thing that occurred to me as I read it: it appears that your program
is intended to face 4 possibilities, and do a particular series of computations
for each of those 4 possibilities.

Perhaps, either due to the data being presented to your program, or due
to a flaw in your program, you are either not performing any of those 4
possibilities, or you are performing more than 1 of them.

That is, you could try structuring your program as:

   if( case 1 )
   else if( case 2 )
   else if( case 3 )
   else if( case 4 )
   else print information about what data we actually saw that didn't fit.

Then if you ever see that "else" case, you have some clues about
why your data didn't match your program.

thanks,

bryan

Re: SQLDataException when updating rows (Vert.x)

[Bryan Pendleton]

One thing I'll note is that I /don't/ get this error when I execute the update 
via other means.


Perhaps, those other tools (Squirrel, etc.) are not using the special
form of Statement.executeUpdate() which requests that the auto-generated
keys should be made available, while the Vert.x framework is.

https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#executeUpdate(java.lang.String,%20int)

bryan

Re: SQLDataException when updating rows (Vert.x)

[Bryan Pendleton]

I am not completely sure if this problem is to do with Vert.x and its JDBC 
wrappers or Derby 10.12.1.1.


Me either.


Derby throws an error while trying to interrogate the result set for updated 
rows:




Caused by: ERROR 22018: Invalid character string format for type long.



at org.apache.derby.iapi.types.SQLChar.getLong(SQLChar.java:447)
at 
org.apache.derby.impl.sql.execute.UpdateResultSet.collectAffectedRows(UpdateResultSet.java:534)
at 
org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSet.java:272)


Well, that code in collectAffectedRows is new with Derby 10.12 (DERBY-6742),
so you certainly might have found a new bug in Derby.

Is it possible for you to run your program against Derby 10.11?
If so, does the problem disappear (that would be a strong clue)?


What's happening seems to be an inconsistent expectation about what is in the 
result set.



It addresses the wrong column


Your analysis is very interesting, and I think you have explained the
behavior quite plausibly.

Is there any chance that you can construct a standalone test case
that demonstrates this behavior?

Ideally, a Minimal Complete Verifiable Example 
(http://stackoverflow.com/help/mcve)
would be wonderful, but anything you can do to help simplify the reproduction
of the problem will go a long ways to those developers trying to
track down and fix it.

Possibly, running your test case with statement logging and query plans
enabled (http://wiki.apache.org/db-derby/PerformanceDiagnosisTips) will
give you some clues about how to construct a simpler reproduction case.

At the very least, please log a bug against Derby in the issue tracker
(http://db.apache.org/derby/DerbyBugGuidelines.html) with as much
information as you can provide.

thanks,

bryan

Re: Reclaim unused space, but how exactly?

[Bryan Pendleton]

unstructured data. These data will to a large extent (but perhaps not
completely) be replaced by structured data (in some user defined data type) in
another column. This will reduce the space requirements considerably for each
particular row.


Hi Karl, thanks for the follow-up.

As Myrna suggested, I wonder if it might be worth using two separate tables 
here.

First, insert the unstructured data into the first table.

Later, when the conversion to the structured form occurs, insert the
row (in its structured form) into the second table, and simultaneously
delete the row from the first table.

Over time, I'd expect Derby to successfully re-use the space in the first
table, and I'd expect Derby to successfully store the rows in the
second table in a compact way.

bryan

Re: Reclame unused space, but how exactly?

[Bryan Pendleton]

I want to make the unused space availabe to derby for the insertion of new
rows. It should be optinal to give unused space back to the OS. In order to do
so, I have to use some system utility.


I'm not totally sure whether the 5th word in the above line is "optional"
or "optimal".

To return unused space to the OS, you have to make a special call;
simply deleting your rows from the table with DELETE FROM is not enough,
nor is updating your rows to set every column to the shortest-possible
value.


The only utility I found is SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE. But is
this correct?


It is correct that the SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE procedure
will return space to the operating system if there is space available.

There is also the SYSCS_UTIL.SYSCS_COMPRESS_TABLE procedure.

And there is the DROP TABLE statement.

And the TRUNCATE TABLE statement.


Since SYSCS_UTIL.SYSCS_COMPRESS_TABLE will only give unused space back to the
OS, this is not helpfull either. Or is it?


I don't understand your question: I thought that you **wanted** to "give unused
space back to the OS".


Where is my misunderstanding, i.e. what do I have to do, in order do reclame
the space?

Or does derby somehow optimize disk usage in the described case automatically?


Derby does many such optimizations. You might want to start by reading
the archives of derby-user from May-June, 2005, specifically starting here:

http://mail-archives.apache.org/mod_mbox/db-derby-user/200505.mbox/%3cloom.20050531t105713-...@post.gmane.org%3E

Maybe you could run some experiments, to try different approaches,
and report back to the list about what experiments you have run,
and what you have found, and the list could help you understand
the behaviors you are seeing.

Note that if you actually have "a huge number of rows", where
the data you store in each row is "large, say, on average of about
9000 characters" and then you "replace the data and they are
on average only 10 characters long", you have created a somewhat
mysterious program, to me.

Will you ever make those values longer again? Or will they permanently
be only 10 characters long? Why did you need the 9000 byte values in
the first place? Does this happen with every row in your table, or
is it more of a mixture of some rows growing and some rows shrinking?

The wonderful thing about a database like Derby is that you have many
choices in how you build your application, and Derby will work very
hard to try to accommodate whatever choices you make.

The challenging thing about a database like Derby is that you have many
choices in how you build your application, and Derby will work very
hard to try to accommodate whatever choices you make.

thanks,

bryan

Re: where condition in CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY fails

[Bryan Pendleton]

I have need to get list of table name exported to a file . I am using 
SYSCS_EXPORT_QUERY . However with where condition query system procedure fails

CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY('select t.tablename from sys.systables 
t,sys.sysschemas s where t.schemaid = s.schemaid and 
t.tabletype=T','test.txt',null,'/',null);


In what way does it fail?

bryan

Re: What creates rawStoreDaemon threads in embedded derby db?

[Bryan Pendleton]

It seems to work fine, normally. However, someone else running the program
got several thousand rawStoreDaemon threads.

I can't recreate these extra threads. What's worse I can't even seem to make
more than one derby.rawStoreDaemon threads.  I have had up to 100 active
connections and several users at once, but nothing seems to even make a
second derby.rawStoreDaemon thread.

My basic question is, what makes these threads? How come there is ever more
than one?


People have made similar observations in the past, but we've never
identified a clear reproducible case.

See, e.g.,

https://issues.apache.org/jira/browse/DERBY-594

and

https://issues.apache.org/jira/browse/DERBY-4210

bryan

Re: SYSCS_UTIL.SYSCS_CHECK_TABLE

[Bryan Pendleton]

Is it possible to manipulate the base tables and/or the btree indexes in a way 
to cause inconsistencies


It's hard to do this reliably, or easily.

But I encourage you to try, it sounds like a good project,
and a good way to explore Derby internals and improve your
knowledge of Derby.

Here's a possible idea: take part, but not all, of your Derby
database "back in time".

A base table and its indexes are each stored in separate physical
files, which Derby calls "conglomerates".

So if you define a table with a number of secondary indexes,
and then insert some rows into that table, Derby will populate
the table and its indexes.

Then, shut down Derby cleanly and make a copy of the entire
database folder.

Then, start Derby back up and add some more rows into that
table.

Then, shut down Derby cleanly again.

Then, try (using yet another copy of the Derby database folder)
copying some of the table's index conglomerates from your first
backup copy into your master copy.

This will cause the base table rows to be "missing" some
index entries because you have essentially "rolled back" the
index conglomerates to an earlier time, causing them to be missing
the index entries that were created by your second batch of
insert statements.

Then see how the consistency-checking tools behave on that
artifically-damaged database.

Similar experiments are possible using other types of
integrity constraints (unique constraints, foreign key
constraints, etc), and using other types of "back-in-time"
intentional damage, such as rolling an index back to before
an update of an indexed column, or rolling an index back to
before a delete of a row, or rolling back one table but
not other table, when the tables have a referential
integrity constraint.

These are the sorts of consistency violations that a consistency-checking
tool is intended to catch, so it would be good to know if you
are able to provoke such types of damage and then observe
the tools helping you to diagnose the problem.

Regarding internal sorts of Btree integrity, that is a harder
problem, but it would be good for you to verify that you
can detect the simpler problems first.

thanks,

bryan

Re: Using the WHERE clause in a ResultSet

[Bryan Pendleton]

On 11/3/2015 3:35 PM, Bob M wrote:

I am using the following code:-

rs = s.executeQuery("SELECT Field1, Field2, Field3, Field4 FROM XX "
 + "WHERE Field1 LIKE String1 AND Field2 LIKE String2 AND Field3 LIKE
String3 AND Field4 LIKE String4 "
 + "GROUP BY Field1, Field2, Field3, Field4");

XX - table name
Field1  thru Field4 are VARCHAR variables

and am getting the error on String1


If String1 is a literal string constant, for example

WHERE Field1 like California%

then you want to put your literal string constant in
single quotes, as in:

WHERE Field1 like 'California%'

or use PreparedStatement substitution, as in:

WHERE Field1 like ?

ps.SetString( 1, 'California%' );

bryan

Re: Unknown page format at page Page(7,Container(0, 161)) [SYSCOLUMNS_INDEX1] on bootup

[Bryan Pendleton]

I understand 161 refers to the conglomerate SYSCOLUMNS_INDEX1 and the file 
ca1.dat.

Is there any magic I can do to somehow drop this "index" and regenerate it?


Do you have any slightly older backups of this particular database that you
can copy ca1.dat from?

Alternatively, did you try (desperation, but ...) creating a new database with
identical DDL statements, and then copy its SYSCOLUMNS_INDEX1 conglomerate
to this database's ca1.dat?

index conglomerates are pretty closely tied to their base conglomerate,
so yet a third desperate measure would be to copy both the index and the
base conglomerate from a different (but similar) db to this db.

Good luck!

bryan

Re: How do I embed and deploy Apache Derby DB to end users

[Bryan Pendleton]

So, this is a humble request to your community about how to deploy it with the 
Java application.
*N.B : I use Netbeans for Java development and I included the derby jar library 
to my project.*


Assuming you are using the embedded configuration, including
the derby.jar library into your project should be all you need
to do.

What was the *exact* behavior that you observed, when you tried this?

thanks,

bryan

Re: can't get warning 01003 in nested select in update

[Bryan Pendleton]

3, when the select in an update statement, can't get the warning 01003.
ij(CONNECTION1) UPDATE APP.EMP T1 SET SALARY = ( SELECT AVG ( T2 . SALARY ) FRO
M APP.EMP T2 ) WHERE WORKDEPT = 'D21' AND SALARY  ( SELECT AVG ( T3 . SALARY )
FROM APP.EMP T3 );
7 rows inserted/updated/deleted


The lack of the warning seems incorrect to me. But I'm not sure if this
behavior is governed by the SQL Standards or not.

thanks,

bryan

Re: Performance degradation with Derby Embedded under Tomcat vs Jetty

[Bryan Pendleton]

I've tried to figure out what the differences are between these two
environments without luck.


Perhaps the amount of memory available to Derby is dramatically
different in the one case than in the other, and Derby is working
very hard to accomplish its tasks with insufficient memory?

bryan

Re: ASD COMPANY

[Bryan Pendleton]

When I turn on my derby service I have problem its start then its stop I don’t 
know why its stop


Can you find the 'derby.log' for your Derby service?

It should be in the home directory for your Derby service,
which is typically the current directory of the command
which starts the Derby service.

Try searching your hard disk for the file 'derby.log',
if you aren't sure where your derby.system.home is.

bryan

Re: error 08001 local database

[Bryan Pendleton]

On 2/8/2015 1:00 PM, Ignacio Yuste Lopez wrote:

hi,

I use the first way. I installed java using directly but derby with the 
repository of Ubuntu.


Did you try using a more recent version of Derby,
downloaded from the Apache website?

Alternatively, can you find more details about the
problem? Do you have a derby.log file?

thanks,

bryan

Re: error 08001 local database

[Bryan Pendleton]

On 2/7/2015 2:46 AM, Ignacio Yuste Lopez wrote:

  ij connect 'jdbc:derby:MyDbTest;create=true';
ERROR 08001: No suitable driver found for jdbc:derby:MyDbTest;create=true



Which of the 3 ways of running ij that are documented here
http://db.apache.org/derby/docs/10.11/getstart/tgsrunningij.html
did you use?


I copy the exit of sysinfo if somebody could show me the problem

Versión de Java: 1.8.0_25


This is very new.


[/usr/share/javadb/lib/derby.jar] 10.3.2.1 - (599110)


This is very old.

Why did you select such an old version of Derby?

Does your problem persist if you use Derby 10.11?

thanks,

bryan

Re: Initializing a new record where field is NULL by default

[Bryan Pendleton]

 You have to spell out the columns you are inserting into,
 unless the columns with default values are the last columns
 in the table. In that case you can just omit them in the insert


I'm pretty sure it's a bit more flexible than that. I believe
that if you don't say NOT NULL, and you don't have a DEFAULT
clause, then DEFAULT NULL is automatic.

And I believe you can name the columns in any order (so long
as they line up with the VALUES values), so they don't have
to be the last columns in the table.

So you can INSERT INTO T (c, f, a, g, b ) VALUES ( .. )
and columns d and e and h will get NULL values since they
weren't mentioned in the insert.

thanks,

bryan

Re: Getting fail to start database and unknown page format Exception

[Bryan Pendleton]

It looks like derby database is corrupt, and it is causing critical problem
in our e derby.log
http://apache-database.10148.n7.nabble.com/file/n143610/derby.log  nd with
the huge business impact. And the path which its saying derby not found is
valid and correct path . Still why suddenly derby got corrputed and what are
the reasons for the corruption ?


There are various possibilities:

1) You experienced a hard-disk failure on the disk holding /usr/ironhide/var/db
2) or, you experienced a power-failure which caused the operating system to
   write incorrect data to the filesystem, or fail to flush its buffers 
properly,
   perhaps because write-caching was enabled on the filesystem hardware
3) or, you filled up the filesystem /usr/ironhide/var/db, and all the databases
   and their redo logs were on the same filesystem and all were damaged
   simultaneously
4) or, some other piece of software, not Derby, wrote updates to the Derby files
   directly

There are certainly other possibilities.

Have you inspected your system logs and talked to your system administrators
for more information about what was going on at the moment that you noticed
the problems?

Do you have backups of these databases?

Can you print the *full* exception logs from your attempts to access
the databases (http://wiki.apache.org/db-derby/UnwindExceptionChain)

thanks,

bryan

Re: drop schema question

[Bryan Pendleton]

I create a wrong schema,when drop,The system is error:cannot be dropped because 
it is not empty.


First, issue DROP TABLE, DROP VIEW, DROP TRIGGER etc.
statements for any tables, indexes, views, triggers, etc.
that you created in that schema.

Then drop the schema.

thanks,

bryan

Re: Storing an RSS feed news headlines in Derby

[Bryan Pendleton]

I wish to store title and pubDate for each headline news item on a RSS feed
in a Derby database

Would appreciate some pointers as to how to achieve this


The critical bit of code that you need to write probably
looks something like this:

PreparedStatement insRSS = con.prepareStatement(
insert into rssfeed (title, pubdate) values( ?,? ) );

insRSS.setString( 1, title );
insRSS.setDate( 2, pubdate );

insRSS.executeUpdate();

con.commit();

There's more to it than this, but this is a start that should get
you closer to building a prototype, at which point you can see
how well it works and where you want to improve it.

You'll find lots of details to consider here:

http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

thanks,

bryan

Re: How to access orphaned db.

[Bryan Pendleton]

Access through the net is rejected


Do you mean: access from some remote machine to your
Windows machine is rejected?

If so, that sounds like the Windows Firewall, which
is typically enabled on a fresh Windows installation and
has to be disabled to allow external machines to open
connections to your machine.

If you've configured the Windows Firewall to allow
the connection, then are you sure that you started the
Derby Network Server on your Windows machine? Maybe
before you re-installed Windows you had Derby set to
automatically start the server at Windows startup, but
that configuration was lost when you reinstalled Windows.


But ij also doesn't work with the net driver.


I'm not sure what you mean by this sentence. Ij won't start? It starts,
but you don't know how to use it? It starts, and you know how
to use it, but when you issue a command you get an error?

If you can post detailed information about a specific
problem you're having, for example paste in the ij session
that you tried, together with the error message that resulted,
perhaps that will give us more clues about what problem you're
trying to solve.

thanks,

bryan

Re: Database hanging after Heap space error

[Bryan Pendleton]

What is in 'derby.log' when you try to restart Derby?

thanks,

bryan

Re: Apache Derby - Locks up.

[Bryan Pendleton]

java.lang.OutOfMemoryError: Java heap space


Have you experimented with giving more memory to your JVM?

thanks,

bryan

Re: Apache Derby - Locks up.

[Bryan Pendleton]

It's interesting that you are trying to set it to use 6 G, and
yet in your other message there was the line:

 Total Memory : 1756889088   Free Memory : 306272128

which seems to indicate that it's only using 1.7 GB.

Like maybe you're running a 32 bit JVM, not a 64 bit JVM, somehow?

bryan

Re: Derby DB suddenly can't open

[Bryan Pendleton]

On 5/14/2014 2:38 AM,  wrote:

I am use 10.8.2.x for more than 2 years, and it works well till today. Suddenly 
I can't open it


Do you have the complete exception stack?

http://wiki.apache.org/db-derby/UnwindExceptionChain

thanks,

bryan

Re: deleting a corrupted database with invalid checksum on page

[Bryan Pendleton]

What can I do to just delete this table and then rebuild it from the Mirror
file?


Can you use RENAME TABLE?

Then you can create your new table with the old name, and just
leave that old, damaged table around.

Of course, you'll want to arrange a more long-term solution,
but that might get you back on your feet again while you
build a more solid overall plan.

bryan

Re: Connect to derby database on raspberry pi

[Bryan Pendleton]

that the database server runs under port 1527. But I can´t get a connection
to the database from my windows 7 PC. I checked my firewall (comodo) and
opened the port 1527.


The database server does run on port 1527 (by default), but you
have to start that server yourself.

http://db.apache.org/derby/docs/10.10/adminguide/tadmincbdjhhfd.html

thanks,

bryan

Re: Apache Derby Command Line?

[Bryan Pendleton]

FireDaemon loads the process a little differently than the command line but
it works because other java applications ive run as services run fine


1) What, precisely, does FireDaemon do differently, and
2) What, precisely, goes wrong when you try to run Derby?

I.e., do you get an error message? Do you get an exception? Do
you get messages in the derby.log file? Do you get a Network
Server that starts but isn't behaving the way you expect? In what way?

If running that exact command line is working just fine when you
run it from the Windows Command Prompt, but running that exact
command line from the FireDaemon tool behaves differently, you
need to be as precise as possible about what behavior changes
you're seeing and what problems they're causing you.

thanks,

bryan

Re: Apache Derby Command Line?

[Bryan Pendleton]

My command line is:

-jar derby.jar %DERBY_OPTS% -classpath %DERBY_RUNPATH% 
org.apache.derby.drda.NetworkServerControl start


Is it a typo? It looks like you are missing the word java
at the start of your command line.

thanks,

bryan

Re: Number of connection to an embedded derby database

[Bryan Pendleton]

I have used *EmbeddedConnectionPoolDataSource *class for connection pooling and 
I just want to check if my connection are actually being pooled.


You could attach a memory debugger, like the Eclipse Heap
Analysis Tool, and look at the histogram of classes, and
look at the number of instances of the classes in question.

For example, you could look for N instances of your high-level
connection class, and expect to see K instances of the lower-level
Derby connection class, where K is the number that you configured
into your pool.

thanks,

bryan

Re: How to log queries in Apache Derby?

[Bryan Pendleton]

 I launch derby by issuing the command (from $DERBY_HOME)

It's always been a bit of a trick to figure out which
directory the Network Server is trying to read your
properties from.

Does it work if you do:

  java -jar $DERBY_HOME/lib/derbyrun.jar -Dderby.language.logStatementText=true 
server start

If it's looking in the wrong directory, one thing to do
is to run 'sysinfo' against the Network Server.

http://db.apache.org/derby/docs/10.10/adminguide/tadminconfigsysteminformation.html

thanks,

bryan

Re: Configuring a Server Policy for Derby on Windows

[Bryan Pendleton]

On 1/31/2014 8:43 AM, John I. Moore, Jr. wrote:

One final point of clarification to my original email:


John, thanks for sharing all your findings, and thanks all of you
for the pointers to detailed information. I've tried to collect
it all at:

https://wiki.apache.org/db-derby/SecurityPolicyTips

thanks,

bryan

Re: How know databases connected with Derby networkServer

[Bryan Pendleton]

How do I know databases connected at a given moment ?
In order to :
- Have knowledge of potential users connected to each connected dataBase
- Shutdown these databases in order to remove them without shutDown derby.


You didn't say what platform you are on, but one simple way is to use
a tool like 'lsof' (on Linux) or 'ProcessExplorer' (on Windows) to
determine what files are open by your Network Server.

In particular, the lock files are important.

thanks,

bryan

Re: Creating tables - migrating from MySQL.

[Bryan Pendleton]

I'm trying to migrate a system (72 tables) from MySQL to Apache.


Have you considered using ddlutils: http://db.apache.org/ddlutils/

thanks,

bryan

Re: Can I run derby for a long time?

[Bryan Pendleton]

Can I run derby for a long time with many inserts, deletes and updates? Are the 
memories, disk spaces recovered after deletion/updating?


I have run Derby as the primary database for a
mid-size Build Automation application. The application
ran 24 hours/day, 7 days/week, querying and updating
the database from up to 100 concurrent clients.

Derby was extremely reliable, and we ran it for
many months at a time between restart, with no
resource leak problems.

As long as my application was careful to close all its
ResultSet, Statement, and Connection objects appropriately,
we never detected any memory leaks.

I did not monitor space reclamation *within* database
tables, because that was not a critical issue for
my particular usage pattern.

thanks,

bryan

Re: slow query? (from a practical newbie)

[Bryan Pendleton]

So I tried:

ij select * from ducc.Job where id  117000 order by stateIndex asc, id desc;


You're right; the ORDER BY has to be the final clause, not before the WHERE.
Thanks for correcting that.


And I see this query plan:


OK, I'm afraid I've forgotten the overall context. This query plan
looks just like the previous query plan, and in both cases they appear
to be using the index to restrict the number of rows searched.

Are the queries that we're discussing here running slowly for you?

According to the query plan, they're running quite efficiently.

thanks,

bryan

Re: slow query? (from a practical newbie)

[Bryan Pendleton]

On 1/7/2014 12:52 PM, degenaro wrote:

Bryan Pendleton-3 wrote

Are the queries that we're discussing here running slowly for you?


Yes, very slow.  With about 50,000 rows the query takes about 30 seconds
give or take.  But if I remove the where clause, the time is sub second.


Wow, that's crazy! That query should take about .03 seconds, not 30!

What happens if you change the query from

SELECT * FROM ...
to
SELECT COUNT(*) FROM ...

That is, select the count rather than the actual rows.

Also, instead of

SELECT * FROM ...
try
SELECT ID FROM ...

In both cases, I'm wondering if somehow the problem is in the output
phase, like there's a huge amount of output because there's a BLOB
column in your table or something like that, or the client side is
just taking an eternity to process the output.

Because the query plan indicates that you're just reading 57 pages,
and 50K rows, and that's nothing that should even cause Derby to
break a sweat.

thanks,

bryan

Re: slow query? (from a practical newbie)

[Bryan Pendleton]

select a.* from (select * from ducc.job order by stateIndex asc, id
desc) a where id  117000


Why is this query written like this, as opposed to, say:

  select * from ducc.job order by stateIndex asc, id desc
 where id  117000

Do you get a different query plan / run time if you use a
single top-level query and eliminate the sub-query?

I think the optimizer does a pretty good job in general of
optimizing sub queries but maybe the presence of the ORDER BY
in the sub query is making it hard for the query optimizer
to perform the query efficiently?

thanks,

bryan

Re: slow query? (from a practical newbie)

[Bryan Pendleton]

Ostensibly the where clause really slow does the query.  How come and how do I 
remedy?


Here's a good place to start:

http://wiki.apache.org/db-derby/PerformanceDiagnosisTips

thanks,

bryan

Re: JDK 8 and setObject

[Bryan Pendleton]

java.sql.SQLDataException: An attempt was made to get a data value of type 
'DATE' from a data value of type 'java.time.LocalDate'.


According to

http://download.java.net/jdk8/docs/api/java/sql/Date.html
http://download.java.net/jdk8/docs/api/java/util/Date.html
and
http://download.java.net/jdk8/docs/api/java/time/LocalDate.html

a java.sql.Date is a sub-class of java.util.Date, but a java.time.LocalDate
has no relationship to either of the above.

I don't see any information in the LocalDate JavaDoc that says
anything implying that a java.time.LocalDate is supposed to
be in any way interoperable with the java.util.Date class.

Is this the question you are asking?

thanks,

bryan

Re: Commiting several operations

[Bryan Pendleton]

In my code I am expecting to add a new record and delete the oldest record
each time the process is run
However the results I get are :-


I generally find these sorts of problems easiest to work out by
using an interactive SQL tool like Derby's IJ or the SquirrelSQL tool.

Another useful skill to develop is to run your Derby engine with
-Dderby.language.logStatementText=true, and then learn to read
your derby.log file, which will contain detailed information about
what actual statements your program is executing, and what commits
and rollbacks are occurring.

http://db.apache.org/derby/docs/10.7/ref/rrefproper43517.html

thanks,

bryan

Re: Problem updating a record

[Bryan Pendleton]

// Update this record by adding predicted return and predicted class
psUpdate = conn.prepareStatement(UPDATE USD_JPY SET
Return_predicted=?,Class_predicted=?);
statements.add(psUpdate);

psUpdate.setDouble(1, return_current);
psUpdate.setString(2, class_current);

conn.commit();

However, the record is NOT being updated as I am wishing it to be ?


For one thing, it seems like you should call executeUpdate() on
the prepared statement in order to execute it.

For another thing, since the UPDATE statement has no WHERE clause,
it's going to update every row in the table, not just a single one.

thanks,

bryan

Re: Problem retrieving the 'oldest' record

[Bryan Pendleton]

It would appear that my so-called 'date' field is not a good field to have
in a primary key!


You can have just about any datatype in your primary key.

The only hard requirement is that the primary key must be unique.

Using existing fields from your record which uniquely identify
your data is called using a natural primary key.

Another approach is to use a synthetic primary key by using
an abstract unique identifier for each row in your table.

Derby can assist with this by automatically generating values for
such a synthetic primary key.

Here's a great place to start learning about these issues:

http://www.agiledata.org/essays/keys.html

thanks,

bryan

Re: Commiting several operations

[Bryan Pendleton]

On 12/1/2013 3:44 PM, Bob M wrote:

I am wishing to commit several add, delete, update operations,


I'm not sure what your question is.

Are you asking about Connection.commit()? See:

http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#commit()

If you're unsure how to use commit(), start here:

http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html

thanks,

bryan

Re: selecting records in a derby database

[Bryan Pendleton]

rs = s.executeQuery(SELECT COUNT(*) FROM TABLE_NAME);
int recordCount = ??;


The query returned a result set.

Call next() on the result set to move to the first (and only)
row in the result set.

Then you can call getInt() to get the integer value of the
first (and only) column in that row.

Here's a good place to start with your learning:

http://docs.oracle.com/javase/tutorial/jdbc/basics/

thanks,

bryan

Re: selecting records in a derby database

[Bryan Pendleton]

int recordCount = rs.getInt();

I get compilation error on last line

The method getInt(int) in the type ResultSet in not applicable for the
arguments ()


Indeed, you have to specify the columnIndex.

http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#getInt(int)

Specify getInt( 1 ), since there is one and only one
column in the query select count(*) from...

bryan