"ODBCOnUnix" . "40179ea37985d54b46cf51de0ea8eee9" . . "2016-09-14T12:28:33.392394"^^ . "---+ ODBC: The Unix Story\n\nTim Haynes, OpenLink Software, Tue Jun 3 09:09:38 2003\n\n\n%TOC%\n\n\n---++ What is ODBC? \n\nODBC is a system for providing an abstraction on top of database access,\ntransferring SQL and meta-data queries to the database and conveying the\nresults back.\n\nFrom the end-user perspective, one application can be used quickly, easily\nand uniformly against many different backend sources of data.\n\nFrom the programmer's perspective, ODBC is a C-based API that saves time\nand effort writing applications. Instead of writing using native access\nmethods for Oracle, Sybase, PostgreSQL etc, you write once against ODBC and\npoint the data-source wherever you desire at run-time.\n\nFrom the system administrator's perspective, it's just another small\npackage to maintain.\n\n\n\n---++ A Walk-through of ODBC Components\n\nStarting at the top, an ODBC application needs to invoke the Driver Manager\ncomponent - normally this involves being linked, either dynamically or\nstatically, against libodbc.so or libiodbc.so.\n\nBecause of the generic nature of ODBC, an\nODBC application should not be written with any particular RDBMS product in\nmind for the backend.\n\nThe Driver Manager handles all the calls the application makes - such as\n\"what data-sources do you have?\", \"connect to ThatDataSource\", \"execute the\nquery `select * from ThatTable;'\", and so on. For this reason, the\napplication should be linked against libiodbc.so or libodbc.so, in order to\nprovide all the SQLsomething() functions.\n\nThe most useful ODBC calls the application will make involve using a\nspecific driver to connect to a specifc backend database. The Driver\nManager's job is to load the appropriate driver and pass the relevant ODBC\ncalls straight to it.\n\nThe ODBC driver itself makes a connection to the backend database; this is\nnormally possible through either a Unix socket or using TCP/IP, optionally\nover a remote network to a different server. In the case of OpenLink\nMulti-tier drivers, this driver can involve multiple components - a generic\nclient, a request broker on the server and a database agent to handle the\nconnection, adding extra levels of security and better performance.\n\nIt is the driver's responsibility to pass queries on to the driver in a\nsyntactically valid form (for the level of SQL compliance the driver claims\nto implement) and maintain integrity when conveying the results back. It\nalso adds value to the connection, however, in that much meta-data about\nthe driver and the database can be used dynamically. Also, transaction\nsupport is standardized and can be coupled with reusable (prepared) queries\nto increase performance significantly.\n\nFor example, suppose you wished to execute the SQL statement\n\n\ninsert into mytable values (99, 'Tim', '01-03-2003');\n\n\nrepeatedly, changing the values as you go. While it is certainly possible\nto generate varying statement strings in your application and then execute\nthem, the database must compile and run the statement in entirety every\ntime, taking locking considerations on the row or even whole tables into\naccount. This is highly inefficient.\n\nThe alternative is to prepare a query of the form:\n\n\ninsert into mytable values (?, ?, ?);\n\n\nthen for each row intended, bind parameters to each of the place-holders,\nand repeat just the execution phase. If several rows are batched-up at a\ntime and a transaction put around them, the net time taken can be reduced\nby an order of magnitude.\n\nIt is the ODBC driver's job to handle the binding of parameters here, and\nto control the transaction/isolation levels, etc.\n\nNow you also have extra flexibility as an application writer; the above\nsimple statement had an obvious ambiguity in the date field. Typically,\ndatabases vary in both the format of dates supported (and preferred for a\nparticular database instance), and in the size and precision of timestamps.\nODBC works around all this by providing the application author a metadata\ncall to establish what the native name for a given type of field is - DATE\nor TIMESTAMP or DATETIME, whatever - and three unified escape-syntaxes, for\ndate, time, and timestamp. The driver will then transform the escaped\nsyntax into one the database is configured to understand with no ambiguity.\n\n\n---++ Installing ODBC \n\n\n---+++ Driver Manager implementations\n\nUnlike Windows, Unix has historically never come with a system-wide ODBC\ninstallation, therefore there is greater scope for setting everything up\nthe way you wish.\n\nAlso on Unix, there are two main-stream implementations of ODBC: UnixODBC\nand iODBC. The former comes with a QT-based implementation of a GUI, while\niODBC provides a GTK+-based administrator.\n\nThe iODBC package is available in several formats - source tarball, source\nRPM, and a choice of binary components. Installing the binary packages is a\nsimple exercise - for example,\n\n\n\nsudo rpm -Uhv libiodbc-3.0.6-2.i386-glibc21.rpm\n\n\nwill install the iODBC runtime library.\n\nThere follows an alternative, walking through an unpacking, configure and\nbuild of the complete sources from tarball:\n\n\n\nzsh, purple 6:10PM C/ % tar xvpfz libiodbc-3.0.6.tar.gz\nlibiodbc-3.0.6/\nlibiodbc-3.0.6/admin/\nlibiodbc-3.0.6/admin/Makefile.am\nlibiodbc-3.0.6/admin/Makefile.in\n[snip]\nzsh, purple 6:10PM C/ % cd libiodbc-3.0.6\nzsh, purple 6:10PM libiodbc-3.0.6/ % ls\n\nAUTHORS LICENSE NEWS bin/ etc/ mac/\nCOPYING LICENSE.BSD README configure* include/ samples/\n\n\nConfiguration, starting with a check of interesting options specific to iODBC:\n\n\nzsh, purple 6:10PM libiodbc-3.0.6/ % ./configure --help\n\nDefaults for the options are specified in brackets.\n\nConfiguration:\n-h, --help display this help and exit\n--help=short display options specific to this package\n--help=recursive display the short help of all the included packages\n[snip]\n--enable-fast-install=PKGS optimize for fast installation default=yes\n--disable-libtool-lock avoid locking (might break parallel builds)\n--enable-gui build GUI applications (default),\n--disable-gui build GUI applications\n--disable-gtktest Do not try to compile and run a test GTK program\n--enable-odbc3 build ODBC 3.x compatible driver manager (default)\n--disable-odbc3 build ODBC 2.x compatible driver manager\n[snip]\n\nzsh, purple 6:10PM libiodbc-3.0.6/ % ./configure --prefix=/usr/local/stow/iodbc --with-gtk --enable-gui && nice make\n\nchecking for a BSD-compatible install... /bin/install -c\nchecking whether build environment is sane... yes\nchecking for gawk... gawk\n...\n\n\n\n\nInstallation, starting with becoming root by sudo, then stow-ing the\npackage tidily into /usr/local/:\n\n\n\nzsh, purple 6:15PM libiodbc-3.0.6/ % sudo -s\nPassword:\nzsh, purple 6:17PM libiodbc-3.0.6/ # make install\n\nMaking install in admin\nmake[1]: Entering directory `/home/tim/C/libiodbc-3.0.6/admin'\n[snip]\nmake[2]: Nothing to be done for `install-exec-am'.\nmake[2]: Nothing to be done for `install-data-am'.\nmake[2]: Leaving directory `/home/tim/C/libiodbc-3.0.6'\nmake[1]: Leaving directory `/home/tim/C/libiodbc-3.0.6'\n\nzsh, purple 6:18PM libiodbc-3.0.6/ # cd /usr/local/stow/\n\nzsh, purple 6:18PM stow/ # chmod -R og=rX iodbc/\nzsh, purple 6:18PM stow/ # stow iodbc/\nzsh, purple 6:18PM stow/ # ^D\nzsh, purple 6:18PM libiodbc-3.0.6/ % ls /usr/local/stow/iodbc/bin/\niodbc-config* iodbcadm-gtk* odbctest*\n\n\n\n---++ Configuring DSNs \n\nThe graphical (GTK+-based) configuration screen makes setting up your\ndata-sources (DSNs) quite intuitive, especially if you're used to the setup\nscreens on Windows:\n\nAn example of adding a new datasource follows:\n\n\n\n\n---++ The real files \n\nThe iODBC library searches for its DSN through a set few files:\n \n * $ODBCINI - the environment variable, if set\n * ~/.odbc.ini - in your home-directory, if it exists\n * /etc/odbc.ini - a system-wide default\n \nThe format of this file is very simple; there are 3 sections, one for ODBC\nitself (setting up tracing), one for a list of DSNs, and one for the\ndefinitions of those DSNs, thus:\n\n\n\n[ODBC]\nDebug = 1\nTrace = 0\nDebugFile = /home/tim/temp/odbc-debugfile.log\nTraceFile = /home/tim/temp/odbc-tracefile.log\nTraceAutoStop = 1\n\n\n\n[ODBC Data Sources]\nVirtuoso30 = OpenLink Virtuoso 3.0\n\n\n\n[Virtuoso30]\nDescription = Virtuoso 3.0\nDriver = /opt/opl/virtuoso-o12/lib/virtodbc.so\nAddress = localhost:1111\nUserName = dba\nUser = dba\n\n\nEach DSN configured has an entry in the `ODBC Data Sources' section, and a\ncomplete definition in a paragraph section of its own.\n\nThere is also an ODBCINSTINI file; this contains descriptions of the ODBC\ndrivers available.\n\n\n[ODBC Drivers]\nOpenLink Generic = installed\n\n[OpenLink Generic]\nDriver = /opt/openlink/odbcsdk/lib/oplodbc.so\n\n\n\n---++ Making a test connection \n\nTo test that a DSN connects correctly, you can use the supplied `odbctest' utility.\n\n\n\nzsh, purple 3:58PM bin/ % ls\niodbc-config* iodbcadm-gtk* odbctest*\nzsh, purple 3:58PM bin/ % echo $ODBCINI \n/home/tim/.odbc.ini\nzsh, purple 3:58PM bin/ % ./odbctest \niODBC Demonstration program\nThis program shows an interactive SQL processor\n\nEnter ODBC connect string (? shows list): \n\nProgress9.x(solaris) | OpenLink Generic ODBC Driver \nProgress9.x(solaris) | OpenLink Generic ODBC Driver \npgsqlPurple | PostgreSQL native driver \npgsqlPurpleOpl | PostgreSQL using OpenLink driver \npgsqlPurpleVirtDemo | Virtuoso database driver \nSQLServer | OpenLink Generic ODBC Driver \n\nEnter ODBC connect string (? shows list): DSN=pgsqlPurpleOpl\nDriver: 04.50.0801 OpenLink Generic ODBC Driver (oplodbc.so)\n\nSQL>select count(*) from timtest;\n\ncount \n-----------\n100 \n\nresult set 1 returned 1 rows.\n\n\nAny of the DSN attributes can be overridden in the connect-string, which takes the form\n\n\nDSN=dsn_name[;attr=value]*\n\n\nThe attributes themselves depend on the database driver behind the DSN;\nnormally they control the username (where the attribute could be\ncalled`userid' or `uid') and password (if specified) used to connect to the\ndatabase, some form of server hostname specification (`host=' or\n`server='), and a means to identify a database instance on that server\n(`database='). A driver may also have custom attributes, such as\nFetchBufferSize, Port, etc.\n\n\n\n---++ Known working ODBC applications \n\nThe following environments are where ODBC is commonly to be found:\n\nPHP scripts, typically on a webserver: PHP itself contains no intrinsic\nnative database-abstraction layer, meaning code has to be written against\nOracle, MySQL, PostgreSQL, .. etc. ODBC to the rescue - PHP does support\nthis as a backend.\n\nPerl - typically found as a CGI script or using mod_perl, on a webserver:\nPerl does have the DBI for an abstraction layer, which makes writing\nscripts against a database uniform, and the syntax gels well with the\nlanguage. ODBC is one possible backend here.\n\nRuby - another, object-orientated, scripting language with its own\nimplementation of the DBI standard.\n\nOpen Office - this has the ability to interact with an ODBC data-source for\nits own database functionality; notably its address-book components are\njust another database.\n\n\n\n---++ An example of the API in action \n\n\n---+++ Handling multiple versions\n\nOver the years, the ODBC API itself has changed from version 1.0, through\n2.5 up to 3.0 and latterly 3.5. The main changes from 2.5 to 3.5 have been\nthe introduction of SQLSetStmtAttr() and related functions, instead of the\nolder SQLSetStmtOption() and relatives; also, in 3.0 and above, support for\nConnection Pooling was added, cursor support was enhanced, etc.\n\nFortunately, it is the job of the ODBC Driver Manager component to ensure\nthat function-calls made by the application in a given version of ODBC are\nmapped to the same version that the driver understands. It is also possible\nfor the application to enquire on a more fine-grained basis, to determine\nwhich features are available in a driver, on a given connection and on a\nstatement-handle, primarily through the SQLGetInfo() function call.\n\n\n---+++ Visualising ODBC call sequences\n\nThe OpenLink `odbctest.c' application is a simple example C application\nwritten using the ODBC API. As an overview, the flow of control for\nconnection, executing a simple statement and retrieving data is as follows:\n\nThis diagram shows the following salient features:\n \n * Functions from the ODBC API appear in the left element of each box.\n * Arguments passed between functions are represented within each function's box. For example, the SQLConnect() function returns a `connection handle' entity which is then passed to subsequent function-calls.\n * The SQLGetInfo() function-calls (three of them) return data about the nature of the connection, using the handle to do so: one for option 23 (cursor commit behaviour), one for option 24 (cursor behaviour during rollback operations) and 17 (SQL DBMS name). This meta-information is passed back to the application for use in further flow-control decisions.\n * The SQLPrepare() function is used to tell the database to prepare a query for execution on a given statement handle. This statement may be re-executed multiple times with different parameters each time.\n * After each execution of the prepared query, the number and types of the columnsin the result-set are determined, using the SQLNumResultCols() and SQLDescribeCol() functions. Then, while there are still rows remaining, SQLFetch() is called to return the next row, and SQLGetData() called to retrieve all the fields in the given row.\n * After all the rows have been retrieved, a call to SQLMoreResults() is made to ascertain whether the query returned multiple result-sets or not. If so, the SQLFetch() + SQLGetData() loops may be repeated.\n * Finally, the statement handle is retired with a call to SQLFreeStmt(), and the connection-handle destroyed with a call to SQLDisconnect().\n \nThere is scope for further enhancement as follows:\n \n * Attributes may be set on the current connection, such as enabling or disabling auto-commit (by default, in ODBC, this is enabled on a per-statement basis), or setting the whole connection read-only.\n * Multiple paramaters may be bound to the query, and their datatypes determined in real-time if necessary, by using SQLBindParameter() calls anywhere in the loops between SQLAllocHandle() and SQLExecute().\n * Some databases only support binding parameters after the statement has been prepared; in order to use them beforehand, the application should make the relevant SQLGetInfo() calls in advance.\n \n\n\n---++ ODBC Application Considerations \n\n\n---+++ Scrollable Cursors \n\nAny real-time application has to be sensitive to changes in the data at the\nbackend - the validity of any given query's resultset could potentially be\nquite short-lived.\n\nConsequentially, ODBC provides various models of cursors, some implemented\nin the Driver Manager, others left for individual drivers to support.\n\nThe models of cursors available are:\n\n * Static: this is a snapshot of a table, meaning it is insensitive to changes.\n * Forward-only: this is sensitive to changes that occur prior to subsequent records being read; however, changes the previously read records are not affected.\n * Keyset: a key set derived from unique record identifiers is used to fetch records into rowsets, so changes to any existing row will be observed, but this model is insensitive to record inserts and deletions; the keyset is built at cursor-open time, and it is larger than the row set which does the scrolling.\n * Dynamic: the keysets are rebuilt for each row set, so there is the additional sensitivity to inserts and deletions which is missing from the keyset model.\n * Mixed: the keyset is of configurable size such that keysets are rebuilt prior to rowset reaching their end points; this delivers more sensitivity than the keyset model, and less overhead than the dynamic model\n \n---+++ Transaction Isolation levels \n\nODBC provides 4 levels of isolation for transactions. The following\ndefinitions are used:\n \n * Dirty Reads: this occurs when a transaction reads data that has not yet been committed by another. It causes problems when the second transaction is rolled-back instead of committed.\n * Nonrepeatable Reads: a nonrepeatable read occurs when a transaction reads the same row twice but gets different data each time. For example, suppose transaction 1 reads a row. Transaction 2 updates or deletes that row and commits the update or delete. If transaction 1 rereads the row, it retrieves different row values or discovers that the row has been deleted.\n * Phantoms: this is when a row matches the search criteria but is not initially seen. For example, suppose transaction 1 reads a set of rows that satisfy some search criteria. Transaction 2 generates a new row (through either an update or an insert) that matches the search criteria for transaction 1. If transaction 1 reexecutes the statement that reads the rows, it gets a different set of rows.\n\nThe defined isolation levels are:\n \n * Read uncommitted: this combines all 3 of the above.\n * Read committed: this combines non-repeatable reads and phantoms.\n * Repeatable read: (same as above)\n * Serializable: none of the above options.\n \n---+++ Escape Syntaxes \n\nODBC provides unified syntaxes for several SQL expressions that typically\nvary between vendors' implementations: notably,\n \n * Date and Timestamps: historically, a plethora of date and/or time formats has abounded, normally causing much confusion. In ODBC, these things can all be represented in standard format across all RDBMS backend types, as appropriate: * Dates: {d 'yyyy-mm-dd'}\n * Times: {t 'hh:MM[:ss.[ffff]]'} (the `f's are optional fraction-of-a-second parts)\n * Timestamps: {ts 'yyyy-mm-dd hh:MM[:ss[.ffff]]'}\n \n * Scalar Functions: these encompass string, numeric, date+time, system and conversion functions. For example: {fn DAYOFMONTH(AdateField)} or {fn lcase(aStringField)}.\n * Stored Procedures: normally, there is some kind of `exec' syntax with scope for input- and/or output-paramaters. In ODBC, we use {call sp_name (?,?)} and the driver converts it to the native syntax for the current connection.\n \n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n" . . "2016-09-14T12:28:33.392394"^^ . . . . . . . . . . . . . . . "ODBCOnUnix" . . . . . . . . . . . . . . . . . "ODBCOnUnix" . . "2016-09-14T12:28:33Z" . "2016-09-14T12:28:33Z" .