I'm evaluating your driver and try to use it instead of standard driver.
It appeared that HXTT ACCESS doesn't handle primary key text field with non-ascii properly. It only observed when primary key is set from Access itself.
Here is a testcase:
- Run the code below and you will get:
QUERY: SELECT t1.ID AS ID1, t2.ID AS ID2, t1.text FROM t1 INNER JOIN t2 ON t1.text=t2.text
ID1 INTEGER 11 10 0
ID2 INTEGER 11 10 0
text VARCHAR 24 0 0
1 10 ���������������������
2 11 Hello
- Open Access (tested with Access 2003) and reset primary key from 'text' fields in tables t1, t2 and then set keys again
- Change true to false in condition clause and run the code again and you will get:
QUERY: SELECT t1.ID AS ID1, t2.ID AS ID2, t1.text FROM t1 INNER JOIN t2 ON t1.text=t2.text
ID1 INTEGER 11 10 0
ID2 INTEGER 11 10 0
text VARCHAR 24 0 0
2 11 Hello
What we see? One row has gone!
Could you please look at this issue?
import java.sql.*;
import java.util.Properties;
public class HXTTTester {
public HXTTTester() {
}
public static void main(String[] args) {
try {
Class.forName("com.hxtt.sql.access.AccessDriver");
testStringJoin();
} catch (SQLException sqle) {
do {
System.out.println(sqle.getMessage());
System.out.println("Error Code:" + sqle.getErrorCode());
System.out.println("SQL State:" + sqle.getSQLState());
sqle.printStackTrace();
} while ((sqle = sqle.getNextException()) != null);
} catch (Exception e) {
System.out.println(e.getMessage());
e.printStackTrace();
}
}
private static void testStringJoin() throws SQLException {
String url = "jdbc:Access:////home/nikita/share";
Connection con = DriverManager.getConnection(url, new Properties());
if (true) { //init database
Statement astat = con.createStatement();
astat.execute("create database test");
astat.executeUpdate("create table test.t1 (ID int, text varchar(12),primary key (text));");
astat.executeUpdate("create table test.t2 (ID int, text varchar(12),primary key (text));");
astat.close();
con.setCatalog("test.MDB");
PreparedStatement ps1 = con.prepareStatement("insert into t1 (ID, text) values(?,?);");
PreparedStatement ps2 = con.prepareStatement("insert into t2 (ID, text) values(?,?);");
String[] texts={"���������������������", "Hello"};
for (int i = 0; i < texts.length; i++) {
ps1.setInt(1, i+1); //ID
ps1.setString(2, texts[i]); //text
ps1.executeUpdate();
ps2.setInt(1, i+10); //ID
ps2.setString(2, texts[i]); //text
ps2.executeUpdate();
}
ps1.close();
ps2.close();
}
con.setCatalog("test.MDB");
String sql = "SELECT t1.ID AS ID1, t2.ID AS ID2, t1.text FROM t1 INNER JOIN t2 ON t1.text=t2.text";
printQuery(con, sql);
con.close();
}
private static void printQuery(Connection con, String sql) throws SQLException {
System.out.println("QUERY: "+sql);
PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
ResultSetMetaData resultSetMetaData = rs.getMetaData();
int iNumCols = resultSetMetaData.getColumnCount();
for (int j = 1; j <= iNumCols; j++) {
System.out.println(resultSetMetaData.getColumnLabel(j) + " "
+ resultSetMetaData.getColumnTypeName(j) + " "
+ resultSetMetaData.getColumnDisplaySize(j) + " "
+ resultSetMetaData.getPrecision(j) + " "
+ resultSetMetaData.getScale(j));
}
Object colval;
rs.beforeFirst();
int count = 0;
while (rs.next()) {
count++;
for (int j = 1; j <= iNumCols; j++) {
colval = rs.getObject(j);
System.out.print(colval + " ");
}
System.out.println();
}
rs.close();
rs = null;
ps.close();
}
}
|
Sorry, Access 2002 was used in this testcase not 2003
|
v2.1.068 supports a variety of sort orders for JET4 database(General, German Phone Book, France, Icelandic, Dutch, Traditional Spanish, Spanish, Swedish/ Finnish, Croatian, Czech, Hungarian, Polish, Romanian, Slovak, Slovenian, Estonian, Latvian, Lithuanian, Macedonian, Ukrainian, Chinese Stroke Count (Taiwan), Georgian Modern, Hungarian Technical, Japanese Unicode, Korean Unicode, Chinese Pronunciation, Chinese Stroke Count, Turkish, Vietnamese, Chinese Bopomofo (Taiwan), Japanese, Korean, and Thai). Please download the latest package.
|
Great! Now it works.
Thank you very much!
|