Re: [Iodbc-macosx] OSX 1.5: ODBC-error IM002 or IM003 using Apache, PHP

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Hi, Bert --

Everything you've provided to date suggests that

1. PHP is not finding the odbcinst.ini and/or odbc.ini file,

or

2. PHP is not linked to and/or loading iODBC properly

What I'm trying to lead you through --

1. Configure a DSN which tests successfully using the driver setup
    and/or driver manager dialogs.  Apple's "ODBC Administrator.app"
    has no TEST button, so I provided download links which would
    get you the "OpenLink ODBC Administrator.app" which *DOES* have
    a TEST button.

2. Test that same DSN with iODBC Test.app, iodbctest, or other
    simple application built more natively for Mac OS X than PHP.
    Again, these tools are included in the .dmg downloads I linked
    previously.

3. Test PHP against the now known-functional DSN.

By proceeding through these three steps in order, we narrow down
and resolve each problem area, and move most swiftly from failure
to success.

On Jun 2, 2009, at 07:07 PM, Bert Aerts wrote:
> I've tried your suggestions, and here are the results :
>
> First suggestion: the stray odbc.ini files : I found none, other
> than those you mentioned that had to be in place. I tried the
> commands  find / -name odbc*.ini -print 2> /users/bertaerts/err.txt
> and find / -name .odbc*.ini -print 2> /users/bertaerts/err.txt
> (redirects just to get nuisance warning from stderr out of my
> view), and all they showed was:
> /Library/ODBC/odbc.ini
> /Library/ODBC/odbcinst.ini
> /Users/bertaerts/Library/ODBC/odbc.ini
> /Users/bertaerts/Library/ODBC/odbcinst.ini

So far, so good.  It may be necessary to review the full content
of these files.  Please send them directly to me (or put on a
temporary FTP or similar download site), rather than sending to
all list members.

> I checked the directories you suggested (I assumed the tilde is
> my user directory, so /Users/bertaerts) :
> ls -alpG /Users/bertaerts  -> yields no .odbc.ini, nor .odbcinst.ini
> ls -alpG /etc -> /etc is a symbolic link for /private/etc
> ls -alpG /private/etc  -> no odbc.ini, no .odbc.ini, only a PHP.INI.
> (For info, I checked the PHP.INI, only reference to ODBC is an active
> (un-commented) line "extension=php_pdo_odbc.dll" - which, given the
> extension, is no factor on a Mac.)

Yes, the tilde stands for your user directory, and is understood by
the Mac's shell.  `ls -alpG ~` would have worked as well as your
first command, above.

Output is as desired.

It may be worth reviewing the php.ini content -- not just about ODBC
as such, but also about dynamic library locations and other general
settings which play a role in the ODBC environment.  The full output
of the phpinfo() can also be useful in this analysis.

> Secondly, the iodbctest app. I looked up the web manual for MySQL
> ODBC 5.1 Connector at
> <http://dev.mysql.com/doc/refman/5.1/en/connector-odbc-configuration-connection-parameters.html 
> >,
> and got iodbctest to work :
>
> iODBC Demonstration program
> This program shows an interactive SQL processor
> Driver Manager: 03.52.0406.1211
>
> Enter ODBC connect string (? shows list): ?
>
> DSN                              | Driver
> ------------------------------------------------------------------------------
> THUIS                            | MySQL ODBC 5.1 Driver
>
> Enter ODBC connect string (? shows list):user=root;password=
> secret_pwd;database=thuis
> 1: SQLDriverConnect = [iODBC][Driver Manager]Data source name not
> found and no default driver specified. Driver could not be loaded
> (0) SQLSTATE=IM002
> 1: ODBC_Connect = [iODBC][Driver Manager]Data source name not
> found and no default driver specified. Driver could not be loaded
> (0) SQLSTATE=IM002

This failure is not surprising -- you specified no DSN= in your
connect string, so there was no "Data source name" to be found...

> Have a nice day.bert-aertss-macbook-pro:bin bertaerts$ iodbctest
> iODBC Demonstration program
> This program shows an interactive SQL processor
> Driver Manager: 03.52.0406.1211
>
> Enter ODBC connect string (? shows list): driver=MySQL ODBC 5.1
> Driver;database=thuis;server=localhost;user=root;password=secret_pwd
> Driver: 05.01.0005 (libmyodbc5.so)

This is a "DSNless connect string" -- you have entirely bypassed
the odbc.ini file.

However, by specfying the driver by name (not by file location),
you did use the odbcinst.ini file, and proved that the driver,
driver manager, and odbcinst.ini file are all working as desired.

You've also confirmed the minimum set of attributes needed for a
good DSN with that driver.

> SQL>show tables;
>
> Tables_in_thuis
> <lis of my tables>
>
> Logically, I immediately pasted this "DSN"-like definition in my
> connect script - and this failed ...

That doesn't surprise me.

> This time, I noticed again something different from last attempts.
> Before starting the MySQL Server, I executed the following
> statements:
> bertaerts$ export ODBCINI=/Library/ODBC/odbc.ini
> bertaerts$ export ODBCSYSINI=/Library/ODBC

Once again -- ODBCINSTINI was left unset.  This is KEY to making
a DSN-based connection, and to making any connection in which the
driver is specified by name ("MySQL ODBC 5.1 Driver") rather than
path ("/Library/ODBC/...")

The ODBCSYSINI environment variable is specific to unixODBC, which
plays no role on Mac OS X, unless you've installed it yourself --
not generally recommended, for a great many reasons.  ODBCSYSINI
may therefor be left unset.

> bert-aertss-macbook-pro:~ bertaerts$ cd /usr/local/mysql/bin
> bert-aertss-macbook-pro:bin bertaerts$ ./mysqld_safe
> (i.a.w. <http://dev.mysql.com/doc/refman/5.1/en/connector-odbc-configuration-dsn-unix.html 
> > .
> I tried it, because one of your focal points was the default
> location of an odbc(inst).ini file)

For Mac OS X, the relevant Connector/ODBC documentation is here --
<http://dev.mysql.com/doc/refman/5.1/en/connector-odbc-configuration-dsn-macosx.html 
 >

> The odbc_connect( ) calls using the DSN failed with IM003, those
> with "driver=...;database=...;" failed with IM002.
>
> This is a snippet from the script return to illustrate:
>
> Warning: odbc_connect() [function.odbc-connect]: SQL error:
> [iODBC][Driver Manager]Specified driver could not be loaded,
> SQL state IM003 in SQLConnect in /Users/bertaerts/Documents/
> Apache-PHP/DB_thuis/connect.php on line 26
>
> Could not connect to ODBC source 'thuis' with 'root', pwd
> 'secret_pwd'
>
> Warning: odbc_connect() [function.odbc-connect]: SQL error:
> [iODBC][Driver Manager]Data source name not found and no default
> driver specified. Driver could not be loaded, SQL state IM002 in
> SQLConnect in /Users/bertaerts/Documents/Apache-PHP/DB_thuis/
> connect.php on line 32
>
> Could not connect to ODBC source 'DRIVER={MySQL ODBC 5.1 Driver};
> DATABASE=thuis;SERVER=localhost' with '', pwd ''
>
> I hope this sheds a new light ?

The PHP ODBC connect calls don't make allowance for DSNless connect
strings (unless there's been a recent change); they require that you
pass the DSN, UID, and PWD, as you initially attempted.

Regards,

Ted

-- 
A: Yes.                      http://www.guckes.net/faq/attribution.html
| Q: Are you sure?
| | A: Because it reverses the logical flow of conversation.
| | | Q: Why is top posting frowned upon?

Ted Thibodeau, Jr.           //               voice +1-781-273-0900 x32
Evangelism & Support         //        mailto:tth...@op...
OpenLink Software, Inc.      //              http://www.openlinksw.com/
                                  http://www.openlinksw.com/weblogs/uda/
OpenLink Blogs              http://www.openlinksw.com/weblogs/virtuoso/
                                http://www.openlinksw.com/blog/~kidehen/
     Universal Data Access and Virtual Database Technology Providers