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

David

This looks like a bug. The only work around at the moment is not to use
ORDER BY when you intend to use ResultSet.updateRow().

InstantDB maintains integer mappings from real result set rows to ordered
rows and similar mappings from results set rows to underlying table rows.
When ORDER BY gets used on an updateable ResultSet it forgets to combine the
two. I'll see that this gets fixed in the next release.

Regards

Peter Hearty
Lutris Technologies UK Ltd.

----- Original Message -----
From: Best
To: bugs@enhydra.org
Sent: Wednesday, May 10, 2000 12:09 PM
Subject: ResultSet.updateRow()


InstantDb support:

I can't understand why when I call updateRow() the result in the database is
one row updated and the following row overwritten with the updated row. I've
copied everything into thie email to avoid attachments.

Any assistance you can offer is greatly appreciated.

Thank you.

David Dee
----------------------------------------------------------------------------
---------------------------------------

****************************************************************************
****************************


I've created a simple test java program which I'm using to learn JDBC.
This is the code of my java program:
****************************************************************************
****************************

import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import java.io.*;
import java.util.*;

public class Test_Main
{
Connection con;
public static void main(String args[]) {
new Test_Main();
}

public Test_Main(){
openDB();
try{
writePage();
}catch(SQLException ex){
System.err.println("Test_Main() SQLException: " + ex.getMessage());
}finally{
shutdown();
}
}

private void
nDB(){ 
try{
Class.forName("jdbc.idbDriver");
}catch(java.lang.ClassNotFoundException e){
System.err.print("ClassNotFoundException: "); 
System.err.println(e.getMessage());
}
try{
con = DriverManager.getConnection("jdbc:idb:sample.pr
p");
File tblFile = new File("D:/TeacherPortal/tables/Test.tbl");
if( ! tblFile.isFile() ){
Statement stmt = con.createStatement();
stmt.executeUpdate("CREATE TABLE Test ( name VARCHAR(32), password CHAR(10),
tCode CHAR(4) )");
System.out.println ("Test.openDB() D:/TeacherPortal/tables/Test.tbl
created");
stmt.close();
addRecords();
}
}catch(SQLException ex){
System.err.println("SQLException: " + ex.getMessage());
}

}

private void shutdown() {
try{
if(con != null){
con.close();
con=null;
}
}catch(SQLException ex){
System.err.println("Test_Main().shutdown() " + ex.getMessage());
}
}

private void addRecords() throws SQLException{
String[] records = new String[5];
records[0] = "'Molly Smith', '12345','t_1'";
records[1] = "'Henry Smith', '67890','t_2'";
records[2] = "'Judy Miller', '13459','t_3'";
records[3] = "'Sandy Wise', '33333','t_4'";
records[4] = "'Vern George', '44444','t_5'";
Statement stmt = con.createStatement();
for(int i=0; i<records.length; i++){
stmt.executeUpdate("INSERT INTO Test VALUES("+records[i]+")");
}
stmt.close();
}


private void writePage() throws SQLException{
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM Test ORDER BY 3");

System.out.println("Lets print the Test file:");
String s = null;
while (rs.next()){
s = rs.getString("name") +" "+ rs.getString("password") +" "+
rs.getString("tCode")+" db row#:"+rs.getRow();
System.out.println(s);
}
System.out.println("Lets Update Judy's record [3]");
rs.absolute(3);
System.out.println("row1:"+rs.getRow());
System.out.println("old column2="+rs.getString(2));
rs.updateString(2, "xxxxx");
System.out.println("new column2="+rs.getString(2));

System.out.println("Lets Write Judy's new record to the database");
System.out.println("row before:"+rs.getRow());
rs.updateRow();
System.out.println("row after:"+rs.getRow());

System.out.println("Lets see Judy's changed record [3] in the resultSet:");
rs.beforeFirst();
while (rs.next()){
s = rs.getString("name") +" "+ rs.getString("password") +" "+
rs.getString("tCode")+" db row#:"+rs.getRow();
System.out.println(s);
}

rs.close();
stmt.close();
shutdown();
System.out.println("Lets see if Judy's record was updated");
openDB();
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT * FROM Test ORDER BY 3");
while (rs.next()){
s = rs.getString("name") +" "+ rs.getString("password") +" "+
rs.getString("tCode")+" db row#:"+rs.getRow();
System.out.println(s);
}
stmt.close();
}
}
****************************************************************************
****************************

This is the .bat file used to compile it:
****************************************************************************
****************************
D:\jdk1.2.2\bin\javac -classpath
D:\j2sdkee1.2\lib\j2ee.jar;D:\InstantDB\Classes\idb.jar;D:\TeacherPortal
D:\TeacherPortal\Test_Main.java
****************************************************************************
****************************

This is the .bat file used to run it:
****************************************************************************
****************************
@echo off
set _CLASSPATH=%CLASSPATH%
set JAVA_HOME=D:\jdk1.2.2
set CLASSPATH=%CLASSPATH%;%JAVA_HOME%\lib\tools.jar
set
CLASSPATH=%CLASSPATH%;D:\InstantDB\Classes\idb.jar;D:\InstantDB\Classes\idbf
.jar;D:\InstantDB\Classes\idbexmpl.jar
java -Xms16m -Xmx32m Test_Main
set CLASSPATH=%_CLASSPATH%
****************************************************************************
****************************
Here's the output when I run it:
****************************************************************************
****************************
D:\TeacherPortal>run
InstantDB - Version 3.13
Copyright (c) 1997-2000 Instant Computer Solutions Ltd.
TeacherLogon.openDB() getDatabaseProductName()= InstantDB
... getDriverName()= InstantDB JDBC Driver
... getURL()= jdbc:idb:sample.prp
... supportsResultSetType(TYPE_FORWARD_ONLY)= true
... supportsResultSetType(TYPE_SCROLL_INSENSITIVE)= true
Test.openDB() D:/TeacherPortal/tables/Test.tbl created
Lets print the Test file:
Molly Smith 12345 t_1 db row#:1
Henry Smith 67890 t_2 db row#:2
Judy Miller 13459 t_3 db row#:3
Sandy Wise 33333 t_4 db row#:4
Vern George 44444 t_5 db row#:5
Lets Update Judy's record [3]
row1:3
old column2=13459
new column2=xxxxx
Lets Write Judy's new record to the database
row before:3
row after:3
Lets see Judy's changed record [3] in the resultSet:
Molly Smith 12345 t_1 db row#:1
Henry Smith 67890 t_2 db row#:2
Judy Miller 13459 t_3 db row#:3
Sandy Wise 33333 t_4 db row#:4
Vern George 44444 t_5 db row#:5
Lets see if Judy's record was updated
TeacherLogon.openDB() getDatabaseProductName()= InstantDB
... getDriverName()= InstantDB JDBC Driver
... getURL()= jdbc:idb:sample.prp
... supportsResultSetType(TYPE_FORWARD_ONLY)= true
... supportsResultSetType(TYPE_SCROLL_INSENSITIVE)= true
Molly Smith 12345 t_1 db row#:1
Henry Smith 67890 t_2 db row#:2
Judy Miller xxxxx t_3 db row#:3
Sandy Wise 33333 t_4 db row#:4
Vern George 44444 t_5 db row#:5
D:\TeacherPortal>
****************************************************************************
*******************

Looks OK now. Then I run it again and Judy's record is copied over Sandy's.
This is the output:
****************************************************************************
*******************
D:\TeacherPortal>run
InstantDB - Version 3.13
Copyright (c) 1997-2000 Instant Computer Solutions Ltd.
TeacherLogon.openDB() getDatabaseProductName()= InstantDB
... getDriverName()= InstantDB JDBC Driver
... getURL()= jdbc:idb:sample.prp
... supportsResultSetType(TYPE_FORWARD_ONLY)= true
... supportsResultSetType(TYPE_SCROLL_INSENSITIVE)= true
Lets print the Test file:
Molly Smith 12345 t_1 db row#:1
Henry Smith 67890 t_2 db row#:2
Judy Miller xxxxx t_3 db row#:3
Sandy Wise 33333 t_4 db row#:4
Vern George 44444 t_5 db row#:5
Lets Update Judy's record [3]
row1:3
old column2=xxxxx
new column2=xxxxx
Lets Write Judy's new record to the database
row before:3
row after:3
Lets see Judy's changed record [3] in the resultSet:
Molly Smith 12345 t_1 db row#:1
Henry Smith 67890 t_2 db row#:2
Judy Miller xxxxx t_3 db row#:3
Sandy Wise 33333 t_4 db row#:4
Vern George 44444 t_5 db row#:5
Lets see if Judy's record was updated
TeacherLogon.openDB() getDatabaseProductName()= InstantDB
... getDriverName()= InstantDB JDBC Driver
... getURL()= jdbc:idb:sample.prp
... supportsResultSetType(TYPE_FORWARD_ONLY)= true
... supportsResultSetType(TYPE_SCROLL_INSENSITIVE)= true
Molly Smith 12345 t_1 db row#:1
Henry Smith 67890 t_2 db row#:2
Judy Miller xxxxx t_3 db row#:3
Judy Miller xxxxx t_3 db row#:4
Vern George 44444 t_5 db row#:5
D:\TeacherPortal>
****************************************************************************
*******************

This is what the file looks like:
****************************************************************************
*******************
iMolly Smith12345t_1Henry Smith67890t_2Judy Millerxxxxxt_3
Sandy Wiser33333t_4Vern George44444t_5Judy Millerxxxxxt_3Judy Millerxxxxxt_3
****************************************************************************
*******************


-----------------------------------------------------------------------------
To unsubscribe from this mailing list, send email to majordomo@enhydra.org
with the text "unsubscribe instantdb" in the body of the email.
If you have other questions regarding this mailing list, send email to
the list admin at owner-instantdb@enhydra.org.