Looking for good documentation of odbc.ini and odbcinst.ini files on Linux

Question

I saw partial information, old information, but nothing as good as the actual sample files with brief comments that I have locally.

I need to understand the exact relationship between odbc.ini and odbcinst.ini. Superficially it is pretty obvious - at the top of the odbc.ini file there is a section like:

[ODBC Data Sources]
MYDSN = MyDriverName
...

However, I am not sure if I can, for example, have settings in either the driver or the DSN section.

1) I have a line Driver = /path/to/file/.so in both files and the values sometimes differ. Does this even make sense? If so, which prevails?

2) Is odbcinst.ini a JavaScript-like "prototype" for odbc.ini? In other words, if I am creating a number of DSNs with common settings, can I promote common settings from odbc.ini into odbcinst.ini?

3) What is the difference between Driver and Setup in odbcinst.ini? They seem to have the same values. Are these settings database-specific or are they universal?

Solution 1

I use FreeTDS on Debian to connect a php-driven website to a MS-SQL Server 2005 Database.

The explanation I can give to the config files:

/etc/odbc.ini

Holds the instance that is referred to within the handler (e.g. php) that connects to the database (see example below). The configuration defines the server it needs to connect to.

[freetds_odbc_connection]
Driver          =       FreeTDS
Description     =       test
Database        =       MyCompanyDb
Server          =       frodo
Readonly        =       Yes
Port            =       1433
Trace           =       No

-

/etc/odbcinst.ini

Holds the configuration for the Driver section in odbc.ini.

[FreeTDS]
Description     = TDS connection
Driver          = /usr/lib/odbc/libtdsodbc.so
Setup           = /usr/lib/odbc/libtdsS.so
UsageCount      = 1
FileUsage       = 1
Trace           = Yes
TraceFile       = /tmp/odbcinst_tr

-

show-companies.php

Example php code to demonstrate how I set up and use the connection.

  $host="freetds_odbc_connection";
  $user="freetds";
  $password="secretpassword";
  $conn_id = odbc_connect($host, $user ,$password) or die (odbc_errormsg());

  $sql_companies =  "SELECT * from AMGR_Client_Tbl WHERE Record_Type='1'";

  $query_companies = odbc_exec($conn_id, $sql_companies);
  while (odbc_fetch_row($query_companies))
  {
    $client_id     = odbc_result($query_companies, 6);
    $company_name  = odbc_result($query_companies, 9);
  }

etc.. etc..

I'm sure there's many other variables that can be set and used but this is the simplest explanation I can give of the files you asked about.

Solution 2

If you only look at Unix implementations, you'll get some interesting ideas about how ODBC works. None of these implementations are 100% comparable to the reference implementation on Windows, produced by the maintainer of the ODBC spec, i.e., Microsoft.

There are two ODBC driver managers commonly found on Unix. iODBC, whose documentation relevant to this question is here, is maintained and supported by my employer. UnixODBC is the other, and was discussed in other answers. These are meant to be API equivalent to each other and to the Windows implementation, as both are platform-agnostic implementations of the standard.

In simple terms, odbcinst.ini is a registry and configuration file for ODBC drivers in an environment, while odbc.ini is a registry and configuration file for ODBC DSNs (Data Source Names), typically based on drivers registered in the other.

You had a few specific questions...

1) I have a line Driver = /path/to/file/.so in both files and the values sometimes differ. Does this even make sense? If so, which prevails?

The Driver = /path/to/file.so should generally be the same in both files when both are expressed as paths. In odbc.ini, this entry may instead be Driver = {name of driver} where the name is as indexed in odbcinst.ini. Generally speaking, settings in odbc.ini prevail over conflicting settings in odbcinst.ini if such exist.

2) Is odbcinst.ini a JavaScript-like "prototype" for odbc.ini? In other words, if I am creating a number of DSNs with common settings, can I promote common settings from odbc.ini into odbcinst.ini?

No, odbcinst.ini is not a "prototype" in this way. odbcinst.ini settings are relevant to the driver, but not to the DSNs based on that driver.

3) What is the difference between Driver and Setup in odbcinst.ini? They seem to have the same values. Are these settings database-specific or are they universal?

In odbcinst.ini, the Driver = refers to the driver library, and Setup = to the setup library. The latter is entirely optional, and when it does exist, it may but need not be used during a data connection; it is primarily intended for use by an ODBC Administrator when "setting up" such connections, to be saved as DSNs. Sometimes, these libraries are found in the same physical file, but they need not be, and, for instance, are typically not in the OS X environment.

Solution 3

Okay simple difference between odbcinst.ini and odbc.ini from unixodbc site which is the first hit on google:

The system file odbcinst.ini contains information about ODBC drivers available to all users, and the odbc.ini file contains information about DSN's available to all users. These "System DSN's" are useful for application such as web servers that may not be running as a real user and so will not have a home directory to contain a .odbc.ini file.

Comments

[PersianGulf]

unixodbc.org/doc

[Schrute]

I think the link above would help answer. Think about it this, if you configure an odbc.ini file for ORACLE datasource, how does the system know what ORACLE is, this is what the odbcinst.ini defines. This can be just local to a specific set of paths/software, instead of "system wide".

[Leonid]

Thanks. I forgot to mention that I inferred that much from the syntax - indeed every DSN must specify a driver at the very top of odbc.ini with the {DSN} = {Driver} syntax but I am still much confused.