In Java, I wrote a program that reads a UTF8 text file. The text file contains an SQL query of the form SELECT. The program then executes the query in the Microsoft Access 2007 database and writes all the fields of the first line to the UTF8 text file.
The problem is returning a string containing unicode characters such as "βͺ". These characters appear as "?" in a text file.
I know that text files are read and written correctly because the dummy character UTF8 ("β") is read from a text file containing an SQL query and written to a text file containing the resulting string. The UTF8 character looks correct when a written text file is opened in Notepad, so reading and writing text files is not part of the problem.
This is how I connect to the database and how I execute the SQL query:
Connection c = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:/database.accdb;Pwd=temp"); ResultSet r = c.createStatement().executeQuery(sql);
I tried to create a charSet property for Connection, but that doesn't matter:
Properties p = new Properties(); p.put("charSet", "utf-8"); p.put("lc_ctype", "utf-8"); p.put("encoding", "utf-8"); Connection c = DriverManager.getConnection("...", p);
Tried with "utf8" / "UTF8" / "UTF-8", no difference. If I enter UTF-16, I get the following exception:
java.lang.IllegalArgumentException: Illegal replacement
Search around for hours without any results, and now I hope for you. Please, help!
I also accept workarounds. =) What I want to do is make a Unicode request (for example, one that searches for messages containing the "γ" character) and get the results with the correct and saved Unicode characters.
Thanks!
Update Here is a self-sufficient example of a problem:
package test; import java.io.BufferedReader;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.InputStreamReader;import java.io.OutputStreamWriter;import java.nio.charset.Charset;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.util.Properties; public class Standalone { public static void main(String[] args) { try { Properties p = new Properties(); p.put("charSet", "UTF8"); Connection c = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=./dummy.accdb;Pwd=pass", p); ResultSet r = c.createStatement().executeQuery("SELECT TOP 1 * FROM main;"); r.next(); OutputStreamWriter osw = new OutputStreamWriter(new FileOutputStream(new File("results.txt")), Charset.forName("UTF-8")); osw.write(new BufferedReader(new InputStreamReader(new FileInputStream("utf8.txt"), Charset.forName("UTF-8"))).readLine() +" : "+ r.getString("content")); osw.close(); c.close(); System.out.println("Done."); } catch (Exception e) { e.printStackTrace(); } } }
Which example shows that it opens the database "dummy.accdb", encrypted with the password "pass" and pulls the first column from "main". Then it reads the text file "utf8.txt" and writes the text file "results.txt", which will contain the first line of "utf8.txt" plus the value of the "content" field obtained from the database.
In the file "utf8.txt" I saved "ββββββββββββββββββ". In the field "main" of the database table "database" I saved "β« βͺ γ γ γΏ β³β΄β― β₯ βΫββ".
After the application is completed, "results.txt" has the following content: "ββββββββββββββββββ: ????? Moe? 8 ???".
It successfully reads and writes the UTF8 characters of the text file "utf8.txt", but was unable to get the correct characters from the database. This is problem.
Update I think I should mention that the field in the database is of type βmemoβ, I tried havig βUnicode Compressionβ set to βNoβ and βYesβ (recreating the message between attempts to make sure there was no compression when "No" is selected). As far as I understand, Access uses UTF-16 when it saves Unicode characters, however with compression on it UTF-8 changes. In any case, it did not matter.
Bonus question, does anyone know how to connect to a database using a pure ODBC provider in Java? Or any other method? That would provide me with a good workaround.
Update I am trying to pass these four getConnection:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=./dummy.accdb" "jdbc:odbc:Provider=Microsoft.Jet.OLEDB.4.0;Data Source=./dummy.accdb" "jdbc:odbc:Driver={Microsoft.Jet.OLEDB.4.0};Data Source=./dummy.accdb" "jdbc:odbc:Provider=Microsoft.ACE.OLEDB.12.0;Data Source=./dummy.accdb"
First, give the error "java.sql.SQLException: there is no suitable driver for Provider = Microsoft.Jet.OLEDB.4.0; Data source =. / Dummy.accdb", and two in the middle receive "java.sql.SQLException: [Microsoft] [ ODBC driver manager] No data source name found, and no default driver specified. " The latter gets "java.sql.SQLException: [Microsoft] [ODBC Driver Manager] The data source name is too long."
I do not understand what getConnection wants. The parameter description is as follows: "url - URL of the jdbc: subprotocol: subname database". A? I clearly donβt understand what this means.
Does anyone know of any alternative ways to connect to an Access 2007 database through Java? Perhaps the providers I tried are not supported, but there may be others?