DB/Unix ODBC

There are different ODBC managers for Unix, notably unixODBC http://www.unixodbc.org and iODBC http://www.iodbc.org . Mac OS X includes iODBC manager and Administrator application.

For particular databases a driver needs to be installed and set up in ODBC configuration. For SQLite a good driver is SQLite ODBC Driver (see in references). There is a Mac OS X binary installer on that site.

odbc.ijs has a number of issues that may need to be addressed before it can be used productively on particular platforms.

• library binding needs to allow for different platforms
• some of the declarations incorrectly user int for short type, which is OK for little-endian systems but will blow on PowerPC for example.
• does not support some important ODBC types, notably DATE, which makes it impossible to use for enterprise applications
• like all cd calling libraries, will improve with ">" interface

SQLite Example on Mac OS X

• download the Northwind database above; it can be browsed with SQLite Browser
• download the odbc.ijs patch above and copy it over to j601/system/packages/odbc/
• download and install SQLite ODBC Driver
• add the driver to ODBC Administrator (from Applications / Utilities)
  • name it SQLite and provide path /usr/local/lib/libsqlite3odbc.dylib
• add Northwind data source to User DSN
  • choose SQLite driver, name it Northwind and provide path

Sample Session::

   load'dd'
   ddsrc''
+---------+------+
|Northwind|SQLite|
+---------+------+

   ]ch=. ddcon 'dsn=Northwind'
166876592

   ddfet _1,~ ddtbl ch
+-+-+------------+-----+-+
| | |Categories  |TABLE| |
+-+-+------------+-----+-+
| | |Customers   |TABLE| |
+-+-+------------+-----+-+
| | |Employees   |TABLE| |

  'Categories' ddcol ch
+----------+------------+---------+---------+---------+------+-----+-----+--------+
|TABLE_NAME|COLUMN_NAME |DATA_TYPE|TYPE_NAME|PRECISION|LENGTH|RADIX|SCALE|NULLABLE|
+----------+------------+---------+---------+---------+------+-----+-----+--------+
|Categories|CategoryID  |4        |INTEGER  |9        |10    |10   |0    |1       |
+----------+------------+---------+---------+---------+------+-----+-----+--------+
|Categories|CategoryName|_1       |TEXT     |0        |65536 |10   |0    |1       |
+----------+------------+---------+---------+---------+------+-----+-----+--------+
|Categories|Description |_1       |TEXT     |0        |65536 |10   |0    |1       |

   ddfet _1,~ 'select * from Categories where CategoryID < 4' ddsel ch
+-+-----------+----------------------------------------------------------+
|1|Beverages  |Soft drinks, coffees, teas, beers, and ales               |
+-+-----------+----------------------------------------------------------+
|2|Condiments |Sweet and savory sauces, relishes, spreads, and seasonings|
+-+-----------+----------------------------------------------------------+
|3|Confections|Desserts, candies, and sweet breads                       |

   dddis ch
0

See Also

• iODBC for Mac OS X
• SQLite ODBC Driver

Contributed by Oleg Kobchenko