I have a .mdb Access Database which I have previously updated through a vbs script, which required some .csv files to be dumped from a site into a specified directory.
I realised that the site i loaded the csv files also had a webservice, so I decided to load through that instead. while testing, I wondered why the data in the tables wasn't being received properly. so I wrote a little test script:
import java.io.UnsupportedEncodingException;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.Properties;
import java.util.Set;
import java.util.SortedMap;
import java.util.logging.Level;
import java.util.logging.Logger;
public class ConventusImport {
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
int jdkType = Integer.parseInt(System.getProperty("sun.arch.data.model"));
if (jdkType != 32) {
System.err.println("Running " + jdkType + " bit JDK - this may cause ODBC Issues. please run with 32-bit JDK");
}
try {
//SQL connection
String mdbFileName = "F:/TestDatabase.accdb";
String dbConnectString = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + mdbFileName;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Properties props = new Properties();
props.put("charSet", "windows-1252");//virker ved inserts, men ikke ved selects
Connection c = DriverManager.getConnection(dbConnectString, props);
Statement stmt = c.createStatement();
String query = "SELECT * FROM testTabel where id = 12"; // Ægår Ødisøn Ådisbærg
stmt.executeQuery(query);
ResultSet rs = stmt.getResultSet();
ResultSetMetaData rsmd = rs.getMetaData();
rs.next();
byte[] rawBytes = rs.getBytes(2);
String updateQuery = "insert into testTabel values(14, 'æøå ÆØÅ é€èéÈ')";
stmt.executeUpdate(updateQuery);
//run through all charsets available, and try to decode byte array
SortedMap availableCharsetsMap = Charset.availableCharsets();
Set keySet = availableCharsetsMap.keySet();
Iterator it = keySet.iterator();
if (rawBytes != null && rawBytes.length != 0) {
while (it.hasNext()) {
Charset chset = (Charset) availableCharsetsMap.get((String) it.next());
String stringLine = new String(rawBytes, chset);
System.out.println(stringLine + "\t\t" + chset.name());
}
}
c.close();
} catch (ClassNotFoundException ex) {
Logger.getLogger(ConventusImport.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
Logger.getLogger(ConventusImport.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
I tried several charsets, and cp1252 works when I'm inserting, but when I'm selecting from the access database, I get ?'s instead of the characters I expect.
For example, Åse Sørensen becomes ?se S?rensen
the only other stackexchange post I can see are relevant don't solve my problem Jdbc-odbc bridge with ms access don't understand greek characters? How to correctly return special Spanish characters from a MS Access db with jdbc-odbc driver
EDIT:
I have tried UCANACCESS and it solves a lot of problems with ODBC and machine architecture..
unfortunately i cannot delete the question now.
