Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v7.1
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Cobol v5.0
  HXTT DBF v7.1
  HXTT Excel v6.1
  HXTT Json v1.0
  HXTT Paradox v7.1
  HXTT PDF v2.0
  HXTT Text(CSV) v7.1
  HXTT Word v1.1
  HXTT XML v4.0
Offshore Outsourcing
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Heng Xing Tian Tai Lab of Xi'an City (abbr, HXTT)

HXTT ACCESS
Primary key on text field doesn't work with non ascii symbols
Nikita
2007-08-28 06:58:56
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();
}

}

Re:Primary key on text field doesn't work with non ascii symbols
Nikita
2007-08-28 07:06:23
Sorry, Access 2002 was used in this testcase not 2003
Re:Re:Primary key on text field doesn't work with non ascii symbols
HXTT Support
2007-09-02 22:31:43
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.
Re:Re:Re:Primary key on text field doesn't work with non ascii symbols
Nikita
2007-09-03 03:26:12
Great! Now it works.

Thank you very much!

Search Key   Search by Last 50 Questions




Google
 

Email: webmaster@hxtt.com
Copyright © 2003-2019 Heng Xing Tian Tai Lab of Xi'an City. | All Rights Reserved. | Privacy | Legal | Refund | Sitemap