hmm..I thought of it as the closest implementation to the doc. How else would you write it?
Indeed, the derby.properties file is almost empty. 2010/3/1, Rick Hillegas <[email protected]>: > Hi Gabriele, > > Have you checked the contents of derby.properties? The following code > looks a little odd. It looks as though you are writing the Properties > object to disk before you have actually set the properties. > > Regards, > -Rick > > Gabriele Kahlout wrote: >> I've added the following code to trace: >> >> final Properties prop = new java.util.Properties(); >> prop.save(new FileOutputStream(new >> File("derby.properties")), >> "derby.properties"); >> prop.setProperty("derby.locks.deadlockTrace", "true"); >> prop.setProperty("derby.language.logStatementText", >> "true"); >> prop.setProperty("derby.language.logStatementText", >> "true"); >> >> Is this not enough? >> >> I still get the same stuff written to the log: >> >> 2010-03-01 19:14:59.864 GMT: >> Booting Derby version The Apache Software Foundation - Apache Derby - >> 10.5.1.1 - (764942): instance a816c00e-0127-1b27-19bf-00000065a670 >> on database directory /Users/simpatico/ws/MemorizEasy/db.sqlwrapper >> >> Database Class Loader started - derby.database.classpath='' >> >> 2010/3/1, Rick Hillegas <[email protected]>: >> >>> 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 <[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) ).
