Done, but I'd see how to read the derby.log. There is a derby file that contains only:
---------------------------------------------------------------- 2010-02-27 05:55:42.390 GMT: Booting Derby version The Apache Software Foundation - Apache Derby - 10.5.1.1 - (764942): instance a816c00e-0127-0dfe-9c0a-000000412938 on database directory C:\Documents and Settings\Gabriele\workspace\MemorizEasy\db.sqlwrapper Database Class Loader started - derby.database.classpath='' 2010/2/26, Rick Hillegas <[email protected]>: > 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 <[email protected]>: >> >>> 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 <[email protected]>: >>>> >>>> >>>>> 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 <[email protected]>: >>>>>> >>>>>> >>>>>> >>>>>>> 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 <[email protected]>: >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>>> 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. >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>> >>>> >>>> >>> >> >> >> > > -- Regards, K. Gabriele --- unchanged since 25/1/10 --- P.S. Unless a notification (LON), please reply either with an answer OR with " ACK" appended to this subject within 48 hours. Otherwise, I might resend. In(LON, this) ∨ In(48h, TimeNow) ∨ ∃x. In(x, MyInbox) ∧ IsAnswerTo(x, this) ∨ (In(subject(this), subject(x)) ∧ In(ACK, subject(x)) ∧ ¬IsAnswerTo(x,this)) ⇒ ¬IResend(this). Also note that correspondence may be received only from specified a priori senders, or if the subject of this email ends with a code, eg. -LICHT01X, then also from senders whose reply contains it. ∀x. In(x, MyInbox) ⇒ In(senderAddress(x), MySafeSenderList) ∨ (∃y. In(y, subject(this) ) ∧ In(y,x) ∧ isCodeLike(y, -LICHT01X) ).
