Using Windows ODBC drivers

[Florian Wunderlich]

Brian Vincent (C) wrote:
> 
>  > 5.14.2. Using Windows ODBC drivers
>  > Does anyone actually have any experience of this and anything to add?
>  >
>  >
>  > I do, with Microsoft Access and the JetProxy JDBC. If there's any
>  > interest, I can give you a quick summary on how to set things up.
> 
> It looks like the stuff on WineHQ is a bit out of date.
> A patch I submitted in March added some info that Boaz
> put together.  Now that section should read:
> 
>       <title>Using Windows ODBC drivers</title>
>         <para>
>           Native ODBC drivers have been reported to work for many types of
>           databases including MSSQL and Oracle. In fact, some like MSSQL 
> can
>         only be accessed on Linux through a Winelib app.   Rather than
>         just copying DLL files, most ODBC drivers require a Windows-based
>         installer to run to properly configure things such as registry 
> keys.
>         </para>
>         <para>
>           In order to set up MSSQL support you will first need to download
>           and run the mdac_typ.exe installer from microsoft.com.  In 
> order to
>         configure your ODBC connections you must then run CLICONFG.EXE and
>           ODBCAD32.EXE under Wine.  You can find them in the windows\system
>         directory after mdac_typ runs.  Compare the output of these 
> programs
>         with the output on a native Windows machine.  Some things, such
>         as protocols, may be missing because they rely on being installed
>           along with the operating system.  If so, you may be able to copy
>         missing functionality from an existing Windows installation as
>         well as any registry values required.  A native Windows 
> installation
>         configured to be used by Wine should work the same way it did
>         when run natively.
> 
> If you'd like to make any additions or corrections, please
> work from the version in CVS.  If you'd like me to do the
> SGML markup, feel free to email me raw text to add.  If so,
> no rush - I'll be out of town till May 10th.
> 
> Thanks!


To be honest, I'm too lazy to get the CVS version, so here's my raw 
text, which you may want to edit anyway.

First of all, I'm working with wine-20040309 from debian-unstable.

Using the Microsoft Access ("Jet") ODBC driver from Office 2000 is 
pretty straight forward.

You need the following DllOverrides:

"oleaut32"      = "native, builtin"
"ole32"         = "native, builtin"
"odbc32"        = "native, builtin"
"vbajet32"      = "native, builtin"
"msjet35"       = "native, builtin"
"msjet40"       = "native, builtin"
"msjetoledb40"  = "native, builtin"

Run the control panel using wine windows/control.exe, and configure a 
new ODBC data source as usual. Done.

Be aware that performance really suffers here. Queries run noticeably 
slower, but you would want this setup for a test system only anyway.


Using the Advantage Database Driver (see http://www.extendedsystems.com) 
is a bit more complicated. I use the same DllOverrides, though obviously 
this driver does not need overrides for the msjet stuff.

The installer works completely under Wine, except for creating the icons 
at the end - just answer no when asked wether icons should be created. 
Setting up a data source for the driver works with control.exe too.

If you want to connect to a remote Advantage server, you should be done 
now. If you plan to use the "local server" (file database, like Access 
mdb files), you have to put the files in a root directory - for example, 
if you have your .add file and the various .adt and .adi in 
/var/lib/advantage/mydb, put something like this in your ~/.wine/config:

[Drive I]
"Path" = "/var/lib/advantage/mydb"
"Type" = "hd"
"Label" = "mydb"
"Filesystem" = "win95"

then start control.exe, add a data source for the Advantage driver, and 
check "Data Dictionary" and "Local Server", uncheck "Remote Server" and 
"Internet Server", and enter as Database "I:\mydb.add". Don't use just 
the directory name.

Even though the .add file and its friends can be in any directory under 
Windows, this does not work with Wine. I don't know why - the ODBC 
driver just complains that you don't have the necessary permissions 
(even on a FAT filesystem).

If you get errors about the collation order in an index not matching 
your settings then the database you use was created with a local server 
with a different charset. As there is no mention of the charset of the 
existing index you have to reinstall the ODBC driver and choose a 
different charset (first option in the options dialog after the files 
are copied) until you find the right one. You do not have to uninstall 
the ODBC driver each time, even though the setup program asks you wether 
you want to - just overwrite the existing installation and choose a new 
charset.

The performance for this setup is ok. I didn't notice longer query times 
(yet).

As the native Linux ODBC driver for Advantage does not seem to support 
the "local server", this is the only way to access a local Advantage 
database under Linux that I know of (JDBC doesn't implement the "local 
server" either) except using the proprietary API.


Even accessing the databases via JDBC is possible, if you feel really 
adventurous. You can use the JetProxy 
<http://www.jetools.com/products/jetproxy> running from a JVM inside 
Wine. To get the j2re-1.4.1_01 java.exe running, you *have* to set 
"Windows"="winxp" inside you ~/.wine/config [Version] section.

Then create a jetproxy.properties as described in the documentation 
using the jdbc-odbc-bridge (using a CONNECTION_URL like jdbc:odbc:mydb - 
the DRIVER_CLASS sun.jdbc.odbc.JdbcOdbcDriver picks up the right ODBC 
DSN automatically).

Run the proxy like this:

wine -- ../../Java/j2re1.4.1_01/bin/java.exe \
         -classpath $LOCALCLASSPATH com.jet.jdbc.server.JETProxyServer \
         -config $CONFIG_FILE

where $CONFIG_FILE points to the properties file and $LOCALCLASSPATH 
includes all the jars in the jars directory of the JetProxy.

Now you can use any ODBC database - I've tested this with Access and 
Advantage as described above - from your Java programs. Meaning you have 
access under Linux to a Microsoft Access database. Good stuff.


BTW, the single most useful feature in Wine wouldn't be better support 
for win32, as most stuff works already anyway, but this 
[AppDefaults\\appname\\section] functionality universally for all 
sections. I'm aware that this is probably not such an easy task, but 
Microsoft Access for example runs fine with "Windows"="win98", but 
crashes with "winxp", while its the exact opposite with java.exe. Well, 
I guess you're working on this already anyway.

Also, the "ttydrv" GraphicsDriver sucks.