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) ).
