Hi Gabriele,
Have you checked the contents of derby.properties? The following code
looks a little odd. It looks as though you are writing the Properties
object to disk before you have actually set the properties.
Regards,
-Rick
Gabriele Kahlout wrote:
I've added the following code to trace:
final Properties prop = new java.util.Properties();
prop.save(new FileOutputStream(new
File("derby.properties")),
"derby.properties");
prop.setProperty("derby.locks.deadlockTrace", "true");
prop.setProperty("derby.language.logStatementText",
"true");
prop.setProperty("derby.language.logStatementText",
"true");
Is this not enough?
I still get the same stuff written to the log:
2010-03-01 19:14:59.864 GMT:
Booting Derby version The Apache Software Foundation - Apache Derby -
10.5.1.1 - (764942): instance a816c00e-0127-1b27-19bf-00000065a670
on database directory /Users/simpatico/ws/MemorizEasy/db.sqlwrapper
Database Class Loader started - derby.database.classpath=''
2010/3/1, Rick Hillegas <[email protected]>:
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 <[email protected]>:
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 <[email protected]>:
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 <[email protected]>:
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 <[email protected]>:
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 <[email protected]>:
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.
