Interop Systems Inc.
Accessing SQL Server and Other Databases from SFU 3.5
Microsoft Windows Services For UNIX 3.5 has no built-in libraries or interfaces for accessing relational databases stored on other platforms. Nor is it possible to access a Microsoft SQL Server database on the same system, running in the Windows subsystem.
However, there are several packages that can ease this problem. Our Database Tools area contains recent builds of the following (along with source files and a Readme.Interix with each source file):
All of these applications and libraries are built with support for threads enabled by default.
Note: Special thanks to our database consulting partner, MSB Associates, for porting these tools, and thanks to Microsoft for providing the funding. This is a new page within our UNIX Tools Community so in keeping with the "community" spirit please post your comments, questions, examples and bugs in the new area of the UNIX Tools Forum. If you require one-on-one assistance, please refer to our Services page for our telephone and email support packages.
Microsoft SQL Server 2000
Microsoft SQL Server 7.0
Sybase Adaptive Server Anywhere 12.0 and 12.5
Ancient versions of either SQL Server or Sybase
The configuration file /usr/local/etc/freetds.conf should contain entries for your databases. Use the entries there as templates for each server type.
Further information is available at www.freetds.org.
Perl is especially complex to build correctly under SFU 3.5. There are several pitfalls an unwary or inexperienced developer can fall into.
The best approach is to use the config.sh and Policy.sh supplied files and edit these as appropriate. Then run $ ./Configure -der
to rebuild the makefiles etc. Then gmake to build a new binary.
Almost all the tests run by gmake test now run correctly. However, there are a number of special cases for SFU (for example, the privileged user does not have UID 1 in SFU).
In a very few cases, gmake test (or prove -b -v t/*.t) will hang after all the tests have completed successfully. It is believed that this is an issue with end-of-file signaling on pipes in Interix, and is still under investigation.
The FreeTDS binary package installs into /usr/local/freetds. A configuration file, etc/freetds.conf describes the databases it can connect to (NOTE the relationship between the name in this file and that in the ODBC configuration files; the ODBC file Servername describes an entry in this file). FreeTDS supports a number of different ways of setting up the configuration files; the method described here is strongly recommended. It is known as the "ODBC-Combined" method. It is described in more detail at http://www.freetds.org/userguide/odbcombo.htm. The configuration file can be overridden with the environment variable FREETDSCONF.
A sample configuration file:
# # The freetds.conf file is a replacement for the original interfaces # file developed by Sybase. You may use either this or the interfaces # file, but not both. # # FreeTDS will search for a conf file in the following order: # # 1) check if a file was set programatically via dbsetifile() and # is in .conf format, if so use that, # # 2) look in ~/.freetds.conf # # 3) look in @sysconfdir@/freetds.conf # # If FreeTDS has found no suitable conf file it will then search for # an an interfaces file in the following order: # # 1) check if a file was set programatically via dbsetifile() and # is in interfaces format, if so use that, # # 2) look in ~/.interfaces # # 3) look in $SYBASE/interfaces (where $SYBASE is an environment # variable) # # Only hostname, port number, and protocol version can be specified # using the interfaces format. # # The conf file format follows a modified Samba style layout. There # is a [global] section which will affect all database servers and # basic program behavior, and a section headed with the database # server's name which will have settings which override the global # ones. # # Global settings, any value here may be overridden by a database # server specific section [global] # TDS protocol version tds version = 4.2 initial block size = 512 swap broken dates = no swap broken money = no # Database server login method, if both server and domain # logins are enabled, domain login is tried first if a domain # is specified, and if that fails the server login will be used. try server login = yes try domain login = no # The default authentication domain, can be overridden by # specifying a username with a domain prefix, e.g. DOMAIN\username ; nt domain = WORKGROUP # If the server responds with different domain try that one? cross domain login = no # Whether to write a TDSDUMP file for diagnostic purposes # (setting this to /tmp is insecure on a multi-user system) ; dump file = /tmp/freetds.log ; debug level = 10 # Command and connection timeouts ; timeout = 10; ; connect timeout = 10; # This is a Sybase hosted database server, if you are directly on the # net you can use it to test. [JDBC] host = 18.104.22.168 port = 4444 tds version = 5.0 # A typical Microsoft SQL Server 7.0 configuration ;[MyServer70] ; host = ntmachine.domain.com ; port = 1433 ; tds version = 7.0 # A typical Microsoft SQL Server 7.0 configuration using domain logins ;[MyServer70] ; host = ntmachine.domain.com ; port = 1433 ; tds version = 7.0 ; try domain logins = yes ; try server logins = no ; nt domain = MYDOMAIN # A typical Microsoft SQL Server 6.x configuration ;[MyServer65] ; host = ntmachine.domain.com ; port = 1433 ; tds version = 4.2 [thames] host = 10.88.176.148 port = 1433 tds version = 8.0
3.x - 5.x
2.x - 5.x
9.x - 11.x
3.x - 5.x
IBM RS6000, PowerPC
x86, Itanium, PowerPC
Mac OS X
Concurrent Maxion 9200 MP
SGI Irix SVR4
IP12 MIPS, IP22 MIPS
Sun Sparc, PCx86
As the iODBC driver manager uses autoconf/automake/libtool it should be portable to most modern UNIX platforms out of the box. However if you do need to make changes to the code or the configuration files, we would appreciate you share your changes with the rest of the internet community by mailing your patches to . so we can include them for the next build.
Porting of iODBC driver manager to some non-UNIX operating systems such as Windows family(3.x, 95, NT), OS/2 and Mac Classic is supported but has not been compiled and tested recently. Of course, you need to supply a make/build file and a short LibMain for creating the iodbc.dll.
Microsoft SFU users should follow the directions in README.interix. In particular, ensure that the environment variableCFLAGS=-D_ALL_SOURCE is set.
Mac OS X users should read the separate README.MACOSX document for more detail of porting on this platform. Otherwise:
Run configure to adjust to target platform
Run gmake install
The configure program will examine your system for various compiler flags, system options etc. In some cases extra flags need to be added for the C compiler to work properly.
e.g. on HP systems you may need:
$ CFLAGS="-Ae -O" ./configure --prefix=/usr/local ..........<
Note that the path of the system wide odbc.ini file is calculated as follows (based on flags to ./configure):
default is /etc/odbc.ini
If the `--with-layout=' option is set, then the prefix and sysconfdir parameters will be changed accordingly. Currently, this parameter understands values of `gentoo', `redhat', `gnu', `debian' or `opt' (with everything going into /opt/iodbc/). If both are specified, a --prefix argument will overrule a --with-layout.
The binary package was built with
$ ./configure --prefix=/usr/local/iodbc --enable-pthreads --with-iodbc-inidir=/usr/local/iodbc/etc
http://www.iodbc.org/ iODBC project home page. Binaries, source, documentation
http://sourceforge.net/projects/iodbc Source, CVS tree, mailing lists, forums, bug reports
http://www.openlinksw.com/ OpenLink Software. Free trials and support for OpenLink's ODBC drivers.
http://www.microsoft.com/data/odbc/ Microsoft's ODBC pages.
Both the unixODBC and iODBC driver managers use two configuration files to control their operation. The first of these, odbcinst.ini describes the ODBC drivers installed on the system. It resides in the etc directory of the Driver Manager, and should be maintained by the System Administrator.
The other file, odbc.ini describes the data sources, or database instances, to which applications will be connecting. As for MS Windows, there can be two sorts of datasource, User and System.
User datasources are found in the file ~/.odbc, while system datasources are in etc/odbc.ini under the driver manager tree.
For each driver manager, the environment variable ODBCINI overrides the search for a dsn, and describes a file to be used in place of either ~/.odbc.ini or etc/odbc.ini.
It is a good idea to make symbolic links from the etc/*.ini files to the /etc directory for other programs that want to examine these files.
The relevant odbc.ini describes data sources. A data source is a section (enclosed in square parenthesis), and the attributes for a data source are given within this section. The most important attribute for each datasource is the Driver attribute. This must point to the shared library for the ODBC driver associated with the data source, or to an entry in the etc/odbcinst.ini file.
As an example, the OpenLink ODBC drivers have a number of attributes which can be set for a data source. Here is a description (with ODBC connect string tags between parenthesis):
The hostname where the database resides (HOST).
The type of server (see oplrqb.ini on the server, SVT).
Server-specific extra options. See OpenLink server documentation for agents which can use this.
The database to use (DATABASE).
Connect options for the database (OPTIONS).
The name of the user (a password cannot be specified in the UDBCINI file, UID/PWD).
A Yes/No value in order to make the connection read-only (READONLY=Y/N).
The number of records that are transferred in a single call to the server. Default is 5; maximum is 99, minimum is 1 (FBS=value).
The protocol to use. Leave set to ``TCP'' for this release.
Apart from these data source-specific settings, you may add a section called [Communications], which you may use to tune our driver further:
The time the client application will wait for the database agent to finish the request (default is 60 seconds).
The time the client application will wait for the request broker to accept of reject a database connection (default is 30 seconds).
RPC send buffer size. A value of 0 (the default) will cause the application to use system-dependent defaults.
RPC receive buffer size. A value of 0 (the default) will cause the application to use system-dependent defaults.
If set, the name of a file to which debugging output from the driver should be directed.
Driver managers and drivers use odbc.ini file or connection string when establishing a data source connection. On Windows, odbc.ini is located in Windows directory.
On UNIX, both the driver managers look for the odbc.ini file in the following sequence:
check environment variable ODBCINI
check $HOME/.odbc.ini (iODBC only, not unixODBC)
check home in /etc/passwd and try .odbc.ini in there
system-wide odbc.ini (settable at configuration time)
Item 1 is the easiest as most drivers will also look at this variable.
The format of odbc.ini( or ~/.odbc.ini ) is defined as:
odbc.ini ::= data_source_list data_source_list ::= /* empty */ | data_source '\n' data_source_list data_source ::= '[' data_source_name ']' '\n' data_source_desc data_source_name ::= 'default' | [A-Za-z]*[A-Za-z0-9_]* data_source_desc ::= /* empty */ | attrib_desc '\n' data_source_desc addrib_desc ::= Attrib '=' attrib_value Attrib ::= 'Driver' | 'PID' | 'UID' | driver_def_attrib driver_def_attrib ::= [A-Za-z]*[A-Za-z0-9_]*
An example of an odbcinst.ini file:
[ODBC] Trace = Yes TraceFile = /tmp/sql.log [OOB] Description = Easysoft ODBC-ODBC Bridge Driver = /usr/local/easysoft/oob/client/lib/libesoobclient_r.so Setup = FileUsage = 1 [TDS] Description = FreeTDS ODBC Driver Driver = /usr/local/lib/libtdsodbc.so FileUsage = 1
An example of an odbc.ini file:
; ; odbc.ini ; [demo] Driver = OOB Description = Easysoft ODBC-ODBC Bridge demo data source SERVER = demo.easysoft.com PORT = 8888 TRANSPORT = tcpip TARGETDSN = pubs LOGONUSER = demo LOGONAUTH = easysoft TargetUser = demo TargetAuth = easysoft [orasvr] Driver = OOB Description = Oracle server on MSBIT TARGETDSN = orasvr SERVER = localhost TARGETUSER = scott TARGETAUTH = tiger LOGONUSER = nick LOGONAUTH = xxxx TRANSPORT = tcpip PORT = 8888 [sqlsvr] Driver = OOB Description = SQL server on THAMES TARGETDSN = sqlsvr SERVER = localhost TARGETUSER = sa TARGETAUTH = sa LOGONUSER = nick LOGONAUTH = xxxx TRANSPORT = tcpip PORT = 8888 [thames] Driver = TDS Description = SQL server via FreeTDS Servername = thames
Both the unixODBC and iODBC drivers manager trace the driver's ODBC call invoked by the driver manager. Default tracing file is ./odbc.log for iODBC, or /tmp/odbc.log for unixODBC. Tracing option (i.e. on/off or optional tracing file name) can be set in odbc.ini (iODBC) or odbcinst.ini (unixODBC) file under the [ODBC] heading as:
ODBC] TraceFile = optional_trace_file Trace = ON | On | on | 1 | OFF | Off | off | 0
For iODBC only, if optional_trace_file is stderr or stdout, i.e.
TraceFile = stderr
TraceFile = stdout
The tracing message will go to the terminal screen (if it is available).