. . . "IODBCRubyHOWTO" . "2018-10-16T21:15:09.665093"^^ . . . . . . "IODBCRubyHOWTO" . . . "2016-09-14T12:28:33.727184"^^ . . . . . . "2018-10-16T21:15:09Z" . . . . "---+iODBC and Ruby\r\n\r\n%TOC%\r\n\r\n---++Abstract\r\n\r\nThis HOWTO is intended to walk you through the process of installing and \r\nconfiguring iODBC, Ruby and the Ruby/ODBC bridge module with a goal of \r\nwriting and executing simple scripts to effect a database connection. It is \r\ncurrently maintained by Tim Haynes of [[http://www.openlinksw.com/][OpenLink Software]],\r\n<[[mailto:iodbc@openlinksw.com][iodbc@openlinksw.com]]>, that is iodbc at openlinksw.com. \r\n\r\nWe assume you have some familiarity with using either Terminal on Apple \r\nMac OS X or the shell on a GNU/Linux platform such as Ubuntu, Debian, \r\nRedHat, or Fedora Core, etc.\r\n\r\n---++Required Components\r\n\r\n ---+++iODBC Driver Manager\r\n \r\n We start by installing the iODBC Driver Manager.\r\n \r\n ---++++Linux\r\n \r\n Some Linux distributions (Debian, Ubuntu, Gentoo) have their own packages \r\n for iODBC, so you should only need to type a command such as one of\r\n\r\n * sudo apt-get install libiodbc2 iodbc\r\n * sudo emerge libiodbc\r\n * sudo yum install iodbc\r\n \r\n to install it, and possibly some dependencies (GTK+ libraries for the \r\n adminstrator utility), automatically.\r\n \r\n ---++++ Mac OS X\r\n \r\n For Mac OS X users, we provide a DMG installer on our [[Downloads][Downloads]]\r\n page. This will install the iODBC Framework, development environment and \r\n graphical configuration utility.\r\n \r\n Note that this supersedes the version of iODBC supplied by Apple with Mac OS X,\r\n as it resolves two bugs:\r\n * on 64-bit machines, ruby would be built in 64-bit mode by default but \r\n the system libiodbc is only 32-bit, making building the ruby-odbc bridge\r\n impossible; \r\n * the handling of SQLError() in ruby-odbc. \r\n \r\n ---++++Other Unix operating systems\r\n \r\n On other unix operating systems, you need to compile iODBC from source. This\r\n is generally easy:\r\n\r\n * [[Downloads][Download]] the iODBC source tarball\r\n * unpack it using a command such as gzip -cd libiodbc-3.52.7.tar.gz| tar xvf -\r\n * ./configure --prefix=/usr/local/iODBC/\r\n * make\r\n * become root and run make install, e.g., su root -c 'make install'\r\n \r\n Of course you can also build the same way on linux; the ./configure\r\n script takes a few options you might want to vary, notably --with-prefix=\r\n for where the files will be installed and --enable-gui for whether\r\n to build the GTK+ Administrator or not. \r\n \r\n If you wish to build from source on Mac OS X, after unpacking the archive, run\r\n * cd mac\r\n * make\r\n * sudo make install\r\n\r\nThis will build the Framework version and create a subdirectory under \r\n/usr/local/iODBC/ so that other applications (eg PHP, Perl, Ruby) can link\r\nagainst it without having to modify them to look for frameworks.\r\n\r\n---++++Configuring and Testing iODBC\r\n\r\nIt is wise to test that iODBC works directly before trying to layer any Ruby \r\nODBC module on top. Either:\r\n\r\n * use the graphical Administrator application (iodbcadm-gtk on linux/unix or\r\n iODBC Administrator.app (/Applications/iODBC/) on Mac OS X) to register an\r\n ODBC driver, add a System or User DSN using the driver, and test it, or\r\n * edit your own odbc.ini (/etc/odbc.ini, ~/.odbc.ini, or $ODBCINI environment variable) \r\n to contain something similar to the following:\r\n \r\n\r\n[ODBC Data Sources]\r\npgdata = Native PostgreSQL ODBC driver\r\n\r\n[pgdata]\r\nDriver=/usr/lib/odbc/psqlodbcw.so\r\nHost = data\r\nServer = data\r\nServerName = data\r\nDatabase = me\r\nUserName = me\r\nUID = me\r\nPort = 5432\r\n\r\n \r\nFrom the commandline you should be able to run iodbctest DSN=pgdata and it should attempt to connect:\r\n \r\n\r\nzsh, ubuntu libiodbc-3.52.7/ % iodbctest DSN=pgdata \r\niODBC Demonstration program\r\nThis program shows an interactive SQL processor\r\nDriver Manager: 03.52.0709.0909\r\nDriver: 08.03.0200 (psqlodbcw.so)\r\n\r\nSQL>\r\n \r\n\r\nThe SQL> prompt there shows iodbctest has connected and is now\r\nawaiting a SQL command as input; additionally it understands the command\r\n`tables' which lists tables visible in the current database. \r\n\r\nNote the `DSN=pgdata' parameter: this is part of an ODBC connection string,\r\nnot just a data-source name. As such it takes the form\r\n\r\n\r\nDSN=somedsn;[param=value[;]]*\r\n\r\n\r\nwhere the parameters are specific to the ODBC driver being used. In this case,\r\nthe PostgreSQL native ODBC driver requires Host, Database and Port, above. \r\nFor MySQL and OpenLink drivers, the parameters vary.\r\n\r\nThe [[FAQ][iODBC FAQ]] contains a section of common error messages in the \r\nevent of something going wrong at this stage.\r\n\r\n---+++Ruby\r\n\r\nRuby is an open-source, interpreted, dynamic, object-oriented scripting \r\nlanguage in the same space as Perl, Python on the desktop and with the famous\r\nRuby-on-Rails engine in web-space (to compare with PHP).\r\n\r\nThere are two ways in which Ruby interfaces with ODBC: a ruby-odbc binding\r\nmodule which presents the ODBC API at the Ruby layer with little abstraction,\r\nmaintained by Christian Werner; and a DBD::ODBC module that provides a driver\r\ncompatible with DBI (a database-interaction specification familiar to Perl\r\nusers, now implemented in Ruby). The DBD::ODBC driver depends upon the \r\nruby-odbc binding, so we continue building the packages in hierarchical order.\r\n\r\n---++++Linux\r\n\r\nMost Linux distributions have packages for \r\n[[http://www.ruby-lang.org/en/][Ruby]] already. Remember if your distribution\r\ndistinguishes between runtime and development versions packages, you will need\r\nto install the development libraries in order to compile other packages from \r\nsource against them.\r\n * Debian/Ubuntu: sudo apt-get install ruby ruby-dev\r\n * Gentoo: sudo emerge -av ruby\r\n\r\n---++++Mac OS X\r\n\r\nMac OS X, from Tiger onwards, comes with Ruby 1.8 by default. No further\r\ninstallation is required, unless you really want to build it from source\r\nyourself.\r\n\r\n---++++From Source\r\n\r\nFor other unix environments, it might be required to build Ruby from source.\r\nThat process is not documented here except to say that it follows a similar \r\npath to iODBC from source: download, unpack, run ./configure, \r\nmake and sudo make install.\r\n\r\n---+++Ruby/ODBC Binding\r\n\r\nChristian Werner maintains the \r\n[[http://ch-werner.de/rubyodbc/][ruby-odbc binding module]].\r\n\r\n---++++Linux\r\n\r\nSome GNU/Linux distributions (debian and ubuntu in particular) have already\r\npackaged the ruby-odbc binding, so a simple\r\n\r\n\r\nzsh, ubuntu ruby/ % sudo apt-get install libodbc-ruby1.8 \r\n\r\n\r\nis all that's required.\r\n\r\nHowever, for better handling of SQLError(), we recommend you use the latest \r\niODBC (3.52.7) and ruby-odbc module (0.9997) so you might still need to build\r\nfrom source, as follows.\r\n\r\n---++++ Mac OS X, other Unix platforms: build from source \r\n\r\nThe following instructions apply equally to Linux, Mac OS X, and other Unix-like operating\r\nsystems, as we're building from source here.\r\n\r\nDownload this and unpack it as usual:\r\n\r\n\r\nzsh, pizza C/ % tar xvfz ruby-odbc-0.9997.tar.gz\r\n./ruby-odbc-0.9997/\r\n./ruby-odbc-0.9997/doc/\r\n./ruby-odbc-0.9997/doc/odbc.html\r\n./ruby-odbc-0.9997/test/\r\n...\r\nzsh, pizza C/ % cd ruby-odbc-0.9997 \r\nzsh, pizza ruby-odbc-0.9997/ % \r\n\r\n\r\nTo configure it, run\r\n\r\nzsh, pizza ruby-odbc-0.9997/ % ruby extconf.rb --with-odbc-dir=/usr/local/iODBC\r\nchecking for version.h... yes\r\nchecking for sql.h... yes\r\nchecking for sqlext.h... yes\r\nchecking for SQLTCHAR in sqltypes.h... yes\r\nchecking for SQLLEN in sqltypes.h... yes\r\nchecking for SQLULEN in sqltypes.h... yes\r\nchecking for odbcinst.h... yes\r\nchecking for SQLAllocConnect() in -liodbc... yes\r\n\r\n\r\n*Note* three things: \r\n * first, we tell it where to find the iODBC libraries using --with-odbc-dir \r\n (which works well with Mac OS X, where ordinarily libraries are provided \r\n using frameworks, so to avoid needing to use those, the iODBC installer \r\n package provides a /usr/local/iODBC/ directory with symbolic links into the\r\n Frameworks directory); \r\n * secondly, it must report successful checking using -liodbc, \r\n not -lodbc - unless you've symlinked the two libraries together;\r\n * thirdly, you need iODBC version 3.52.7 and ruby-odbc version 0.9997 to\r\n fix a bug in the handling of SQLError() \r\n\r\nWe continue to build and install it:\r\n\r\n\r\nzsh, pizza ruby-odbc-0.9997/ % make\r\n/usr/bin/gcc-4.0 -I. -I. -I/usr/local/ruby/1.8/powerpc-darwin8 -I. \\\r\n\t-DHAVE_VERSION_H -DHAVE_SQL_H -DHAVE_SQLEXT_H -DHAVE_TYPE_SQLTCHAR \\\r\n\t-DHAVE_TYPE_SQLLEN -DHAVE_TYPE_SQLULEN -DHAVE_ODBCINST_H \\ \r\n\t-DHAVE_SQLINSTALLERERROR -I/usr/local/iODBC/include -I/opt/local/include \\ \r\n\t-D_XOPEN_SOURCE -D_DARWIN_C_SOURCE -I/opt/local/include -fno-common \\\r\n\t-O2 -fno-common -pipe -fno-common \\\r\n\t-c init.c\r\n...\r\nzsh, pizza ruby-odbc-0.9997/ % sudo make install\r\nPassword:\r\n/usr/bin/install -m 0755 odbc.bundle /usr/local/ruby/site_ruby/1.8/powerpc-darwin8\r\n\r\n\r\nTo check that it built correctly, run one of the following commands to \r\nsee the shared library dependencies; there should be a mention of `iodbc' somewhere\r\nin the output:\r\n\r\nOn Mac OS X:\r\n\r\nzsh, pizza ruby-odbc-0.9997/ % otool -L /usr/local/ruby/site_ruby/1.8/powerpc-darwin8/odbc.bundle \r\n/usr/local/ruby/site_ruby/1.8/powerpc-darwin8/odbc.bundle:\r\n /usr/local/lib/libruby.dylib (compatibility version 1.8.0, current version 1.8.7)\r\n /usr/local/lib/libiodbcinst.1.dylib (compatibility version 2.0.0, current version 2.0.0)\r\n /usr/local/lib/libiodbc.1.dylib (compatibility version 2.0.0, current version 2.0.0)\r\n\r\n\r\nOn Linux/Unix:\r\n\r\n\r\nzsh, ubuntu ruby-odbc-0.9997/ % ldd /usr/lib/ruby/site_ruby/1.8/i486-linux/odbc.so \r\n[...]\r\n libc.so.6 => /lib/libc.so.6 (0xb7d0e000)\r\n libiodbc.so.2 => /usr/local/lib/libiodbc.so.2 (0xb7cc6000)\r\n libiodbcinst.so.2 => /usr/local/lib/libiodbcinst.so.2 (0xb7cb3000)\r\n /lib/ld-linux.so.2 (0xb7fd2000)\r\n\r\n \r\n\r\n---++++Testing\r\n\r\nHaving installed the ruby-odbc binding module, you can write a simple ruby script\r\nto test it such as the attached which makes a connection and probes some \r\nmetadata available through the ODBC connection:\r\n\r\n * odbc-metadata.rb\r\n\r\n\r\nzsh, ubuntu ruby/ % ./odbc-metadata.rb pgdata \"\" \"\"\r\nConnecting to [pgdata, , ]\r\nConnected: true\r\n\r\nData-types:\r\n\r\nType integer:\r\nNative: int4\r\n TYPE_NAME: int4\r\n DATA_TYPE: 4\r\n PRECISION: 10\r\n LITERAL_PREFIX: \r\n LITERAL_SUFFIX: \r\n CREATE_PARAMS: \r\n NULLABLE: 1\r\n CASE_SENSITIVE: 0\r\n SEARCHABLE: 2\r\n UNSIGNED_ATTRIBUTE: 0\r\n MONEY: 0\r\n AUTO_INCREMENT: 0\r\n LOCAL_TYPE_NAME: \r\n MINIMUM_SCALE: 0\r\n MAXIMUM_SCALE: 0\r\n SQL_DATA_TYPE: 4\r\n SQL_DATETIME_SUB: \r\n NUM_PREC_RADIX: 10\r\n INTERVAL_PRECISION: 0\r\n...\r\n\r\nTrying type SQL_CURSOR_ROLLBACK_BEHAVIOR (String): ret=[2]\r\n\r\nTrying type SQL_DYNAMIC_CURSOR_ATTRIBUTES1 (String): ret=[0]\r\n\r\nTrying type SQL_KEYWORDS (String): ret=[]\r\n\r\nTrying type SQL_LIKE_ESCAPE_CLAUSE (String): ret=[N]\r\n\r\nTrying type SQL_COLUMN_ALIAS (String): ret=[Y]\r\n\r\nTrying type SQL_ORDER_BY_COLUMNS_IN_SELECT (String): ret=[N]\r\n\r\n\r\n\r\n---+++Ruby/DBI DBD::ODBC Driver\r\n\r\nThe second method to connect Ruby to ODBC is using the DBI interface; this \r\nprovides a higher-level interface, consisting of an controlling module (DBI)\r\nthat invokes drivers for specific databases (DBD::<something> modules)\r\n as required, one of which is DBD::ODBC.\r\n\r\n---++++Cross-platform: Ruby GEM\r\n\r\nRuby has its own packaging system, known as rubygems, which includes the dbi \r\nmodule. \r\n\r\n\r\nzsh, pizza C/ % sudo gem install dbi dbd-odbc --remote\r\nPassword:\r\nSuccessfully installed dbi-0.4.3\r\nSuccessfully installed dbd-odbc-0.2.5\r\n2 gems installed\r\nInstalling ri documentation for dbi-0.4.3...\r\nInstalling ri documentation for dbd-odbc-0.2.5...\r\nInstalling RDoc documentation for dbi-0.4.3...\r\nInstalling RDoc documentation for dbd-odbc-0.2.5...\r\n\r\n\r\nIf you are using gems for maintaining ruby packages, no further installation\r\nis required after this.\r\n \r\n---++++Linux Packages\r\n\r\nAs before, some GNU/Linux distributions (Debian and Ubuntu) already have packages\r\nfor Ruby's DBI and DBD modules, so a simple\r\n\r\n\r\nsudo apt-get install libdbd-odbc-ruby1.8 \r\n\r\n\r\nsuffices, and will include the DBI module as a dependency too.\r\n\r\n\r\n---++++ Mac OS X and other Unix-like systems: building from source\r\n\r\nOn other platforms, if not using the gems above, you have the option to \r\nbuild DBI & DBD::ODBC from source.\r\n\r\n---+++++DBI\r\n\r\nDownload the ruby-dbi module from [[http://ruby-dbi.rubyforge.org/][ruby-forge]]\r\nand unpack it:\r\n\r\n\r\nzsh, ubuntu C/ % tar xvfz dbi-0.4.3.tar.gz\r\ndbi-0.4.3/\r\ndbi-0.4.3/build/\r\ndbi-0.4.3/build/rake_task_lib.rb\r\ndbi-0.4.3/build/Rakefile.dbi.rb\r\ndbi-0.4.3/Rakefile\r\n...\r\nzsh, ubuntu C/ % cd dbi-0.4.3 \r\nzsh, ubuntu dbi-0.4.3/ % ls\r\nbin/ ChangeLog lib/ Rakefile setup.rb\r\nbuild/ examples/ LICENSE README test/\r\n\r\n\r\nThen run the following 3 commands to build and install it:\r\n\r\n * ruby setup.rb config\r\n * ruby setup.rb setup\r\n * sudo ruby setup.rb install\r\n\r\n\r\nzsh, ubuntu dbi-0.4.3/ % ruby setup.rb config\r\n---> bin\r\n<--- bin\r\n---> lib\r\n---> lib/dbi\r\n---> lib/dbi/utils\r\n<--- lib/dbi/utils\r\n---> lib/dbi/handles\r\n<--- lib/dbi/handles\r\n---> lib/dbi/base_classes\r\n<--- lib/dbi/base_classes\r\n---> lib/dbi/sql\r\n<--- lib/dbi/sql\r\n<--- lib/dbi\r\n<--- lib\r\n\r\nzsh, ubuntu dbi-0.4.3/ % ruby setup.rb setup \r\n---> bin\r\nupdating shebang: test_broken_dbi\r\nupdating shebang: dbi\r\n<--- bin\r\n---> lib\r\n---> lib/dbi\r\n---> lib/dbi/utils\r\n<--- lib/dbi/utils\r\n---> lib/dbi/handles\r\n<--- lib/dbi/handles\r\n---> lib/dbi/base_classes\r\n<--- lib/dbi/base_classes\r\n---> lib/dbi/sql\r\n<--- lib/dbi/sql\r\n<--- lib/dbi\r\n<--- lib\r\n\r\nzsh, ubuntu dbi-0.4.3/ % sudo ruby setup.rb install\r\nrm -f InstalledFiles\r\n---> bin\r\nmkdir -p /usr/local/stow/ruby/bin\r\ninstall test_broken_dbi /usr/local/stow/ruby/bin/\r\ninstall dbi /usr/local/stow/ruby/bin/\r\n<--- bin\r\n---> lib\r\nmkdir -p /usr/local/stow/ruby/lib/ruby/site_ruby/1.9.1\r\ninstall dbi.rb /usr/local/stow/ruby/lib/ruby/site_ruby/1.9.1/\r\n...\r\n\r\n\r\n---+++++DBD::ODBC\r\n\r\nThe installation of ruby dbd-odbc follows a similar path to the dbi module.\r\n\r\nDownload and unpack dbd-odbc from [[http://rubyforge.org/frs/?group_id=234&release_id=36729][ruby-forge]]:\r\n\r\n\r\nzsh, ubuntu C/ % tar xvfz dbd-odbc-0.2.5.tar.gz \r\ndbd-odbc-0.2.5/\r\ndbd-odbc-0.2.5/ChangeLog\r\ndbd-odbc-0.2.5/README\r\ndbd-odbc-0.2.5/lib/\r\ndbd-odbc-0.2.5/lib/dbd/\r\ndbd-odbc-0.2.5/lib/dbd/odbc/\r\nzsh, ubuntu dbd-odbc-0.2.5/ % ls\r\nbuild/ ChangeLog lib/ LICENSE Rakefile README setup.rb test/\r\n\r\n\r\n\r\nThen run the following 3 commands to build and install it:\r\n\r\n * ruby setup.rb config\r\n * ruby setup.rb setup\r\n * sudo ruby setup.rb install\r\n \r\n\r\nzsh, ubuntu dbd-odbc-0.2.5/ % ruby setup.rb config\r\n---> lib\r\n---> lib/dbd\r\n---> lib/dbd/odbc\r\n<--- lib/dbd/odbc\r\n<--- lib/dbd\r\n<--- lib\r\n\r\nzsh, ubuntu dbd-odbc-0.2.5/ % ruby setup.rb setup \r\n---> lib\r\n---> lib/dbd\r\n---> lib/dbd/odbc\r\n<--- lib/dbd/odbc\r\n<--- lib/dbd\r\n<--- lib\r\n\r\nzsh, ubuntu dbd-odbc-0.2.5/ % sudo ruby setup.rb install\r\nrm -f InstalledFiles\r\n---> lib\r\nmkdir -p /usr/local/stow/ruby/lib/ruby/site_ruby/1.9.1\r\n---> lib/dbd\r\nmkdir -p /usr/local/stow/ruby/lib/ruby/site_ruby/1.9.1/dbd\r\ninstall ODBC.rb /usr/local/stow/ruby/lib/ruby/site_ruby/1.9.1/dbd\r\n---> lib/dbd/odbc\r\n... \r\n\r\n\r\n---+++++Testing Ruby, DBI, ODBC\r\n\r\nThe attached script is a quick demonstration of some of DBI's capabilities,\r\ngoing via the ODBC driver.\r\n\r\n * odbc-test-dbd.rb\r\n\r\n\r\nzsh, ubuntu C/ % ./odbc-test-dbd.rb pgdata \"\" \"\"\r\n[1, \"This is a varchar 10\"]\r\n[2, \"This is a varchar 40\"]\r\n[3, \"This is a varchar 90\"]\r\n[4, \"This is a varchar 160\"]\r\n[5, \"This is a varchar 250\"]\r\n[6, \"This is a varchar 360\"]\r\n[7, \"This is a varchar 490\"]\r\n[8, \"This is a varchar 640\"]\r\n[9, \"This is a varchar 810\"]\r\n[10, \"This is a varchar 1000\"]\r\n\r\n\r\nFirst the script connects; then it drops and recreates a table called `test';\r\nthen it inserts 10 rows and selects them back, before closing off the \r\nstatement and connection handles.\r\n\r\n " . . . . . . "IODBCRubyHOWTO" . "c6e48bf9cba178b75bd146557eac8009" . "2016-09-14T12:28:33Z" .