I've adjusted it :-
HTH,
Nigel
-----8<------------
package test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.h2.tools.DeleteDbFiles;
public class H2Test {
static int val = 0;
public static int getMaxRev(Connection conn)
throws SQLException {
//System.out.println("MaxRev = " + val);
return val;
}
public static void main(String[] a) throws Exception {
Class.forName("org.h2.Driver");
DeleteDbFiles.execute("data/test", "test", true);
Connection conn = DriverManager.getConnection(
"jdbc:h2:data/test/test", "sa", "sa");
Statement stat = conn.createStatement();
stat.execute("create table test(id int, val int, x varchar,
primary key (id,val))");
for(int i=1;i<=10;i++)
{
stat.execute("insert into test values (1," + i + ",'val" + i
+ "')");
}
stat.execute("CREATE ALIAS GET_MAX_REV " +
"FOR \"test.H2Test.getMaxRev\"");
ResultSet rs;
stat.execute("CREATE VIEW THINGY AS " +
"SELECT tbl.* FROM TEST AS tbl, (SELECT ID, MAX(VAL)
innerVal FROM test WHERE val <= GET_MAX_REV() GROUP BY ID) tbi" +
" WHERE tbl.id=tbi.id AND tbl.val=tbi.innerVal"
);
val = 8;
System.out.println("SELECT * FROM THINGY - expect result with "
+ val);
rs = stat.executeQuery("SELECT * FROM THINGY");
dump(rs);
val = 5;
System.out.println("SELECT * FROM THINGY - expect result with "
+ val);
rs = stat.executeQuery("SELECT * FROM THINGY");
dump(rs);
conn.close();
}
protected static void dump(ResultSet rs) throws SQLException
{
for(int i=0;i<rs.getMetaData().getColumnCount();i++)
{
System.out.print(rs.getMetaData().getColumnName(i+1) + "\t");
}
System.out.println
("\n-------------------------------------------------");
while(rs.next())
{
for(int i=0;i<rs.getMetaData().getColumnCount();i++)
{
System.out.print(rs.getObject(i+1) + "\t");
}
}
System.out.println
("\n=================================================");
}
}
On Jul 31, 7:38 am, Thomas Mueller <[email protected]>
wrote:
> Hi,
>
> Sorry I can't reproduce the problem so far. My test case is:
>
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.sql.Statement;
> import org.h2.tools.DeleteDbFiles;
>
> public class TestDb2 {
> public static int getMaxRev(Connection conn)
> throws SQLException {
> ResultSet rs = conn.createStatement().
> executeQuery("select max(id) from test");
> rs.next();
> System.out.println(" getMaxRev: " + rs.getInt(1));
> return rs.getInt(1);
> }
> public static void main(String[] a) throws Exception {
> Class.forName("org.h2.Driver");
> DeleteDbFiles.execute("data/test", "test", true);
> Connection conn = DriverManager.getConnection(
> "jdbc:h2:data/test/test", "sa", "sa");
> Statement stat = conn.createStatement();
> stat.execute("create table test(id identity) " +
> "as select x from system_range(1, 2);");
> stat.execute("CREATE ALIAS GET_MAX_REV " +
> "FOR \"TestDb2.getMaxRev\"");
> stat.execute("CREATE VIEW THINGY AS " +
> "SELECT * FROM TEST WHERE ID<=GET_MAX_REV()");
> ResultSet rs;
> System.out.println("SELECT * FROM THINGY");
> rs = stat.executeQuery("SELECT * FROM THINGY");
> while (rs.next()) {
> System.out.println("a: " + rs.getString(1));
> }
> stat.execute("insert into test " +
> "select x from system_range(10, 11);");
> System.out.println("SELECT * FROM THINGY");
> rs = stat.executeQuery("SELECT * FROM THINGY");
> while (rs.next()) {
> System.out.println("b: " + rs.getString(1));
> }
> conn.close();
> }
>
> }
>
> Could you change my test case so it fails?
>
> Regards,
> Thomas
>
> On Fri, Jul 24, 2009 at 10:30 PM, nigelm<[email protected]> wrote:
>
> > Hi
>
> > I have migrated away from HSQLDB mostly with success, save for 1
> > issue.
>
> > We create some database views that use a function in order to control
> > their results. E.g:
>
> > CREATE ALIAS GET_MAX_REV FOR "net.foo.bar.getMaxRev"
>
> > CREATE VIEW THINGY AS
> > SELECT * FROM WIDGETS
> > WHERE REV <= GET_MAX_REV()
>
> > This works, but with one shortcoming. The getMaxRev function is
> > volatile, so successive calls to
> > SELECT * FROM THINGY
>
> > Ought to potentially return dramatically different results. But I can
> > see by setting a breakpoint in getMaxRev that periodically it is not
> > being called.
>
> > I assume that either the function result, or the query result has been
> > cached somewhere. Is there a way to declare that the function is
> > volatile (perhaps by passing an unused timestamp parameter) ?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en
-~----------~----~----~----~------~----~------~--~---
