Re: Using Windows ODBC drivers

[Brian Vincent (C)]

> 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!

---------------
Brian Vincent
Copper Mountain Telecom
[e-mail]

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.

Using Windows ODBC drivers

Hi Brian,

I'm not sure if this is useful, but I have been able to access MSSQL
databases from Linux using Unix ODBC and FreeTDS. I was unable to get Wine
to use this method of accessing the database, so I switched back to Windows
ODBC drivers.

Simon Tyler

"Brian Vincent                                                
             (C)"                                                          
             <VincentB@copperc                                          To 
             olorado.com>              "'[e-mail]'"         
             Sent by:                             
             wine-devel-admin@                                          cc 
             winehq.org                "'[e-mail]'"           
                                                    
                                                                   Subject 
             04/29/2004 01:33          Re: Using Windows ODBC drivers      
             PM
> 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!

---------------
Brian Vincent
Copper Mountain Telecom
[e-mail]