Make your own free website on Tripod.com

InstantDB Project
About InstantDB
Project Mail Lists
Short History
Reporting Bugs
Screen Shots
3rd Party Examples
FAQs

Software
Downloads
Documentation
CVS Repositories
Roadmap
License

About Enhydra.org
Who We Are
News, Articles & Events
Getting Involved
Contact Us

Community
Demos
Contributions
Resources
Case Studies
On The Edge! -NEW-
Commercial Vendors


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

InstantDB: Exception with LEFT OUTER JOIN


Hi,
 
I've just started using InstantDB (3.14) and have come across a problem with the following query...
 
SELECT country.id, country.name, city.id, city.name FROM country LEFT OUTER JOIN city ON country.id=city.country_id;
 
This results in the following exception...
 
java.sql.SQLException: Error fetching row in joined table: java.sql.SQLException: IO problem: java.io.IOException: Negative seek offsetNegative seek offset
        at org.enhydra.instantdb.db.SQLProg.execute(SQLProg.java:276)
        at org.enhydra.instantdb.jdbc.idbStatement.execute(idbStatement.java:235)
        at org.enhydra.instantdb.jdbc.idbStatement.executeQuery(idbStatement.java:91)
        at org.enhydra.instantdb.SampleThread.run(ScriptTool.java:542)
        at java.lang.Thread.run(Unknown Source)
If I add a WHERE clause everything works as expected e.g. SELECT country.id, country.name, city.id, city.name FROM country LEFT OUTER JOIN city ON country.id=city.country_id WHERE country.id = 1;
 
However other WHERE conditions fail, eg country.id >= 1
 
Here is a script that reproduces the problem...
 
; First load the JDBC driver and open a database.
d org.enhydra.instantdb.jdbc.idbDriver;
o jdbc:idb=sample.prp;
 
; dump SQL and results to "trace.log"
e SET EXPORT "trace.log" TRACE 2 CONSOLE;
 
; Create the tables
e DROP TABLE country;
e CREATE TABLE country (
  id     int not null,
  name   varchar(200) not null );
e CREATE UNIQUE INDEX country_pk ON country ( id );
 
e DROP TABLE city;
e CREATE TABLE city (
  id         int not null,
  country_id int not null,
  name       varchar(200) not null );
e CREATE UNIQUE INDEX city_pk ON city ( id );
 
; Create a country with no cities.
e INSERT INTO country (id,name) VALUES (1,"USA");
 
; Returns single country with nulls for city.id and city.name - as expected.
q SELECT country.id, country.name, city.id, city.name FROM country LEFT OUTER JOIN city ON country.id=city.country_id WHERE country.id = 1;
 
; Same as above but without the WHERE clause.  Result should be the same.  Creates java.io exception!!
q SELECT country.id, country.name, city.id, city.name FROM country LEFT OUTER JOIN city ON country.id=city.country_id;
 
c close;
 
Hope someone can help.
 
Kind regards,
 
Phillip Baird