iOS Open Database Connectivity SDK |
 |
• Introduction
• Technical Specifications
• Product Download
• iPhone SQL Integration
• SQL Database Security
• Technical Specifications
• Product Download
• iPhone SQL Integration
• SQL Database Security
Mobile devices started out as web access devices but quickly evolved elegant apps such as Facebook that are rapidly displacing web pages like Facebook.com. But development for mobile devices produces a new set of issues we did not ever have to consider during the past decade of building web apps. These issues form the main case for ODBC on Apple iOS, including:
- A fundamental challenge to minimize power usage from the device's typically non-removable battery while maintaining excellent responsiveness on slow CPU chips in the 300Mhz-800Mhz range (or slightly faster in tablet devices with larger batteries); so first and foremost, we should use the efficient Objective C compiler and abandon the text-oriented JavaScript interpreter and create app binaries (not web scripts) wherever possible.
- Unlike a web page, app binaries must be downloaded ahead of time (via the AppStore), not upon each desired use. This has an important ramification in that any change made to an app's server has to maintain backwards compatibility with all prior releases of the app's binary or negative feedback will likely appear in the AppStore from prior consumers. Over time, we must make many changes for support of fixes, new iOS releases, database structure and additional functionality to remain competitive, but if we spent hundreds of hours writing our own server-side scripts to provide our database interface and each of those scripts maintained backwards compatibility with previous releases of our app binary to maintain customer satisfaction, then our scripts would become exponentially more complex, less secure and less reliable as they mature.
- In iOS 3.x-4.x, there is no accessible JavaScript interpreter built into the Objective C runtime to elegantly convert an arbitrary JSON-string arriving from a server-side script into native objects that may be passed to the Apple iOS SDK. Such an embedded interpreter As developers, we cannot easily demand that users upgrade to the latest version of our iPhone apps without risking significant negative feedback, as many users do not want to run newer versions of iOS on their older model devices or break compatibility with external (audio) accessories, or dock to a computer, use mobile bandwidth or enter an ''Apple ID'' when their device is managed for them by someone else.would require a faster CPU chip and larger battery, thus up until now, Apple has found that JavaScript was better suited for use in its Mobile Safari environment, where such battery-draining usage would be mitigated by a smaller screen size (driving users to simplified, mobile-enhanced, web-pages that don't rely as much upon JavaScript). Further, even if out of familiarity, we were to use JSON for database integration and even if we did not have to rely upon a non-Apple JSON parser, we would still need to manually maintain a layer of Objective C "glue" that has knowledge of the specific format of our database-results (to move the marshalled JSON into hard-coded objects), knowing that the "glue" code will break each time we make changes to the database-query, thus creating potential backwards compatibility issues and the more potential for negative feedback in the AppStore.
Web Database Integration
For pure web apps, we created libraries of scripts (using PHP, Ruby, Java, ASP, CFM, etc..) that ran atop Apache, IIS and Tomcat hypertext servers and internally used a proprietary database API to support specific functions such as get_customerInfo?id=123. Those server-side functions would then encode any database result set into JSON-strings that would be returned to our requesting web browser. As we maintained the server-side scripts (to track changes to the underlying database and support new app features), there was only a minimal impact upon our web browser because all JSON-string parsing was performed by the built-in eval function. We were further able to make changes at any time because each new visitor to the site received a fresh copy of the JavaScript client code as the web page opened in their web browser. While the overhead of encoding binary database results into JSON-strings like that required seemingly overpowered servers and while we must be constantly vigilant to prevent SQL Injection attack upon all generations of our server-side scripts, this web model sustained us until the recent trend towards battery-powered mobile devices became more pervasive.
- While Apple does supply a built-in library for parsing XML documents (even though XML was designed for batch processing with power cords and air conditioning), it is best used for infrequent processing of one-off "config files" and "feeds", not actions occurring in real-time response to user interface events.
Using the new ODBC SDK for iOS, a single folder is dragged into the XCode project enabling it to Connect, Disconnect and Query the app database in about three (3) lines of code without any server-side scripts (or even a web-server) and with all parameters and results exchanged in an efficient binary format. Such queries may include standard SQL commands such as CALL, SELECT, INSERT, UPDATE and DELETE. Because the SDK implements the ODBC industry standard, it has compatibility with almost any database server ever produced and because it is designed for iOS, the low-level industry-standard API calls in C-Language are supplemented by easier high-level calls in Objective C that return query results as native iOS objects (NSStrings, UIImages, NSNumbers, ...) that may be passed directly into the Apple iOS SDK (UITableView, ...) eliminating client and server side JSON-string processing yielding more-users-per-server and longer mobile device battery life.
Elimination of server-side scripts (that would otherwise be maintained with backwards compatibility to all prior app binary releases) is achieved through ODBC ROUTER, the server-side component of the SDK developed by AugSoft for more than ten years. A key benefit of ODBC is that it is a true de facto industry standard supported by virtually every database (MySQL, ORACLE, IBM, Microsoft, ...) and switching an app to run against one database product or another is simply a matter of installing a new ODBC driver using the server's standard ODBC control panel --usually no code changes-- much as if we installed a new printer driver.
Because virtually every database system supports Windows in every past, present and future release, the ODBC ROUTER is designed to run exclusively on Windows, either in a virtual machine on top of Linux (using a free VMware Server) or Mac (using VMWare Fusion) or other (potentially free) hypervisors such as VMware VSphere, RedHat KVM, Citrix Xen or Parallels Bare Metal. Installation of ODBC ROUTER on Windows is trivial and works along side ODBC drivers supplied by the database vendors themselves (not obscure third-parties), for example, MySQL 5.1 ODBC/Connector, ORACLE ODBC and IBM DB/2 Connector.
Although not required, Microsoft's new, highly-secure and inexpensive "Windows Web Edition" may be used to host the ODBC ROUTER and the database system's official ODBC drivers for as little as a $10/month premium above Linux (or on your own server for free with a 180 day evaluation) and is typically configured with only port 6110 (and perhaps Remote Desktop port 3389) open on the built-in Windows Firewall.
The database server itself (eg, MySQL, SQLServer, DB/2, Excel, Firebird, Visual FoxPro, Access, ...) may either be installed on the same Windows instance as ODBC ROUTER (but perhaps only accessible to applications running on that same instance via shared-memory/named-pipes) or elsewhere in the back-end network or VPN/extranet on Linux, Unix, Mac, Windows or a legacy mini or mainframe.