RE: CPU Utilization

[Pavel Bortnovskiy]

Thank you, Kristian, for your quick response.

How can I diagnose this and determine what Derby is doing during the periods of 
high CPU utilization?
As far as I can determine from watching the log and the top simultaneously, 
it seems that utilization peeks when data are accessed.
In other words, the query itself seems to run very fast, but accessing the data 
(with the JDBC's ResultSet) seems to peek the CPU usage.

P.

From: Kristian Waagan [mailto:kristian.waa...@oracle.com]
Sent: Wednesday, January 09, 2013 2:42 PM
To: derby-user@db.apache.org
Subject: Re: CPU Utilization

On 07.01.2013 20:51, Pavel Bortnovskiy wrote:
Hello:

This is more of a general question. Our application uses Derby in the in-memory 
mode only.
When the application is configured to use complex queries, such configuration 
causes CPU utilization (on the Linux server) to go as high as 300% or even 
higher. Using simpler queries don't seem to lead to such high utilization. Is 
there any way to control or lower CPU utilization by Derby?

Hi Pavel,

I don't believe there is such a mechanism in place, at least not specifically 
for the in-memory back end.

Since this is in-memory, the latency associated with page operations is very 
low. Do you know if Derby is using the CPU for something useful (i.e. 
processing queries effectively), or is the CPU spent on wasteful activities?
One potential wasteful activity is moving data back and forth from the page 
cache (page cache too small?).
Is this a highly concurrent scenario?


Regards,
--
Kristian



Thank you,
Pavel.

Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited. In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited. In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

CPU Utilization

[Pavel Bortnovskiy]

Hello:

This is more of a general question. Our application uses Derby in the in-memory 
mode only.
When the application is configured to use complex queries, such configuration 
causes CPU utilization (on the Linux server) to go as high as 300% or even 
higher. Using simpler queries don't seem to lead to such high utilization. Is 
there any way to control or lower CPU utilization by Derby?

Thank you,
Pavel.

Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited. In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

RE: number of parameters

[Pavel Bortnovskiy]

Thank you, Rick.

-Original Message-
From: Rick Hillegas [mailto:rick.hille...@oracle.com]
Sent: Thursday, January 03, 2013 11:14 AM
To: derby-user@db.apache.org
Subject: Re: number of parameters

On 12/27/12 1:34 PM, Pavel Bortnovskiy wrote:

 Hello:

 we have a need to create a java-mapped function which will take 92
 parameters, but we are running into Derby's limitation:

 Caused by: java.sql.SQLException: The limit for the number of
 parameters for a procedure has been exceeded. Limit is 90 and number
 of parameters for procedure CALC_VALUES are 92.

 http://db.apache.org/derby/docs/10.8/ref/rrefdbmlimits.html

 Can this limit either be increased or worked-around?

 Thanks,

 Pavel.



 Jefferies archives and monitors outgoing and incoming
 e-mail. The contents of this email, including any
 attachments, are confidential to the ordinary user of the
 email address to which it was addressed. If you are not
 the addressee of this email you may not copy, forward,
 disclose or otherwise use it or any part of it in any form
 whatsoever. This email may be produced at the request of
 regulators or in connection with civil litigation.
 Jefferies accepts no liability for any errors or omissions
 arising as a result of transmission. Use by other than
 intended recipients is prohibited. In the United Kingdom,
 Jefferies operates as Jefferies International Limited;
 registered in England: no. 1978621; registered office:
 Vintners Place, 68 Upper Thames Street, London EC4V 3BJ.
 Jefferies International Limited is authorised and
 regulated by the Financial Services Authority.

I agree with Knut that the limit is arbitrary and that it ought to be safe to 
remove this restriction. I have logged
https://issues.apache.org/jira/browse/DERBY-6033 to track this issue.
This is probably a trivial change.

Thanks,
-Rick

Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited. In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

RE: number of parameters

[Pavel Bortnovskiy]

 [mailto:kosurusek...@gmail.com]
Sent: Friday, December 28, 2012 1:22 AM
To: derby-user@db.apache.org
Subject: Re: number of parameters


Pavel Bortnovskiy pbortnovskiy@... writes:




 Hello:

 we have a need to create a java-mapped function which will take 92
parameters, but we are running into Derby’s limitation:

 Caused by: java.sql.SQLException: The limit for the number of
 parameters for
a procedure has been exceeded. Limit is 90 and number of parameters for 
procedure
  CALC_VALUES are 92.

 http://db.apache.org/derby/docs/10.8/ref/rrefdbmlimits.html

 Can this limit either be increased or worked-around?

 Thanks,
 Pavel.



Hi Pavel

The best solution is rather than passing 92 method parameters we can create 
User Defined Type in Derby. An UDT will point to external java class where we 
can have all parameters as properties. now you can use this class as a single 
parameter.


Regards
Sekhar.





Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited. In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

number of parameters

[Pavel Bortnovskiy]

Hello:

we have a need to create a java-mapped function which will take 92 parameters, 
but we are running into Derby's limitation:

Caused by: java.sql.SQLException: The limit for the number of parameters for a 
procedure has been exceeded. Limit is 90 and number of parameters for procedure 
CALC_VALUES are 92.

http://db.apache.org/derby/docs/10.8/ref/rrefdbmlimits.html

Can this limit either be increased or worked-around?

Thanks,
Pavel.

Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited. In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

RE: what do errors like these mean?

[Pavel Bortnovskiy]

Thank you, Knut, for your prompt response.

It seems that my caching of Prepared Statements is causing some problems.
In some previous responses, it was indicated that Derby is caching them 
internally anyway, so maybe a better approach for me is not to cache them on my 
side and create them anew? Most of my inserts/updates are done in batches, so I 
could create a PrepStmt before the batch and remove a reference to it at the 
end of the batch's execution. If the performance penalty for compilation of 
PrepStmt is not that great, then such approach may be more desirable to avoid 
errors in the production environment.

Thanks,
P.

-Original Message-
From: Knut Anders Hatlen [mailto:knut.hat...@oracle.com]
Sent: Friday, December 21, 2012 8:59 AM
To: Derby Discussion
Subject: Re: what do errors like these mean?

Pavel Bortnovskiy pbortnovs...@jefferies.com writes:

 Once in a while, I see the following errors. What may cause them?

 java.sql.SQLException: Container 1,329 not found.

The error means that one of the database files (table or index) cannot be 
found. It typically happens because some DDL operation (for example DROP INDEX, 
TRUNCATE TABLE or SYSCS_COMPRESS_TABLE) has removed the file, and an already 
compiled statement still references it.

The error indicates a bug in Derby, so if you find a way to reproduce it, or 
some pattern that seems to increase the likelihood of the error, please file a 
bug report.

Derby should invalidate already compiled statement referencing the table when 
DDL is performed on it, and that should make the statement recompile 
automatically the next time it is executed. There have been bugs in that area, 
though. (We fixed some of them in 10.9.1.0, in case you haven't already 
upgraded.)

One possible workaround in that case is to call the 
SYSCS_UTIL.SYSCS_EMPTY_STATEMENT_CACHE procedure to remove the stale query 
plans from the statement cache.

--
Knut Anders

Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited. In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

RE: what do errors like these mean?

[Pavel Bortnovskiy]

I'm using an embedded driver with Derby in the in-memory only mode.
My application creates in-memory tables in Derby during its run, which are then 
accessed by various components through JDBC.

-Original Message-
From: Kristian Waagan [mailto:kristian.waa...@oracle.com]
Sent: Friday, December 21, 2012 11:38 AM
To: derby-user@db.apache.org
Subject: Re: what do errors like these mean?

On 21.12.2012 16:18, Pavel Bortnovskiy wrote:
 Thank you, Knut, for your prompt response.

 It seems that my caching of Prepared Statements is causing some problems.
 In some previous responses, it was indicated that Derby is caching them 
 internally anyway, so maybe a better approach for me is not to cache them on 
 my side and create them anew? Most of my inserts/updates are done in batches, 
 so I could create a PrepStmt before the batch and remove a reference to it at 
 the end of the batch's execution. If the performance penalty for compilation 
 of PrepStmt is not that great, then such approach may be more desirable to 
 avoid errors in the production environment.

Hi Pavel,

Are you using the embedded driver or the client driver?
The client driver can cache statements on the client side if you use the 
ConnectionPoolDataSource. This may save you some round-trips, but note that 
this caching is in addition to the caching that happens on the server side.


--
Kristian


 Thanks,
 P.

 -Original Message-
 From: Knut Anders Hatlen [mailto:knut.hat...@oracle.com]
 Sent: Friday, December 21, 2012 8:59 AM
 To: Derby Discussion
 Subject: Re: what do errors like these mean?

 Pavel Bortnovskiy pbortnovs...@jefferies.com writes:

 Once in a while, I see the following errors. What may cause them?

 java.sql.SQLException: Container 1,329 not found.
 The error means that one of the database files (table or index) cannot be 
 found. It typically happens because some DDL operation (for example DROP 
 INDEX, TRUNCATE TABLE or SYSCS_COMPRESS_TABLE) has removed the file, and an 
 already compiled statement still references it.

 The error indicates a bug in Derby, so if you find a way to reproduce it, or 
 some pattern that seems to increase the likelihood of the error, please file 
 a bug report.

 Derby should invalidate already compiled statement referencing the
 table when DDL is performed on it, and that should make the statement
 recompile automatically the next time it is executed. There have been
 bugs in that area, though. (We fixed some of them in 10.9.1.0, in case
 you haven't already upgraded.)

 One possible workaround in that case is to call the 
 SYSCS_UTIL.SYSCS_EMPTY_STATEMENT_CACHE procedure to remove the stale query 
 plans from the statement cache.

 --
 Knut Anders

 Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
 this email, including any attachments, are confidential to the ordinary user 
 of the email address to which it was addressed. If you are not the addressee 
 of this email you may not copy, forward, disclose or otherwise use it or any 
 part of it in any form whatsoever. This email may be produced at the request 
 of regulators or in connection with civil litigation. Jefferies accepts no 
 liability for any errors or omissions arising as a result of transmission. 
 Use by other than intended recipients is prohibited. In the United Kingdom, 
 Jefferies operates as Jefferies International Limited; registered in England: 
 no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, 
 London EC4V 3BJ. Jefferies International Limited is authorised and regulated 
 by the Financial Services Authority.


Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited. In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

what do errors like these mean?

[Pavel Bortnovskiy]

Once in a while, I see the following errors. What may cause them?

java.sql.SQLException: Container 1,329 not found.
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown 
Source)
at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown 
Source)
at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown 
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown 
Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown 
Source)
at 
org.apache.derby.impl.jdbc.EmbedResultSet.closeOnTransactionError(Unknown 
Source)
at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown 
Source)
at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source)

Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited. In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

RE: NPE

[Pavel Bortnovskiy]

Dag, et al. if I may add a bit of color to what's happening...
I've done lots of various tests (unfortunately, still unable to distill it down 
to an easy-to-reproduce tester).
And what I discovered is baffling...

The issue seems to occur (with 100% certainty) under the following 
circumstances:
(1) two db connections - one to Derby (in-memory only mode) and Oracle
(2) a prepared statement is executed (such as select * from table) against 
Derby
(3) Oracle is queried for tables with:
final ResultSet resultSet = databaseMetaData.getTables(null, null, null, new 
String[]{TABLE});
(4) if the result set from (2) is iterated over AFTER the (3) then Derby fails 
with the described NPE; however, if it is iterated over BEFORE (3), then OK!

It may also be worth noting that my resultsets are made rewindable, i.e. with:
statement = connection.prepareStatement(
sql,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);


Finally: although I am completely perplexed as to what's causing this problem, 
I found a work-around which, in my estimation, will have minimal impact: at a 
particular point in my code (but effectively before calling getTables() in 
(3)), the result set from (2) is iterated to the first record and then rewound:

(not exact code, but that's what effectively is occurring):

If (resultSet.next()) {
   resultSet.beforeFirst();
}

As odd as it sounds, it seems to have fixed the issue...

I am planning to return to this as time allows. Then I may be able to create a 
stand-alone tester.
But if you have any thoughts as to what might be going on, I would be most 
appreciative.

Regards,
Pavel.

-Original Message-
From: Dag Wanvik [mailto:dag.wan...@oracle.com]
Sent: Monday, December 10, 2012 3:15 PM
To: derby-user@db.apache.org
Subject: Re: NPE


On 10.12.2012 19:21, Pavel Bortnovskiy wrote:
 I upgraded to 10.9.1.0 and rerun with debug libraries (please disregard my 
 previous message).

 Mon Dec 10 13:19:44 EST 2012
 Thread[DatabasePublisher[BOND_WRITER],5,main] (XID = 289), (SESSIONID = 5), 
 (DATABASE = memory:test), (DRDAID = null), Failed Statement is: select *  
 from READER java.lang.NullPointerException
 at 
 org.apache.derby.impl.store.access.conglomerate.GenericScanController.reopenAfterEndTransaction(GenericScanController.java:1048)
 at

It looks like container is null, cf. the line 1048:

case SCAN_HOLD_INIT:
*** reusableRecordIdSequenceNumber =
container.getReusableRecordIdSequenceNumber();

i.e. has been closed, cf the assignment just above:

ContainerHandle container = open_conglom.reopen();

The call to reopen just returns null if OpenConglomerate has been closed 
somehow.

Smells like a race condition of sorts, maybe some missing synchronization in 
the Derby code.
If you are able to post a self contained repro program, although this might be 
tricky if this is indeed a race, this would increase your chances of having 
this fixed soon..

Thanks,
Dag


 In any case the NullPointerException shoudln't be seen by the application 
code, so this is a bug.


 org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchRows(GenericScanController.java:622)
 at 
 org.apache.derby.impl.store.access.heap.HeapScan.fetchNextGroup(HeapScan.java:322)
 at 
 org.apache.derby.impl.sql.execute.BulkTableScanResultSet.reloadArray(BulkTableScanResultSet.java:353)
 at 
 org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCore(BulkTableScanResultSet.java:308)
 at 
 org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(ProjectRestrictResultSet.java:261)
 at 
 org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowFromSource(ScrollInsensitiveResultSet.java:801)
 at 
 org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowCore(ScrollInsensitiveResultSet.java:518)
 at 
 org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(BasicNoPutResultSetImpl.java:478)
 at 
 org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:432)
 at 
 org.apache.derby.impl.jdbc.EmbedResultSet.next(EmbedResultSet.java:376)
 at QueryResults$1.hasNext(QueryResults.java:268)
 at DatabasePublisher.process(DatabasePublisher.java:318)
 at java.lang.Thread.run(Thread.java:662)

 -Original Message-
 From: Rick Hillegas [mailto:rick.hille...@oracle.com]
 Sent: Monday, December 10, 2012 1:05 PM
 To: derby-user@db.apache.org
 Subject: Re: NPE

 Hi Pavel,

 Can you reproduce this problem with the debug version of Derby? That would 
 give us line numbers in the stack trace.

 Thanks,
 -Rick

 On 12/10/12 9:23 AM, Pavel Bortnovskiy wrote:
 Hello:

 While testing my application, which uses Derby in memory-only mode, I
 get NPE inside Derby under mysterious circumstances

NPE

[Pavel Bortnovskiy]

Hello:

While testing my application, which uses Derby in memory-only mode, I get NPE 
inside Derby under mysterious circumstances.
At this point, I can't even figure out what's triggering it and where this 
could happen, but let me preempt my troubleshooting with some stack traces. 
Perhaps someone, with good knowledge of Derby's code, may offer some 
explanation. Here are some stack traces:

Mon Dec 10 12:12:36 EST 2012 Thread[DatabasePublisher[WRITER],5,main] (XID = 
235), (SESSIONID = 5), (DATABASE = memory:test), (DRDAID = null), Failed 
Statement is: select * from READER
java.lang.NullPointerException
   at 
org.apache.derby.impl.store.access.conglomerate.GenericScanController.reopenAfterEndTransaction(Unknown
 Source)
   at 
org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchRows(Unknown
 Source)
   at 
org.apache.derby.impl.store.access.heap.HeapScan.fetchNextGroup(Unknown Source)
   at 
org.apache.derby.impl.sql.execute.BulkTableScanResultSet.reloadArray(Unknown 
Source)
   at 
org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCore(Unknown 
Source)
   at 
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown
 Source)
   at 
org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowFromSource(Unknown
 Source)
   at 
org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowCore(Unknown
 Source)
   at 
org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(Unknown 
Source)
   at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown Source)
   at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source)
   at QueryResults$1.hasNext(QueryResults.java:268)
   at DatabasePublisher.process(DatabasePublisher.java:318)
   at java.lang.Thread.run(Thread.java:662)

or

2012-12-10 12:12:36,537 [DatabasePublisher[WRITER]] ERROR QueryResults  - 
Unable to execute ResultSet.next() for Statement select * from READER 
[DERBY]
java.sql.SQLException: Java exception: ': java.lang.NullPointerException'.
   at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
   at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
   at org.apache.derby.impl.jdbc.Util.javaException(Unknown Source)
   at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown 
Source)
   at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown 
Source)
   at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown 
Source)
   at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown 
Source)
   at 
org.apache.derby.impl.jdbc.EmbedResultSet.closeOnTransactionError(Unknown 
Source)
   at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown Source)
   at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source)
   at QueryResults$1.hasNext(QueryResults.java:268)
   at DatabasePublisher.process(DatabasePublisher.java:318)
   at java.lang.Thread.run(Thread.java:662)
Caused by: java.sql.SQLException: Java exception: ': 
java.lang.NullPointerException'.
   at 
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
   at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
 Source)
   ... 14 more
Caused by: java.lang.NullPointerException
   at 
org.apache.derby.impl.store.access.conglomerate.GenericScanController.reopenAfterEndTransaction(Unknown
 Source)
   at 
org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchRows(Unknown
 Source)
   at 
org.apache.derby.impl.store.access.heap.HeapScan.fetchNextGroup(Unknown Source)
   at 
org.apache.derby.impl.sql.execute.BulkTableScanResultSet.reloadArray(Unknown 
Source)
   at 
org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCore(Unknown 
Source)
   at 
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown
 Source)
   at 
org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowFromSource(Unknown
 Source)
   at 
org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowCore(Unknown
 Source)
   at 
org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(Unknown 
Source)
   ... 6 more


One more thing I can share is that this seems to also be a timing problem - if 
the code is executed slowly, line-by-line in the debugger, then this problem 
doesn't occur. But if run at full speed, then these exceptions are thrown. The 
problem is not intermittent and easily reproduced 100% of the time on my end.

Any thoughts would be greatly appreciated.

Pavel.

Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was 

RE: NPE

[Pavel Bortnovskiy]

Hello, Rick:

Thank you for your prompt response.
I'm currently using Derby 10.8.2.2.
Should I use the same version or upgrade?

Can you please also advise which debug jars I can use?

Thx,
Pavel.

-Original Message-
From: Rick Hillegas [mailto:rick.hille...@oracle.com]
Sent: Monday, December 10, 2012 1:05 PM
To: derby-user@db.apache.org
Subject: Re: NPE

Hi Pavel,

Can you reproduce this problem with the debug version of Derby? That would give 
us line numbers in the stack trace.

Thanks,
-Rick

On 12/10/12 9:23 AM, Pavel Bortnovskiy wrote:

 Hello:

 While testing my application, which uses Derby in memory-only mode, I
 get NPE inside Derby under mysterious circumstances.

 At this point, I can't even figure out what's triggering it and where
 this could happen, but let me preempt my troubleshooting with some
 stack traces. Perhaps someone, with good knowledge of Derby's code,
 may offer some explanation. Here are some stack traces:

 Mon Dec 10 12:12:36 EST 2012 Thread[DatabasePublisher[WRITER],5,main]
 (XID = 235), (SESSIONID = 5), (DATABASE = memory:test), (DRDAID =
 null), Failed Statement is: select * from READER

 java.lang.NullPointerException

 at
 org.apache.derby.impl.store.access.conglomerate.GenericScanController.
 reopenAfterEndTransaction(Unknown
 Source)

 at
 org.apache.derby.impl.store.access.conglomerate.GenericScanController.
 fetchRows(Unknown
 Source)

 at
 org.apache.derby.impl.store.access.heap.HeapScan.fetchNextGroup(Unknow
 n Source)

 at
 org.apache.derby.impl.sql.execute.BulkTableScanResultSet.reloadArray(U
 nknown
 Source)

 at
 org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCor
 e(Unknown
 Source)

 at
 org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowC
 ore(Unknown
 Source)

 at
 org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRo
 wFromSource(Unknown
 Source)

 at
 org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRo
 wCore(Unknown
 Source)

 at
 org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(U
 nknown
 Source)

 at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown
 Source)

 at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source)

 at QueryResults$1.hasNext(QueryResults.java:268)

 at DatabasePublisher.process(DatabasePublisher.java:318)

 at java.lang.Thread.run(Thread.java:662)

 or

 2012-12-10 12:12:36,537 [DatabasePublisher[WRITER]] ERROR QueryResults
 - Unable to execute ResultSet.next() for Statement select * from
 READER [DERBY]

 java.sql.SQLException: Java exception: ': java.lang.NullPointerException'.

 at
 org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unkno
 wn
 Source)

 at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown
 Source)

 at org.apache.derby.impl.jdbc.Util.javaException(Unknown Source)

 at
 org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(
 Unknown
 Source)

 at
 org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unk
 nown
 Source)

 at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
 Source)

 at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
 Source)

 at
 org.apache.derby.impl.jdbc.EmbedResultSet.closeOnTransactionError(Unkn
 own
 Source)

 at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown
 Source)

 at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source)

 at QueryResults$1.hasNext(QueryResults.java:268)

 at DatabasePublisher.process(DatabasePublisher.java:318)

 at java.lang.Thread.run(Thread.java:662)

 Caused by: java.sql.SQLException: Java exception: ':
 java.lang.NullPointerException'.

 at
 org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
 Source)

 at
 org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportA
 crossDRDA(Unknown
 Source)

 ... 14 more

 Caused by: java.lang.NullPointerException

 at
 org.apache.derby.impl.store.access.conglomerate.GenericScanController.
 reopenAfterEndTransaction(Unknown
 Source)

 at
 org.apache.derby.impl.store.access.conglomerate.GenericScanController.
 fetchRows(Unknown
 Source)

 at
 org.apache.derby.impl.store.access.heap.HeapScan.fetchNextGroup(Unknow
 n Source)

 at
 org.apache.derby.impl.sql.execute.BulkTableScanResultSet.reloadArray(U
 nknown
 Source)

 at
 org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCor
 e(Unknown
 Source)

 at
 org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowC
 ore(Unknown
 Source)

 at
 org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRo
 wFromSource(Unknown
 Source)

 at
 org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRo
 wCore(Unknown
 Source)

 at
 org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(U
 nknown
 Source)

 ... 6 more

 One more thing I can share is that this seems to also be a timing
 problem - if the code is executed slowly, line-by-line in the
 debugger

RE: NPE

[Pavel Bortnovskiy]

I upgraded to 10.9.1.0 and rerun with debug libraries (please disregard my 
previous message).

Mon Dec 10 13:19:44 EST 2012 Thread[DatabasePublisher[BOND_WRITER],5,main] (XID 
= 289), (SESSIONID = 5), (DATABASE = memory:test), (DRDAID = null), Failed 
Statement is: select *  from READER
java.lang.NullPointerException
at 
org.apache.derby.impl.store.access.conglomerate.GenericScanController.reopenAfterEndTransaction(GenericScanController.java:1048)
at 
org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchRows(GenericScanController.java:622)
at 
org.apache.derby.impl.store.access.heap.HeapScan.fetchNextGroup(HeapScan.java:322)
at 
org.apache.derby.impl.sql.execute.BulkTableScanResultSet.reloadArray(BulkTableScanResultSet.java:353)
at 
org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCore(BulkTableScanResultSet.java:308)
at 
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(ProjectRestrictResultSet.java:261)
at 
org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowFromSource(ScrollInsensitiveResultSet.java:801)
at 
org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowCore(ScrollInsensitiveResultSet.java:518)
at 
org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(BasicNoPutResultSetImpl.java:478)
at 
org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:432)
at 
org.apache.derby.impl.jdbc.EmbedResultSet.next(EmbedResultSet.java:376)
at QueryResults$1.hasNext(QueryResults.java:268)
at DatabasePublisher.process(DatabasePublisher.java:318)
at java.lang.Thread.run(Thread.java:662)

-Original Message-
From: Rick Hillegas [mailto:rick.hille...@oracle.com]
Sent: Monday, December 10, 2012 1:05 PM
To: derby-user@db.apache.org
Subject: Re: NPE

Hi Pavel,

Can you reproduce this problem with the debug version of Derby? That would give 
us line numbers in the stack trace.

Thanks,
-Rick

On 12/10/12 9:23 AM, Pavel Bortnovskiy wrote:

 Hello:

 While testing my application, which uses Derby in memory-only mode, I
 get NPE inside Derby under mysterious circumstances.

 At this point, I can't even figure out what's triggering it and where
 this could happen, but let me preempt my troubleshooting with some
 stack traces. Perhaps someone, with good knowledge of Derby's code,
 may offer some explanation. Here are some stack traces:

 Mon Dec 10 12:12:36 EST 2012 Thread[DatabasePublisher[WRITER],5,main]
 (XID = 235), (SESSIONID = 5), (DATABASE = memory:test), (DRDAID =
 null), Failed Statement is: select * from READER

 java.lang.NullPointerException

 at
 org.apache.derby.impl.store.access.conglomerate.GenericScanController.
 reopenAfterEndTransaction(Unknown
 Source)

 at
 org.apache.derby.impl.store.access.conglomerate.GenericScanController.
 fetchRows(Unknown
 Source)

 at
 org.apache.derby.impl.store.access.heap.HeapScan.fetchNextGroup(Unknow
 n Source)

 at
 org.apache.derby.impl.sql.execute.BulkTableScanResultSet.reloadArray(U
 nknown
 Source)

 at
 org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCor
 e(Unknown
 Source)

 at
 org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowC
 ore(Unknown
 Source)

 at
 org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRo
 wFromSource(Unknown
 Source)

 at
 org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRo
 wCore(Unknown
 Source)

 at
 org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(U
 nknown
 Source)

 at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown
 Source)

 at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source)

 at QueryResults$1.hasNext(QueryResults.java:268)

 at DatabasePublisher.process(DatabasePublisher.java:318)

 at java.lang.Thread.run(Thread.java:662)

 or

 2012-12-10 12:12:36,537 [DatabasePublisher[WRITER]] ERROR QueryResults
 - Unable to execute ResultSet.next() for Statement select * from
 READER [DERBY]

 java.sql.SQLException: Java exception: ': java.lang.NullPointerException'.

 at
 org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unkno
 wn
 Source)

 at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown
 Source)

 at org.apache.derby.impl.jdbc.Util.javaException(Unknown Source)

 at
 org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(
 Unknown
 Source)

 at
 org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unk
 nown
 Source)

 at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
 Source)

 at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
 Source)

 at
 org.apache.derby.impl.jdbc.EmbedResultSet.closeOnTransactionError(Unkn
 own
 Source)

 at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown
 Source)

 at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source

RE: concurrent execution of Statement's .addBatch() and .executeBatch()

[Pavel Bortnovskiy]

Does Derby use some kind of PrepStatement pooling?

-Original Message-
From: Dag Wanvik [mailto:dag.wan...@oracle.com]
Sent: Monday, December 10, 2012 2:53 PM
To: derby-user@db.apache.org
Subject: Re: concurrent execution of Statement's .addBatch() and .executeBatch()


On 07.12.2012 16:53, Pavel Bortnovskiy wrote:
 Thank you, Knut.
 It has been my assumption that creation of PreparedStatement is expensive. 
 Thus I create cached Prepared Statements (such as INSERT, UPDATE, DELETE, 
 SELECT COUNT(*) FROM table) and then use them. Would it be an improvement 
 in my design to create multiple PreparedStatements for those (assuring proper 
 addition of objects and execution)? Would you still recommend caching and 
 reusing them or creating them anew?
Please also note that as long as the statements are textually the same, i.e. 
arguments are given dynamically as ? , Derby is able to use its internal 
cache to avoid SQL compilation for more than the first thread, even if each of 
the thread has a created a separate PreparedStatement in its own connection. Cf.
http://db.apache.org/derby/docs/10.9/ref/rrefproperstatementcachesize.html

Thanks,
Dag


 For instance, if I have 3,000 records to insert, I could split them into 
 6x500 batches, create 6 PreparedStatements and have each of them execute 
 their corresponding batches in parallel.

 Thank you,
 Pavel.

 -Original Message-
 From: Knut Anders Hatlen [mailto:knut.hat...@oracle.com]
 Sent: Friday, December 07, 2012 7:01 AM
 To: Derby Discussion
 Subject: Re: concurrent execution of Statement's .addBatch() and
 .executeBatch()

 Pavel Bortnovskiy pbortnovs...@jefferies.com writes:

 Hello:

 Is it safe to call .addBatch() and .executeBatch() methods from
 multiple threads on the same PreparedStatement?

 Simple example: batching and executing a large number of the same
 statements (with the same PreparedStatement) by using ExecutorService.
 Hi Pavel,

 Both addBatch() and executeBatch() do their work in a block synchronized on 
 the connection, so in theory it should work to have many threads adding 
 batches to the same PreparedStatement. I don't think it has been heavily 
 tested, though.

 And if the threads set any parameters on the PreparedStatement, your
 application needs to synchronize the threads manually. For example, if
 two threads call

   ps.setInt(1, id);
   ps.setObject(2, value);
   ps.addBatch();

 on the same statement, you need to add synchronization to ensure that the 
 addBatch() call in one thread doesn't end up using one or more parameter 
 values set by the other thread.

 --
 Knut Anders

 Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
 this email, including any attachments, are confidential to the ordinary user 
 of the email address to which it was addressed. If you are not the addressee 
 of this email you may not copy, forward, disclose or otherwise use it or any 
 part of it in any form whatsoever. This email may be produced at the request 
 of regulators or in connection with civil litigation. Jefferies accepts no 
 liability for any errors or omissions arising as a result of transmission. 
 Use by other than intended recipients is prohibited. In the United Kingdom, 
 Jefferies operates as Jefferies International Limited; registered in England: 
 no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, 
 London EC4V 3BJ. Jefferies International Limited is authorised and regulated 
 by the Financial Services Authority.


Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited. In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

RE: NPE

[Pavel Bortnovskiy]

Thanks, Dag. Yeah, I wish I could figure out how to make a self-contained test 
prog which can reproduce it. I will try.
Although I do agree with you that it looks like a race condition, I can 
reproduce it (within my program)
with 100% certainty - every time. However, it might be worth stating the 
following:
my program is written as plug-n-play modules. When I plug in any other module, 
the error never occurs (or is well-masked).
It's only with this newly written module, that it started to fail.

In the meantime, what might be causing the container to be NULL?
Is it possible that there is a bug in my program which sets it to NULL (or does 
not assign it) in such a way that other parts of Derby do not fail nor notice 
that it is NULL?

-Original Message-
From: Dag Wanvik [mailto:dag.wan...@oracle.com]
Sent: Monday, December 10, 2012 3:15 PM
To: derby-user@db.apache.org
Subject: Re: NPE


On 10.12.2012 19:21, Pavel Bortnovskiy wrote:
 I upgraded to 10.9.1.0 and rerun with debug libraries (please disregard my 
 previous message).

 Mon Dec 10 13:19:44 EST 2012
 Thread[DatabasePublisher[BOND_WRITER],5,main] (XID = 289), (SESSIONID = 5), 
 (DATABASE = memory:test), (DRDAID = null), Failed Statement is: select *  
 from READER java.lang.NullPointerException
 at 
 org.apache.derby.impl.store.access.conglomerate.GenericScanController.reopenAfterEndTransaction(GenericScanController.java:1048)
 at

It looks like container is null, cf. the line 1048:

case SCAN_HOLD_INIT:
*** reusableRecordIdSequenceNumber =
container.getReusableRecordIdSequenceNumber();

i.e. has been closed, cf the assignment just above:

ContainerHandle container = open_conglom.reopen();

The call to reopen just returns null if OpenConglomerate has been closed 
somehow.

Smells like a race condition of sorts, maybe some missing synchronization in 
the Derby code.
If you are able to post a self contained repro program, although this might be 
tricky if this is indeed a race, this would increase your chances of having 
this fixed soon..

Thanks,
Dag


 In any case the NullPointerException shoudln't be seen by the application 
code, so this is a bug.


 org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchRows(GenericScanController.java:622)
 at 
 org.apache.derby.impl.store.access.heap.HeapScan.fetchNextGroup(HeapScan.java:322)
 at 
 org.apache.derby.impl.sql.execute.BulkTableScanResultSet.reloadArray(BulkTableScanResultSet.java:353)
 at 
 org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCore(BulkTableScanResultSet.java:308)
 at 
 org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(ProjectRestrictResultSet.java:261)
 at 
 org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowFromSource(ScrollInsensitiveResultSet.java:801)
 at 
 org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowCore(ScrollInsensitiveResultSet.java:518)
 at 
 org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(BasicNoPutResultSetImpl.java:478)
 at 
 org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:432)
 at 
 org.apache.derby.impl.jdbc.EmbedResultSet.next(EmbedResultSet.java:376)
 at QueryResults$1.hasNext(QueryResults.java:268)
 at DatabasePublisher.process(DatabasePublisher.java:318)
 at java.lang.Thread.run(Thread.java:662)

 -Original Message-
 From: Rick Hillegas [mailto:rick.hille...@oracle.com]
 Sent: Monday, December 10, 2012 1:05 PM
 To: derby-user@db.apache.org
 Subject: Re: NPE

 Hi Pavel,

 Can you reproduce this problem with the debug version of Derby? That would 
 give us line numbers in the stack trace.

 Thanks,
 -Rick

 On 12/10/12 9:23 AM, Pavel Bortnovskiy wrote:
 Hello:

 While testing my application, which uses Derby in memory-only mode, I
 get NPE inside Derby under mysterious circumstances.

 At this point, I can't even figure out what's triggering it and where
 this could happen, but let me preempt my troubleshooting with some
 stack traces. Perhaps someone, with good knowledge of Derby's code,
 may offer some explanation. Here are some stack traces:

 Mon Dec 10 12:12:36 EST 2012 Thread[DatabasePublisher[WRITER],5,main]
 (XID = 235), (SESSIONID = 5), (DATABASE = memory:test), (DRDAID =
 null), Failed Statement is: select * from READER

 java.lang.NullPointerException

 at
 org.apache.derby.impl.store.access.conglomerate.GenericScanController.
 reopenAfterEndTransaction(Unknown
 Source)

 at
 org.apache.derby.impl.store.access.conglomerate.GenericScanController.
 fetchRows(Unknown
 Source)

 at
 org.apache.derby.impl.store.access.heap.HeapScan.fetchNextGroup(Unkno
 w
 n Source)

 at
 org.apache.derby.impl.sql.execute.BulkTableScanResultSet.reloadArray(
 U
 nknown
 Source)

 at
 org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCo
 r
 e

RE: concurrent execution of Statement's .addBatch() and .executeBatch()

[Pavel Bortnovskiy]

Thank you, Knut.

Yes, my app is doing precisely that, so I may have to use a single thread to 
operate on the same PreparedStatement, or serialize access to it that only 
one thread may set objects and execute.

May I ask a related question, then?

It has been my assumption that creation of PreparedStatement is expensive. Thus 
I create cached Prepared Statements (such as INSERT, UPDATE, DELETE, SELECT 
COUNT(*) FROM table) and then use them. Would it be an improvement in my 
design to create multiple PreparedStatements for those (assuring proper 
addition of objects and execution)? Would you still recommend caching and 
reusing them or creating them anew?

For instance, if I have 3,000 records to insert, I could split them into 6x500 
batches, create 6 PreparedStatements and have each of them execute their 
corresponding batches in parallel.

Thank you,
Pavel.

-Original Message-
From: Knut Anders Hatlen [mailto:knut.hat...@oracle.com]
Sent: Friday, December 07, 2012 7:01 AM
To: Derby Discussion
Subject: Re: concurrent execution of Statement's .addBatch() and .executeBatch()

Pavel Bortnovskiy pbortnovs...@jefferies.com writes:

 Hello:

 Is it safe to call .addBatch() and .executeBatch() methods from
 multiple threads on the same PreparedStatement?

 Simple example: batching and executing a large number of the same
 statements (with the same PreparedStatement) by using ExecutorService.

Hi Pavel,

Both addBatch() and executeBatch() do their work in a block synchronized on the 
connection, so in theory it should work to have many threads adding batches to 
the same PreparedStatement. I don't think it has been heavily tested, though.

And if the threads set any parameters on the PreparedStatement, your 
application needs to synchronize the threads manually. For example, if two 
threads call

  ps.setInt(1, id);
  ps.setObject(2, value);
  ps.addBatch();

on the same statement, you need to add synchronization to ensure that the 
addBatch() call in one thread doesn't end up using one or more parameter values 
set by the other thread.

--
Knut Anders

Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited. In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

concurrent execution of Statement's .addBatch() and .executeBatch()

[Pavel Bortnovskiy]

Hello:

Is it safe to call .addBatch() and .executeBatch() methods from multiple 
threads on the same PreparedStatement?
Simple example: batching and executing a large number of the same statements 
(with the same PreparedStatement) by using ExecutorService.

Thank you,
Pavel.

Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited. In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

Guidance/Help/Book/References?

[Pavel Bortnovskiy]

Hello, all:

Derby is used heavily in my project and its tables are frequently accessed 
concurrently by multiple threads. Some threads update one or several tables, 
while other threads perform run select statements against those. I've written 
to this group several times whenever errors occurred, but some of those message 
have either been ignored or contained references to fairly short explanations 
on the website. So, the problems continue and it is difficult to find the 
proper solution.

All problems happen due to the concurrent access to tables. For instance, 
today's problem had to do with one thread updating one table (the table is 
first truncated and then re-populated to stay in sync with the source of data), 
while the other thread was trying to create a PreparedStatement. So, while one 
thread was truncating a table, another threw this exception (probably caused by 
nested select statements):

java.sql.SQLException: The conglomerate (1,280) requested does not exist.
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown 
Source)
at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown 
Source)
at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown 
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown 
Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown 
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.init(Unknown 
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.init(Unknown 
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.init(Unknown 
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.init(Unknown 
Source)
at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown 
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown 
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown 
Source)
at 
com.jefco.fi.commons.database.SqlSyntaxStatement.init(SqlSyntaxStatement.java:76)
at 
com.jefco.fi.commons.database.SqlSyntaxStatement.init(SqlSyntaxStatement.java:44)
   ...
Caused by: java.sql.SQLException: The conglomerate (1,280) requested does not 
exist.
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
 Source)
... 19 more
Caused by: ERROR XSAI2: The conglomerate (1,280) requested does not exist.
at org.apache.derby.iapi.error.StandardException.newException(Unknown 
Source)
at 
org.apache.derby.impl.store.access.heap.HeapConglomerateFactory.readConglomerate(Unknown
 Source)
at 
org.apache.derby.impl.store.access.RAMAccessManager.conglomCacheFind(Unknown 
Source)
at 
org.apache.derby.impl.store.access.RAMTransaction.findExistingConglomerate(Unknown
 Source)
at 
org.apache.derby.impl.store.access.RAMTransaction.getStaticCompiledConglomInfo(Unknown
 Source)
at 
org.apache.derby.impl.sql.compile.BaseJoinStrategy.fillInScanArgs1(Unknown 
Source)
at 
org.apache.derby.impl.sql.compile.HashJoinStrategy.getScanArgs(Unknown Source)
at 
org.apache.derby.impl.sql.compile.FromBaseTable.getScanArguments(Unknown Source)
at 
org.apache.derby.impl.sql.compile.FromBaseTable.generateResultSet(Unknown 
Source)
at org.apache.derby.impl.sql.compile.FromBaseTable.generate(Unknown 
Source)
at 
org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(Unknown 
Source)
at 
org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(Unknown Source)
at 
org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(Unknown 
Source)
at 
org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(Unknown Source)
at org.apache.derby.impl.sql.compile.GroupByNode.generate(Unknown 
Source)
at 
org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(Unknown 
Source)
at 
org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(Unknown Source)
at 
org.apache.derby.impl.sql.compile.SubqueryNode.generateExpression(Unknown 
Source)
at 
org.apache.derby.impl.sql.compile.BinaryOperatorNode.generateExpression(Unknown 
Source)
at 
org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(Unknown 
Source)
at 
org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(Unknown Source)
at org.apache.derby.impl.sql.compile.UnionNode.generate(Unknown Source)
at 
org.apache.derby.impl.sql.compile.NormalizeResultSetNode.generate(Unknown 
Source)
at 
org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(Unknown 
Source)
  

setting column to NULL

[Pavel Bortnovskiy]

Hello,

It seems that running the following SQL statement results in an error in Derby, 
while it's working fine in other DBs (such as Oracle or Sybase):

select
   a as ColumnA,
  null as ColumnB
from SomeTable

java.sql.SQLSyntaxErrorException: Syntax error: Encountered null at line 10, 
column 2.
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at 
org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown 
Source)
at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown 
Source)
at 
org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at 
org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at 
org.apache.derby.impl.jdbc.EmbedPreparedStatement.init(Unknown Source)
at 
org.apache.derby.impl.jdbc.EmbedPreparedStatement20.init(Unknown Source)
at 
org.apache.derby.impl.jdbc.EmbedPreparedStatement30.init(Unknown Source)
at 
org.apache.derby.impl.jdbc.EmbedPreparedStatement40.init(Unknown Source)
at 
org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
at 
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at 
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)

Is this by design? And is there a work-around? What needs to be done to set a 
column to null?

Thank you,
Pavel.
Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited. In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

RE: setting column to NULL

[Pavel Bortnovskiy]

Thank you so much, Rick, for your prompt response - it's very helpful.

It actually makes a lot of sense, esp. for my application.

Respectfully,
Pavel.

-Original Message-
From: Rick Hillegas [mailto:rick.hille...@oracle.com]
Sent: Wednesday, June 20, 2012 1:10 PM
To: derby-user@db.apache.org
Subject: Re: setting column to NULL

On 6/20/12 9:55 AM, Pavel Bortnovskiy wrote:

 Hello,

 It seems that running the following SQL statement results in an error
 in Derby, while it's working fine in other DBs (such as Oracle or Sybase):

 select

 a as ColumnA,

 null as ColumnB

 from SomeTable

 java.sql.SQLSyntaxErrorException: Syntax error: Encountered null at
 line 10, column 2.

 at
 org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unkno
 wn
 Source)

 at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
 Source)

 at
 org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(
 Unknown
 Source)

 at
 org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unk
 nown
 Source)

 at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
 Source)

 at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
 Source)

 at org.apache.derby.impl.jdbc.EmbedPreparedStatement.init(Unknown
 Source)

 at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.init(Unknown
 Source)

 at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.init(Unknown
 Source)

 at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.init(Unknown
 Source)

 at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown
 Source)

 at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
 Source)

 at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
 Source)

 Is this by design? And is there a work-around? What needs to be done
 to set a column to null?

Hi Pavel,

Derby does not have a default datatype for untyped nulls. You need to use a 
cast clause in order to give the null a type. Here's an example:

connect 'jdbc:derby:memory:db;create=true';

create table t( a int );
insert into t( a ) values ( 1 );

-- untyped null fails
select null as b from t;

-- typed null succeeds
select cast( null as int ) as b from t;

Hope this helps,
-Rick

 Thank you,

 Pavel.


 Jefferies archives and monitors outgoing and incoming
 e-mail. The contents of this email, including any
 attachments, are confidential to the ordinary user of the
 email address to which it was addressed. If you are not
 the addressee of this email you may not copy, forward,
 disclose or otherwise use it or any part of it in any form
 whatsoever. This email may be produced at the request of
 regulators or in connection with civil litigation.
 Jefferies accepts no liability for any errors or omissions
 arising as a result of transmission. Use by other than
 intended recipients is prohibited. In the United Kingdom,
 Jefferies operates as Jefferies International Limited;
 registered in England: no. 1978621; registered office:
 Vintners Place, 68 Upper Thames Street, London EC4V 3BJ.
 Jefferies International Limited is authorised and
 regulated by the Financial Services Authority.


Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited. In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

Heap Container is closed

[Pavel Bortnovskiy]

Hello, all:

What does this error mean? What might cause it and how can it be avoided?

java.sql.SQLException: The heap container with container id Container(-1, 
1340111446708) is closed.
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown 
Source)
at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown 
Source)
at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown 
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown 
Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown 
Source)
at 
org.apache.derby.impl.jdbc.EmbedResultSet.closeOnTransactionError(Unknown 
Source)
at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown 
Source)
at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source)
at 
com.jefco.fi.commons.database.QueryResults$1.hasNext(QueryResults.java:284)

Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited. In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

SYSCS_DIAG.TRANSACTION_TABLE

[Pavel Bortnovskiy]

Hello, all:

While trying to debug an anomaly within my app, I wanted to see all the active 
transactions within Derby (in-memory mode).
However, accessing SYSCS_DIAG.TRANSACTION_TABLE  table didn't prove very 
useful, as it is not possible to identify what statements those transactions 
execute, with the exception of one - the select from that table itself.

[cid:image001.png@01CD4306.49B6BD70]

Is there any other way to get more diag information?
Can I find the statements' text by XIDs elsewhere?

Thanks,
Pavel.
Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited. In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.
inline: image001.png

RE: SYSCS_DIAG.TRANSACTION_TABLE

[Pavel Bortnovskiy]

Excellent! Thank you, Rick for your prompt response.

-Original Message-
From: Rick Hillegas [mailto:rick.hille...@oracle.com]
Sent: Tuesday, June 05, 2012 12:30 PM
To: derby-user@db.apache.org
Subject: Re: SYSCS_DIAG.TRANSACTION_TABLE

Hi Pavel,

Note that the SQL_TEXT column will be null for any transaction which is not 
currently executing a statement. If you turn on statement logging 
(-Dderby.language.logStatementText=true), then your statement text will be 
logged to derby.log. You can use the syscs_diag.error_log_reader() vti to see 
what statements were run in each transaction. So the following sequence of 
calls may give you a lot of useful information:

select * from syscs_diag.transaction_table;

select * from table (syscs_diag.error_log_reader()) as t1;

Hope this helps,
-Rick

On 6/5/12 7:30 AM, Pavel Bortnovskiy wrote:

 Hello, all:

 While trying to debug an anomaly within my app, I wanted to see all
 the active transactions within Derby (in-memory mode).

 However, accessing SYSCS_DIAG.TRANSACTION_TABLE table didn't prove
 very useful, as it is not possible to identify what statements those
 transactions execute, with the exception of one - the select from that
 table itself.

 Is there any other way to get more diag information?

 Can I find the statements' text by XIDs elsewhere?

 Thanks,

 Pavel.


 Jefferies archives and monitors outgoing and incoming
 e-mail. The contents of this email, including any
 attachments, are confidential to the ordinary user of the
 email address to which it was addressed. If you are not
 the addressee of this email you may not copy, forward,
 disclose or otherwise use it or any part of it in any form
 whatsoever. This email may be produced at the request of
 regulators or in connection with civil litigation.
 Jefferies accepts no liability for any errors or omissions
 arising as a result of transmission. Use by other than
 intended recipients is prohibited. In the United Kingdom,
 Jefferies operates as Jefferies International Limited;
 registered in England: no. 1978621; registered office:
 Vintners Place, 68 Upper Thames Street, London EC4V 3BJ.
 Jefferies International Limited is authorised and
 regulated by the Financial Services Authority.



Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited. In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

Derby Locks - best practices

[Pavel Bortnovskiy]

Hello, all:

Derby is used in my application in the in-memory only mode. For a long time 
Derby's lock logic caused no worries, but recently some use cases failed with 
lock timeouts. Thus I'm looking for guidance on best practices for handling 
locks in Derby. A use-case which may cause timeouts to obtain a lock: one 
thread is executing an SQL statement which accesses two (or more) in-memory 
tables. Those two tables are being modified by  other threads at random times. 
So, situations in which the SQL is executed for a long time and the other 
threads are frequently updating the tables may cause lock timeouts.

Besides best practices to avoid timeouts and deadlocks, I would like to ask the 
following questions:

1)  What's the default length of lock timeouts?

2)  Does my app need another layer of synchronization mechanism/locks to 
avoid attempts to update in-memory tables or execute SQLs against them?

3)  Can my application utilize Derby's locks through some API - to query 
their state or to use them in making a decision of whether to batch updates or 
to execute them, to wait or execute the SQLs?

Your help would be greatly appreciated,

Pavel.
Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited. In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

RE: Derby Locks - best practices

[Pavel Bortnovskiy]

Hello, David, thanks for your quick response.

Usually it's one thread per in-memory table. Tables can be updated at random 
times and their random rows may be updated, some rows deleted or new rows 
inserted. In some other configuration, to avoid deletions, updates and inserts, 
the in-memory table is truncated and then all the records (the new state of 
the source data) are inserted into it.

The thread which runs SQL against all those tables frequently may do a scan of 
the whole table.

-Original Message-
From: David Zanter [mailto:dzan...@gmail.com]
Sent: Friday, June 01, 2012 3:46 PM
To: Derby Discussion
Subject: Re: Derby Locks - best practices

Do mean the scenario of:
Multiple threads are updating the exact same rows

or
Multiple threads doing updates to different rows, but due to 
queries/indexes/etc are causing contention between each other.

On Fri, Jun 1, 2012 at 3:16 PM, Pavel Bortnovskiy pbortnovs...@jefferies.com 
wrote:
 Hello, all:



 Derby is used in my application in the in-memory only mode. For a long
 time Derby's lock logic caused no worries, but recently some use cases
 failed with lock timeouts. Thus I'm looking for guidance on best
 practices for handling locks in Derby. A use-case which may cause
 timeouts to obtain a
 lock: one thread is executing an SQL statement which accesses two (or
 more) in-memory tables. Those two tables are being modified by  other
 threads at random times. So, situations in which the SQL is executed
 for a long time and the other threads are frequently updating the
 tables may cause lock timeouts.



 Besides best practices to avoid timeouts and deadlocks, I would like
 to ask the following questions:

 1)  What's the default length of lock timeouts?

 2)  Does my app need another layer of synchronization
 mechanism/locks to avoid attempts to update in-memory tables or execute SQLs 
 against them?

 3)  Can my application utilize Derby's locks through some API - to
 query their state or to use them in making a decision of whether to
 batch updates or to execute them, to wait or execute the SQLs?



 Your help would be greatly appreciated,



 Pavel.

 Jefferies archives and monitors outgoing and incoming e-mail. The
 contents of this email, including any attachments, are confidential to
 the ordinary user of the email address to which it was addressed. If
 you are not the addressee of this email you may not copy, forward,
 disclose or otherwise use it or any part of it in any form whatsoever.
 This email may be produced at the request of regulators or in
 connection with civil litigation. Jefferies accepts no liability for
 any errors or omissions arising as a result of transmission. Use by
 other than intended recipients is prohibited. In the United Kingdom,
 Jefferies operates as Jefferies International Limited; registered in
 England: no. 1978621; registered office: Vintners Place, 68 Upper
 Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised 
 and regulated by the Financial Services Authority.
Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited. In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

RE: Derby Locks - best practices

[Pavel Bortnovskiy]

Kristian, what are the lengths of time for default timeouts?
Can you please either list them or point to where in the code they may be 
defined?

Yes, when I say in-memory, I refer to it as memory subprotocol: 
jdbc:derby:memory:test;create=true

-Original Message-
From: Kristian Waagan [mailto:kristian.waa...@oracle.com]
Sent: Friday, June 01, 2012 6:12 PM
To: derby-user@db.apache.org
Subject: Re: Derby Locks - best practices

On 01.06.2012 21:50, Pavel Bortnovskiy wrote:
 Hello, David, thanks for your quick response.

 Usually it's one thread per in-memory table. Tables can be updated at 
 random times and their random rows may be updated, some rows deleted or new 
 rows inserted. In some other configuration, to avoid deletions, updates and 
 inserts, the in-memory table is truncated and then all the records (the new 
 state of the source data) are inserted into it.

For clarity, are you referring to using Derby's memory subprotocol when talking 
about in-memory tables? As an example, that would be 
'jdbc:derby:memory:mydb;create=true', as opposed to the on-disk version that 
would be 'jdbc:derby:mydb;create=true'.
In terms of locking there is nothing special about in-memory database in Derby, 
except for the likely event that some operations may be faster in-memory than 
on-disk (which could affect timing, but many other things can do that too).

Two common pieces of advice when it comes to locking is to reduce the duration 
of the locks, and to reduce the scope/granularity of the locks.
There may also be  application specific considerations to take, like acceptable 
isolation levels, access patterns and schema design.

In general your application should be prepared to handle lock timeouts, whereas 
deadlocks indicate that the access/lock patterns of your application need to be 
improved.


--
Kristian


 The thread which runs SQL against all those tables frequently may do a scan 
 of the whole table.

 -Original Message-
 From: David Zanter [mailto:dzan...@gmail.com]
 Sent: Friday, June 01, 2012 3:46 PM
 To: Derby Discussion
 Subject: Re: Derby Locks - best practices

 Do mean the scenario of:
  Multiple threads are updating the exact same rows

 or
  Multiple threads doing updates to different rows, but due to 
 queries/indexes/etc are causing contention between each other.

 On Fri, Jun 1, 2012 at 3:16 PM, Pavel Bortnovskiypbortnovs...@jefferies.com 
  wrote:
 Hello, all:



 Derby is used in my application in the in-memory only mode. For a
 long time Derby's lock logic caused no worries, but recently some use
 cases failed with lock timeouts. Thus I'm looking for guidance on
 best practices for handling locks in Derby. A use-case which may
 cause timeouts to obtain a
 lock: one thread is executing an SQL statement which accesses two (or
 more) in-memory tables. Those two tables are being modified by  other
 threads at random times. So, situations in which the SQL is executed
 for a long time and the other threads are frequently updating the
 tables may cause lock timeouts.



 Besides best practices to avoid timeouts and deadlocks, I would like
 to ask the following questions:

 1)  What's the default length of lock timeouts?

 2)  Does my app need another layer of synchronization
 mechanism/locks to avoid attempts to update in-memory tables or execute SQLs 
 against them?

 3)  Can my application utilize Derby's locks through some API - to
 query their state or to use them in making a decision of whether to
 batch updates or to execute them, to wait or execute the SQLs?



 Your help would be greatly appreciated,



 Pavel.

 Jefferies archives and monitors outgoing and incoming e-mail. The
 contents of this email, including any attachments, are confidential
 to the ordinary user of the email address to which it was addressed.
 If you are not the addressee of this email you may not copy, forward,
 disclose or otherwise use it or any part of it in any form whatsoever.
 This email may be produced at the request of regulators or in
 connection with civil litigation. Jefferies accepts no liability for
 any errors or omissions arising as a result of transmission. Use by
 other than intended recipients is prohibited. In the United Kingdom,
 Jefferies operates as Jefferies International Limited; registered in
 England: no. 1978621; registered office: Vintners Place, 68 Upper
 Thames Street, London EC4V 3BJ. Jefferies International Limited is 
 authorised and regulated by the Financial Services Authority.
 Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
 this email, including any attachments, are confidential to the ordinary user 
 of the email address to which it was addressed. If you are not the addressee 
 of this email you may not copy, forward, disclose or otherwise use it or any 
 part of it in any form whatsoever. This email may be produced at the request 
 of regulators or in connection with civil litigation. Jefferies accepts

RE: Derby Locks - best practices

[Pavel Bortnovskiy]

Thank you, Dag.

So, what may be my best strategy in the described situation? Let me recap a 
case like such:

Say Derby is running in in-memory subprotocol. There are two tables and three 
threads. Two threads perform inserts/deletes/updates and merges (implemented as 
update+insert) on each corresponding table. And the third thread runs an SQL on 
those two tables. I forgot to mention a few details:
- the threads perform table updates in a batched way: Prepared statements are 
created and then they are batched. At a certain the batches are executed
- the SQL is very slow running - for argument sake, it may take 3-4 mins to run 
it - much longer than a lock timeout.

What would be the best way to assure proper functioning of the app and avoid 
timeouts?

It has been suggested to repeat a transaction if a lock time out exception is 
caught, but that would mean to execute the whole batch again...

Thank you for your help,

Pavel.

-Original Message-
From: Dag H. Wanvik [mailto:dag.wan...@oracle.com]
Sent: Friday, June 01, 2012 7:30 PM
To: Derby Discussion
Subject: Re: Derby Locks - best practices

Pavel Bortnovskiy pbortnovs...@jefferies.com writes:

 1)  What's the default length of lock timeouts?

60 seconds.

cf. http://db.apache.org/derby/docs/10.8/ref/rrefproper46141.html
Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited. In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

best way to check for the exsitence of a table

[Pavel Bortnovskiy]

Hello:


When reading derby docs, I noticed that the function WwdUtils.wwdChk4Table in 
Derby demos is implemented by executing an update on the table and then relying 
on the exception mechanism to determine whether or not the table exists.



My approach was to execute DatabaseMetaData.getTables method (pls see 
implementation below) and iterate through the resultset. I also wanted to 
assure case insensitivity.



Which method is more preferable from the point of efficiency and database 
operations (locks, etc).

This code might be executed frequently and by multiple threads.



Thank you,

Pavel.



public static boolean tableExists(final Connection connection, final String 
tableName) throws SQLException {
try {
final DatabaseMetaData databaseMetaData = connection.getMetaData();
final ResultSet resultSet = databaseMetaData.getTables(null, null, 
null, new String[]{TABLE});
try {
while (resultSet.next()) {
if 
(resultSet.getString(TABLE_NAME).equalsIgnoreCase(tableName)) {
return true;
}
}
} finally {
connection.commit();
resultSet.close();
}
return false;
} catch (SQLException e) {
Statement statement = null;
try {
final Connection c = 
Components.getMemoryDb().getConnection(true);
statement = c.createStatement();
final String select = SELECT * FROM SYSCS_DIAG.LOCK_TABLE;
final ResultSet resultSet = statement.executeQuery(select);
final int cnt = resultSet.getMetaData().getColumnCount();
final StringBuilder builder = new StringBuilder(Results of \ 
+ select + \:);
while (resultSet.next()) {
builder.append(\n\t);
for (int i = 1; i = cnt; i++) {
if (i  1) {
builder.append(,);
}
builder.append(resultSet.getObject(i));
}
}
logger.info(builder.toString());
} finally {
if (statement != null) {
statement.close();
}
}
throw e;
}
}
Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited. In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

RE: best way to check for the exsitence of a table

[Pavel Bortnovskiy]

Rick, thank you for your prompt and helpful response.

Regards,
Pavel.

-Original Message-
From: Rick Hillegas [mailto:rick.hille...@oracle.com]
Sent: Tuesday, May 22, 2012 3:33 PM
To: derby-user@db.apache.org
Subject: Re: best way to check for the exsitence of a table

Hi Pavel,

Querying the metadata as you suggest is a good approach. You will want to 
adjust the arguments to DatabaseMetaData.getTables() however. The arguments in 
your example may find false matches in other schemas and with tables created 
with double-quoted names. The following example may help you adjust the 
arguments to getTable():

public class z
{
 public  static  voidmain( String... args ) throws Exception
 {
 Connection  conn = DriverManager.getConnection( 
jdbc:derby:memory:db;create=true );

 conn.prepareStatement( create table myTable( a int ) ).execute();

 DatabaseMetaDatadbmd = conn.getMetaData();
 ResultSet   rs;

 rs = dbmd.getTables( null, APP, MYTABLE, new String[] { TABLE } 
);
 System.out.println( Table 'MYTABLE' exists =  + rs.next() );

 rs = dbmd.getTables( null, APP, mytable, new String[] { TABLE } 
);
 System.out.println( Table 'mytable' exists =  + rs.next() );
 }

}

Hope this helps,
-Rick

On 5/22/12 11:33 AM, Pavel Bortnovskiy wrote:

 Hello:

 When reading derby docs, I noticed that the functionWwdUtils.wwdChk4Tablein 
 Derby demos is implemented by executing an update on the table and then 
 relying on the exception mechanism to determine whether or not the table 
 exists.

 My approach was to execute DatabaseMetaData.getTables method (pls see 
 implementation below) and iterate through the resultset. I also wanted to 
 assure case insensitivity.

 Which method is more preferable from the point of efficiency and database 
 operations (locks, etc).
 This code might be executed frequently and by multiple threads.

 Thank you,
 Pavel.



 public static boolean tableExists(final Connection connection,
 final String tableName) throws SQLException {

 try {

 final DatabaseMetaData databaseMetaData =
 connection.getMetaData();

 final ResultSet resultSet =
 databaseMetaData.getTables(null, null, null, new String[]{TABLE});

 try {

 while (resultSet.next()) {

 if
 (resultSet.getString(TABLE_NAME).equalsIgnoreCase(tableName)) {

 return true;

 }

 }

 } finally {

 connection.commit();

 resultSet.close();

 }

 return false;

 } catch (SQLException e) {

 Statement statement = null;

 try {

 final Connection c =
 Components.getMemoryDb().getConnection(true);

 statement = c.createStatement();

 final String select = SELECT * FROM
 SYSCS_DIAG.LOCK_TABLE;

 final ResultSet resultSet =
 statement.executeQuery(select);

 final int cnt =
 resultSet.getMetaData().getColumnCount();

 final StringBuilder builder = new
 StringBuilder(Results of \ + select + \:);

 while (resultSet.next()) {

 builder.append(\n\t);

 for (int i = 1; i = cnt; i++) {

 if (i  1) {

 builder.append(,);

 }

 builder.append(resultSet.getObject(i));

 }

 }

 logger.info(builder.toString());

 } finally {

 if (statement != null) {

 statement.close();

 }

 }

 throw e;

 }

 }


 Jefferies archives and monitors outgoing and incoming
 e-mail. The contents of this email, including any
 attachments, are confidential to the ordinary user of the
 email address to which it was addressed. If you are not
 the addressee of this email you may not copy, forward,
 disclose or otherwise use it or any part of it in any form
 whatsoever. This email may be produced at the request of
 regulators or in connection with civil litigation.
 Jefferies accepts no liability for any errors or omissions
 arising as a result of transmission. Use by other than
 intended recipients is prohibited. In the United Kingdom,
 Jefferies operates as Jefferies International Limited;
 registered in England: no. 1978621; registered office:
 Vintners Place, 68 Upper Thames Street, London EC4V 3BJ.
 Jefferies International Limited is authorised and
 regulated by the Financial Services Authority.


Jefferies archives and monitors outgoing and incoming e-mail

RE: The selected victim is XID

[Pavel Bortnovskiy]

Brett: thank you for your prompt response.

So, how can I solve this situation? I have one thread which runs a select which 
may execute for a minute or longer, and other threads which insert and update 
records (more precisely, they batch inserts/updates/deletes and then execute 
those batches). How can I prevent these errors from happening and how can I 
assure the proper functioning of the application?

Thanks,
Pavel.

From: Bergquist, Brett [mailto:bbergqu...@canoga.com]
Sent: Monday, February 13, 2012 9:01 AM
To: Derby Discussion
Subject: RE: The selected victim is XID

You either had a deadlock as you discovered.  You are going to either have to 
order your transactions to avoid a deadlock or you can catch the exception and 
retry the transaction.  Nothing in Derby is going to automatically retry the 
insert.  Note that the select transaction may have be the one that is 
terminated in the future, it depends on what Derby decides is best to allow one 
of the transactions to complete.

I do not believe this is anything different than for any other database, at 
least not the ones that I have used.

Brett

From: Pavel Bortnovskiy [mailto:pbortnovs...@jefferies.com]
Sent: Monday, February 13, 2012 8:45 AM
To: Derby Discussion (derby-user@db.apache.org)
Subject: The selected victim is XID

Hello:

Today my application threw and exception I haven't seen before:

--REFDATA.INSTYPE in ('M','P','T','G','A') AND
BOOK.ID NOT LIKE 'MD%' AND
BOOK.ID NOT LIKE 'LC%' AND
BOOK.ID NOT LIKE 'P_%'
  Granted XID : {9778, X}
Lock : ROW, WINFITS_DB, (6,10742)
  Waiting XID : {9778, X} , APP, INSERT INTO A_DB (sec_id,val) VALUES (?,?)
  Granted XID : {9749, S}
. The selected victim is XID : 9749.
at org.apache.derby.iapi.error.StandardException.newException(Unknown 
Source)
at org.apache.derby.impl.services.locks.Deadlock.buildException(Unknown 
Source)
at 
org.apache.derby.impl.services.locks.ConcurrentLockSet.lockObject(Unknown 
Source)
at org.apache.derby.impl.services.locks.AbstractPool.lockObject(Unknown 
Source)
at 
org.apache.derby.impl.services.locks.ConcurrentPool.lockObject(Unknown Source)
at 
org.apache.derby.impl.store.raw.xact.RowLocking2.lockRecordForRead(Unknown 
Source)
at 
org.apache.derby.impl.store.access.heap.HeapController.lockRow(Unknown Source)
at 
org.apache.derby.impl.store.access.heap.HeapController.lockRow(Unknown Source)
at 
org.apache.derby.impl.store.access.btree.index.B2IRowLocking3.lockRowOnPage(Unknown
 Source)
at 
org.apache.derby.impl.store.access.btree.index.B2IRowLocking3._lockScanRow(Unknown
 Source)
at 
org.apache.derby.impl.store.access.btree.index.B2IRowLockingRR.lockScanRow(Unknown
 Source)
at 
org.apache.derby.impl.store.access.btree.BTreeForwardScan.fetchRows(Unknown 
Source)
at org.apache.derby.impl.store.access.btree.BTreeScan.fetchNext(Unknown 
Source)
at 
org.apache.derby.impl.sql.execute.TableScanResultSet.getNextRowCore(Unknown 
Source)
at 
org.apache.derby.impl.sql.execute.IndexRowToBaseRowResultSet.getNextRowCore(Unknown
 Source)
at 
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown
 Source)
at 
org.apache.derby.impl.sql.execute.OnceResultSet.getNextRowCore(Unknown Source)
at 
org.apache.derby.exe.acc934c123x0135x7687xc204x1d890d611.g0(Unknown Source)
at 
org.apache.derby.exe.acc934c123x0135x7687xc204x1d890d611.e21(Unknown Source)
at sun.reflect.GeneratedMethodAccessor32.invoke(Unknown Source)
at 
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.derby.impl.services.reflect.ReflectMethod.invoke(Unknown 
Source)
at 
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.doProjection(Unknown 
Source)
at 
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown
 Source)
at 
org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowFromSource(Unknown
 Source)
at 
org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowCore(Unknown
 Source)
at 
org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(Unknown 
Source)
... 9 more

I did find the article on Debugging Locking Situations: 
http://wiki.apache.org/db-derby/LockDebugging
And I did understand the log file that an insert was being made into the same 
table on which select was being executed,
But my question is how to prevent these errors from happening? Also, if such an 
exception was thrown during an insert,
will the insert be attempted again?

Thanks,
Pavel.
Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments

The selected victim is XID

[Pavel Bortnovskiy]

Hello:

Today my application threw and exception I haven't seen before:

--REFDATA.INSTYPE in ('M','P','T','G','A') AND
BOOK.ID NOT LIKE 'MD%' AND
BOOK.ID NOT LIKE 'LC%' AND
BOOK.ID NOT LIKE 'P_%'
  Granted XID : {9778, X}
Lock : ROW, WINFITS_DB, (6,10742)
  Waiting XID : {9778, X} , APP, INSERT INTO A_DB (sec_id,val) VALUES (?,?)
  Granted XID : {9749, S}
. The selected victim is XID : 9749.
at org.apache.derby.iapi.error.StandardException.newException(Unknown 
Source)
at org.apache.derby.impl.services.locks.Deadlock.buildException(Unknown 
Source)
at 
org.apache.derby.impl.services.locks.ConcurrentLockSet.lockObject(Unknown 
Source)
at org.apache.derby.impl.services.locks.AbstractPool.lockObject(Unknown 
Source)
at 
org.apache.derby.impl.services.locks.ConcurrentPool.lockObject(Unknown Source)
at 
org.apache.derby.impl.store.raw.xact.RowLocking2.lockRecordForRead(Unknown 
Source)
at 
org.apache.derby.impl.store.access.heap.HeapController.lockRow(Unknown Source)
at 
org.apache.derby.impl.store.access.heap.HeapController.lockRow(Unknown Source)
at 
org.apache.derby.impl.store.access.btree.index.B2IRowLocking3.lockRowOnPage(Unknown
 Source)
at 
org.apache.derby.impl.store.access.btree.index.B2IRowLocking3._lockScanRow(Unknown
 Source)
at 
org.apache.derby.impl.store.access.btree.index.B2IRowLockingRR.lockScanRow(Unknown
 Source)
at 
org.apache.derby.impl.store.access.btree.BTreeForwardScan.fetchRows(Unknown 
Source)
at org.apache.derby.impl.store.access.btree.BTreeScan.fetchNext(Unknown 
Source)
at 
org.apache.derby.impl.sql.execute.TableScanResultSet.getNextRowCore(Unknown 
Source)
at 
org.apache.derby.impl.sql.execute.IndexRowToBaseRowResultSet.getNextRowCore(Unknown
 Source)
at 
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown
 Source)
at 
org.apache.derby.impl.sql.execute.OnceResultSet.getNextRowCore(Unknown Source)
at 
org.apache.derby.exe.acc934c123x0135x7687xc204x1d890d611.g0(Unknown Source)
at 
org.apache.derby.exe.acc934c123x0135x7687xc204x1d890d611.e21(Unknown Source)
at sun.reflect.GeneratedMethodAccessor32.invoke(Unknown Source)
at 
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.derby.impl.services.reflect.ReflectMethod.invoke(Unknown 
Source)
at 
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.doProjection(Unknown 
Source)
at 
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown
 Source)
at 
org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowFromSource(Unknown
 Source)
at 
org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowCore(Unknown
 Source)
at 
org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(Unknown 
Source)
... 9 more

I did find the article on Debugging Locking Situations: 
http://wiki.apache.org/db-derby/LockDebugging
And I did understand the log file that an insert was being made into the same 
table on which select was being executed,
But my question is how to prevent these errors from happening? Also, if such an 
exception was thrown during an insert,
will the insert be attempted again?

Thanks,
Pavel.
Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited. In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

RE: Shutting down in-memory Derby gracefully

[Pavel Bortnovskiy]

Hello, all

can someone please also shed light on the following:

In addition to the problems described in the previous email (below), I also get 
intermittent  Database 'memory:test' not found errors while shutting down 
Derby:

Error while shutting down:
java.sql.SQLException: Database 'memory:test' not found.
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source) 
  at 
org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown 
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleDBNotFound(Unknown 
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.init(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection30.init(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection40.init(Unknown Source)
at org.apache.derby.jdbc.Driver40.getNewEmbedConnection(Unknown Source)
at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
at org.apache.derby.jdbc.AutoloadedDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:207)
Caused by: java.sql.SQLException: Database 'memory:test' not found.
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
 Source)
... 21 more

This error doesn't happen all the time, it's intermittent and I suspect that it 
might have be somehow connected to other problems I've been having trying to 
shutdown in-memory Derby DB gracefully.

Can someone please help me understand what's causing this particular problem?

My code that's shutting down Derby is quite simple, and oddly it works most of 
the times, but sometimes it throws this  Database 'memory:test' not found  
error!

Here is the excerpt of my shutdown code:

private static final String DB_DROP_URL = 
jdbc:derby:memory:%DB_NAME%;drop=true;
private static final String DB_SHUTDOWN_URL = jdbc:derby:;shutdown=true;

private static class ShutdownAction {
final private String m_url;
final private int m_errorCode;
final private String m_sqlState;

private ShutdownAction(final String url, final int errorCode, final 
String sqlState) {
m_url = url;
m_errorCode = errorCode;
m_sqlState = sqlState;
}

private void doAction(final String dbName) {
final String url = m_url.replace(%DB_NAME%, dbName);
logger.info(Executing Derby Shutdown Command:  + url);
try {
DriverManager.getConnection(url);
} catch (SQLException e) {
if ((e.getErrorCode() != m_errorCode) || 
(!m_sqlState.equals(e.getSQLState( {
logger.error(Error while shutting down:, e);
}
}
}
}

private static final ShutdownAction[] ACTIONS = {
new ShutdownAction(DB_DROP_URL, 45000, 08006),
new ShutdownAction(DB_SHUTDOWN_URL, 5, XJ015)
};

private String m_dbName;
private String m_dbCreateURL;

private NetworkServerControlPinger m_derbyServerControl;

@Override
final public void shutdown() {
for (final ShutdownAction action : ACTIONS) {
action.doAction(m_dbName);
}

m_derbyServerControl.shutdown();
}



-Original Message-
From: Pavel Bortnovskiy [mailto:pbortnovs...@jefferies.com]
Sent: Friday, January 13, 2012 10:01 AM
To: Derby Discussion
Subject: RE: Shutting down in-memory Derby gracefully

Hello, Bryan:

Thank you for your prompt response. I will report it as per guidelines.

Would you be able to suggest anything that I could do in the meantime?
We have watchdog processes monitoring error files and when the error files get 
populated with these messages, the watchdog raises all kinds of alarms.
This is more than just a nuisance, as it's causing havoc in some cases.

Thank you,
Pavel.

-Original Message-
From: Bryan Pendleton [mailto:bpendleton.de...@gmail.com]
Sent: Friday, January 13, 2012 9:58 AM
To: Derby Discussion
Subject: Re: Shutting down in-memory Derby gracefully

 While our application is shutting down, it is possible that some of
 its components may continue execution or even begin execution of the
 queries on the in-memory Derby DB. In such situation, Derby starts throwing 
 all kinds of exceptions, including a Null Pointer Exception (pls see excerpt 
 from our log below).

That looks like a bug to me, and worthy of reporting to the Derby

RE: Shutting down in-memory Derby gracefully

[Pavel Bortnovskiy]

Hello, Bryan:

Thank you for your prompt response. I will report it as per guidelines.

Would you be able to suggest anything that I could do in the meantime?
We have watchdog processes monitoring error files and when the error files get 
populated with these messages, the watchdog raises all kinds of alarms.
This is more than just a nuisance, as it's causing havoc in some cases.

Thank you,
Pavel.

-Original Message-
From: Bryan Pendleton [mailto:bpendleton.de...@gmail.com]
Sent: Friday, January 13, 2012 9:58 AM
To: Derby Discussion
Subject: Re: Shutting down in-memory Derby gracefully

 While our application is shutting down, it is possible that some of
 its components may continue execution or even begin execution of the
 queries on the in-memory Derby DB. In such situation, Derby starts throwing 
 all kinds of exceptions, including a Null Pointer Exception (pls see excerpt 
 from our log below).

That looks like a bug to me, and worthy of reporting to the Derby developers.

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

thanks,

bryan


Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited. In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

Shutting down in-memory Derby gracefully

[Pavel Bortnovskiy]

Hello, all:

While our application is shutting down, it is possible that some of its 
components may continue execution or even begin execution of the queries on the 
in-memory Derby DB. In such situation, Derby starts throwing all kinds of 
exceptions, including a Null Pointer Exception (pls see excerpt from our log 
below). Is it possible to have Derby shut down more gracefully, so that the 
application doesn't pester our error log files with these exceptions?

Of course, I may be able to work around by implementing an atomic reference 
counter, which gets incremented before a query is executed and decremented when 
it's done. And then, upon a request to shut down Derby, wait until that counter 
gets to 0 before sending the shutdown URL. But it seems kludgy to me and I 
would wish for a more elegant way for Derby itself to handle this, as it has 
the intimate knowledge of what's being executed and what's not.

Or, perhaps, you could recommend a better approach?

Thank you.
Pavel.

2012-01-12 16:15:23,224 [main] INFO  Executing Derby Shutdown Command: 
jdbc:derby:memory:geni;drop=true
2012-01-12 16:15:23,230 SQL Exception due to executing statement
java.sql.SQLException: Java exception: ': java.lang.NullPointerException'.
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.javaException(Unknown Source)
at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown 
Source)
at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown 
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown 
Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown 
Source)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown 
Source)
at 
org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown 
Source)
at 
org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeQuery(Unknown Source)
...
Caused by: java.sql.SQLException: Java exception: ': 
java.lang.NullPointerException'.
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
 Source)
... 13 more
Caused by: java.lang.NullPointerException
at java.util.LinkedList.remove(LinkedList.java:225)
at 
org.apache.derby.impl.services.locks.LockControl.removeWaiter(Unknown Source)
at 
org.apache.derby.impl.services.locks.LockControl.getNextWaiter(Unknown Source)
at 
org.apache.derby.impl.services.locks.ConcurrentLockSet.lockObject(Unknown 
Source)
at 
org.apache.derby.impl.services.locks.ConcurrentLockSet.zeroDurationLockObject(Unknown
 Source)
at 
org.apache.derby.impl.services.locks.AbstractPool.zeroDurationlockObject(Unknown
 Source)
at 
org.apache.derby.impl.services.locks.ConcurrentPool.zeroDurationlockObject(Unknown
 Source)
at 
org.apache.derby.impl.store.raw.xact.RowLocking2nohold.lockRecordForRead(Unknown
 Source)
at 
org.apache.derby.impl.store.access.conglomerate.OpenConglomerate.lockPositionForRead(Unknown
 Source)
at 
org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchRows(Unknown
 Source)
at 
org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchSet(Unknown
 Source)
at 
org.apache.derby.impl.store.access.BackingStoreHashTableFromScan.init(Unknown 
Source)
at 
org.apache.derby.impl.store.access.RAMTransaction.createBackingStoreHashtableFromScan(Unknown
 Source)
at org.apache.derby.impl.sql.execute.HashScanResultSet.openCore(Unknown 
Source)
at org.apache.derby.impl.sql.execute.JoinResultSet.openRight(Unknown 
Source)
at org.apache.derby.impl.sql.execute.JoinResultSet.openCore(Unknown 
Source)
at 
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(Unknown 
Source)
at 
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(Unknown 
Source)
at 
org.apache.derby.impl.sql.execute.GroupedAggregateResultSet.openCore(Unknown 
Source)
at 
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(Unknown 
Source)
at 
org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.openCore(Unknown 
Source)
at 
org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.open(Unknown Source)
at 
org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown 
Source)
... 6 more
2012-01-12 16:15:23,231 SQL Exception due to executing statement
java.sql.SQLNonTransientConnectionException: Database 'memory:geni' shutdown.
at 

RE: Date/Time Formatting

[Pavel Bortnovskiy]

Thank you, Rick, for your prompt response.
I was able to solve it in the meantime with:

year(current_date) * 1 + month(current_date) * 100 + 
day(current_date) as InsertDate,
hour(current_time) * 1 + minute(current_time) * 100 + 
second(current_time) as InsertTime,

But I was certainly hoping for a more elegant solution (wished there was a 
format date/time function)...

-Original Message-
From: Rick Hillegas [mailto:rick.hille...@oracle.com] 
Sent: Tuesday, November 08, 2011 11:20 AM
To: Derby Discussion
Subject: Re: Date/Time Formatting

Hi Pavel,

I don't see a builtin function which does what you want. You might be able to 
cobble together what you need using the LOCATE and SUBSTR functions, but the 
statement would probably look ugly. Alternatively, you can register your own 
user defined formatting function using the CREATE FUNCTION statement: 
http://db.apache.org/derby/docs/10.8/ref/ref-single.html#crefsqlj95081

Hope this helps,
-Rick

On 11/8/11 7:32 AM, Pavel Bortnovskiy wrote:

 Hello:

 Are there Derby built-in functions which can allow me to extract 
 current date/time as integers as:

 Time HHmmss: 103152

 Date MMdd:  2008

 I could get as far as:

 *cast*(*current_time as **VARCHAR*(8)),

 *cast*(*current_date as **VARCHAR*(10)),

 but I was unable to find a function similar to str_replace in Sybase 
 to remove “:” and “-“

 Thank you,

 Pavel.

 Jefferies archives and monitors outgoing and incoming e-mail. The 
 contents of this email, including any attachments, are confidential to 
 the ordinary user of the email address to which it was addressed. If 
 you are not the addressee of this email you may not copy, forward, 
 disclose or otherwise use it or any part of it in any form whatsoever.
 This email may be produced at the request of regulators or in 
 connection with civil litigation. Jefferies accepts no liability for 
 any errors or omissions arising as a result of transmission. Use by 
 other than intended recipients is prohibited. In the United Kingdom, 
 Jefferies operates as Jefferies International Limited; registered in
 England: no. 1978621; registered office: Vintners Place, 68 Upper 
 Thames Street, London EC4V 3BJ. Jefferies International Limited is 
 authorised and regulated by the Financial Services Authority.

OutOfMemoryException when executing 180,000 batches

[Pavel Bortnovskiy]

Is it unusual that Derby (used in-memory only) seems to throw an out of memory 
exception when executing 180,000 batched insert statements?
(The JVM was started with -Xms1024m -Xmx2048m):

Caused by: java.sql.SQLException: Java exception: 'GC overhead limit exceeded: 
java.lang.OutOfMemoryError'.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown 
Source)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.javaException(Unknown Source)
at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown 
Source)
at org.apache.derby.impl.jdbc.EmbedResultSet.noStateChangeException(Unknown 
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.setDate(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.setDate(Unknown Source)
at org.apache.derby.iapi.types.SQLDate.setInto(Unknown Source)
at 
org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeBatchElement(Unknown 
Source)

Are there memory limitations for Derby running in the in-memory only mode?
Is there anything that can be done to avoid getting such errors?

Thanks,
Pavel.


Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
 the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited. In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

upsert/merge?

[Pavel Bortnovskiy]

Hello,

Let me immediately state that I am aware that many people have 
requested/suggested to have the merge (Oracle) or upsert implemented and 
that there is a an issue created in Jira.

My question, although related, is a bit different.

In short, in my Java program I need to be able to:
- support INSERT, UPDATE, DELETE and MERGE/UPSERT on a variety of 
databases (thus the solution must be standard) - Derby, Oracle, Sybase, MS 
SQL
- avoid stored procedures
- be able to addBatch() all those JDBC PreparedStatements and invoke 
executeBatch() to efficiently process them

Currently, MERGE/UPSERT has been implemented by executing two statements - 
first a JDBC PreparedStatement UPDATE would be executed, and if it returns 
0 count, then the INSERT PreparedStatement. This is very inefficient and 
cannot be easily refactored to run as a batch. To batch it now I would 
have to implement a two-stage approach: to batch first all UPDATE 
statements with addBatch(), then invoke executeBatch(), analyze int[] that 
it returns and repeat those actions for the batch of the UPDATE 
statements. This is complicated, convoluted, difficult to support and 
implement.

I tried to create a Prepared Statement for something like this:

IF (EXISTS 
(SELECT 1 FROM test_table WHERE PK_INTEGER_BS519=?)
) BEGIN 
UPDATE test_table SET 
PK_INTEGER_BS519=?,TIME_850T2=?,BIGINT_63J3P=?,REAL_ZAF15=?,SMALLINT_731O9=?,BIT_4027E=?,INTEGER_95FDU=?,BIGINT_K1YYZ=?,DOUBLE_SRS0O=?,VARCHAR_L2NE2=?,VARCHAR_Z0U7L=?,BIT_32CP2=?,VARCHAR_3UD3C=?,BIGINT_1K7S0=?,DOUBLE_7NGCA=?,INTEGER_HMT78=?,DOUBLE_75L55=?,SMALLINT_MP89H=?,REAL_YOVS7=?,INTEGER_371TT=?
 
WHERE PK_INTEGER_BS519=?
END
ELSE
BEGIN INSERT INTO 
test_table(PK_INTEGER_BS519,TIME_850T2,BIGINT_63J3P,REAL_ZAF15,SMALLINT_731O9,BIT_4027E,INTEGER_95FDU,BIGINT_K1YYZ,DOUBLE_SRS0O,VARCHAR_L2NE2,VARCHAR_Z0U7L,BIT_32CP2,VARCHAR_3UD3C,BIGINT_1K7S0,DOUBLE_7NGCA,INTEGER_HMT78,DOUBLE_75L55,SMALLINT_MP89H,REAL_YOVS7,INTEGER_371TT)
 
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
END

Just to find out that Derby doesn't support IF:



So, is there a better and more efficient way to do so in one SQL statement 
which can also be (1) batched and (2) standard (so to be supported by 
other databases, such as Sybase, Oracle, MS SQL, so that the same code can 
be used with all of them)?

Any other suggestions or thoughts would be greatly appreciated.

Pavel.




Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.image/gif

Derby (in-memory) and Hibernate

[Pavel Bortnovskiy]

Are there any issues in using Derby (in-memory only mode) with Hibernate?
Is it fully supported?

Thank you.
Pavel.




Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

Re: Table exists in same JVM after Derby is shutdown

[Pavel Bortnovskiy]

Thank you, Kristian.

Can you please clarify some of the points you made (or point me to an 
online primer that describes it):
- what is the difference between shutting down a specific db and the whole 
engine?
- what does it mean that shutting down the Derby engine which runs an 
in-memory-only DB doesn't drop tables?
Are you saying that if that engine were to be restarted, the tables would 
be accessible again?
Does it also mean that the memory taken by the engine and its tables 
doesn't get reclaimed until it's shutdown (or dropped)?

Regards,
Pavel.



From:
Kristian Waagan kristian.waa...@oracle.com
To:
derby-user@db.apache.org
Date:
08/09/2011 01:42 AM
Subject:
Re: Table exists in same JVM after Derby is shutdown



On 08.08.11 16:41, Pavel Bortnovskiy wrote:
 Thank you, Dag.

 It looks like in order to get this to work, I had to make two calls to
 DriverManager.getConnection.
 Combining /shutdown=true;drop=true/ didn't work.
 So, is this the correct implementation (if in-line image doesn't work,
 please see attachment):

Hello Pavel,

To simply drop an in-memory database, you only need the drop attribute. 
Shutdown of the specified database happens as part of the drop operation.

It is correct that you need two calls to DriverManager.getConnection if 
you want to drop a database and additionally shut down the Derby engine. 
For clarity, note that there is a difference between shutting down a 
specific database and the Derby engine/system.

Further, shutting down an in-memory database does not drop it, neither 
does shutting down the Derby engine. I don't know, maybe it would make 
sense to drop all in-memory databases when the Derby system is shut down 
- but that's not being done currently.


Hope this helps,
-- 
Kristian






 From:  dag.wan...@oracle.com (Dag H. Wanvik)
 To:Derby Discussion derby-user@db.apache.org
 Date:  08/05/2011 06:36 PM
 Subject:   Re: Table exists in same JVM after Derby is 
shutdown


 



 Pavel Bortnovskiy pbortnovs...@jefferies.com writes:

   1) I create an in-memory database and then a table in it.
   Then the database is shut down.
   I would expect that the shutdown effectively cleans everything up.
   Is it not so?

 Ah, I misunderstood you Pavel. Have a look here:

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

 As you can see, you need to provide the drop=true connection
 attribute to wipe the data.

 Thanks,
 Dag

  
   Thanks,
   Pavel.
  
  
  
   From:
   dag.wan...@oracle.com (Dag H. Wanvik)
   To:
   Derby Discussion derby-user@db.apache.org
   Date:
   08/05/2011 05:57 PM
   Subject:
   Re: Table exists in same JVM after Derby is shutdown
  
  
  
   Pavel Bortnovskiy pbortnovs...@jefferies.com writes:
  
   Hello, all:
  
   While executing a bunch of JUnit tests within the same JVM (all
 executed
  
   by IntelliJ IDEA) I started seeing strange and unexpected errors
   occurring
   .
   Upon closer inspection, I noticed that in many of those tests tables
   with
   the same names are attempted to be created.
   Then I realized that although Derby is shutdown and then re-created,
 the
  
   tables remain, thus causing collisions.
  
   The create=true connection attribute is ignored (with a warning) if
   the database with the same name alrady exists.
  
  
   I've created a digest (attached) which is executed as one JUnit test 
to
   illustrate what I'm seeing.
   The behavior I would expect is that once Derby is shutdown, no 
tables
   would remain in the JVM, and if a database (with the same name) is
   re-created, it would be a tabula rasa.
  
   The tables are no longer in memory (or should not be unless you found 
a
   bug), but they are not erased from the disk image of the database. As
   per the above, one would need to explicitly delete it using OS file
   system tools for the data to be cleared. Some JUnit tests delete 
tables
   in TestCase#tearDown, others use singleUseDatabaseDecorator to 
isolate
   itself from the rest of the tests. The tests not necessarily very
   consistent in their patterns for this..
  
   Dag
  
  
   Can you please let me know whether my expectations are erroneous and
   whether I should find workarounds (albeit trivial to implement).
   However, what would concern me in that case is that the tables and 
the
   data remain in the JVM, thus consuming memory
   (and, if unused, creating memory leaks).
  
   Thank you,
  
   Pavel.
  

 snip 


 Jefferies archives and monitors outgoing and incoming e-mail. The
 contents of this email, including any attachments, are confidential to
 the ordinary user of the email address to which it was addressed. If you
 are not the addressee of this email you may not copy, forward, disclose
 or otherwise use it or any part of it in any form whatsoever. This email
 may be produced at the request of regulators or in connection with civil
 litigation

Re: Table exists in same JVM after Derby is shutdown

[Pavel Bortnovskiy]

Thank you, Dag.

It looks like in order to get this to work, I had to make two calls to 
DriverManager.getConnection.
Combining shutdown=true;drop=true didn't work.
So, is this the correct implementation (if in-line image doesn't work, 
please see attachment):






From:
dag.wan...@oracle.com (Dag H. Wanvik)
To:
Derby Discussion derby-user@db.apache.org
Date:
08/05/2011 06:36 PM
Subject:
Re: Table exists in same JVM after Derby is shutdown



Pavel Bortnovskiy pbortnovs...@jefferies.com writes:

 1) I create an in-memory database and then a table in it.
 Then the database is shut down.
 I would expect that the shutdown effectively cleans everything up.
 Is it not so?

Ah, I misunderstood you Pavel. Have a look here:

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

As you can see, you need to provide the drop=true connection
attribute to wipe the data.

Thanks,
Dag


 Thanks,
 Pavel.



 From:
 dag.wan...@oracle.com (Dag H. Wanvik)
 To:
 Derby Discussion derby-user@db.apache.org
 Date:
 08/05/2011 05:57 PM
 Subject:
 Re: Table exists in same JVM after Derby is shutdown



 Pavel Bortnovskiy pbortnovs...@jefferies.com writes:

 Hello, all:

 While executing a bunch of JUnit tests within the same JVM (all 
executed 

 by IntelliJ IDEA) I started seeing strange and unexpected errors 
 occurring
 .
 Upon closer inspection, I noticed that in many of those tests tables 
 with 
 the same names are attempted to be created.
 Then I realized that although Derby is shutdown and then re-created, 
the 

 tables remain, thus causing collisions.

 The create=true connection attribute is ignored (with a warning) if
 the database with the same name alrady exists.


 I've created a digest (attached) which is executed as one JUnit test to 

 illustrate what I'm seeing.
 The behavior I would expect is that once Derby is shutdown, no tables 
 would remain in the JVM, and if a database (with the same name) is 
 re-created, it would be a tabula rasa.

 The tables are no longer in memory (or should not be unless you found a
 bug), but they are not erased from the disk image of the database.  As
 per the above, one would need to explicitly delete it using OS file
 system tools for the data to be cleared. Some JUnit tests delete tables
 in TestCase#tearDown, others use singleUseDatabaseDecorator to isolate
 itself from the rest of the tests. The tests not necessarily very
 consistent in their patterns for this..

 Dag


 Can you please let me know whether my expectations are erroneous and 
 whether I should find workarounds (albeit trivial to implement).
 However, what would concern me in that case is that the tables and the 
 data remain in the JVM, thus consuming memory
 (and, if unused, creating memory leaks).

 Thank you,

 Pavel.






 Jefferies archives and monitors outgoing and incoming e-mail. The
 contents of this email, including any attachments, are confidential to
 the ordinary user of the email address to which it was addressed. If
 you are not the addressee of this email you may not copy, forward,
 disclose or otherwise use it or any part of it in any form
 whatsoever. This email may be produced at the request of regulators or
 in connection with civil litigation. Jefferies accepts no liability
 for any errors or omissions arising as a result of transmission. Use
 by other than intended recipients is prohibited.  In the United
 Kingdom, Jefferies operates as Jefferies International Limited;
 registered in England: no. 1978621; registered office: Vintners Place,
 68 Upper Thames Street, London EC4V 3BJ.  Jefferies International
 Limited is authorised and regulated by the Financial Services
 Authority.






 Jefferies archives and monitors outgoing and incoming e-mail. The
 contents of this email, including any attachments, are confidential to
 the ordinary user of the email address to which it was addressed. If
 you are not the addressee of this email you may not copy, forward,
 disclose or otherwise use it or any part of it in any form
 whatsoever. This email may be produced at the request of regulators or
 in connection with civil litigation. Jefferies accepts no liability
 for any errors or omissions arising as a result of transmission. Use
 by other than intended recipients is prohibited.  In the United
 Kingdom, Jefferies operates as Jefferies International Limited;
 registered in England: no. 1978621; registered office: Vintners Place,
 68 Upper Thames Street, London EC4V 3BJ.  Jefferies International
 Limited is authorised and regulated by the Financial Services
 Authority.






Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection

Table exists in same JVM after Derby is shutdown

[Pavel Bortnovskiy]

Hello, all:

While executing a bunch of JUnit tests within the same JVM (all executed 
by IntelliJ IDEA) I started seeing strange and unexpected errors occurring
.
Upon closer inspection, I noticed that in many of those tests tables with 
the same names are attempted to be created.
Then I realized that although Derby is shutdown and then re-created, the 
tables remain, thus causing collisions.

I've created a digest (attached) which is executed as one JUnit test to 
illustrate what I'm seeing.
The behavior I would expect is that once Derby is shutdown, no tables 
would remain in the JVM, and if a database (with the same name) is 
re-created, it would be a tabula rasa.

Can you please let me know whether my expectations are erroneous and 
whether I should find workarounds (albeit trivial to implement).
However, what would concern me in that case is that the tables and the 
data remain in the JVM, thus consuming memory
(and, if unused, creating memory leaks).

Thank you,

Pavel.






Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

DerbyTest.java
Description: Binary data

ResultSet.getObject(int) for SMALLINT returns Integer, not Short

[Pavel Bortnovskiy]

Hello:

Can you please tell me whether this behavior expected or is it a bug?

1. a table with SMALLINT is created:

CREATE TABLE Test_Table (pk_VARCHAR_5FDG6 VARCHAR(256) NOT NULL, 
SMALLINT_Q6Y06 SMALLINT, PRIMARY KEY(pk_VARCHAR_5FDG6))

2. values are inserted and Java Short datatype is used to set SMALLINT 
values

3. values in the table are read with a PreparedStatement select * from 
Test_Table and then read with:

while (resultSet.next()) {
final Object object = resultSet.getObject(columnIdx);
}

4. at which point, resultSet.getMetaData().getColumnType(columnIdx) == 
SMALLINT, but object.getClass() is java.lang.Integer, whereas I would be 
expecting java.lang.Short.

Thank you,

Pavel.




Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

Re: ResultSet.getObject(int) for SMALLINT returns Integer, not Short

[Pavel Bortnovskiy]

Thank you, Knut, for your prompt response. 
However, such asymmetry (java.lang.Short-SMALLINT-java.lang.Integer) is 
causing trouble in my code and will require inefficient type checking to 
override such behavior.



From:
Knut Anders Hatlen knut.hat...@oracle.com
To:
Derby Discussion derby-user@db.apache.org
Date:
07/26/2011 04:13 PM
Subject:
Re: ResultSet.getObject(int) for SMALLINT returns Integer, not Short



Pavel Bortnovskiy pbortnovs...@jefferies.com writes:

 Hello: 

 Can you please tell me whether this behavior expected or is it a bug? 

 1. a table with SMALLINT is created: 

 CREATE TABLE Test_Table (pk_VARCHAR_5FDG6 VARCHAR(256) NOT NULL,
 SMALLINT_Q6Y06 SMALLINT, PRIMARY KEY(pk_VARCHAR_5FDG6)) 

 2. values are inserted and Java Short datatype is used to set SMALLINT
 values 

 3. values in the table are read with a PreparedStatement select *
 from Test_Table and then read with: 

 while (resultSet.next()) { 
 final Object object = resultSet.getObject(columnIdx); 
 } 

 4. at which point, resultSet.getMetaData().getColumnType(columnIdx) ==
 SMALLINT, but object.getClass() is java.lang.Integer, whereas I would
 be expecting java.lang.Short. 

Hi Pavel,

java.lang.Integer is the correct type for SMALLINT. See table B-3
(Mapping from JDBC Types to Java Object Types) in the JDBC 4.0
specification.

There's a note in the specification saying that this mapping is for
compatibility with JDBC 1.0, which was finalized before java.lang.Byte
and java.lang.Short were added to the Java language.

-- 
Knut Anders






Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

NullPointerException deep in Derby

[Pavel Bortnovskiy]

Hello:

I just got the following NullPointerException, seemingly inside Derby 
code:

2011-02-09 10:39:13,576 ERROR DataProcessor  - SQL Exception due to 
executing statement after shutdown
java.sql.SQLException: The exception 'java.lang.NullPointerException' was 
thrown while evaluating an expression.
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown 
Source)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown 
Source)
at org.apache.derby.impl.jdbc.Util.seeNextException(Unknown 
Source)
at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown 
Source)
at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown 
Source)
at 
org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at 
org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at 
org.apache.derby.impl.jdbc.EmbedResultSet.closeOnTransactionError(Unknown 
Source)
at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown 
Source)
at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source)
at 
com.jefco.processors.DataProcessor.process(DataProcessor.java:189)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.SQLException: The exception 
'java.lang.NullPointerException' was thrown while evaluating an 
expression.
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown 
Source)
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
 
Source)
... 13 more
Caused by: java.sql.SQLException: Java exception: ': 
java.lang.NullPointerException'.
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown 
Source)
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
 
Source)
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown 
Source)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown 
Source)
at org.apache.derby.impl.jdbc.Util.javaException(Unknown Source)
at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown 
Source)
... 10 more
Caused by: java.lang.NullPointerException
at 
org.apache.derby.exe.ac96c5c136x012ex0b13x5d52x9ccc25960.e9(Unknown 
Source)
at 
org.apache.derby.impl.services.reflect.DirectCall.invoke(Unknown Source)
at 
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.doProjection(Unknown 
Source)
at 
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown
 
Source)
at 
org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowFromSource(Unknown
 
Source)
at 
org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowCore(Unknown
 
Source)
at 
org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(Unknown 
Source)
... 5 more




Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

Re: NullPointerException deep in Derby

[Pavel Bortnovskiy]

Additional information:

the message executing statement after shutdown is misleading. There was 
no shutdown occurring and the connection was open and valid.

Our query (which I cannot show here for now) was complex, with case 
statements and group by.
Interestingly, as soon as the query was reworked to remove group by 
clause, the code worked flawlessly.

P.



From:
Pavel Bortnovskiy pbortnovs...@jefferies.com
To:
Derby Discussion derby-user@db.apache.org
Date:
02/09/2011 10:43 AM
Subject:
NullPointerException deep in Derby



Hello: 

I just got the following NullPointerException, seemingly inside Derby 
code: 

2011-02-09 10:39:13,576 ERROR DataProcessor  - SQL Exception due to 
executing statement after shutdown 
java.sql.SQLException: The exception 'java.lang.NullPointerException' was 
thrown while evaluating an expression. 
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown 
Source) 
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown 
Source) 
at org.apache.derby.impl.jdbc.Util.seeNextException(Unknown 
Source) 
at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown 
Source) 
at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown 
Source) 
at 
org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source) 

at 
org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source) 

at 
org.apache.derby.impl.jdbc.EmbedResultSet.closeOnTransactionError(Unknown 
Source) 
at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown 
Source) 
at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source) 
at 
com.jefco.processors.DataProcessor.process(DataProcessor.java:189) 
at java.lang.Thread.run(Thread.java:619) 
Caused by: java.sql.SQLException: The exception 
'java.lang.NullPointerException' was thrown while evaluating an 
expression. 
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown 
Source) 
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
 
Source) 
... 13 more 
Caused by: java.sql.SQLException: Java exception: ': 
java.lang.NullPointerException'. 
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown 
Source) 
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
 
Source) 
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown 
Source) 
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown 
Source) 
at org.apache.derby.impl.jdbc.Util.javaException(Unknown Source) 
at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown 
Source) 
... 10 more 
Caused by: java.lang.NullPointerException 
at 
org.apache.derby.exe.ac96c5c136x012ex0b13x5d52x9ccc25960.e9(Unknown 
Source) 
at 
org.apache.derby.impl.services.reflect.DirectCall.invoke(Unknown Source) 
at 
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.doProjection(Unknown 
Source) 
at 
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown
 
Source) 
at 
org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowFromSource(Unknown
 
Source) 
at 
org.apache.derby.impl.sql.execute.ScrollInsensitiveResultSet.getNextRowCore(Unknown
 
Source) 
at 
org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(Unknown 
Source) 
... 5 more 



Jefferies archives and monitors outgoing and incoming e-mail. The contents 
of this email, including any attachments, are confidential to the ordinary 
user of the email address to which it was addressed. If you are not the 
addressee of this email you may not copy, forward, disclose or otherwise 
use it or any part of it in any form whatsoever. This email may be 
produced at the request of regulators or in connection with civil 
litigation. Jefferies accepts no liability for any errors or omissions 
arising as a result of transmission. Use by other than intended recipients 
is prohibited. In the United Kingdom, Jefferies operates as Jefferies 
International Limited; registered in England: no. 1978621; registered 
office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies 
International Limited is authorised and regulated by the Financial 
Services Authority.





Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies

Re: NullPointerException deep in Derby

[Pavel Bortnovskiy]

Hello, Kathey:

thank you for prompt response.

I was running 10.6. Upgraded to 10.7 and encountered the same NPE.

Downgraded to 10.3 and 10.2 but got a different error 
Caused by: java.sql.SQLException: No suitable driver found for 
jdbc:derby:memory:testdb;create=true

What could be a problem there?



From:
Kathey Marsden kmarsdende...@sbcglobal.net
To:
Derby Discussion derby-user@db.apache.org
Cc:
Pavel Bortnovskiy pbortnovs...@jefferies.com
Date:
02/09/2011 12:45 PM
Subject:
Re: NullPointerException deep in Derby



On 2/9/2011 7:49 AM, Pavel Bortnovskiy wrote: 
Additional information: 

the message executing statement after shutdown is misleading. There was 
no shutdown occurring and the connection was open and valid. 

Our query (which I cannot show here for now) was complex, with case 
statements and group by. 
Interestingly, as soon as the query was reworked to remove group by 
clause, the code worked flawlessly. 

P. 

Hi Pavel, 

Since your problem is in generated code it is hard to figure out what went 
wrong without more information.
Firstly, I was wondering what version of Derby you are using?  I would 
suggest  moving to the latest release,  10.7 if you are not already there 
to see if the problem still exists. 


If it does, run your query with:
derby.debug.true=DumpClassFile and it will dump the generated class file 
which you can decompile and perhaps get a clue where the problem lies.  I 
don't think that Derby should ever throw this NPE, so I would say at least 
file a Jira bug  with the information that you can share and the stack 
trace.

As a further diagnostic step, there was a major rework and clean up of 
group by in 10.3,  DERBY-681 which resulted in some regressions. Usually 
when I see a group by issue, I will first try the build before and after 
that change to see if it might be related. If you don't want to build 
Derby, as a first step it would be interesting to see if occurs with 10.3. 
but not 10.2 to see if DERBY-681 is suspect. 

Thanks

Kathey




From: 
Pavel Bortnovskiy pbortnovs...@jefferies.com 
To: 
Derby Discussion derby-user@db.apache.org 
Date: 
02/09/2011 10:43 AM 
Subject: 
NullPointerException deep in Derby




Hello: 

I just got the following NullPointerException, seemingly inside Derby 
code: 

2011-02-09 10:39:13,576 ERROR DataProcessor  - SQL Exception due to 
executing statement after shutdown 
java.sql.SQLException: The exception 'java.lang.NullPointerException' was 
thrown while evaluating an expression. 
   at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown 
Source) 
   at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown 
Source) 
   at org.apache.derby.impl.jdbc.Util.seeNextException(Unknown Source) 

   at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(UnknownSource)
 

   at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(UnknownSource)
 

   at 
org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source) 

   at 
org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source) 

   at 
org.apache.derby.impl.jdbc.EmbedResultSet.closeOnTransactionError(UnknownSource)
 

   at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown 
Source) 
   at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source) 
   at 
com.jefco.processors.DataProcessor.process(DataProcessor.java:189) 
   at java.lang.Thread.run(Thread.java:619) 
Caused by: java.sql.SQLException: The exception 
'java.lang.NullPointerException' was thrown while evaluating an 
expression. 
   at 
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown 
Source) 
   at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(UnknownSource)
 

   ... 13 more 
Caused by: java.sql.SQLException: Java exception: ': 
java.lang.NullPointerException'. 
   at 
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown 
Source) 
   at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(UnknownSource)
 

   at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown 
Source) 
   at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown 
Source) 
   at org.apache.derby.impl.jdbc.Util.javaException(Unknown Source) 
   at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(UnknownSource)
 

   ... 10 more 
Caused by: java.lang.NullPointerException 
   at 
org.apache.derby.exe.ac96c5c136x012ex0b13x5d52x9ccc25960.e9(Unknown 
Source) 
   at org.apache.derby.impl.services.reflect.DirectCall.invoke(Unknown 
Source) 
   at 
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.doProjection(UnknownSource)
 

   at 
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(UnknownSource

Re: NullPointerException deep in Derby

[Pavel Bortnovskiy]

Did so, Kathey, but got the same error...

maybe I can't use URL=jdbc:derby:memory:testdb;create=true

in 10.2 and 10.3?



From:
Kathey Marsden kmarsdende...@sbcglobal.net
To:
Derby Discussion derby-user@db.apache.org
Cc:
Pavel Bortnovskiy pbortnovs...@jefferies.com
Date:
02/09/2011 01:32 PM
Subject:
Re: NullPointerException deep in Derby



On 2/9/2011 10:19 AM, Pavel Bortnovskiy wrote: 
Hello, Kathey: 

thank you for prompt response. 

I was running 10.6. Upgraded to 10.7 and encountered the same NPE. 

Downgraded to 10.3 and 10.2 but got a different error 
Caused by: java.sql.SQLException: No suitable driver found for 
jdbc:derby:memory:testdb;create=true 

What could be a problem there? 
Older versions do not have driver autoloading.  You will need to add:
Class.forName(org.apache.derby.jdbc.EmbeddedDriver).newInstance();  to 
your code in the beginning to get the driver loaded.






Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

Re: NullPointerException deep in Derby

[Pavel Bortnovskiy]

I've always used Derby only as an in-memory DB...



From:
Kathey Marsden kmarsdende...@sbcglobal.net
To:
Derby Discussion derby-user@db.apache.org
Cc:
Pavel Bortnovskiy pbortnovs...@jefferies.com
Date:
02/09/2011 01:54 PM
Subject:
Re: NullPointerException deep in Derby



On 2/9/2011 10:46 AM, Pavel Bortnovskiy wrote: 
Did so, Kathey, but got the same error... 

maybe I can't use URL=jdbc:derby:memory:testdb;create=true 

in 10.2 and 10.3? 

Oh yes, I missed that part. in nemory db came later.  Do you get the same 
error with the query against a regular database?







Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

Re: Issue with COUNT function

[Pavel Bortnovskiy]

I'd like to chime in and say that we run such SELECT COUNT(*) queries all 
the time in Derby and have never encountered a problem.
Don't think that AS aliasing is necessary.



From:
Chris Wilson ch...@aptivate.org
To:
Derby Discussion derby-user@db.apache.org
Date:
01/21/2011 01:07 PM
Subject:
Re: Issue with COUNT function



Hi NBW,

On Fri, 21 Jan 2011, NBW wrote:

 SELECT COUNT(*) FROM (SELECT file.md5, instCollection.collectionId FROM
 file, collection leafCollection, collection instCollection WHERE
 (file.status=1 OR file.status=2) and file.collectionId =
 leafCollection.collectionId AND leafCollection.instanceCollectionId =
 instCollection.collectionId GROUP BY file.md5, 
instCollection.collectionId)
 
 That query under Oracle works fine. Under Derby I get the following 
error:
 
 [42X01][3] Syntax error: Encountered EOF at line 1, column 313.
 
 If I run the query without the SELECT COUNT(*) it will run fine in Derby
 too, but I am trying to get the count :-)
 
 I feel like I must be missing something obvious here. 

I think that every sub-SELECT must have a table alias in Derby, so add AS 

t to the end of the query (after the closing parenthesis) and it might 
work :)

Cheers, Chris.
-- 
Aptivate | http://www.aptivate.org | Phone: +44 1223 760887
The Humanitarian Centre, Fenner's, Gresham Road, Cambridge CB1 2ES

Aptivate is a not-for-profit company registered in England and Wales
with company number 04980791.





Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

Can it be that Derby (in-memory) is deadlocking on this query?

[Pavel Bortnovskiy]

Hello:

while running my application, I noticed that when the following query 
(which uses a subquery with tables joining on themselves) is executed, the 
application processes 185 records and then sits indefinitely in 
ResultSet.next() method:

SELECT 
P1.ID ,
R1.description
P1.BOOK
P1.NOMINAL
P1.NOMINAL * R1.Factor
FROM
P_TABLE P1,
R_TABLE R1,
(
  select 
R2.IN_ID
  from 
P_TABLE P2,
R_TABLE R2
  where 
P2.IN_ID = R2.IN_ID AND
P2.NOMINAL  0  AND
R2.IType='X'
  GROUP BY R2.IN_ID 
  HAVING COUNT(*) 1
) as MULTI
WHERE
P1.IN_ID = R1.IN_ID  AND
MULTI.IN_ID = R1.IN_ID


Then I tried running AquaDataStudio with this query and it's been over 16 
minutes without any results back:



However, when I run the subquery itself, it executes practically 
instanteneously:



And if I replace the subquery with where R1.IN in ('P32764', 'P32765', ... 
[all results from subquery]), it executes in a few ms:



I have a suspicion that Derby (which is running in in-memory only mode) is 
deadlocking.
What can I do on my end (without exposing our data) to help you diagnose 
this.

Please respond as soon as you can, since this is quite important and 
urgent.

Thank you,
Pavel.




Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.image/gifimage/gifimage/gif

Fw: Can it be that Derby (in-memory) is deadlocking on this query? (UPDATE)

[Pavel Bortnovskiy]

the more I am working through this issue, the more I get convinced that 
it's a Derby issue.
When the select statement is modified slightly (not using joining of 
tables on themselves, but rather using IN), then everything works without 
a hiccup:

SELECT 
P1.ID ,
R1.description
P1.BOOK
P1.NOMINAL
P1.NOMINAL * R1.Factor
FROM
P_TABLE P1,
R_TABLE R1
WHERE
P1.IN_ID = R1.IN_ID  AND
R1.IN_ID in (
  select 
R2.IN_ID
  from 
P_TABLE P2,
R_TABLE R2
  where 
P2.IN_ID = R2.IN_ID AND
P2.NOMINAL  0  AND
R2.IType='X'
  GROUP BY R2.IN_ID 
  HAVING COUNT(*) 1
)


So, this leads me to believe that SELECT statements such as this causes a 
problem within Derby:

select
*
from
TABLE T1,
 (
 select field from TABLE T2
 ) as M
where
M.field = T1.field

- Forwarded by Pavel Bortnovskiy/JEFCO on 01/20/2011 03:55 PM -

From:
Pavel Bortnovskiy/JEFCO
To:
Derby Discussion derby-user@db.apache.org
Date:
01/20/2011 01:28 PM
Subject:
Can it be that Derby (in-memory) is deadlocking on this query?


Hello:

while running my application, I noticed that when the following query 
(which uses a subquery with tables joining on themselves) is executed, the 
application processes 185 records and then sits indefinitely in 
ResultSet.next() method:

SELECT 
P1.ID ,
R1.description
P1.BOOK
P1.NOMINAL
P1.NOMINAL * R1.Factor
FROM
P_TABLE P1,
R_TABLE R1,
(
  select 
R2.IN_ID
  from 
P_TABLE P2,
R_TABLE R2
  where 
P2.IN_ID = R2.IN_ID AND
P2.NOMINAL  0  AND
R2.IType='X'
  GROUP BY R2.IN_ID 
  HAVING COUNT(*) 1
) as MULTI
WHERE
P1.IN_ID = R1.IN_ID  AND
MULTI.IN_ID = R1.IN_ID


Then I tried running AquaDataStudio with this query and it's been over 16 
minutes without any results back:



However, when I run the subquery itself, it executes practically 
instanteneously:



And if I replace the subquery with where R1.IN in ('P32764', 'P32765', ... 
[all results from subquery]), it executes in a few ms:



I have a suspicion that Derby (which is running in in-memory only mode) is 
deadlocking.
What can I do on my end (without exposing our data) to help you diagnose 
this.

Please respond as soon as you can, since this is quite important and 
urgent.

Thank you,
Pavel.




Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.image/gifimage/gifimage/gif

Re: Can it be that Derby (in-memory) is deadlocking on this query? (UPDATE)

[Pavel Bortnovskiy]

Hello, Lily:

thank you for your response.

while I am still trying to create a test for Derby users, your experiment 
is not the same. Notice that in our query we are joining on the same 
table. So, T2 is not a different table. It's still the same T1 table, but 
aliased as T2...

try something like this:

select
*
from
TABLE T1,
(
select
T2.col
from
TABLE T2
) as M
where
M.col = T1.col

Once I succeed reproducing the error, I will show my test.

What makes me suspect that there is a problem with Derby is two-fold:
- our code seems to hang in ResultSet.next(), which is a call to Derby 
JDBC API implementation
- accessing those in-memory tables with AquaDataStudio and running the 
same query never returns/succeeds (I stopped after 20 minutes of having it 
running, where as the subqueries themselves run in a few milliseconds). 
AquaDataStudio is a commercial product and I doubt that it breaks 
precisely at the same point and query as our code...

P.



From:
Lily Wei lily...@yahoo.com
To:
pbortnovs...@jefferies.com
Cc:
lily...@yahoo.com
Date:
01/20/2011 05:25 PM
Subject:
Re: Can it be that Derby (in-memory) is deadlocking on this query? 
(UPDATE)



Hi Pavel:
 How are you? This is Lily Wei. I am one of the Derby committers. I am 
also doing projects for clients.

 I am curious in turn of how do you draw to conclusion that this is 
one of Derby's problem.
What error message you get from Derby? What message is print to derby.log.

 I did a simple experiment and the query seem to work. However,  it is 
totally possible that your query is different than my query.

 For example:
 =
create table T1 (col1 int, col2 char(20));
insert into T1 values (1, 'row 1';);
insert into T1 values (2, 'row 2');
create table T2 (col1 int, col2 char(20));
insert into T2 values (1, 'row 1 for T2');
insert into T2 values (2, 'row 2 for T2');
ij select T.col1, T.col2 from T1 T, (select col1, col2 from T2) as M 
where M.co
l1 = T.col1;
COL1   |COL2

1  |row 1
2  |row 2

  Would you mind forward me your table definition, your data and the 
query you were running that Derby can not handle. It will also be good to 
have derby.log information as well?

   Sorry for so many questions. I sincerely hope I can help you.


Thanks,
Lily


From: Pavel Bortnovskiy pbortnovs...@jefferies.com
To: Derby Discussion derby-user@db.apache.org
Sent: Thu, January 20, 2011 1:03:11 PM
Subject: Fw: Can it be that Derby (in-memory) is deadlocking on this 
query? (UPDATE)

the more I am working through this issue, the more I get convinced that 
it's a Derby issue. 
When the select statement is modified slightly (not using joining of 
tables on themselves, but rather using IN), then everything works without 
a hiccup: 

SELECT 
P1.ID , 
R1.description 
P1.BOOK 
P1.NOMINAL 
P1.NOMINAL * R1.Factor 
FROM 
P_TABLE P1, 
R_TABLE R1 
WHERE 
P1.IN_ID = R1.IN_ID  AND 
R1.IN_ID in ( 
  select 
  R2.IN_ID 
  from 
  P_TABLE P2, 
  R_TABLE R2 
  where 
  P2.IN_ID = R2.IN_ID AND 
  P2.NOMINAL  0  AND 
  R2.IType='X' 
  GROUP BY R2.IN_ID 
  HAVING COUNT(*) 1 
) 


So, this leads me to believe that SELECT statements such as this causes a 
problem within Derby: 

select 
* 
from 
TABLE T1, 
 ( 
 select field from TABLE T2 
 ) as M 
where 
M.field = T1.field 

- Forwarded by Pavel Bortnovskiy/JEFCO on 01/20/2011 03:55 PM - 
From: 
Pavel Bortnovskiy/JEFCO 
To: 
Derby Discussion derby-user@db.apache.org 
Date: 
01/20/2011 01:28 PM 
Subject: 
Can it be that Derby (in-memory) is deadlocking on this query?



Hello: 

while running my application, I noticed that when the following query 
(which uses a subquery with tables joining on themselves) is executed, the 
application processes 185 records and then sits indefinitely in 
ResultSet.next() method: 

SELECT 
P1.ID , 
R1.description 
P1.BOOK 
P1.NOMINAL 
P1.NOMINAL * R1.Factor 
FROM 
P_TABLE P1, 
R_TABLE R1, 
( 
  select 
  R2.IN_ID 
  from 
  P_TABLE P2, 
  R_TABLE R2 
  where 
  P2.IN_ID = R2.IN_ID AND 
  P2.NOMINAL  0  AND 
  R2.IType='X' 
  GROUP BY R2.IN_ID 
  HAVING COUNT(*) 1 
) as MULTI 
WHERE 
P1.IN_ID = R1.IN_ID  AND 
MULTI.IN_ID = R1.IN_ID 


Then I tried running AquaDataStudio with this query and it's been over 16 
minutes without any results back: 



However, when I run the subquery itself, it executes practically 
instanteneously: 



And if I replace the subquery with where R1.IN in ('P32764', 'P32765', ... 
[all results from subquery]), it executes in a few ms: 



I have a suspicion that Derby (which is running

Left-Join extremely slow in comparison to Inner-Join. How to tune Derby?

[Pavel Bortnovskiy]

Hello, all:

I am using Derby as an in-memory DB. While executing a complex query 
(which contains a union and several left-joins), we discovered that this 
query with a left join would take about 3.5 mins to execute and fetch the 
data, whereas the same query with the left join replaced with the inner 
join would take just 30 seconds.

What tools or API is available at our disposal to tune the in-memory 
Derby, to see its execution plan, to try to figure out why it is taking 
such an extraordinary time to fetch about 2,500-3000 records.

I also noticed that by running the same query over and over, the time to 
execute and fetch seems to be slowly creeping up as well - it started with 
2.5 mins, then grew to 3 mins and now it's almost 4 mins.

Any help would be greatly appreciated!

Regards,

Pavel.




Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

Fw: Left-Join extremely slow in comparison to Inner-Join. How to tune Derby?

[Pavel Bortnovskiy]

In addition, what seems to be odd is that fetching the data takes most of 
the time from the total time.
For example (snapshot from Aqua Data Studio retrieving the data from the 
Derby in-memory table):



Would it be considered normal to take over 2 minutes to fetch 2,357 
records?

Here is the same snapshot when the Left-Join was converted to the 
Inner-Join:






- Forwarded by Pavel Bortnovskiy/JEFCO on 01/05/2011 12:12 PM -

From:
Pavel Bortnovskiy/JEFCO
To:
Derby Discussion derby-user@db.apache.org
Date:
01/05/2011 12:04 PM
Subject:
Left-Join extremely slow in comparison to Inner-Join. How to tune Derby?


Hello, all:

I am using Derby as an in-memory DB. While executing a complex query 
(which contains a union and several left-joins), we discovered that this 
query with a left join would take about 3.5 mins to execute and fetch the 
data, whereas the same query with the left join replaced with the inner 
join would take just 30 seconds.

What tools or API is available at our disposal to tune the in-memory 
Derby, to see its execution plan, to try to figure out why it is taking 
such an extraordinary time to fetch about 2,500-3000 records.

I also noticed that by running the same query over and over, the time to 
execute and fetch seems to be slowly creeping up as well - it started with 
2.5 mins, then grew to 3 mins and now it's almost 4 mins.

Any help would be greatly appreciated!

Regards,

Pavel.




Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.image/gifimage/gif

Re: Left-Join extremely slow in comparison to Inner-Join. How to tune Derby?

[Pavel Bortnovskiy]

Hello, Witold:

thanks for your response. It might be the same issue and I will try to 
follow the same suggestions that Bryan gave you.

FYI: I am using Derby 10.6.2.1

If any of the Derby folks can shed some additional light onto this, it 
would be greatly appreciated,

Pavel.



From:
Witold Szczerba pljosh.m...@gmail.com
To:
Derby Discussion derby-user@db.apache.org
Date:
01/05/2011 01:37 PM
Subject:
Re: Left-Join extremely slow in comparison to Inner-Join. How to tune 
Derby?



Hi there,
Look at this:
https://issues.apache.org/jira/browse/DERBY-4595
Do you think it might be the same case I had some time ago?
There were some changes in the code in Jun/2010, maybe you have older
version or the change did not resolved the problem...

The funny thing is that the Apache Derby guys changed the issue type
from bug to improvement - so, for them, a query which should last 2 or
3 seconds is executing like 1 hour, fetching entire database, is just
a 'feature' :/

Regards,
Witold Szczerba






Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

Connecting to Derby via ODBC

[Pavel Bortnovskiy]

Hello:
Can Derbe in-memory DB be connected to via ODBC? And if so, what would be 
the right way to do it? We've tried various approaches and have been 
unsuccessful so far. For instance, this would be one of the errors 
received:

SQL30081N  A communication error has been detected.  Communication 
protocol
being used: TCP/IP.  Communication API being used: SOCKETS.  Location
where the error was detected: 10.152.73.11.  Communication function
detecting the error: connect.  Protocol specific error code(s): 10061, 
*,
*.  SQLSTATE=08001
Thank you,
Pavel.




Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

Socket Closed Exception when pinging NetworkServerControl

[Pavel Bortnovskiy]

Hello:

I am getting intermittent (rare, but causing fatal shutdown) exceptions 
when pinging NetworkServerControl:

2010-08-02 15:31:25,491 [ApacheDerbyServer] INFO 
components.DerbyServerComponent  - 2010-08-02 19:31:25.491 GMT : Error on 
client socket:Socket closed
2010-08-02 15:31:25,492 [ApacheDerbyServer] INFO 
components.DerbyServerComponent  - 2010-08-02 19:31:25.492 GMT : Socket 
closed
2010-08-02 15:31:25,492 [ApacheDerbyServer] INFO 
components.DerbyServerComponent  - java.net.SocketException: Socket closed
2010-08-02 15:31:25,492 [ApacheDerbyServer] INFO 
components.DerbyServerComponent  -at 
java.net.SocketInputStream.socketRead0(Native Method)
2010-08-02 15:31:25,493 [ApacheDerbyServer] INFO 
components.DerbyServerComponent  -at 
java.net.SocketInputStream.read(SocketInputStream.java:129)
2010-08-02 15:31:25,493 [ApacheDerbyServer] INFO 
components.DerbyServerComponent  -at 
java.net.SocketInputStream.read(SocketInputStream.java:90)
2010-08-02 15:31:25,493 [ApacheDerbyServer] INFO 
components.DerbyServerComponent  -at 
org.apache.derby.impl.drda.NetworkServerControlImpl.fillReplyBuffer(Unknown 
Source)
2010-08-02 15:31:25,494 [ApacheDerbyServer] INFO 
components.DerbyServerComponent  -at 
org.apache.derby.impl.drda.NetworkServerControlImpl.readResult(Unknown 
Source)
2010-08-02 15:31:25,494 [ApacheDerbyServer] INFO 
components.DerbyServerComponent  -at 
org.apache.derby.impl.drda.NetworkServerControlImpl.pingWithNoOpen(Unknown 
Source)
2010-08-02 15:31:25,494 [ApacheDerbyServer] INFO 
components.DerbyServerComponent  -at 
org.apache.derby.impl.drda.NetworkServerControlImpl.ping(Unknown Source)
2010-08-02 15:31:25,495 [ApacheDerbyServer] INFO 
components.DerbyServerComponent  -at 
org.apache.derby.drda.NetworkServerControl.ping(Unknown Source)
2010-08-02 15:31:25,495 [ApacheDerbyServer] INFO 
components.DerbyServerComponent  -at 
com.jefco.fi.geni.components.DerbyServerComponent.run(DerbyServerComponent.java:142)
2010-08-02 15:31:25,495 [ApacheDerbyServer] INFO 
components.DerbyServerComponent  -at 
java.lang.Thread.run(Thread.java:619)
2010-08-02 15:31:25,496 [ApacheDerbyServer] ERROR 
components.DerbyServerComponent  - Error while pinging 
NetworkServerControl:
java.net.SocketException: Socket closed
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(SocketInputStream.java:129)
at java.net.SocketInputStream.read(SocketInputStream.java:90)
at 
org.apache.derby.impl.drda.NetworkServerControlImpl.fillReplyBuffer(Unknown 
Source)
at 
org.apache.derby.impl.drda.NetworkServerControlImpl.readResult(Unknown 
Source)
at 
org.apache.derby.impl.drda.NetworkServerControlImpl.pingWithNoOpen(Unknown 
Source)
at 
org.apache.derby.impl.drda.NetworkServerControlImpl.ping(Unknown Source)
at org.apache.derby.drda.NetworkServerControl.ping(Unknown Source)

My code pings the NSC in two places - there is one thread which implements 
run() method like this:

final public void run() {
final Thread currentThread = Thread.currentThread();

try {
m_server = new NetworkServerControl(m_host, m_port);
m_server.start(new PrintWriter(new LoggingOutputStream(logger, 
Level.INFO), true));

try {
while (m_thread == currentThread) {
try {
Thread.sleep(m_sleep);

if (logger.isTraceEnabled()) {
logger.trace(Pinging NetworkServerControl on 
[ + m_host + : + m_port + ]);
}

---m_server.ping();   --- Exception Thrown
} catch (InterruptedException e) {
logger.info(toString() +  sleep has been 
interrupted);
}
}
} catch (Exception e) {
logger.error(Error while pinging NetworkServerControl:, 
e);
}

logger.info(Stopping NetworkServerControl on [ + m_host + 
: + m_port + ]);
m_server.shutdown();
} catch (Exception e) {
logger.error(Error while starting/stopping 
NetworkServerControl:, e);
}
}

And another validating thread calls this method at regular intervals:

final public String isRuntimeValid() {
final StringBuilder builder = new StringBuilder();
builder.append(super.isRuntimeValid());

if (m_server == null) builder.append(\n\t).append(Server is 
null);
if (m_thread == null) builder.append(\n\t).append(Thread is 
null);

try {
m_server.ping();
} catch (Exception e) {
builder.append(\n\t).append(Server cannot be pinged);
}

return builder.toString();
}

Can there be any problem if those two are called at around the same time? 
This is probably the only scenario which could, I imagine, cause a 
problem...
Would 

Re: Resetting ResultSet with first() or beforeFirst()

[Pavel Bortnovskiy]

Kristian:

thank you for being so observant. Actually, it was a programmatic typo on 
my part, as I just tried to type the pseudo-code into the email.
My production code does follow the described pattern:

final public Object getValue(ResultSet resultSet) throws SQLException 
{
final Object object = resultSet.getObject(m_columnIdx);
if (resultSet.wasNull()) {
return null;
}
return object;
}

/**/

final Object object = resultSet.getObject(i);
String string;
if (object == null || resultSet.wasNull()) {
string = m_nullValueIndicator;
} else {
switch (columnTypes[i]) {

/**/

while (resultSet.next()) {
tableBuilder.append(TR);
for (int i = 1; i = columnCount; i++) {
final String string = resultSet.getString(i);
final String value = (string == null || 
resultSet.wasNull() ? nbsp; : string.trim());
tableBuilder.append(TD).append(value).append(/TD);
}
tableBuilder.append(/TR\n);
}


Much appreciated,
Pavel.






Kristian Waagan kristian.waa...@sun.com 
Sent by: kristian.waa...@sun.com
06/10/2010 04:59 AM
Please respond to
Derby Discussion derby-user@db.apache.org


To
derby-user@db.apache.org
cc

Subject
Re: Resetting ResultSet with first() or beforeFirst()






On 09.06.10 19:32, Pavel Bortnovskiy wrote:

 Hi:

 I am trying to reset a given ResultSet back to the first record, so that
 I could iterate through its records one more time, after it's already
 been done once. So, my code does something like this (it's pseudo-code,
 and in the real application these two passes are done in different parts
 of the application):

 // get ResultSet
 final ResultSet resultSet = ((PreparedStatement)
 m_statement).executeQuery();
 final int columnCount = resultSet.getMetaData().getColumnCount();
 // First Pass through the data
 while (resultSet.next()) {
 for (int i = 1; i = columnCount; i++) {
 if (resultSet.wasNull()) {

Hi,

I don't know of this issue is present in your actual code, but note that 
ResultSet.wasNull must be called *after* a getter method has been called.
 From the JavaDocs:

Reports whether the last column read had a value of SQL NULL. Note that 
you must first call one of the getter methods on a column to try to read 
its value and then call the method wasNull to see if the value read was 
SQL NULL.

 doSomethingWithNULL();
 } else {
 doSomethingWithObject(resultSet.getObject(i));

Assuming wasNull was called after the getter method, the second 
getObject above may raise an exception in Derby. The restriction that 
you can call a getter only once for a given column is limited to some 
data types (i.e. BLOB and CLOB) and some getters (getXXXStream, 
getObject for the data types mentioned).

Just thought I'd mention it :)


Regards,
-- 
Kristian

 }
 }
 }
 // Reset ResultSet - but it generates SQLException: The 'beforeFirst()'
 method is only allowed on scroll cursors
 resultSet.beforeFirst();
 // Second Pass through the data
 while (resultSet.next()) {
 for (int i = 1; i = columnCount; i++) {
 if (resultSet.wasNull()) {
 doSomethingElseWithNULL();
 } else {
 doSomethingElseWithObject(resultSet.getObject(i));
 }
 }
 }


 However, resultSet.beforeFirst() generates an exception:

 java.sql.SQLException: The 'beforeFirst()' method is only allowed on
 scroll cursors.
 at
 org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
 Source)
 at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
 at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
 at org.apache.derby.impl.jdbc.EmbedResultSet.checkScrollCursor(Unknown
 Source)
 at org.apache.derby.impl.jdbc.EmbedResultSet.beforeFirst(Unknown Source)
 Caused by: java.sql.SQLException: The 'beforeFirst()' method is only
 allowed on scroll cursors.
 at
 org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
 Source)
 at
 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
 Source)
 ... 8 more

 Could someone please help me out to understand what may be done
 incorrectly and what would be the right approach to enable two-pass
 logic through the RecordSet?

 Thanks,

 Pavel.



 Jefferies archives and monitors outgoing and incoming e-mail. The
 contents of this email, including any attachments, are confidential to
 the ordinary user of the email address to which it was addressed. If you
 are not the addressee of this email you may not copy, forward, disclose
 or otherwise use it or any part of it in any form whatsoever. This email
 may be produced at the request of regulators or in connection with civil
 litigation. Jefferies accepts no liability for any errors or omissions
 arising as a result

Resetting ResultSet with first() or beforeFirst()

[Pavel Bortnovskiy]

Hi:

I am trying to reset a given ResultSet back to the first record, so that I 
could iterate through its records one more time, after it's already been 
done once. So, my code does something like this (it's pseudo-code, and in 
the real application these two passes are done in different parts of the 
application):

// get ResultSet
final ResultSet resultSet = ((PreparedStatement) 
m_statement).executeQuery();
final int columnCount = resultSet.getMetaData().getColumnCount();
// First Pass through the data
while (resultSet.next()) {
for (int i = 1; i = columnCount; i++) {
if (resultSet.wasNull()) {
doSomethingWithNULL();
} else {
doSomethingWithObject(resultSet.getObject(i));
}
}
}
// Reset ResultSet - but it generates SQLException: The 'beforeFirst()' 
method is only allowed on scroll cursors
resultSet.beforeFirst();
// Second Pass through the data
while (resultSet.next()) {
for (int i = 1; i = columnCount; i++) {
if (resultSet.wasNull()) {
doSomethingElseWithNULL();
} else {
doSomethingElseWithObject(resultSet.getObject(i));
}
}
}


However, resultSet.beforeFirst() generates an exception:

java.sql.SQLException: The 'beforeFirst()' method is only allowed on 
scroll cursors.
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown 
Source)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown 
Source)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown 
Source)
at 
org.apache.derby.impl.jdbc.EmbedResultSet.checkScrollCursor(Unknown 
Source)
at org.apache.derby.impl.jdbc.EmbedResultSet.beforeFirst(Unknown 
Source)
Caused by: java.sql.SQLException: The 'beforeFirst()' method is only 
allowed on scroll cursors.
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown 
Source)
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
 
Source)
... 8 more

Could someone please help me out to understand what may be done 
incorrectly and what would be the right approach to enable two-pass logic 
through the RecordSet?

Thanks,

Pavel.




Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

Re: Resetting ResultSet with first() or beforeFirst()

[Pavel Bortnovskiy]

Thank you, Knut.

Is there a large performance penalty for specifying 
ResultSet.TYPE_SCROLL_INSENSITIVE, ||ResultSet.CONCUR_READ_ONLY?
For the sake of efficiency, should my code set those only when it is 
necessary to reset the ResultSet?

Pavel.






Knut Anders Hatlen knut.hat...@sun.com 
Sent by: knut.hat...@sun.com
06/09/2010 06:21 PM
Please respond to
Derby Discussion derby-user@db.apache.org


To
derby-user@db.apache.org
cc

Subject
Re: Resetting ResultSet with first() or beforeFirst()






On 06/ 9/10 07:32 PM, Pavel Bortnovskiy wrote:

 Hi:

 I am trying to reset a given ResultSet back to the first record, so
 that I could iterate through its records one more time, after it's
 already been done once. So, my code does something like this (it's
 pseudo-code, and in the real application these two passes are done in
 different parts of the application):

 // get ResultSet
 final ResultSet resultSet = ((PreparedStatement)
 m_statement).executeQuery();
 final int columnCount = resultSet.getMetaData().getColumnCount();
 // First Pass through the data
 while (resultSet.next()) {
 for (int i = 1; i = columnCount; i++) {
 if (resultSet.wasNull()) {
 doSomethingWithNULL();
 } else {
 doSomethingWithObject(resultSet.getObject(i));
 }
 }
 }
 // Reset ResultSet - but it generates SQLException: The
 'beforeFirst()' method is only allowed on scroll cursors
 resultSet.beforeFirst();
 // Second Pass through the data
 while (resultSet.next()) {
 for (int i = 1; i = columnCount; i++) {
 if (resultSet.wasNull()) {
 doSomethingElseWithNULL();
 } else {
 
 doSomethingElseWithObject(resultSet.getObject(i));
 }
 }
 }


 However, resultSet.beforeFirst() generates an exception:

 java.sql.SQLException: The 'beforeFirst()' method is only allowed on
 scroll cursors.
 at
 org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
 Source)
 at
 org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
 at
 org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
 at
 org.apache.derby.impl.jdbc.EmbedResultSet.checkScrollCursor(Unknown
 Source)
 at
 org.apache.derby.impl.jdbc.EmbedResultSet.beforeFirst(Unknown Source)
 Caused by: java.sql.SQLException: The 'beforeFirst()' method is only
 allowed on scroll cursors.
 at
 org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
 Source)
 at
 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
 Source)
 ... 8 more

 Could someone please help me out to understand what may be done
 incorrectly and what would be the right approach to enable two-pass
 logic through the RecordSet?

You need to specify that the result should be scrollable when you
prepare the statement. Something like this:

PreparedStatement ps = conn.prepareStatement(
sql, |ResultSet.TYPE_SCROLL_INSENSITIVE, 
||ResultSet.CONCUR_READ_ONLY);|


-- 
Knut Anders







Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

Case Sensitivity

[Pavel Bortnovskiy]

Hello, all:

when executing a statement, such as:

select
Id as id,
Code as code,
TypeStr as typeStr
  from MyTable

against a Derby in-memory table, the ResultSetMetaData present column 
names all in upper case and the original case is not preserved.

Is there a setting which would allow to preserve the case specified in the 
SQL statement, i.e. with the statement above, the first column would be 
id, second - code, third - typeStr, etc...

Thank you,

Pavel.




Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

Re: Case Sensitivity

[Pavel Bortnovskiy]

Thank you, Peter, for your prompt response. Is enclosing them in quotes 
the only way to do so? Is there any setting which can be applied to Derby 
(or a -Define), so that the quotes could be omitted?






Peter Ondru?ka peter.ondru...@gmail.com 
03/24/2010 05:33 PM
Please respond to
Derby Discussion derby-user@db.apache.org


To
Derby Discussion derby-user@db.apache.org
cc

Subject
Re: Case Sensitivity






select
Id as id,
Code as code,
TypeStr as typeStr
  from MyTable

On Wed, Mar 24, 2010 at 10:30 PM, Pavel Bortnovskiy
pbortnovs...@jefferies.com wrote:

 Hello, all:

 when executing a statement, such as:

 select
 Id as id,
 Code as code,
 TypeStr as typeStr
   from MyTable

 against a Derby in-memory table, the ResultSetMetaData present column 
names
 all in upper case and the original case is not preserved.

 Is there a setting which would allow to preserve the case specified in 
the
 SQL statement, i.e. with the statement above, the first column would be
 id, second - code, third - typeStr, etc...

 Thank you,

 Pavel.



 Jefferies archives and monitors outgoing and incoming e-mail. The 
contents
 of this email, including any attachments, are confidential to the 
ordinary
 user of the email address to which it was addressed. If you are not the
 addressee of this email you may not copy, forward, disclose or otherwise 
use
 it or any part of it in any form whatsoever. This email may be produced 
at
 the request of regulators or in connection with civil litigation. 
Jefferies
 accepts no liability for any errors or omissions arising as a result of
 transmission. Use by other than intended recipients is prohibited.  In 
the
 United Kingdom, Jefferies operates as Jefferies International Limited;
 registered in England: no. 1978621; registered office: Vintners Place, 
68
 Upper Thames Street, London EC4V 3BJ.  Jefferies International Limited 
is
 authorised and regulated by the Financial Services Authority.






Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

Re: Extracting dependencies from SQL statements

[Pavel Bortnovskiy]

Thank you, Rick for your expedient response.

Your comments to DERBY-3946 indicate that I need to apply the patch and 
build Derby. Is it possible to avoid? (I am currently using Derby version 
10.5.3.0).

I tried to compile only TreeWalker.java and ASTParser.java while linking 
with Derby's jars, but the build was unsuccessful, as many classes weren't 
found.
Would you be able to advise, please?

Thanks,
Pavel.







Rick Hillegas richard.hille...@sun.com 
Sent by: richard.hille...@sun.com
03/09/2010 08:35 AM
Please respond to
Derby Discussion derby-user@db.apache.org


To
Derby Discussion derby-user@db.apache.org
cc

Subject
Re: Extracting dependencies from SQL statements






Hi Pavel,

I don't know of an easy way to do this. You could run the statement 
through the Derby parser to get the parsed representation, the Abstract 
Syntax Tree. Then you could write a Visitor to walk the AST, looking for 
the nodes which represent tables. See the following JIRAs for some 
pointers on how to produce and walk the AST: DERBY-3946 and DERBY-791.

Unfortunately, there is no systematic primer on the AST nodes 
themselves. All we have is the javadoc for the package 
org.apache.derby.impl.sql.compile.

Hope this helps,
-Rick

Pavel Bortnovskiy wrote:

 Hello:

 is it possible to use Derby's SQL parser to extract dependencies 
 from a given SQL statement?
 (or access the parser once the statement has been parsed).

 Whether it's a simple SELECT or a JOIN, UNION or a more complex 
 statement, I would like to get a list of tables that this statement 
 would depend on.
 Looking for FROM clauses and attempting to do the parsing myself seems 
 like a difficult, error prone and impractical way to approach this.

 Any suggestions, please?

 Thanks,
 Pavel.



 Jefferies archives and monitors outgoing and incoming e-mail. The 
 contents of this email, including any attachments, are confidential to 
 the ordinary user of the email address to which it was addressed. If 
 you are not the addressee of this email you may not copy, forward, 
 disclose or otherwise use it or any part of it in any form whatsoever. 
 This email may be produced at the request of regulators or in 
 connection with civil litigation. Jefferies accepts no liability for 
 any errors or omissions arising as a result of transmission. Use by 
 other than intended recipients is prohibited.  In the United Kingdom, 
 Jefferies operates as Jefferies International Limited; registered in 
 England: no. 1978621; registered office: Vintners Place, 68 Upper 
 Thames Street, London EC4V 3BJ.  Jefferies International Limited is 
 authorised and regulated by the Financial Services Authority. 







Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

Re: Extracting dependencies from SQL statements

[Pavel Bortnovskiy]

I was able to compile both files by disabling SanityManager method 
invocation and replacing ContextId.LANG_CONNECTION with its string 
LanguageConnectionContext. 
No other changes to ASTParser and TreeWalker have been done, however, when 
running them the following exception is thrown:

Parsing:
select a from t, s where t.a = s.a
Exception in thread main java.sql.SQLSyntaxErrorException: Table/View 
'T' does not exist.
at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown 
Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown 
Source)
at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown 
Source)
at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown 
Source)
at 
org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at 
org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at 
org.apache.derby.impl.jdbc.EmbedPreparedStatement.init(Unknown Source)
at 
org.apache.derby.impl.jdbc.EmbedPreparedStatement20.init(Unknown Source)
at 
org.apache.derby.impl.jdbc.EmbedPreparedStatement30.init(Unknown Source)
at 
org.apache.derby.impl.jdbc.EmbedPreparedStatement40.init(Unknown Source)
at 
org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
at 
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown 
Source)
at 
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown 
Source)
at TreeWalker.execute(TreeWalker.java:95)
at TreeWalker.main(TreeWalker.java:79)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at 
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at 
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at 
com.intellij.rt.execution.application.AppMain.main(AppMain.java:110)






Rick Hillegas richard.hille...@sun.com 
Sent by: richard.hille...@sun.com
03/09/2010 08:35 AM
Please respond to
Derby Discussion derby-user@db.apache.org


To
Derby Discussion derby-user@db.apache.org
cc

Subject
Re: Extracting dependencies from SQL statements






Hi Pavel,

I don't know of an easy way to do this. You could run the statement 
through the Derby parser to get the parsed representation, the Abstract 
Syntax Tree. Then you could write a Visitor to walk the AST, looking for 
the nodes which represent tables. See the following JIRAs for some 
pointers on how to produce and walk the AST: DERBY-3946 and DERBY-791.

Unfortunately, there is no systematic primer on the AST nodes 
themselves. All we have is the javadoc for the package 
org.apache.derby.impl.sql.compile.

Hope this helps,
-Rick

Pavel Bortnovskiy wrote:

 Hello:

 is it possible to use Derby's SQL parser to extract dependencies 
 from a given SQL statement?
 (or access the parser once the statement has been parsed).

 Whether it's a simple SELECT or a JOIN, UNION or a more complex 
 statement, I would like to get a list of tables that this statement 
 would depend on.
 Looking for FROM clauses and attempting to do the parsing myself seems 
 like a difficult, error prone and impractical way to approach this.

 Any suggestions, please?

 Thanks,
 Pavel.



 Jefferies archives and monitors outgoing and incoming e-mail. The 
 contents of this email, including any attachments, are confidential to 
 the ordinary user of the email address to which it was addressed. If 
 you are not the addressee of this email you may not copy, forward, 
 disclose or otherwise use it or any part of it in any form whatsoever. 
 This email may be produced at the request of regulators or in 
 connection with civil litigation. Jefferies accepts no liability for 
 any errors or omissions arising as a result of transmission. Use by 
 other than intended recipients is prohibited.  In the United Kingdom, 
 Jefferies operates as Jefferies International Limited; registered in 
 England: no. 1978621; registered office: Vintners Place, 68 Upper 
 Thames Street, London EC4V 3BJ.  Jefferies International Limited is 
 authorised and regulated by the Financial Services Authority. 







Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies

Re: Extracting dependencies from SQL statements

[Pavel Bortnovskiy]

Rick (while I am reading  and trying DERBY-4415), that seems like there is 
no other way to use prod jars and yet stop the parser from binding?

Would it be at all possible to use the javacc compiler with sqlgrammar.jj 
(that are probably bundled in derby.jar) to do the work?

Pavel.






Rick Hillegas richard.hille...@sun.com 
Sent by: richard.hille...@sun.com
03/09/2010 01:13 PM
Please respond to
Derby Discussion derby-user@db.apache.org


To
Derby Discussion derby-user@db.apache.org
cc

Subject
Re: Extracting dependencies from SQL statements






Hi Pavel,

You are right, the XmlTreeWalker class also tries to stop the compiler 
after parsing. This can only be done with a debug engine. The error you 
are seeing comes from the bind() phase of compilation. Derby is 
complaining that the query doesn't make sense: the objects it mentions 
don't exist.

There's another issue linked from the XmlTreeWalker issue. Take a look 
at DERBY-4415. This is a slightly more involved solution but may get you 
closer. This solution assumes that you are trying to examine the various 
phases of the AST for a query against real objects in your database. 
This should work with the production jars.

Hope this is more useful,
-Rick

Pavel Bortnovskiy wrote:

 Once again, Rick, thanks for your prompt response. To answer your 
 questions:

 (1) The compilation errors initially had to do with the SanityManager 
 and ContextId not available in prod jars.

 (2) Since I am trying to get this code to work in our application, 
 using debug jars may not be desirable.
 More over, our app is using Derby's in-memory tables as well as its 
 NetworkServerController.
 So, this parsing/tree-walking code should not conflict with the other 
 two uses.

 In the comments for DERBY-791, I did notice We should provide some 
 mechanism for printing these trees in production (non-debug) servers.
 So, I ran XmlTreeWalker linking only to 
 db-derby-10.5.3.0-bin/lib/derby.jar, but got the same exception:

 ?xml version=1.0 encoding=UTF-8?
 queryTree
 queryTextselect a from t, s where t.a = s.a/queryText
 Exception in thread main java.sql.SQLSyntaxErrorException: 
 Table/View 'T' does not exist.
 at 
 org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown 

 Source)
 at 
 org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
 at 
 
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown 

 Source)
 at 
 
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown 

 Source)
 at 
 org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown 
 Source)
 at 
 org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown 
 Source)
 at 
 org.apache.derby.impl.jdbc.EmbedPreparedStatement.init(Unknown Source)
 at 
 org.apache.derby.impl.jdbc.EmbedPreparedStatement20.init(Unknown 
 Source)
 at 
 org.apache.derby.impl.jdbc.EmbedPreparedStatement30.init(Unknown 
 Source)
 at 
 org.apache.derby.impl.jdbc.EmbedPreparedStatement40.init(Unknown 
 Source)
 at 
 org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
 at 
 org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown 
 Source)
 at 
 org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown 
 Source)
 at XmlTreeWalker.execute(XmlTreeWalker.java:130)
 at XmlTreeWalker.main(XmlTreeWalker.java:111)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at 
 
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) 


 at 
 
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
 


 at java.lang.reflect.Method.invoke(Method.java:597)
 at 
 com.intellij.rt.execution.application.AppMain.main(AppMain.java:110)
 Caused by: java.sql.SQLException: Table/View 'T' does not exist.
 at 
 org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown 
 Source)
 at 
 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
 

 Source)
 ... 20 more
 Caused by: ERROR 42X05: Table/View 'T' does not exist.
 at 
 org.apache.derby.iapi.error.StandardException.newException(Unknown 
 Source)
 at 
 
org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unknown 

 Source)
 at 
 org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknown 

 Source)
 at 
 org.apache.derby.impl.sql.compile.FromList.bindTables(Unknown Source)
 at 
 org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(Unknown 
 Source)
 at 
 org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(Unknown 
 Source)
 at 
 org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source)
 at 
 org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown

Re: Extracting dependencies from SQL statements

[Pavel Bortnovskiy]

Rick:

ASTVisitor (used in public class XmlASTPrinter implements ASTVisitor) is 
nowhere to be found: import org.apache.derby.iapi.sql.compile.ASTVisitor;

It is neither in the src tree nor in the JIRA DERBY-4415. Can you please 
advise?

Thanks,
Pavel.






Rick Hillegas richard.hille...@sun.com 
Sent by: richard.hille...@sun.com
03/09/2010 01:13 PM
Please respond to
Derby Discussion derby-user@db.apache.org


To
Derby Discussion derby-user@db.apache.org
cc

Subject
Re: Extracting dependencies from SQL statements






Hi Pavel,

You are right, the XmlTreeWalker class also tries to stop the compiler 
after parsing. This can only be done with a debug engine. The error you 
are seeing comes from the bind() phase of compilation. Derby is 
complaining that the query doesn't make sense: the objects it mentions 
don't exist.

There's another issue linked from the XmlTreeWalker issue. Take a look 
at DERBY-4415. This is a slightly more involved solution but may get you 
closer. This solution assumes that you are trying to examine the various 
phases of the AST for a query against real objects in your database. 
This should work with the production jars.

Hope this is more useful,
-Rick

Pavel Bortnovskiy wrote:

 Once again, Rick, thanks for your prompt response. To answer your 
 questions:

 (1) The compilation errors initially had to do with the SanityManager 
 and ContextId not available in prod jars.

 (2) Since I am trying to get this code to work in our application, 
 using debug jars may not be desirable.
 More over, our app is using Derby's in-memory tables as well as its 
 NetworkServerController.
 So, this parsing/tree-walking code should not conflict with the other 
 two uses.

 In the comments for DERBY-791, I did notice We should provide some 
 mechanism for printing these trees in production (non-debug) servers.
 So, I ran XmlTreeWalker linking only to 
 db-derby-10.5.3.0-bin/lib/derby.jar, but got the same exception:

 ?xml version=1.0 encoding=UTF-8?
 queryTree
 queryTextselect a from t, s where t.a = s.a/queryText
 Exception in thread main java.sql.SQLSyntaxErrorException: 
 Table/View 'T' does not exist.
 at 
 org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown 

 Source)
 at 
 org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
 at 
 
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown 

 Source)
 at 
 
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown 

 Source)
 at 
 org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown 
 Source)
 at 
 org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown 
 Source)
 at 
 org.apache.derby.impl.jdbc.EmbedPreparedStatement.init(Unknown Source)
 at 
 org.apache.derby.impl.jdbc.EmbedPreparedStatement20.init(Unknown 
 Source)
 at 
 org.apache.derby.impl.jdbc.EmbedPreparedStatement30.init(Unknown 
 Source)
 at 
 org.apache.derby.impl.jdbc.EmbedPreparedStatement40.init(Unknown 
 Source)
 at 
 org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
 at 
 org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown 
 Source)
 at 
 org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown 
 Source)
 at XmlTreeWalker.execute(XmlTreeWalker.java:130)
 at XmlTreeWalker.main(XmlTreeWalker.java:111)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at 
 
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) 


 at 
 
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
 


 at java.lang.reflect.Method.invoke(Method.java:597)
 at 
 com.intellij.rt.execution.application.AppMain.main(AppMain.java:110)
 Caused by: java.sql.SQLException: Table/View 'T' does not exist.
 at 
 org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown 
 Source)
 at 
 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
 

 Source)
 ... 20 more
 Caused by: ERROR 42X05: Table/View 'T' does not exist.
 at 
 org.apache.derby.iapi.error.StandardException.newException(Unknown 
 Source)
 at 
 
org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unknown 

 Source)
 at 
 org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknown 

 Source)
 at 
 org.apache.derby.impl.sql.compile.FromList.bindTables(Unknown Source)
 at 
 org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(Unknown 
 Source)
 at 
 org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(Unknown 
 Source)
 at 
 org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source)
 at 
 org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown 
 Source

Re: Extracting dependencies from SQL statements

[Pavel Bortnovskiy]

Rick: 

your second point grossly understimated the amount of time it took me to 
get frustrated... it was not quick - it was instanteneous - as soon as I 
opened sqlgrammar.jj  ;-D

Last question, then, on the subject. Is there any way to easily simplify 
sqlgrammar.jj, so that it frees itself of all the derby dependencies and 
so that
javacc'ed code can just take a string and give me some kind of lexical 
tree?

Alternatively, javacc project has various .jj files in its repository, two 
of which are PL/SQL-based (there is one for Oracle 9i).
Perhaps using those .jj files may work for my purposes?.. Your thoughts 
would be appreciated.

Thank you for your time and attention.

Pavel.






Rick Hillegas richard.hille...@sun.com 
Sent by: richard.hille...@sun.com
03/09/2010 02:45 PM
Please respond to
Derby Discussion derby-user@db.apache.org


To
Derby Discussion derby-user@db.apache.org
cc

Subject
Re: Extracting dependencies from SQL statements






Hi Pavel,

Some comments inline...

Pavel Bortnovskiy wrote:

 Rick (while I am reading  and trying DERBY-4415), that seems like 
 there is no other way to use prod jars and yet stop the parser from 
 binding?

It looks to me as though the shortcircuiting logic is guarded by a check 
for whether the engine is the debug version.

 Would it be at all possible to use the javacc compiler with 
 sqlgrammar.jj (that are probably bundled in derby.jar) to do the work?
I think you will discover that the grammar relies on a fair amount of 
session context supplied by the rest of the Derby SQL interpreter. I 
think you will be frustrated very quickly if you try this approach.

Hope this helps,
-Rick

 Pavel.





 *Rick Hillegas richard.hille...@sun.com*
 Sent by: richard.hille...@sun.com

 03/09/2010 01:13 PM
 Please respond to
 Derby Discussion derby-user@db.apache.org


 
 To
Derby Discussion derby-user@db.apache.org
 cc
 
 Subject
Re: Extracting dependencies from SQL statements



 





 Hi Pavel,

 You are right, the XmlTreeWalker class also tries to stop the compiler
 after parsing. This can only be done with a debug engine. The error you
 are seeing comes from the bind() phase of compilation. Derby is
 complaining that the query doesn't make sense: the objects it mentions
 don't exist.

 There's another issue linked from the XmlTreeWalker issue. Take a look
 at DERBY-4415. This is a slightly more involved solution but may get you
 closer. This solution assumes that you are trying to examine the various
 phases of the AST for a query against real objects in your database.
 This should work with the production jars.

 Hope this is more useful,
 -Rick

 Pavel Bortnovskiy wrote:
 
  Once again, Rick, thanks for your prompt response. To answer your
  questions:
 
  (1) The compilation errors initially had to do with the SanityManager
  and ContextId not available in prod jars.
 
  (2) Since I am trying to get this code to work in our application,
  using debug jars may not be desirable.
  More over, our app is using Derby's in-memory tables as well as its
  NetworkServerController.
  So, this parsing/tree-walking code should not conflict with the other
  two uses.
 
  In the comments for DERBY-791, I did notice We should provide some
  mechanism for printing these trees in production (non-debug) servers.
  So, I ran XmlTreeWalker linking only to
  db-derby-10.5.3.0-bin/lib/derby.jar, but got the same exception:
 
  ?xml version=1.0 encoding=UTF-8?
  queryTree
  queryTextselect a from t, s where t.a = s.a/queryText
  Exception in thread main java.sql.SQLSyntaxErrorException:
  Table/View 'T' does not exist.
  at
  
 org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
  Source)
  at
  org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
  at
  
 
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown 


  Source)
  at
  
 
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown 


  Source)
  at
  org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
  Source)
  at
  org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
  Source)
  at
  org.apache.derby.impl.jdbc.EmbedPreparedStatement.init(Unknown 
Source)
  at
  org.apache.derby.impl.jdbc.EmbedPreparedStatement20.init(Unknown
  Source)
  at
  org.apache.derby.impl.jdbc.EmbedPreparedStatement30.init(Unknown
  Source)
  at
  org.apache.derby.impl.jdbc.EmbedPreparedStatement40.init(Unknown
  Source)
  at
  org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown 
Source)
  at
  org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
  Source)
  at
  org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
  Source)
  at XmlTreeWalker.execute(XmlTreeWalker.java:130)
  at XmlTreeWalker.main(XmlTreeWalker.java:111

Re: Extracting dependencies from SQL statements

[Pavel Bortnovskiy]

Rick:

thank you for your patience and continuing support on this issue.

(1) Even prior to your email, I grepped for StopAfterParsing and found the 
code you are referring to in GenericStatement.java.
I am not too keen on going that route, since that would require rebuilding 
Derby and I would rather not temper with prod-quality code.

(2) I have downloaded that PlSql.jj and ran javacc on it. It generated a 
bunch of java files and with a small change to the main, I got it to 
report:

Tables in select (select 1 from dual), a.string, b.string from a, b where 
exists (select * from c where c.id = a.id);:
A
B
C
DUAL

This is what I am looking for.

(3) My application is Derby powered. The app is relying on Derby's 
in-memory tables for internal data exchange and NetworkServerController to 
monitor these tables from the outside 
of the application. However, just as you properly assumed, parts of my 
application need to analyze dependencies. So, a portion of the application 
(with a configured select) may depend on other
components (whose data may be accessed by the configured select). So, 
while making that dependency analysis, I would need to get a list of all 
the tables referenced in the select.
So far I have done a regex search for the word where and then had to do 
some hacky parsing thereafter. But I don't like that approach. Besides, in 
the current form it wouldn't be able to
handle more complex, nested statements (as the one above that I've tested 
PlSql.jj with).

(4) Off this particular topic. While looking through Derby's code I 
discovered to my amazement that Derby is using OSGi (Apache Felix). Can 
you please take a moment and enlighten me 
a little on what it is used for? The reason why I am asking is that I am 
using already a lot of Apache code (CLI, CODEC, DBCP, POOL) besides 
extensive use of Derby. But now I am keenly interested in refactoring my 
application to use OSGi. I have been looking at two alternatives - Spring 
and Felix and I've been leaning towards Felix. Seeing that Derby is using 
it as well makes me even more inclined
to proceed in that direction.


Thanks,
Pavel.






Rick Hillegas richard.hille...@sun.com 
Sent by: richard.hille...@sun.com
03/09/2010 03:39 PM
Please respond to
Derby Discussion derby-user@db.apache.org


To
Derby Discussion derby-user@db.apache.org
cc

Subject
Re: Extracting dependencies from SQL statements






Hi Pavel,

Some comments inline...

Pavel Bortnovskiy wrote:

 Rick:

 your second point grossly understimated the amount of time it took me 
 to get frustrated... it was not quick - it was instanteneous - as soon 
 as I opened sqlgrammar.jj  ;-D

 Last question, then, on the subject. Is there any way to easily 
 simplify sqlgrammar.jj, so that it frees itself of all the derby 
 dependencies and so that
 javacc'ed code can just take a string and give me some kind of lexical 
 tree?
This sounds like a frustrating approach too. If you are willing to hack 
the Derby code and create your owned forked version, then I would 
recommend going into GenericStatement.java and hacking the prepMinion() 
method so that you can control the short-circuiting yourself. Look for 
the string StopAfterParsing.

 Alternatively, javacc project has various .jj files in its repository, 
 two of which are PL/SQL-based (there is one for Oracle 9i).
 Perhaps using those .jj files may work for my purposes?.. Your 
 thoughts would be appreciated.
I have never looked at those grammars so I can't advise you here. This 
approach also will be peppered with frustrations if what you want to 
achieve is a Derby-powered app which performs some extra dependency 
tracking. Since the javacc-supplied grammars aren't the Derby grammar, 
statements which parse in one grammar may not parse in the other.

So let's step back for a moment. What do you need to achieve here:

o Are you writing a Derby-powered app or just trying to use the Derby 
parser against statements which actually run in some other database?

o If this is a Derby-powered app, do you need to track the dependencies 
before the statement is executed?

Thanks,
-Rick

 Thank you for your time and attention.

 Pavel.





 *Rick Hillegas richard.hille...@sun.com*
 Sent by: richard.hille...@sun.com

 03/09/2010 02:45 PM
 Please respond to
 Derby Discussion derby-user@db.apache.org


 
 To
Derby Discussion derby-user@db.apache.org
 cc
 
 Subject
Re: Extracting dependencies from SQL statements



 





 Hi Pavel,

 Some comments inline...

 Pavel Bortnovskiy wrote:
 
  Rick (while I am reading  and trying DERBY-4415), that seems like
  there is no other way to use prod jars and yet stop the parser from
  binding?

 It looks to me as though the shortcircuiting logic is guarded by a check
 for whether the engine is the debug version.
 
  Would it be at all possible to use the javacc compiler with
  sqlgrammar.jj (that are probably bundled in derby.jar) to do

Extracting dependencies from SQL statements

[Pavel Bortnovskiy]

Hello:

is it possible to use Derby's SQL parser to extract dependencies from a 
given SQL statement?
(or access the parser once the statement has been parsed).

Whether it's a simple SELECT or a JOIN, UNION or a more complex statement, 
I would like to get a list of tables that this statement would depend on.
Looking for FROM clauses and attempting to do the parsing myself seems 
like a difficult, error prone and impractical way to approach this.

Any suggestions, please?

Thanks,
Pavel.




Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

Re: Using NetworkServerControl to access in-memory (only) tables of Embedded Derby

[Pavel Bortnovskiy]

Hello, Kristian:

Thanks for your response.

I think I found what the problem is... When my IDE (IntelliJ IDEA) hits a 
breakpoint, it suspends all threads. I find it funny that in all these 
years of programming and using IDEA, I've never realized it.
(possibly because I was too occupied debugging the code at the breakpoint 
and not thinking about other threads at that moment). I've searched 
through the options but couldn't find any setting
which will prevent other threads from being suspended. Will continue 
looking.

I am now curious, what IDE do you use that it worked for you? And did you 
need to enable any special settings to allow all other threads to continue 
execution?

Regards,

Pavel.







Kristian Waagan kristian.waa...@sun.com 
Sent by: kristian.waa...@sun.com
02/16/2010 03:32 AM
Please respond to
Derby Discussion derby-user@db.apache.org


To
Derby Discussion derby-user@db.apache.org
cc

Subject
Re: Using NetworkServerControl to access in-memory (only) tables of 
Embedded Derby






On 16.02.10 03:02, Pavel Bortnovskiy wrote:
 Bryan: thank you for your response.

 I do see the thread you mention:

  Thread Group [derby.daemons]{10}(Daemon)
  Thread [derby.antiGC]{1}(Daemon)(WAITING)
  Thread [derby.rawStoreDaemon]{5}(Daemon)(TIMED_WAITING)
  Thread [derby.NetworkServerStarter]{5}(Daemon)(WAITING)
  Thread [NetworkServerThread_2]{5}(Daemon)(RUNNABLE)
  Thread [DRDAConnThread_3]{5}(Daemon)(WAITING)

 However the behavior is still the same - if any other thread stops (for
 instance in debugger), NetworkServerControl becomes unresponsive.
 For instance, if I connect to it from the outside with another db app
 (Aqua Data Studio, for instance), then connection to it breaks or the db
 app can't access it.
 But as soon as I let the thread run in that debugger, the connection
 becomes alive and everything seems to work.

 Has anyone else seen this kind of behavior?
 Or perhaps, someone can share a piece of code (best practice) of how to
 instantiate and run the NetworkServerControl, so that it's not
 experiencing
 such hang-ups.
 

Hi Pavel,

The code I'm about to post is by no means to be considered best 
practice, but I think it does what you want it to do. However, the code 
is basically yours :) (I copied it from your mail)
When I hit the breakpoint (set on the System.out.println in the static 
main method), the ping thread continued to run, and I could also access 
and do things with the database through ij.

Are you sure you're only stopping the current thread in your debugger?
When the system appears to hang, can you obtain a stack trace (using 
jstack or similar) and check what the Derby threads are doing?
Are you sure your stopped thread isn't holding on to some monitors / 
locks required by the other threads? (sounds unlikely, as the Derby 
threads should operate on their own, but I'm asking anyway...)

See the code I tested with below (I'm using localhost as host, so you 
cannot connect to Derby remotely).


Regards,
-- 
Kristian


import java.io.PrintWriter;
import java.net.InetAddress;
import java.util.concurrent.atomic.AtomicBoolean;
import org.apache.derby.drda.NetworkServerControl;

public class ServerTestDebug implements Runnable {

 private final InetAddress m_host;
 private final int m_port = 1527;
 private final AtomicBoolean m_done = new AtomicBoolean(false);
 private final int m_sleep = 5000;

 private NetworkServerControl m_server;

 public ServerTestDebug()
 throws Exception {
 m_host = InetAddress.getByName(localhost);
 }

   final public void start() throws Exception {
 new Thread(this, Derby Server).start();
   }

   public void stop() {
   System.out.println(Telling server to stop...);
   m_done.set(true);
   }

   public void run() {
   try {
 m_server = new NetworkServerControl(m_host, m_port);
 m_server.start(new PrintWriter(System.out));
   } catch (Exception e) {
 e.printStackTrace(System.out);
 return;
   }

 try {
   while (!m_done.get()) {
 System.out.println(pinging...);
 m_server.ping();

 try {
   Thread.sleep(m_sleep);
 } catch (InterruptedException e) {
   /* log */
 }
   }
 } catch (Exception e) {
   /* log - errror pinging server */
 }
   try {
   System.out.println(Shutting down...);
 m_server.shutdown();
   } catch (Exception e) {
   e.printStackTrace();
   }
   }

 public static void main(String[] args)
 throws Exception {
 ServerTestDebug server = new ServerTestDebug();
 server.start();
 System.out.println(Starting main loop...);
 int iterations = 30;
 while (iterations  0) {
 if (iterations == 20) {
System.out.println(breakpoint here!);
 }
 try

Re: Using NetworkServerControl to access in-memory (only) tables of Embedded Derby

[Pavel Bortnovskiy]

Thank you, Kristian and Bryan for all your help. I did locate the setting 
which allows me to suspend on the thread which hit the breakpoint while 
allowing others to execute.
But it was quite an interesting exercise, nevertheless.







Pavel Bortnovskiy/JEFCO 
02/18/2010 12:47 PM

To
Derby Discussion derby-user@db.apache.org
cc
kristian.waa...@sun.com
Subject
Re: Using NetworkServerControl to access in-memory (only) tables of 
Embedded Derby





Hello, Kristian:

Thanks for your response.

I think I found what the problem is... When my IDE (IntelliJ IDEA) hits a 
breakpoint, it suspends all threads. I find it funny that in all these 
years of programming and using IDEA, I've never realized it.
(possibly because I was too occupied debugging the code at the breakpoint 
and not thinking about other threads at that moment). I've searched 
through the options but couldn't find any setting
which will prevent other threads from being suspended. Will continue 
looking.

I am now curious, what IDE do you use that it worked for you? And did you 
need to enable any special settings to allow all other threads to continue 
execution?

Regards,

Pavel.







Kristian Waagan kristian.waa...@sun.com 
Sent by: kristian.waa...@sun.com
02/16/2010 03:32 AM
Please respond to
Derby Discussion derby-user@db.apache.org


To
Derby Discussion derby-user@db.apache.org
cc

Subject
Re: Using NetworkServerControl to access in-memory (only) tables of 
Embedded Derby






On 16.02.10 03:02, Pavel Bortnovskiy wrote:
 Bryan: thank you for your response.

 I do see the thread you mention:

  Thread Group [derby.daemons]{10}(Daemon)
  Thread [derby.antiGC]{1}(Daemon)(WAITING)
  Thread [derby.rawStoreDaemon]{5}(Daemon)(TIMED_WAITING)
  Thread [derby.NetworkServerStarter]{5}(Daemon)(WAITING)
  Thread [NetworkServerThread_2]{5}(Daemon)(RUNNABLE)
  Thread [DRDAConnThread_3]{5}(Daemon)(WAITING)

 However the behavior is still the same - if any other thread stops (for
 instance in debugger), NetworkServerControl becomes unresponsive.
 For instance, if I connect to it from the outside with another db app
 (Aqua Data Studio, for instance), then connection to it breaks or the db
 app can't access it.
 But as soon as I let the thread run in that debugger, the connection
 becomes alive and everything seems to work.

 Has anyone else seen this kind of behavior?
 Or perhaps, someone can share a piece of code (best practice) of how to
 instantiate and run the NetworkServerControl, so that it's not
 experiencing
 such hang-ups.
 

Hi Pavel,

The code I'm about to post is by no means to be considered best 
practice, but I think it does what you want it to do. However, the code 
is basically yours :) (I copied it from your mail)
When I hit the breakpoint (set on the System.out.println in the static 
main method), the ping thread continued to run, and I could also access 
and do things with the database through ij.

Are you sure you're only stopping the current thread in your debugger?
When the system appears to hang, can you obtain a stack trace (using 
jstack or similar) and check what the Derby threads are doing?
Are you sure your stopped thread isn't holding on to some monitors / 
locks required by the other threads? (sounds unlikely, as the Derby 
threads should operate on their own, but I'm asking anyway...)

See the code I tested with below (I'm using localhost as host, so you 
cannot connect to Derby remotely).


Regards,
-- 
Kristian


import java.io.PrintWriter;
import java.net.InetAddress;
import java.util.concurrent.atomic.AtomicBoolean;
import org.apache.derby.drda.NetworkServerControl;

public class ServerTestDebug implements Runnable {

 private final InetAddress m_host;
 private final int m_port = 1527;
 private final AtomicBoolean m_done = new AtomicBoolean(false);
 private final int m_sleep = 5000;

 private NetworkServerControl m_server;

 public ServerTestDebug()
 throws Exception {
 m_host = InetAddress.getByName(localhost);
 }

   final public void start() throws Exception {
 new Thread(this, Derby Server).start();
   }

   public void stop() {
   System.out.println(Telling server to stop...);
   m_done.set(true);
   }

   public void run() {
   try {
 m_server = new NetworkServerControl(m_host, m_port);
 m_server.start(new PrintWriter(System.out));
   } catch (Exception e) {
 e.printStackTrace(System.out);
 return;
   }

 try {
   while (!m_done.get()) {
 System.out.println(pinging...);
 m_server.ping();

 try {
   Thread.sleep(m_sleep);
 } catch (InterruptedException e) {
   /* log */
 }
   }
 } catch (Exception e) {
   /* log - errror pinging server */
 }
   try {
   System.out.println(Shutting down...);
 m_server.shutdown();
   } catch

Re: Using NetworkServerControl to access in-memory (only) tables of Embedded Derby

[Pavel Bortnovskiy]

Bryan: thank you for your response.

I do see the thread you mention:

Thread Group [derby.daemons]{10}(Daemon)
Thread [derby.antiGC]{1}(Daemon)(WAITING)
Thread [derby.rawStoreDaemon]{5}(Daemon)(TIMED_WAITING)
Thread [derby.NetworkServerStarter]{5}(Daemon)(WAITING)
Thread [NetworkServerThread_2]{5}(Daemon)(RUNNABLE)
Thread [DRDAConnThread_3]{5}(Daemon)(WAITING)

However the behavior is still the same - if any other thread stops (for 
instance in debugger), NetworkServerControl becomes unresponsive.
For instance, if I connect to it from the outside with another db app 
(Aqua Data Studio, for instance), then connection to it breaks or the db 
app can't access it.
But as soon as I let the thread run in that debugger, the connection 
becomes alive and everything seems to work.

Has anyone else seen this kind of behavior?
Or perhaps, someone can share a piece of code (best practice) of how to 
instantiate and run the NetworkServerControl, so that it's not 
experiencing
such hang-ups.

Much appreciated,

Pavel.











Bryan Pendleton bpendle...@amberpoint.com 
02/05/2010 12:07 PM
Please respond to
Derby Discussion derby-user@db.apache.org


To
Derby Discussion derby-user@db.apache.org
cc

Subject
Re: Using NetworkServerControl to access in-memory (only) tables of 
Embedded Derby






 DerbyServer thread seems to still be running, yet the server is 
 unresponsive.

In addition to your own DerbyServer thread, which I don't think
actually *needs* to still be running, there should be a separate
thread which is started by the Derby network server code itself,
which has the job of accepting connections and delivering them
to other threads to be processed.

In my running network server, for example, when I look at the
threads that are active, I see:

NetworkServerThread_2 prio=6 tid=0x03044c80 nid=0x27c runnable 
[0x033cf000..0x033cfae8]
 at java.net.PlainSocketImpl.socketAccept(Native Method)
 at java.net.PlainSocketImpl.accept(PlainSocketImpl.java:384)
 - locked 0x22a857d8 (a java.net.SocksSocketImpl)
 at java.net.ServerSocket.implAccept(ServerSocket.java:450)
 at java.net.ServerSocket.accept(ServerSocket.java:421)
 at org.apache.derby.impl.drda.ClientThread$1.run(Unknown Source)
 at java.security.AccessController.doPrivileged(Native Method)
 at org.apache.derby.impl.drda.ClientThread.run(Unknown Source)

Do you see a thread like that?

When your server becomes stuck/wedged/unresponsive, why don't you try 
this:
  - collect a thread dump of the entire JVM
  - look through the threads for any which mention org.apache.derby in 
their stacks
  - edit out all the other threads from your thread dump

Then post a message with a cut-and-paste of just the derby-related threads
in your wedged server, and maybe it will be more clear to somebody else
what the problem is.

thanks,

bryan








Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

Re: Using NetworkServerControl to access in-memory (only) tables of Embedded Derby

[Pavel Bortnovskiy]

Thank you for your response, Bryan.

When such situation arises, I will generate a thread-dump to verify. I 
don't have it yet at this moment.

My thinking is in-line with yours - Derby Network server control should 
run its own thread, but I don't see anything in the API which would allow 
me to do so.
(unless I download the source code and try to debug it).

So, would there be anyone on this forum who could help out please?
If you had any experience running Network Server Control in your 
application, can you please elaborate on how the threading was 
implemented,
so that the NSC is independent of all other threads and their states?

Thanks,
Pavel.






Bryan Pendleton bpendle...@amberpoint.com 
02/05/2010 12:07 PM
Please respond to
Derby Discussion derby-user@db.apache.org


To
Derby Discussion derby-user@db.apache.org
cc

Subject
Re: Using NetworkServerControl to access in-memory (only) tables of 
Embedded Derby






 DerbyServer thread seems to still be running, yet the server is 
 unresponsive.

In addition to your own DerbyServer thread, which I don't think
actually *needs* to still be running, there should be a separate
thread which is started by the Derby network server code itself,
which has the job of accepting connections and delivering them
to other threads to be processed.

In my running network server, for example, when I look at the
threads that are active, I see:

NetworkServerThread_2 prio=6 tid=0x03044c80 nid=0x27c runnable 
[0x033cf000..0x033cfae8]
 at java.net.PlainSocketImpl.socketAccept(Native Method)
 at java.net.PlainSocketImpl.accept(PlainSocketImpl.java:384)
 - locked 0x22a857d8 (a java.net.SocksSocketImpl)
 at java.net.ServerSocket.implAccept(ServerSocket.java:450)
 at java.net.ServerSocket.accept(ServerSocket.java:421)
 at org.apache.derby.impl.drda.ClientThread$1.run(Unknown Source)
 at java.security.AccessController.doPrivileged(Native Method)
 at org.apache.derby.impl.drda.ClientThread.run(Unknown Source)

Do you see a thread like that?

When your server becomes stuck/wedged/unresponsive, why don't you try 
this:
  - collect a thread dump of the entire JVM
  - look through the threads for any which mention org.apache.derby in 
their stacks
  - edit out all the other threads from your thread dump

Then post a message with a cut-and-paste of just the derby-related threads
in your wedged server, and maybe it will be more clear to somebody else
what the problem is.

thanks,

bryan








Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

Using NetworkServerControl to access in-memory (only) tables of Embedded Derby

[Pavel Bortnovskiy]

Hello:

In my application Derby is used as an in-memory database (only) and the 
NetworkServerControl (NSC) is used to provide JDBC access to the tables.
There are two questions:

1) how can actions of those connecting to the NetworkServerControl via 
JDBC be controled (for instance, limited to read only)?

2) a more important question: it seems that if my application stops, 
waits, hangs, then the JDBC connection to NSC is broken.
It seems that NSC just stops running. I couldn't figure out how to put it 
in a separate thread, so that it's independent of other
application threads. The only other thread I could create is the 
monitoring thread which starts and then is continuously pinging the 
server.
But even with this thread, the server still stops (connection breaks) if 
the application's main threads get into a wait/sleep or hang.

public class DerbyServer implements Runnable {
  private NetworkServerControl m_server;

  final public void start() throws Exception {
new Thread(this, Derby Server).start();
  }

  public void run() {
m_server = new NetworkServerControl(m_host, m_port);
m_server.start(new PrintWriter(new LoggingOutputStream(logger, 
Level.INFO), true));

try {
  while (!m_done.get()) {
m_server.ping();

try {
  Thread.sleep(m_sleep);
} catch (InterruptedException e) {
  /* log */
}
  }
} catch (Exception e) {
  /* log - errror pinging server */
}
m_server.shutdown();
  }
}

Thanks,

Pavel pavel.bortnovs...@softwair-corp.com




Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

Re: Using NetworkServerControl to access in-memory (only) tables of Embedded Derby

[Pavel Bortnovskiy]

Thank you for your prompt response, Bryan.

1) I will peruse the docs you recommended.

2) No, the m_done variable is not triggered in the app and the DerbyServer 
thread seems to still be running, yet the server is unresponsive.
Is there the best recommended practice to wrap the NetworkServerControl 
for proper functioning?






Bryan Pendleton bpendle...@amberpoint.com 
02/05/2010 10:22 AM
Please respond to
Derby Discussion derby-user@db.apache.org


To
Derby Discussion derby-user@db.apache.org
cc

Subject
Re: Using NetworkServerControl to access in-memory (only) tables of 
Embedded Derby






 1) how can actions of those connecting to the NetworkServerControl via 
 JDBC be controled (for instance, limited to read only)?

Derby has a variety of access control facilities. Here's a good place to
start learning about them:
http://db.apache.org/derby/docs/10.5/devguide/cdevcsecuree.html

 But even with this thread, the server still stops (connection breaks) if 

 the application's main threads get into a wait/sleep or hang.

It definitely sounds like something in your application is interfering
with the Derby processing, but it's hard to know what.

I noticed, however, that in the sample code that your posted, your
logic not only starts the network server, but also shuts it down.

Perhaps somehow the m_done variable is being set, and your code is
exiting the while loop and shutting the server down itself?

thanks,

bryan








Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.

In-Memory Apache Derby Tables not visible from outside

[Pavel Bortnovskiy]

Hello:

I am writing an application which is using Derby as an in-memory DB:

Class.forName(org.apache.derby.jdbc.EmbeddedDriver);
m_connection = 
DriverManager.getConnection(jdbc:derby:memory:testdb;create=true);

In that application I am also starting the NetworkServerControl:

try {
NetworkServerControl server = new 
NetworkServerControl(InetAddress.getByName(localhost), 1527);
server.start(new PrintWriter(System.out));
} catch (Exception e) {
logger.error(e);
}

My application seems to run happily, creating and populating tables. All 
the tables are well visible from the inside the app, for instance after 
CREATE TABLE called,
this returns cnt=1:

Statement statement = m_connection.createStatement();
ResultSet rset = statement.executeQuery(
SELECT COUNT(*) FROM SYS.SYSTABLES WHERE 
TABLENAME='SAMPLE_TABLE');
int cnt = -1;
if (rset.next()) cnt = rset.getInt(1);

However, if I try to connect with another DB client (currently I am using 
Aqua Data Studio and it's configured to connect to 
jdbc:derby://localhost:1527/testdb;create=true), it connects, but the 
tables created in memory are not visible (while SYS tables are):



So, why could it be that in-memory tables aren't seen from the outside 
and what can be done to make them visible?

Much appreciated!

Regards,

Pavel Bortnovskiy.




Jefferies archives and monitors outgoing and incoming e-mail. The contents of 
this email, including any attachments, are confidential to the ordinary user of 
the email address to which it was addressed. If you are not the addressee of 
this email you may not copy, forward, disclose or otherwise use it or any part 
of it in any form whatsoever. This email may be produced at the request of 
regulators or in connection with civil litigation. Jefferies accepts no 
liability for any errors or omissions arising as a result of transmission. Use 
by other than intended recipients is prohibited.  In the United Kingdom, 
Jefferies operates as Jefferies International Limited; registered in England: 
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London 
EC4V 3BJ.  Jefferies International Limited is authorised and regulated by the 
Financial Services Authority.image/gif