SQLSelect from Microsoft Access database tables with international character set

Question

I am trying to import data from the Northwind (accdb) database and I have both the English and Greek versions. The problems I have are the following:

1) When I am trying to import data from NorthwindGR DB Wolfram outputs everything with ????? is there any way to read/convert to UTF8 ?

2) I am using OpenSQLConnection

northwindConn = OpenSQLConnection[JDBC["Microsoft Access(ODBC)", "C:\\DataIO\\accdb\\Northwind-EN.accdb"]]

and I have also tried the connection successfully with

northwindODBC = OpenSQLConnection[JDBC["ODBC(DSN)", "northwindODBC"]]

then I am reading from Orders table with

SQLSelect[northwindConn, "Orders"]

and

SQLSelect[northwindConn,"Suppliers"]//TableForm

1   Supplier A  Andersen    Elizabeth A.    Null    Sales Manager
2   Supplier B  Weiler      Cornelia        Null    Sales Manager

etc...

without any problem, but when I try to read data from table e.g.

SQLSelect[northwindConn, "Order Details"]
JDBC::error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause. >>
$failed

SQLSelect[northwindConn, "Order Details Status"]
JDBC::error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause. >>
$failed

Any ideas, why I am getting this error message and how it can be solved ?

Thank you.

PS: I am using Wolfram 10.0 on Windows 7 x64, MSOffice 2013 and Microsoft Access Database engine 2010 (x64) for compatibility purposes for some other program that requires this. But that does not seem to be the source of the problem because I tried doing the same as above after uninstalling the Access db engine.

Answer 1

thanks for your comment. I forgot to mention that I was getting the same error in another database with a table name string without any spaces.

SQLSelect[beansODBC, "ORDER"]
JDBC::error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM  clause. >>
$Failed

But you are right, I looked more into Microsoft Access syntax and I found that square brackets are necessary for delimiting an identifier in all of the following cases

• SQL reserved words: "Order"
• Words containing spaces: "Order qty"
• Words containing punctuation: "Order-qty" Words containing
• international characters Column names that are case-sensitive:
• "Order" vs. "order"

Therefore in the above example the correct way to make the query is

SQLSelect[beansODBC, SQLTable["[ORDER]"]]

or another example would be

SQLExecute[northwindConn, "select * from [Order Details]"] // TableForm

Now, I tried to apply the square brackets to table names in Greek from northwindGR database, but it failed. This is what I got.

northwindGRConn = OpenSQLConnection[JDBC["Microsoft Access(ODBC)", "C:\\DataIO\\accdb\\Northwind-GR.accdb"]];
tabs = SQLTables[northwindGRConn];

tabs[[1]]
SQLTable["??????????", "TableType" -> "TABLE"]

SQLSelect[northwindGRConn, tabs[[1]]]
JDBC::error: [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect >>
$Failed

In fact it fails with the same error message at any attempt to pass a unicode string.

SQLSelect[northwindGRConn,"[Προϊόντα]"]
JDBC::error: [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect >>
$Failed

Then I checked the default raw character encoding that the notebook is using for input and output functions

$CharacterEncoding
WindowsANSI

I changed that to WindowsGreek, UTF8, Unicode and tried again but that did not solve the problem.

Then I realized that this is also a Microsoft Access related problem. The query input or output does not support Unicode characters. Instead, it uses the older Windows technology called "code pages" to provide support for non-ASCII characters.

The character encoding that I am getting on the notebook display corresponds to the code page that is used by the Windows system locale as specified in the "Region and Language" control panel (Windows7). Therefore I had to select the Administrative tab and set the Language for non-Unicode programs to my system locale, Greek(Greece).

That solved the problem.....

SQLTables[northwindConnGR]
.....
SQLTable["Προϊόντα", "TableType" -> "TABLE"]
SQLTable["Παραγγελίες", "TableType" -> "TABLE"]
SQLTable["Λεπτομέρειες παραγγελιών", "TableType" -> "TABLE"]

SQLExecute[northwindConnGR, "select * from Προϊόντα"]

and finally an example to demonstrate the complete solution to the problem

SQLSelect[northwindConnGR, "[Λεπτομέρειες παραγγελιών]", {"Αναγνωριστικό", "[Κωδικός είδους]", "[Τιμή μονάδας]", "Ποσότητα"}, "ShowColumnHeadings" -> True] // TableForm

Αναγνωριστικό   Κωδικός είδους  Τιμή μονάδας    Ποσότητα
27              34              14.             100.
28              80              3.5             30.
29              7               30.             10.