db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: how to raise/throw an exception
Date Tue, 02 Mar 2010 15:25:49 GMT
Hi Gabriele,

Hopefully someone else can give you a hand now. I am a little swamped.

Regards,
-Rick

Gabriele Kahlout wrote:
> I've written directly into the properties file, but reading the log
> file I still don't understand where the deadlock is. Of course the
> problem is with the refTable, but why?
>
> The lock info printed is different from the lock example in the documentation.
>
>
> java.sql.SQLException: The exception 'java.sql.SQLException: A lock
> could not be obtained within the time requested.  The lockTable dump
> is:
> 2010-03-01 21:19:01.316 GMT
> XID       |TYPE         |MODE|LOCKCOUNT|LOCKNAME
>                                                  |STATE|TABLETYPE /
> LOCKOBJ                   |INDEXNAME / CONTAINER_ID / (MODE for LATCH
> only)  |TABLENAME / CONGLOM_ID                |
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> *** The following row is the victim ***
> 248       |ROW          |S   |0        |(4,21)
>                                                  |WAIT |S
>                        |NULL
>    |SYSCOLUMNS                            |
> *** The above row is the victim ***
> 202       |ROW          |S   |4        |(1,7)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCONSTRAINTS                        |
> 202       |ROW          |X   |1        |(1,7)
>                                                  |GRANT|T
>                        |NULL
>    |OBJECTS                               |
> 202       |ROW          |X   |1        |(1,8)
>                                                  |GRANT|T
>                        |NULL
>    |OBJECTS                               |
> 202       |TABLE        |IX  |8        |Tablelock
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |TABLE        |IX  |6        |Tablelock
>                                                  |GRANT|T
>                        |NULL
>    |OBJECTS                               |
> 248       |TABLE        |IS  |2        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSSCHEMAS                            |
> 202       |ROW          |X   |1        |(1,8)
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |ROW          |X   |1        |(1,8)
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |ROW          |X   |1        |(1,7)
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |ROW          |X   |1        |(1,7)
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |ROW          |X   |1        |(1,7)
>                                                  |GRANT|T
>                        |NULL
>    |BATCHES                               |
> 202       |TABLE        |IS  |16       |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |TABLE        |IX  |1        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 248       |TABLE        |IS  |2        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |X   |1        |(1,10)
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |ROW          |X   |1        |(1,9)
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |ROW          |S   |4        |(4,20)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 248       |ROW          |S   |2        |(4,20)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |TABLE        |IS  |4        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSKEYS                               |
> 202       |ROW          |S   |4        |(4,21)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |X   |2        |(4,21)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |TABLE        |IS  |12       |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSCONGLOMERATES                      |
> 202       |TABLE        |S   |2        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSCONGLOMERATES                      |
> 202       |TABLE        |IS  |12       |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 248       |TABLE        |IS  |2        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 248       |ROW          |S   |2        |(1,17)
>                                                  |GRANT|S
>                        |NULL
>    |SYSSCHEMAS                            |
> 202       |TABLE        |IX  |2        |Tablelock
>                                                  |GRANT|T
>                        |NULL
>    |BATCHES                               |
> 202       |ROW          |S   |2        |(4,31)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(4,30)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(1,30)
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 202       |ROW          |S   |2        |(4,29)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(1,29)
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 202       |ROW          |S   |4        |(5,17)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCONGLOMERATES                      |
> 202       |ROW          |S   |4        |(1,7)
>                                                  |GRANT|S
>                        |NULL
>    |SYSKEYS                               |
> 202       |ROW          |S   |2        |(4,28)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(4,27)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(4,26)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(4,25)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(4,24)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |TABLE        |IS  |4        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSCONSTRAINTS                        |
> 202       |ROW          |S   |4        |(4,23)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |4        |(1,28)
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 202       |ROW          |S   |4        |(5,16)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCONGLOMERATES                      |
> 202       |ROW          |S   |4        |(4,22)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |4        |(1,27)
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 248       |ROW          |S   |2        |(1,27)
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 202       |TABLE        |IX  |1        |Tablelock
>                                                  |GRANT|T
>                        |NULL
>    |REFERSTO                              |
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------'
> 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.EmbedStatement.executeStatement(Unknown Source)
> 	at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
> 	at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(Unknown Source)
> 	at com.mysimpatico.memorizeasy.engine.Database.insertWords(Database.java:426)
> 	at com.mysimpatico.memorizeasy.engine.Database.insertWords(Database.java:424)
> 	at com.mysimpatico.memorizeasy.engine.Database.insertWords(Database.java:461)
> 	at com.mysimpatico.memorizeasy.engine.Database.insertWords(Database.java:290)
> 	at com.mysimpatico.memorizeasy.engine.Database.importWords(Database.java:296)
> 	at com.mysimpatico.memorizeasy.test.DatabaseTest.testImportWords(DatabaseTest.java:118)
> 	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 org.junit.internal.runners.TestMethodRunner.executeMethodBody(TestMethodRunner.java:99)
> 	at org.junit.internal.runners.TestMethodRunner.runUnprotected(TestMethodRunner.java:81)
> 	at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
> 	at org.junit.internal.runners.TestMethodRunner.runMethod(TestMethodRunner.java:75)
> 	at org.junit.internal.runners.TestMethodRunner.run(TestMethodRunner.java:45)
> 	at org.junit.internal.runners.TestClassMethodsRunner.invokeTestMethod(TestClassMethodsRunner.java:66)
> 	at org.junit.internal.runners.TestClassMethodsRunner.run(TestClassMethodsRunner.java:35)
> 	at org.junit.internal.runners.TestClassRunner$1.runUnprotected(TestClassRunner.java:42)
> 	at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
> 	at org.junit.internal.runners.TestClassRunner.run(TestClassRunner.java:52)
> 	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:45)
> 	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
> 	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
> 	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
> 	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
> 	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
> Caused by: java.sql.SQLException: The exception
> 'java.sql.SQLException: A lock could not be obtained within the time
> requested.  The lockTable dump is:
> 2010-03-01 21:19:01.316 GMT
> XID       |TYPE         |MODE|LOCKCOUNT|LOCKNAME
>                                                  |STATE|TABLETYPE /
> LOCKOBJ                   |INDEXNAME / CONTAINER_ID / (MODE for LATCH
> only)  |TABLENAME / CONGLOM_ID                |
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> *** The following row is the victim ***
> 248       |ROW          |S   |0        |(4,21)
>                                                  |WAIT |S
>                        |NULL
>    |SYSCOLUMNS                            |
> *** The above row is the victim ***
> 202       |ROW          |S   |4        |(1,7)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCONSTRAINTS                        |
> 202       |ROW          |X   |1        |(1,7)
>                                                  |GRANT|T
>                        |NULL
>    |OBJECTS                               |
> 202       |ROW          |X   |1        |(1,8)
>                                                  |GRANT|T
>                        |NULL
>    |OBJECTS                               |
> 202       |TABLE        |IX  |8        |Tablelock
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |TABLE        |IX  |6        |Tablelock
>                                                  |GRANT|T
>                        |NULL
>    |OBJECTS                               |
> 248       |TABLE        |IS  |2        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSSCHEMAS                            |
> 202       |ROW          |X   |1        |(1,8)
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |ROW          |X   |1        |(1,8)
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |ROW          |X   |1        |(1,7)
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |ROW          |X   |1        |(1,7)
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |ROW          |X   |1        |(1,7)
>                                                  |GRANT|T
>                        |NULL
>    |BATCHES                               |
> 202       |TABLE        |IS  |16       |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |TABLE        |IX  |1        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 248       |TABLE        |IS  |2        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |X   |1        |(1,10)
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |ROW          |X   |1        |(1,9)
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |ROW          |S   |4        |(4,20)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 248       |ROW          |S   |2        |(4,20)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |TABLE        |IS  |4        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSKEYS                               |
> 202       |ROW          |S   |4        |(4,21)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |X   |2        |(4,21)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |TABLE        |IS  |12       |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSCONGLOMERATES                      |
> 202       |TABLE        |S   |2        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSCONGLOMERATES                      |
> 202       |TABLE        |IS  |12       |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 248       |TABLE        |IS  |2        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 248       |ROW          |S   |2        |(1,17)
>                                                  |GRANT|S
>                        |NULL
>    |SYSSCHEMAS                            |
> 202       |TABLE        |IX  |2        |Tablelock
>                                                  |GRANT|T
>                        |NULL
>    |BATCHES                               |
> 202       |ROW          |S   |2        |(4,31)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(4,30)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(1,30)
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 202       |ROW          |S   |2        |(4,29)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(1,29)
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 202       |ROW          |S   |4        |(5,17)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCONGLOMERATES                      |
> 202       |ROW          |S   |4        |(1,7)
>                                                  |GRANT|S
>                        |NULL
>    |SYSKEYS                               |
> 202       |ROW          |S   |2        |(4,28)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(4,27)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(4,26)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(4,25)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(4,24)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |TABLE        |IS  |4        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSCONSTRAINTS                        |
> 202       |ROW          |S   |4        |(4,23)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |4        |(1,28)
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 202       |ROW          |S   |4        |(5,16)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCONGLOMERATES                      |
> 202       |ROW          |S   |4        |(4,22)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |4        |(1,27)
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 248       |ROW          |S   |2        |(1,27)
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 202       |TABLE        |IX  |1        |Tablelock
>                                                  |GRANT|T
>                        |NULL
>    |REFERSTO                              |
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------'
> 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)
> 	... 36 more
> Caused by: java.sql.SQLException: A lock could not be obtained within
> the time requested.  The lockTable dump is:
> 2010-03-01 21:19:01.316 GMT
> XID       |TYPE         |MODE|LOCKCOUNT|LOCKNAME
>                                                  |STATE|TABLETYPE /
> LOCKOBJ                   |INDEXNAME / CONTAINER_ID / (MODE for LATCH
> only)  |TABLENAME / CONGLOM_ID                |
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> *** The following row is the victim ***
> 248       |ROW          |S   |0        |(4,21)
>                                                  |WAIT |S
>                        |NULL
>    |SYSCOLUMNS                            |
> *** The above row is the victim ***
> 202       |ROW          |S   |4        |(1,7)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCONSTRAINTS                        |
> 202       |ROW          |X   |1        |(1,7)
>                                                  |GRANT|T
>                        |NULL
>    |OBJECTS                               |
> 202       |ROW          |X   |1        |(1,8)
>                                                  |GRANT|T
>                        |NULL
>    |OBJECTS                               |
> 202       |TABLE        |IX  |8        |Tablelock
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |TABLE        |IX  |6        |Tablelock
>                                                  |GRANT|T
>                        |NULL
>    |OBJECTS                               |
> 248       |TABLE        |IS  |2        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSSCHEMAS                            |
> 202       |ROW          |X   |1        |(1,8)
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |ROW          |X   |1        |(1,8)
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |ROW          |X   |1        |(1,7)
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |ROW          |X   |1        |(1,7)
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |ROW          |X   |1        |(1,7)
>                                                  |GRANT|T
>                        |NULL
>    |BATCHES                               |
> 202       |TABLE        |IS  |16       |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |TABLE        |IX  |1        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 248       |TABLE        |IS  |2        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |X   |1        |(1,10)
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |ROW          |X   |1        |(1,9)
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |ROW          |S   |4        |(4,20)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 248       |ROW          |S   |2        |(4,20)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |TABLE        |IS  |4        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSKEYS                               |
> 202       |ROW          |S   |4        |(4,21)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |X   |2        |(4,21)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |TABLE        |IS  |12       |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSCONGLOMERATES                      |
> 202       |TABLE        |S   |2        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSCONGLOMERATES                      |
> 202       |TABLE        |IS  |12       |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 248       |TABLE        |IS  |2        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 248       |ROW          |S   |2        |(1,17)
>                                                  |GRANT|S
>                        |NULL
>    |SYSSCHEMAS                            |
> 202       |TABLE        |IX  |2        |Tablelock
>                                                  |GRANT|T
>                        |NULL
>    |BATCHES                               |
> 202       |ROW          |S   |2        |(4,31)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(4,30)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(1,30)
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 202       |ROW          |S   |2        |(4,29)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(1,29)
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 202       |ROW          |S   |4        |(5,17)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCONGLOMERATES                      |
> 202       |ROW          |S   |4        |(1,7)
>                                                  |GRANT|S
>                        |NULL
>    |SYSKEYS                               |
> 202       |ROW          |S   |2        |(4,28)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(4,27)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(4,26)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(4,25)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(4,24)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |TABLE        |IS  |4        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSCONSTRAINTS                        |
> 202       |ROW          |S   |4        |(4,23)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |4        |(1,28)
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 202       |ROW          |S   |4        |(5,16)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCONGLOMERATES                      |
> 202       |ROW          |S   |4        |(4,22)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |4        |(1,27)
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 248       |ROW          |S   |2        |(1,27)
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 202       |TABLE        |IX  |1        |Tablelock
>                                                  |GRANT|T
>                        |NULL
>    |REFERSTO                              |
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> 	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.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.EmbedStatement.execute(Unknown Source)
> 	at org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(Unknown Source)
> 	at com.mysimpatico.memorizeasy.engine.Database.refExpTablesComp(Database.java:171)
> 	at org.apache.derby.exe.ac4ac48095x0127x1b97xa6d9x000000652bb04.g0(Unknown
> Source)
> 	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 org.apache.derby.impl.services.reflect.ReflectMethod.invoke(Unknown Source)
> 	at org.apache.derby.impl.sql.execute.CallStatementResultSet.open(Unknown
> Source)
> 	at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown
> Source)
> 	at org.apache.derby.impl.sql.GenericPreparedStatement.executeSubStatement(Unknown
> Source)
> 	at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown
> Source)
> 	at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown
> Source)
> 	at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown
> Source)
> 	at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(Unknown
> Source)
> 	at org.apache.derby.impl.sql.execute.InsertResultSet.open(Unknown Source)
> 	at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown
> Source)
> 	at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> 	... 29 more
> Caused by: ERROR 40XL2: A lock could not be obtained within the time
> requested.  The lockTable dump is:
> 2010-03-01 21:19:01.316 GMT
> XID       |TYPE         |MODE|LOCKCOUNT|LOCKNAME
>                                                  |STATE|TABLETYPE /
> LOCKOBJ                   |INDEXNAME / CONTAINER_ID / (MODE for LATCH
> only)  |TABLENAME / CONGLOM_ID                |
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> *** The following row is the victim ***
> 248       |ROW          |S   |0        |(4,21)
>                                                  |WAIT |S
>                        |NULL
>    |SYSCOLUMNS                            |
> *** The above row is the victim ***
> 202       |ROW          |S   |4        |(1,7)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCONSTRAINTS                        |
> 202       |ROW          |X   |1        |(1,7)
>                                                  |GRANT|T
>                        |NULL
>    |OBJECTS                               |
> 202       |ROW          |X   |1        |(1,8)
>                                                  |GRANT|T
>                        |NULL
>    |OBJECTS                               |
> 202       |TABLE        |IX  |8        |Tablelock
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |TABLE        |IX  |6        |Tablelock
>                                                  |GRANT|T
>                        |NULL
>    |OBJECTS                               |
> 248       |TABLE        |IS  |2        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSSCHEMAS                            |
> 202       |ROW          |X   |1        |(1,8)
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |ROW          |X   |1        |(1,8)
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |ROW          |X   |1        |(1,7)
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |ROW          |X   |1        |(1,7)
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |ROW          |X   |1        |(1,7)
>                                                  |GRANT|T
>                        |NULL
>    |BATCHES                               |
> 202       |TABLE        |IS  |16       |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |TABLE        |IX  |1        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 248       |TABLE        |IS  |2        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |X   |1        |(1,10)
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |ROW          |X   |1        |(1,9)
>                                                  |GRANT|T
>                        |NULL
>    |EXPRESSIONS                           |
> 202       |ROW          |S   |4        |(4,20)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 248       |ROW          |S   |2        |(4,20)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |TABLE        |IS  |4        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSKEYS                               |
> 202       |ROW          |S   |4        |(4,21)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |X   |2        |(4,21)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |TABLE        |IS  |12       |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSCONGLOMERATES                      |
> 202       |TABLE        |S   |2        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSCONGLOMERATES                      |
> 202       |TABLE        |IS  |12       |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 248       |TABLE        |IS  |2        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 248       |ROW          |S   |2        |(1,17)
>                                                  |GRANT|S
>                        |NULL
>    |SYSSCHEMAS                            |
> 202       |TABLE        |IX  |2        |Tablelock
>                                                  |GRANT|T
>                        |NULL
>    |BATCHES                               |
> 202       |ROW          |S   |2        |(4,31)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(4,30)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(1,30)
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 202       |ROW          |S   |2        |(4,29)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(1,29)
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 202       |ROW          |S   |4        |(5,17)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCONGLOMERATES                      |
> 202       |ROW          |S   |4        |(1,7)
>                                                  |GRANT|S
>                        |NULL
>    |SYSKEYS                               |
> 202       |ROW          |S   |2        |(4,28)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(4,27)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(4,26)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(4,25)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |2        |(4,24)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |TABLE        |IS  |4        |Tablelock
>                                                  |GRANT|S
>                        |NULL
>    |SYSCONSTRAINTS                        |
> 202       |ROW          |S   |4        |(4,23)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |4        |(1,28)
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 202       |ROW          |S   |4        |(5,16)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCONGLOMERATES                      |
> 202       |ROW          |S   |4        |(4,22)
>                                                  |GRANT|S
>                        |NULL
>    |SYSCOLUMNS                            |
> 202       |ROW          |S   |4        |(1,27)
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 248       |ROW          |S   |2        |(1,27)
>                                                  |GRANT|S
>                        |NULL
>    |SYSTABLES                             |
> 202       |TABLE        |IX  |1        |Tablelock
>                                                  |GRANT|T
>                        |NULL
>    |REFERSTO                              |
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> 	at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> 	at org.apache.derby.impl.services.locks.Timeout.createException(Unknown Source)
> 	at org.apache.derby.impl.services.locks.Timeout.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.RowLocking3.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.catalog.DataDictionaryImpl.getDescriptorViaIndexMinion(Unknown
> Source)
> 	at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getDescriptorViaIndex(Unknown
> Source)
> 	at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getColumnDescriptorsScan(Unknown
> Source)
> 	at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getColumnDescriptorsScan(Unknown
> Source)
> 	at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.finishTableDescriptor(Unknown
> Source)
> 	at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getTableDescriptorIndex1Scan(Unknown
> Source)
> 	at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getTableDescriptor(Unknown
> Source)CREATE TABLE Expressions (exp VARCHAR(100),RID INT NOT NULL
> GENERATED ALWAYS AS IDENTITY , PRIMARY KEY(exp));
> CREATE TABLE Batches (lastPub DATE,RID INT NOT NULL GENERATED ALWAYS
> AS IDENTITY , PRIMARY KEY(RID));
> CREATE TABLE Objects (batch INT REFERENCES Batches(RID),star SMALLINT
> DEFAULT 0 NOT NULL,hide SMALLINT DEFAULT 0 NOT NULL,RID INT NOT NULL
> GENERATED ALWAYS AS IDENTITY , PRIMARY KEY(RID));
> INSERT  INTO Expressions(exp) VALUES('...so geht die Legende ...');
> INSERT  INTO Expressions(exp) VALUES('...legend has it ...');
> SELECT RID FROM Batches WHERE RID >= (SELECT MAX(RID) FROM Batches);
> SELECT RefersTo.objectId FROM RefersTo WHERE RefersTo.exp='...legend
> has it ...';
> INSERT  INTO Objects(batch) VALUES(-9);
> INSERT  INTO Batches(lastPub) VALUES(null);
> INSERT  INTO Expressions(exp) VALUES('...so geht die Legende ...');
> INSERT  INTO Expressions(exp) VALUES('...legend has it ...');
> SELECT RID FROM Batches WHERE RID >= (SELECT MAX(RID) FROM Batches);
> SELECT RefersTo.objectId FROM RefersTo WHERE RefersTo.exp='...legend
> has it ...';
> INSERT  INTO Objects(batch) VALUES(1);
> SELECT RID FROM Objects WHERE RID >= (SELECT MAX(RID) FROM Objects);
> INSERT  INTO RefersTo(exp,objectId) VALUES('...legend has it ...',2);
>
> 	at org.apache.derby.impl.sql.compile.QueryTreeNode.getTableDescriptor(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)
> 	at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
> 	at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
> 	at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
> Source)
> 	... 48 more
>
>
> 2010/3/1, Rick Hillegas <Richard.Hillegas@sun.com>:
>   
>> Hi Gabriele,
>>
>> The file in question is derby.log, which I believe you have located. If
>> you are not seeing the information you need there, it may be because you
>> have not set the correct tracepoints in order to force the logging of
>> that information. You may get a broader response to your question if you
>> start a new thread under the title "Debugging lock timeouts".
>>
>> Hope this helps,
>> -Rick
>>
>> Gabriele Kahlout wrote:
>>     
>>> So can someone explain me (or refer to where it is explained) how to
>>> read/find this log file with the wanted info?
>>>
>>> 2010/2/26, Rick Hillegas <Richard.Hillegas@sun.com>:
>>>
>>>       
>>>> Hi Gabriele,
>>>>
>>>> The following link will help you debug your lock contention problem:
>>>> http://db.apache.org/derby/faq.html#debug_lock_timeout
>>>>
>>>> Hope this is useful,
>>>> -Rick
>>>>
>>>> Gabriele Kahlout wrote:
>>>>
>>>>         
>>>>> Because of the trigger + procedure the update statement on the
>>>>> affected table throws (not otherwise):
>>>>>
>>>>> java.sql.SQLException: The exception 'java.sql.SQLException: A lock
>>>>> could not be obtained within the time requested' was thrown while
>>>>> evaluating an expression.
>>>>>
>>>>> I tried to make it sleep for 250 nanos. But no avail.
>>>>>
>>>>> 2010/2/26, Rick Hillegas <Richard.Hillegas@sun.com>:
>>>>>
>>>>>
>>>>>           
>>>>>> Hi Gabriele,
>>>>>>
>>>>>> Some comments inline...
>>>>>>
>>>>>> Gabriele Kahlout wrote:
>>>>>>
>>>>>>
>>>>>>             
>>>>>>> Perhaps last question: how to pass as parameter also the table?
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>               
>>>>>> Here's a revised version of the procedure which takes the table and
>>>>>> column names as parameters:
>>>>>>
>>>>>>     public static void vetRow( String tableName, String columnName, int
>>>>>> columnValue ) throws SQLException
>>>>>>     {
>>>>>>         Connection conn = DriverManager.getConnection(
>>>>>> "jdbc:default:connection" );
>>>>>>         String query = "select count(*) from " + tableName +" where " +
>>>>>> columnName + " = ?";
>>>>>>         System.out.println( query );
>>>>>>         PreparedStatement ps = conn.prepareStatement( query );
>>>>>>         ps.setInt( 1, columnValue );
>>>>>>         ResultSet rs = ps.executeQuery();
>>>>>>         rs.next();
>>>>>>         int result = rs.getInt( 1 );
>>>>>>
>>>>>>         rs.close();
>>>>>>         ps.close();
>>>>>>
>>>>>>         if ( result >= 4 ) { throw new SQLException( "Too many copies
>>>>>> of
>>>>>> some value." ); }
>>>>>>     }
>>>>>> }
>>>>>>
>>>>>> and the corresponding script to exercise this procedure:
>>>>>>
>>>>>> connect 'jdbc:derby:memory:dummy;create=true';
>>>>>>
>>>>>> create table t( a int );
>>>>>>
>>>>>> create procedure vetRow( in tableName varchar( 128 ), in columnName
>>>>>> varchar( 128 ), in arg int )
>>>>>> language java parameter style java reads sql data
>>>>>> external name 'z.vetRow';
>>>>>>
>>>>>> create trigger t_trig
>>>>>> no cascade before insert on t
>>>>>> referencing new as newRow
>>>>>> for each row call vetRow( 'T', 'A', newRow.a );
>>>>>>
>>>>>> insert into t( a ) values ( 1 );
>>>>>> insert into t( a ) values ( 1 );
>>>>>> insert into t( a ) values ( 1 );
>>>>>> insert into t( a ) values ( 1 );
>>>>>>
>>>>>> -- fails
>>>>>> insert into t( a ) values ( 1 );
>>>>>>
>>>>>>
>>>>>>
>>>>>>             
>>>>>>> Also, where can I look to understand why int a would be translated as
>>>>>>> the column a?
>>>>>>> This doesn't help:
>>>>>>> http://developers.sun.com/docs/javadb/10.5.3.0/ref/index.html
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>               
>>>>>> You will want to read the syntax descriptions for the CREATE PROCEDURE
>>>>>> and CREATE TRIGGER commands in that Reference Guide.
>>>>>>
>>>>>> Hope this helps,
>>>>>> -Rick
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>             
>>>>>>> 2010/2/26, Rick Hillegas <Richard.Hillegas@sun.com>:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>               
>>>>>>>> Hi Gabriele,
>>>>>>>>
>>>>>>>> Here is a vetting procedure which the trigger could call:
>>>>>>>>
>>>>>>>>     public static void vetRow( int a ) throws SQLException
>>>>>>>>     {
>>>>>>>>         Connection conn = DriverManager.getConnection(
>>>>>>>> "jdbc:default:connection" );
>>>>>>>>         PreparedStatement ps = conn.prepareStatement( "select
>>>>>>>> count(*)
>>>>>>>> from t where a = ?" );
>>>>>>>>         ps.setInt( 1, a );
>>>>>>>>         ResultSet rs = ps.executeQuery();
>>>>>>>>         rs.next();
>>>>>>>>         int result = rs.getInt( 1 );
>>>>>>>>
>>>>>>>>         rs.close();
>>>>>>>>         ps.close();
>>>>>>>>
>>>>>>>>         if ( result >= 4 ) { throw new SQLException( "Too many copies
>>>>>>>> of
>>>>>>>> value " + a + " in t." ); }
>>>>>>>>     }
>>>>>>>>
>>>>>>>> And here again is the sql script which exercises the trigger. I had
>>>>>>>> to
>>>>>>>> modify the trigger declaration slightly: instead of saying that it
>>>>>>>> "contains sql" this version says "reads sql data":
>>>>>>>>
>>>>>>>> connect 'jdbc:derby:memory:dummy;create=true';
>>>>>>>>
>>>>>>>> create table t( a int );
>>>>>>>>
>>>>>>>> create procedure vetRow( in arg int )
>>>>>>>> language java parameter style java reads sql data
>>>>>>>> external name 'z.vetRow';
>>>>>>>>
>>>>>>>> create trigger t_trig
>>>>>>>> no cascade before insert on t
>>>>>>>> referencing new as newRow
>>>>>>>> for each row call vetRow( newRow.a );
>>>>>>>>
>>>>>>>> insert into t( a ) values ( 1 );
>>>>>>>> insert into t( a ) values ( 1 );
>>>>>>>> insert into t( a ) values ( 1 );
>>>>>>>> insert into t( a ) values ( 1 );
>>>>>>>>
>>>>>>>> -- fails
>>>>>>>> insert into t( a ) values ( 1 );
>>>>>>>>
>>>>>>>> Depending on your usage pattern, it might make more sense to declare
>>>>>>>> an
>>>>>>>> AFTER trigger which invokes a vetting procedure once per
>>>>>>>> INSERT/UPDATE
>>>>>>>> rather than per row.
>>>>>>>>
>>>>>>>> Hope this helps,
>>>>>>>> -Rick
>>>>>>>>
>>>>>>>> Gabriele Kahlout wrote:
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>                 
>>>>>>>>> The procedure would run the sanity check query and raise an
>>>>>>>>> exception
>>>>>>>>> if the sanity check failed.
>>>>>>>>> Can you show me an example of how this exception could be thrown?
>>>>>>>>>
>>>>>>>>> About the user-coded function, how may I code what I asked for? A
>>>>>>>>> loop
>>>>>>>>> that counts for each distinct value it's occurences, and returns
>>>>>>>>> true/false if some count is greater than 5.
>>>>>>>>>
>>>>>>>>> 2010/2/26, Rick Hillegas <Richard.Hillegas@sun.com>:
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>                   
>>>>>>>>>> Hi Gabriele,
>>>>>>>>>>
>>>>>>>>>> Here's an example of how Derby triggers invoke database procedures.
>>>>>>>>>> If
>>>>>>>>>> this is still not clear, please keep asking questions. We will get
>>>>>>>>>> to
>>>>>>>>>> the bottom of this:
>>>>>>>>>>
>>>>>>>>>> connect 'jdbc:derby:memory:dummy;create=true';
>>>>>>>>>>
>>>>>>>>>> create table t( a int );
>>>>>>>>>>
>>>>>>>>>> create procedure vetRow( in arg int )
>>>>>>>>>> language java parameter style java contains sql
>>>>>>>>>> external name 'z.vetRow';
>>>>>>>>>>
>>>>>>>>>> create trigger t_trig
>>>>>>>>>> no cascade before insert on t
>>>>>>>>>> referencing new as newRow
>>>>>>>>>> for each row call vetRow( newRow.a );
>>>>>>>>>>
>>>>>>>>>> insert into t( a ) values ( 1 );
>>>>>>>>>>
>>>>>>>>>> Another comment inline...
>>>>>>>>>>
>>>>>>>>>> Gabriele Kahlout wrote:
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>                     
>>>>>>>>>>> That's what i do in SQLite. The question is how to do it in java
>>>>>>>>>>> DB
>>>>>>>>>>> sql
>>>>>>>>>>> syntax.
>>>>>>>>>>> In sqlite it is SELECT( RAISE, 'error) WHERE ...
>>>>>>>>>>> in java db??
>>>>>>>>>>>
>>>>>>>>>>> Also, I was wondering if I could find a way to implement the
>>>>>>>>>>> constraint as a column constraint. Unfortunately the details , are
>>>>>>>>>>> far
>>>>>>>>>>> from complete.
>>>>>>>>>>> http://developers.sun.com/docs/javadb/10.5.3.0/ref/index.html
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>                       
>>>>>>>>>> I don't think so. The only kind of constraint you could use here
>>>>>>>>>> would
>>>>>>>>>> be a CHECK constraint which invokes a user-coded function. The
>>>>>>>>>> function
>>>>>>>>>> would not be allowed to issue SQL.
>>>>>>>>>>
>>>>>>>>>> Hope this helps,
>>>>>>>>>> -Rick
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>                     
>>>>>>>>>>> Basically I'm trying to check that no more than 4 rows have the
>>>>>>>>>>> same
>>>>>>>>>>> value for the column. In SQL that would be the count -group by
>>>>>>>>>>> query,
>>>>>>>>>>> as mentioned below, but since that counts as a not allowed
>>>>>>>>>>> subquery,
>>>>>>>>>>> is there some other way in java db to achieve exactly that?
>>>>>>>>>>>
>>>>>>>>>>> 2010/2/26, Rick Hillegas <Richard.Hillegas@sun.com>:
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>                       
>>>>>>>>>>>> Hi Gabriele,
>>>>>>>>>>>>
>>>>>>>>>>>> One way to solve this problem would be to have your trigger call
>>>>>>>>>>>> a
>>>>>>>>>>>> database procedure, passing in whatever parameters you need from
>>>>>>>>>>>> the
>>>>>>>>>>>> triggering row. The procedure would run the sanity check query
>>>>>>>>>>>> and
>>>>>>>>>>>> raise
>>>>>>>>>>>> an exception if the sanity check failed.
>>>>>>>>>>>>
>>>>>>>>>>>> Hope this helps,
>>>>>>>>>>>> -Rick
>>>>>>>>>>>>
>>>>>>>>>>>> Gabriele Kahlout wrote:
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>                         
>>>>>>>>>>>>> Hello,
>>>>>>>>>>>>>
>>>>>>>>>>>>> I'm porting my application from SQLite to Java DB, but don't
>>>>>>>>>>>>> know
>>>>>>>>>>>>> how
>>>>>>>>>>>>> to enforce an assertion, achieved in SQLite by:
>>>>>>>>>>>>>
>>>>>>>>>>>>> st.execute("CREATE TRIGGER _fk_meanings_update_1 BEFORE UPDATE
>>>>>>>>>>>>> ON
>>>>>>>>>>>>> "
>>>>>>>>>>>>> +
>>>>>>>>>>>>> batTable.getName()  + " FOR EACH ROW BEGIN SELECT RAISE(FAIL,
>>>>>>>>>>>>> 'error')
>>>>>>>>>>>>> WHERE (EXISTS (SELECT COUNT(*) FROM " + batTable.getName() + "
>>>>>>>>>>>>> GROUP
>>>>>>>>>>>>> BY " + lastPubColumn.getName() + " HAVING COUNT(*)>"+maxPub+"));
>>>>>>>>>>>>> END;");
>>>>>>>>>>>>>
>>>>>>>>>>>>> However the constraint couldn't be added as a table constraint,
>>>>>>>>>>>>> and
>>>>>>>>>>>>> I'm not finding how to raise an exception with Java DB, although
>>>>>>>>>>>>> it's
>>>>>>>>>>>>> described here:
>>>>>>>>>>>>>
>>>>>>>>>>>>> http://developers.sun.com/docs/javadb/10.5.3.0/devguide/index.html
>>>>>>>>>>>>>
>>>>>>>>>>>>> One way was to set the identity column to null, but that was not
>>>>>>>>>>>>> allowed at compile time too. I guess I'd have to use some dirty
>>>>>>>>>>>>> way.
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>                           
>>>>>           
>>>
>>>       
>>     
>
>
>   


Mime
View raw message