SQL server + ODBC Bridge (Windows) + unixODBC 2.2.11 (Centos 4.3) php-odbc 4.3.9
commands: isql,iusql,odbcinst,odbc_config,dltest

Note that when working with PHP, unixODBC and a database we have several user accounts to work with at the same time

- database user
- php (apache) user
- unixODBC user


0a) Preparation
Make sure Web server and Application server can communicate with each other (not firewalled etc.)

See below how arp is registering the mac address of the contacted network card.

[root@localhost ssh]# /sbin/arp -a
(192.168.0.1) at 00:0A:79:17:9D:BA [ether] on eth0
[root@localhost ssh]# telnet 192.168.0.4 22002
Trying 192.168.0.4...
Connected to 192.168.0.4 (192.168.0.4).
Escape character is '^]'.
^]

telnet> quit
Connection closed.
[root@localhost ssh]# /sbin/arp -a
(192.168.0.1) at 00:0A:79:17:9D:BA [ether] on eth0
(192.168.0.4) at 00:14:38:B8:3A:56 [ether] on eth0


0b) Windows side preparation

odbrd -install
odbrd -remove
odbrd -debug

or

net start "ODBC Bridge Server"
net stop "ODBC Bridge Server"


Dont forget to

- Set the ODBCBridge service to "automatic" !!


1) Setting up the driver in odbcinst.ini

<might not be required - depending on your setup>


2) Setting up the Data Source Name (DSN) connection in odbc.ini

The DSN configuration options depend on the ODBC Driver you are using. Please see what attributes you need / can to define.

[master]
Driver = /usr/lib/unixODBC/brodbc.so
Server = 192.168.1.55
Port = 22002
Database = master
#Username = Administrator ## might not be required ##
Servercharset = Shift_jis ## Windows (server) side ##
Charset = eucjp ## Linux (client) side ##
Debug = 1

Tips:

For ASA, the section name (dataserver name) in freetds.conf must be the same as the the ASA database name.



possible problems:

a) host,port,connection is firewalled or has no network route

b) bad network connection (slow)

c) Confirm the odbc driver file and its functions



d) Combatibility issues between unixODBC and ODBCBridge

e) configuration mistake (f.e DSN misspell)

f) UnixODBC: Confirming settings and version

odbcinst -h
odbcinst -j

Example:

odbcinst -j
unixODBC 2.2.11
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
USER DATA SOURCES..: /root/.odbc.ini


g) UnixODBC: Which DSN's are defined ? - list user and system data sources

odbcinst -q -s
[test]
[db]
[master]
[slave]

or you might get

[root@localhost etc]# odbcinst -q -s
odbcinst: SQLGetPrivateProfileString failed with .

Make sure the file "odbcinst.ini" exist with the right file permissions.

Also please see the CONFIG_sample.txt (here for PostgreSQL

CPPFLAGS="-I/usr/local/include -I/usr/local/pgsql/include -I/usr/include/openssl"
LDFLAGS="-L/usr/local/lib -L/usr/local/pgsql/lib"
export CPPFLAGS LDFLAGS
configure --enable-unicode --with-unixodbc --with-odbcver=0x0300 --enable-pthreads

Furthermore, Please check of LD_LIBRARY_PATH at the time of execution.


h) "odbc_config" is similar to "odbcinst"

Note:

--version: driver version
--longodbcversion: ODBC compatible version


[root@localhost usr-local-etc]# odbc_config -h
Usage: odbc-config
[--prefix]
[--exec-prefix]
[--include-prefix]
[--lib-prefix]
[--bin-prefix]
[--version]
[--libs]
[--static-libs]
[--libtool-libs]
[--cflags]
[--odbcversion]
[--longodbcversion]
[--odbcini]
[--odbcinstini]

[root@localhost usr-local-etc]# odbc_config --version
2.2.11
[root@localhost usr-local-etc]# odbc_config --odbcversion
3
[root@localhost usr-local-etc]# odbc_config --longodbcversion
3.52
[root@localhost usr-local-etc]# odbc_config --odbcini
/usr/local/etc/odbc.ini
[root@localhost usr-local-etc]# odbc_config --odbcinstini
/usr/local/etc/odbcinst.ini
[root@localhost usr-local-etc]# odbc_config --prefix
/usr/local
[root@localhost usr-local-etc]# odbc_config --exec-prefix
/usr/local
[root@localhost usr-local-etc]# odbc_config --lib-prefix
/usr/local/lib
[root@localhost usr-local-etc]# odbc_config --bin-prefix
/usr/local/bin
[root@localhost usr-local-etc]# odbc_config --libs
-L/usr/local/lib -lodbc
[root@localhost usr-local-etc]# odbc_config --static-libs
/usr/local/lib/libodbc.a
[root@localhost usr-local-etc]# odbc_config --libtool-libs
/usr/local/lib/libodbc.la
[root@localhost usr-local-etc]# odbc_config --cflags
-DHAVE_UNISTD_H -DHAVE_PWD_H -DHAVE_SYS_TYPES_H -DHAVE_LONG_LONG -DSIZEOF_LONG=4


i) Isql: Confirming isql settings

isql -h

Example:

isql <Datasourcename> <username> <password>
(Sectionname in odbc.ini)


NOTE: (depending on the implementation)unixODBC and especially isql can have a poor error and log activity that actually wont help at all but just saying something like SQLError if you do a "cat brodbc.log" in your current directory *sigh*

- Immediate response: [ISQL]ERROR: Could not SQLConnect --> DSN name mistaken

- Response After 5 sec : [ISQL]ERROR: Could not SQLConnect -->
- remove any spaces at the beginning of the line
- check the set database name in odbc.ini
- check IP address of server is set correctly
- check port forwarding set on Firewalls or server machine
- check connection method
- check ODBC driver
- check ODBC Bridge (server side)


j) Checking supported function calls by using dltest

[root@localhost etc]# dltest /usr/local/lib/brodbc.so SQLAllocEnv
SUCCESS: Loaded /usr/local/lib/brodbc.so
SUCCESS: Found SQLAllocEnv



k) Config files

/etc[/unixODBC]/odbc.ini
/etc[/unixodbc]/ODBCINST.INI
/etc/php.d/odbc.ini

or

/usr/local/lib/odbc.ini
/usr/local/lib/odbcinst.ini

or

/usr/lib/unixODBC/odbc.ini
/usr/lib/unixODBC/odbcinst.ini


l) Syntax check for odbc.ini + odbcinst.ini

...


x) Issues & solution proposals:

- Error logging can be an issue (depending on the driver) by NOT providing enough details in error messages. (f.e. isql)

- You might be able to "trace" what the ODBC driver or "Bridge" is doing
- use Netstat and Linux / Windows logfiles to check that the connection is actually attempted between the correct hosts.



more info:

gg: [ISQL]ERROR: Could not SQLConnect sql server ODBC bridge

http://www.unixodbc.org/
/usr/local/lib/odbc.ini
http://www.easysoft.com/developer/interfaces/odbc/linux.html
http://www.easysoft.com/developer/interfaces/odbc/diagnostics_error_status_codes.html
http://www.easysoft.com/developer/interfaces/odbc/sqlstate_status_return_codes.html (ODBC Status Return Codes)
http://www.atmarkit.co.jp/bbs/phpBB/viewtopic.php?topic=9874&forum=10&1

Products:
http://www.amy.hi-ho.ne.jp/jbaba/index.htm
http://www.amy.hi-ho.ne.jp/jbaba/brodbc/brodbc3.htm
http://www.easysoft.com/products/data_access/odbc_odbc_bridge/index.html

http://www.datadirect.com/developer/odbc/index.ssp
http://www.datadirect.com/developer/odbc/troubleshoot_odbc_driver/index.ssp
http://www.datadirect.com/download/docs/dochome/index.ssp

http://www.freetds.org/userguide/index.htm
http://www.freetds.org/userguide/odbcdiagnose.htm