When I create table with a lot of keys, sometimes I receive "Invalid format" when i try to open table using MS Access 2003. Using JDBC-ODBC all is OK.
I send Test program.
The table is created on D:\HXTT\Test.mdb (create this empty mdb).
Regards
Gabriele Cagliani
/*
* TestHXTT
* Gabriele Cagliani
*/
package testhxtt2;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
*
* @author Gabriele
*/
public class TestHXTT2
{
// Change Directory
private static String dbq = "D:\\HXTT\\Test.mdb";
// set isHXTT true / false;
private static boolean isHXTT = true;
/**
* @param args the command line arguments
*/
public static void main(String[] args)
{
new TestHXTT2();
System.exit(0);
}
public TestHXTT2()
{
if (isHXTT)
{
loadHXTTDriver();
}
else
{
loadOdbcDriver();
}
Connection conn = null;
if (isHXTT)
{
conn = connectHXTT();
}
else
{
conn = connectOdbc();
}
if (isHXTT)
{
dropTableHXTT(conn);
commitMdb(conn);
createTableHXTT(conn);
commitMdb(conn);
}
else
{
createTableOdbc(conn);
commitMdb(conn);
}
commitMdb(conn);
closeMdb(conn);
}
private void loadOdbcDriver()
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch (ClassNotFoundException ex)
{
System.out.println("There was a problem loading the database driver." + ex.getMessage());
Logger.getLogger(TestHXTT2.class.getName()).log(Level.SEVERE, null, ex);
}
}
private void loadHXTTDriver()
{
try
{
Class.forName("com.hxtt.sql.access.AccessDriver").newInstance();
}
catch (ClassNotFoundException ex)
{
System.out.println("There was a problem loading the database driver." + ex.getMessage());
Logger.getLogger(TestHXTT2.class.getName()).log(Level.SEVERE, null, ex);
} catch (InstantiationException ex)
{
System.out.println("There was a problem loading the database driver." + ex.getMessage());
Logger.getLogger(TestHXTT2.class.getName()).log(Level.SEVERE, null, ex);
} catch (IllegalAccessException ex)
{
System.out.println("There was a problem loading the database driver." + ex.getMessage());
Logger.getLogger(TestHXTT2.class.getName()).log(Level.SEVERE, null, ex);
}
}
private Connection connectHXTT()
{
Connection conn = null;
try
{
String URL = "jdbc:Access:///" + dbq;
conn = DriverManager.getConnection(URL);
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
}
catch (SQLException ex)
{
System.out.println("There was a problem connecting to the database." + ex.getMessage());
Logger.getLogger(TestHXTT2.class.getName()).log(Level.SEVERE, null, ex);
}
return conn;
}
private Connection connectOdbc()
{
Connection conn = null;
try
{
String URL = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};ExtendedAnsiSQL=1;DBQ=" + dbq + ";DriverID=22;MaxBufferSize=1024;Exclusive=1";
conn = DriverManager.getConnection(URL);
}
catch (SQLException ex)
{
System.out.println("There was a problem connecting to the database." + ex.getMessage());
Logger.getLogger(TestHXTT2.class.getName()).log(Level.SEVERE, null, ex);
}
return conn;
}
private void commitMdb(Connection conn)
{
try
{
conn.commit();
}
catch (SQLException e)
{
}
}
private void closeMdb(Connection conn)
{
try
{
conn.close();
}
catch (SQLException e)
{
}
}
private void dropTableHXTT(Connection conn)
{
try
{
Statement sqlOut = conn.createStatement();
sqlOut.execute("drop table IF EXISTS HXTT_TEST");
sqlOut.close();
conn.commit();
}
catch (SQLException e)
{
}
}
private boolean createTableHXTT(Connection conn)
{
try
{
Statement sqlOut = conn.createStatement();
String sqlOutTxt = "create table HXTT_TEST" +
" (" +
" KKKKKKKKKK1 char (10) NOT NULL," +
" KKKKKKKKKK2 char (10) NOT NULL," +
" KKKKKKKKKK3 char (10) NOT NULL," +
" KKKKKKKKKK4 char (10) NOT NULL," +
" KKKKKKKKKK5 char (10) NOT NULL," +
" KKKKK6 char (10) NOT NULL," +
" HXTT_DATA char (100) NOT NULL," +
" PRIMARY KEY " +
" (" +
" KKKKKKKKKK1, " +
" KKKKKKKKKK2, " +
" KKKKKKKKKK3, " +
" KKKKKKKKKK4, " +
" KKKKKKKKKK5, " +
" KKKKK6" +
" ))";
sqlOut.execute(sqlOutTxt);
sqlOut.clearBatch();
sqlOut.close();
conn.commit();
}
catch (SQLException e)
{
System.out.println("Error executing create Table: " + e.getMessage());
}
return true;
}
private boolean createTableOdbc(Connection conn)
{
try
{
Statement sqlOut = conn.createStatement();
sqlOut.execute("drop table HXTT_TEST");
}
catch (SQLException e)
{
}
try
{
Statement sqlOut = conn.createStatement();
String sqlOutTxt = "create table HXTT_TEST" +
" (" +
" KKKKKKKKKK1 char (10) NOT NULL," +
" KKKKKKKKKK2 char (10) NOT NULL," +
" KKKKKKKKKK3 char (10) NOT NULL," +
" KKKKKKKKKK4 char (10) NOT NULL," +
" KKKKKKKKKK5 char (10) NOT NULL," +
" KKKKK6 char (10) NOT NULL," +
" HXTT_DATA char (100) NOT NULL," +
" CONSTRAINT PK_HXTT_TEST" +
" PRIMARY KEY CLUSTERED" +
" (" +
" KKKKKKKKKK1, " +
" KKKKKKKKKK2, " +
" KKKKKKKKKK3, " +
" KKKKKKKKKK4, " +
" KKKKKKKKKK5, " +
" KKKKK6" +
" ))";
sqlOut.execute(sqlOutTxt);
sqlOut.close();
}
catch (SQLException e)
{
System.out.println("Error executing create Table: " + e.getMessage());
}
return true;
}
}
|
|
Supported. That issue was resulted by that MS Access doesn't know an index name which has a length beyond 128 bytes. Please download the latest package.
|
Main
webmaster@hxtt.com