db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gabriele Kahlout <gabri...@mysimpatico.com>
Subject Re: how to raise/throw an exception
Date Fri, 26 Feb 2010 14:42:59 GMT
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.
>>>>
>>>>
>>>>
>>>
>>
>>
>>
>
>


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

Mime
View raw message