Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
MIME-version: 1.0
Content-transfer-encoding: 7BIT
Content-type: text/plain; CHARSET=US-ASCII; format=flowed
Date: Fri, 26 Feb 2010 09:24:40 -0800
From: Rick Hillegas <Richard.Hillegas@Sun.COM>
Subject: Re: how to raise/throw an exception
In-reply-to: <1f5d398f1002260906q6ceb1536y25d5dff0701b4018@mail.gmail.com>
Sender: Richard.Hillegas@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <4B8803D8.9070904@sun.com>
References: <1f5d398f1002260521m2f61f0feyb41c2bf440adb746@mail.gmail.com>
 <4B87CF9B.6020601@sun.com>
 <1f5d398f1002260559s753439cdge6c2716f74101ea6@mail.gmail.com>
 <4B87DCC2.4040608@sun.com>
 <1f5d398f1002260642q9b6b2e0vda66bd31e73bdbff@mail.gmail.com>
 <4B87E407.6040908@sun.com>
 <1f5d398f1002260906q6ceb1536y25d5dff0701b4018@mail.gmail.com>
User-Agent: Thunderbird 2.0.0.23 (Macintosh/20090812)

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