Insert Problem ! Please Help !

[fengzixuan zheng]

I got a problem when I want to insert a new record into a MS Access table, the table contain following field:
Name Text
Sex Text
Age Int
The codes :
import java.awt.*;
import java.io.*;
import java.sql.*;
public class JDBCTest{
Connection DBConn;
ResultSet rs; // SQL query results
Statement stmt; // SQL statement object
String dsn;
String query;
public JDBCTest(){
try{
Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException ce) {
ce.printStackTrace();
}
dsn = "jdbc:Odbc:;DRIVER=Microsoft Access Driver (*.mdb);DBQ=E:/Programs/JavaTest.mdb";

// Connect to the database
try{
DBConn = DriverManager.getConnection(dsn);
}
catch(SQLException se){
System.out.println("SQLException" + se);
}
query = "SELECT * FROM Test"; //Test is the Access table
try{
stmt = DBConn.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(query);
rs.next();
}
catch(SQLException se){
System.out.println("SQLException" + se);
}
}
public void delete() throws SQLException{
rs.absolute(2);
rs.deleteRow();
}
public void insert() throws SQLException{
rs.insertRow();
rs.moveToInsertRow();
rs.updateString("Name","Carlos");
rs.updateString("Sex","Male");
rs.updateInt("Age",30);
}
public static void main(String args[]) throws Exception{
JDBCTest jt=new JDBCTest();
//jt.delete(); //This method works properly
jt.insert(); //This one doesn't work
System.in.read();
}
}
And the Exceptions:
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]����������������������������
at sun.jdbc.odbc.JdbcOdbcResultSet.setPos(JdbcOdbcResultSet.java:4951)
at sun.jdbc.odbc.JdbcOdbcResultSet.insertRow(JdbcOdbcResultSet.java:3952)
at JDBCTest.insert(JDBCTest.java:59)
at JDBCTest.main(JDBCTest.java:68)
Exception in thread "main" Exit code: 1
There were errors
Please Help !
Thanks a lot!

[Jamie Robertson]

"����������������������������" - can you interpret this for us?? Not your usual error message...although I'll guess that it is "feature not implemented".
Jamie
[This message has been edited by Jamie Robertson (edited September 25, 2001).]

[fengzixuan zheng]

Originally posted by Jamie Robertson:
"����������������������������" - can you interpret this for us?? Not your usual error message...although I'll guess that it is "feature not implemented".
Jamie
[This message has been edited by Jamie Robertson (edited September 25, 2001).]

Dear Jamie,
This message mean that "the grade of generated table is not matching the row list".
But even I use only one statement: resultSet.insertRow();
The exceptions occurs!
Hope your replay
With regards

[Jamie Robertson]

public void insert() throws SQLException{
[B]rs.insertRow();[/B]
rs.moveToInsertRow();
rs.updateString("Name","Carlos");
rs.updateString("Sex","Male");
rs.updateInt("Age",30);
}

Looks like you are trying to insert before you position the cursor on the insert row. Try moving rs.insertRow(); to the end:

public void insert() throws SQLException{
rs.moveToInsertRow();
rs.updateString("Name","Carlos");
rs.updateString("Sex","Male");
rs.updateInt("Age",30);
[B]rs.insertRow();[/B]
}

Jamie

[fengzixuan zheng]

Originally posted by Jamie Robertson:
Looks like you are trying to insert before you position the cursor on the insert row. Try moving rs.insertRow(); to the end:

public void insert() throws SQLException{
rs.moveToInsertRow();
rs.updateString("Name","Carlos");
rs.updateString("Sex","Male");
rs.updateInt("Age",30);
[B]rs.insertRow();[/B]
}

Thank you Jamie, for I never know to put the "rs.insertRow();" to the end. But when I modified the codes and put this statment in the end, It throws a ArrayIndexOutOfBoundsException:
java.lang.ArrayIndexOutOfBoundsException
at sun.jdbc.odbc.JdbcOdbcResultSet.bindCol(JdbcOdbcResultSet.java:4485)
at sun.jdbc.odbc.JdbcOdbcResultSet.insertRow(JdbcOdbcResultSet.java:3941)
at JDBCTest.insert(JDBCTest.java:50)
at JDBCTest.main(JDBCTest.java:76)
Exception in thread "main" Exit code:
Could you help again?
Thanks .
Fengzixuan

[This message has been edited by fengzixuan zheng (edited September 26, 2001).]

[Laurent S]

I have exactly the same problem...I think it's access...I'm going to try with ibm db2

[Jamie Robertson]

Originally posted by Laurent S:
I have exactly the same problem...I think it's access...I'm going to try with ibm db2

It wouldn't be access, but the limitations on the jdbc driver accessing the MSAccess database. Anyways, I have also heard that some people had old "MDAC something or others??" which needed to be updated(I believe the upgrade is at www.merant.com).
never mind. I just did a search and found that it is a jdbc dbc bridge bug. Not your programming. I'm sure it will work on DB2 if it supports programmatic updates(Oracle doesn't!)
Jamie

[fengzixuan zheng]

Originally posted by Laurent S:
I have exactly the same problem...I think it's access...I'm going to try with ibm db2

Dear Laurent,
If you success with DB2, please tell me. I now eager to learn more JDBC.
Thanks.

[fengzixuan zheng]

Originally posted by Jamie Robertson:

never mind. I just did a search and found that it is a jdbc dbc bridge bug. Not your programming. I'm sure it will work on DB2 if it supports programmatic updates(Oracle doesn't!)

Thanks for your research, Jamie.
I will try to find some other drivers instead of JDBC-ODBC bridge. And if you have any suggestion, please let me know.
With regards.
Fengzixuan

[Jamie Robertson]

There are IBM supplied type 4 drivers available for download. These will probably be fully implemented and reliable.
Jamie

[fengzixuan zheng]

Originally posted by Jamie Robertson:
There are IBM supplied type 4 drivers available for download. These will probably be fully implemented and reliable.

I now know the problem, I write to sun JDBC Engineer, they told me is a problem with JDK1.3.1, and suggest to download JDK1.4 beta 2 to solve this problem.