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 Sat, 27 Feb 2010 06:01:59 GMT
Done, but I'd see how to read the derby.log.
There is a derby file that contains only:

----------------------------------------------------------------
2010-02-27 05:55:42.390 GMT:
 Booting Derby version The Apache Software Foundation - Apache Derby -
10.5.1.1 - (764942): instance a816c00e-0127-0dfe-9c0a-000000412938
on database directory C:\Documents and
Settings\Gabriele\workspace\MemorizEasy\db.sqlwrapper

Database Class Loader started - derby.database.classpath=''


2010/2/26, Rick Hillegas <Richard.Hillegas@sun.com>:
> 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.
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>>>>
>>>>
>>>
>>
>>
>>
>
>


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