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 (nike.apache.org: local policy)
MIME-version: 1.0
Content-transfer-encoding: 7BIT
Content-type: text/plain; CHARSET=US-ASCII; format=flowed
Date: Mon, 01 Mar 2010 11:00:05 -0800
From: Rick Hillegas <Richard.Hillegas@Sun.COM>
Subject: Re: how to raise/throw an exception
In-reply-to: <1f5d398f1003010838r36437fd3y3fef63392312651a@mail.gmail.com>
Sender: Richard.Hillegas@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <4B8C0EB5.2070200@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>
 <4B8803D8.9070904@sun.com>
 <1f5d398f1002261050r209fddc5sca7785f4abdec2e2@mail.gmail.com>
 <4B881EE6.6090806@sun.com>
 <1f5d398f1003010838r36437fd3y3fef63392312651a@mail.gmail.com>
User-Agent: Thunderbird 2.0.0.23 (Macintosh/20090812)

Hi Gabriele,

The file in question is derby.log, which I believe you have located. If 
you are not seeing the information you need there, it may be because you 
have not set the correct tracepoints in order to force the logging of 
that information. You may get a broader response to your question if you 
start a new thread under the title "Debugging lock timeouts".

Hope this helps,
-Rick

Gabriele Kahlout wrote:
> So can someone explain me (or refer to where it is explained) how to
> read/find this log file with the wanted info?
>
> 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.
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>                       
>>>>>           
>>>
>>>       
>>     
>
>
>