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 Fri, 26 Feb 2010 19:20:06 GMT
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