DB/Unix ODBC

From J Wiki
Jump to: navigation, search

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.

Wm yes check.png

  • 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

Wm yes check.png

  • 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


Contributed by Oleg Kobchenko