ODBC problem on Mac: Stata/SE 14.1 libiodbc @3.52.10.1_0+x11 (installed via Macports)

[Matthew Gunn]

I have iODBC working on my Mac, but I can't get ODBC to work in Stata. Any suggestions?

The command: "odbc list" incorrectly returns the following output:

Data Source Name Driver
-------------------------------------------------------------------------------
m p

In contrast, running the program iodbctest from a unix terminal correctly returns:
Enter ODBC connect string (? shows list): ?
DSN | Driver
------------------------------------------------------------------------------
mgunn | psql

Furthermore, I can connect and issue queries using iodbctest, but I cannot in Stata! Eg. using iodbctest:
Enter ODBC connect string (? shows list): DSN=mgunn
Driver: 09.05.0210 (psqlodbcw.so)

SQL>select me_idx20 FROM eb2.manager_skill_t1 limit 2;

me_idx20
-----------
2
2
Yay! it works here...

But in Stata, the command: odbc load, table("eb2.manager_skill_t1") dsn("mgunn")
will simply return the error:
The ODBC driver reported the following diagnostics
[iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be
loaded
SQLSTATE=IM002

[Dimitriy V. Masterov]

I had a very similar problem when I had odbc driver set incorrectly. See if one of the last two lines fixes things:

set odbcmgr iodbc
set odbcdriver ansi
set odbcdriver unicode

Here's the output:

​. set odbcmgr iodbc

. set odbcdriver ansi

. odbc list

Data Source Name                   Driver
-------------------------------------------------------------------------------
hopper                             tdata.dylib
mozart                             tdata.dylib
mozart2                            tdata.dylib
vivaldi                            tdata.dylib
-------------------------------------------------------------------------------

. odbc load, exec("select TOP 10 * from DW_USERS;") clear dsn("mozart") sqlshow
select TOP 10 * from DW_USERS;

. set odbcdriver unicode

. odbc list

Data Source Name                   Driver
-------------------------------------------------------------------------------
h                                  t
m                                  t
m                                  t
v                                  t
-------------------------------------------------------------------------------

. odbc load, exec("select TOP 10 * from DW_USERS;") clear dsn("mozart") sqlshow
The ODBC driver reported the following diagnostics
[iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded
SQLSTATE=IM002
r(682);

[Matthew Gunn]

Thanks! `set odbcdriver ansi` appears to do the trick!