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 17:06:22 GMT
Perhaps last question: how to pass as parameter also the table?
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

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


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