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 15:08:55 GMT
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