Re: Multi Tiered ODBC

Let's set the record straight once and for all. If you want to know in detail what single and multi (two or tree) tier drivers mean in connection with ODBC you read the Microsoft white paper available at http://www.microsoft.com/odbc/wpapers/odbcperf.htm Here is an excerpt that gives you the essence: "There are two basic types of drivers in ODBC: single tier and multiple tier. With single-tier drivers, the driver processes both ODBC calls and SQL statements. In this case, the driver performs part of the data source functionality. With multiple-tier drivers, the driver processes ODBC calls and passes SQL statements to the data source." This have little relation to what is meant by single and multi tier in client/server development tools. Microsoft is however entirely correct in describing ODBC drivers as single or multitier as above. It is a feature of human languages that one word can have different meanings in different contecsts. Noone can complain about that. So both Weiqi Gao and Mischa Sandberg are partly correct, but do refer to the white paper if you want more details. It's no use discussing it in detail here. What Microsoft does not describe very well in the document referred to above is what I prefer to call server based ODBC drivers. OpenLink and SCO (Visionware) are examples of such products, and from Sandberg's description SimbaExpress as well. In these implementation there is only a common simple ODBC driver on each workstation (PC). These communicates with a server (usually located on the same machine as the database engine, although that is not a requirement) that does all translation of SQL from ODBC sql to the sql dialect of the particular RDBMS you connect to. If you should want to connect to multiple RDBMS servers from different vendors you still install only one common product on each workstation, while the connection to each RDBMS is handled on the server an can be installed one time only. This arcitecture makes up for both a much simpler installation and maintenance, and faster execution and can give you several other advantages. These types of drivers have now evolved in functionality and performance to where you generally get both better responce times and at least equal functionality as if you use a proprietary API from the database vendor (SQL*Net from Oracle or I-Net from Informix). When Weigi Gao writes: :>Targetting multiple RDBMSs with ODBC sounds like a noble thing to do, but :>doesn't offer any advantage to the corporate developers who have endured a :>painful process of selecting their RDBMS very carefully. he have missed the point to a larger extent than Mischa Sandberg sais. Not only does ODBC give simple access to many kinds of legacy data sources. In many cases it's a better alternative for new developments on newly selected RDBMSs even if this is the only database you want to connect to. Often it's so much better that the RDBMS vendors proprietary API isn't even an option. As an example I would hate to be Xerox and have to install I-Net or SQL*Net on over 20.000 PCs. With proper ODBC drivers it becomes simpler, I would expect better performance, and you get increased functionallity. (Ref. http://www.dbms.mfi.com/9604pnet.html for info on this.) It's sad that there are so many misunderstandings about ODBC and general connectivity from MS Windows based applications to relational databases. There are still significant problems with ODBC (security is a main consern), but proprietary API's is not the answer. Java, JDBC and releated technologies may be a still better alternative for many as these now become viable for large and small systems alike. mischa_sandberg@mindlink.bc.ca wrote: :"Weiqi Gao" <weiqigao@crl.com> wrote: :>> In article <327DE5D2.57DD@businessobjects.com>, :>> wmartin@businessobjects.com wrote: :>> :>> > I would love to hear some feedback from anyone who has had experience :>> > using a multi tiered ODBC solution ... :>Please be aware that the word "multi-tier" means something quite different :>when applied to ODBC. A two-tiered ODBC corresponds to a flat file :>database. A three-tiered ODBC corresponds to the old two-tiered :>client/server (application<->ODBC driver<->RDBMS). And in general, :>whenever the ODBC documentation mentions the word "tier", no "three-tiered :>client/server application" meaning is intended. :I beg to differ. A simple ODBC driver on top of a flat file system is :referred to as a single-tier driver by driver developers I know. A :two-tier ODBC system is an ODBC driver that connects to a server, :usually via a protocol proprietary to the server vendor (e.g. a Sybase :ODBC driver based on CTLIB). A three-tier "driver" is something like :SimbaExpress, where a lightweight client driver connects to an ODBC :SERVER on another platform; that ODBC server (via the driver manager) :loads ODBC drivers which typically are two-tier. :Why go to all that trouble? It's a lot easier to install a single :all-purpose client driver on a hundred networked workstations, and :administer the heavyweight installs of Oracle, Sybase and :what-have-you drivers on a single platform, than to install SQL*Net :and Sybase CTLIB .DLL's everywhere :. :>Targetting multiple RDBMSs with ODBC sounds like a noble thing to do, but :>doesn't offer any advantage to the corporate developers who have endured a :>painful process of selecting their RDBMS very carefully. :ODBC lets you plug new applications (Crystal Reports, Access, :Impromptu) into old databases. It lets you combine different data :sources that "just grew that way" in different environments. For :example, I can join local Access tables to Sybase tables on my servers :and C-ISAM files from my legacy SFAS system, with barely a thought to :where each table is. I agree, nobody keeps switching databases for a :single system. But you inherit a lot of stuff you'd rather not get too :intimate with, and can be thankful when you can pick and choose :Windows tools that all talk the same language even if the underlying :data is baroque ("Quick, put a GUI on our VSAM files") :Engineers believe that the equations approximate reality. :Physicists believe that reality approximates the equations. :Mathematicians never make the connection. And the intelligent philosophers know that reality is the only final arbiter. (The sad thing is that there aren't all that many intelligent philosophers.) Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org