iOS Open Database Connectivity SDK

 Follow AugSoft on Twitter




The industry-standard ODBC C Language API is provided (in the included libodbcnet.a runtime library) but it is complimented by a fully native "Objective C" class that only requires 1-3 lines of code to access any Windows-compatible SQL databases (including IBM mainframes, MySQL, SQLServer, ORACLE, DB/2, etc.) without hard-coding to any proprietary APIs. No prior knowledge of the ODBC industry-standard is required.



High-Level "Objective C" Database Functionality
Low-Level "C" Database Functionality
High-Level "Objective C" MetaData Functionality (optional)
High-Level "Objective C" Non-Database Functionality (optional)


Note: A Low-Level industry-standard ODBC C API is also included, please see here.

Setup Open Database Connectivity

• Initialize - single step initialization of entire ODBC SDK with
+ [ODBCRouter proxy] - (typically called early in the appDelegate's application:didFinishLaunchingWithOptions:)
NOTE: disconnect any [ODBCcontext] objects before sending a release to the [ODBCrouter proxy]

These optional properties are available on the [ODBCRouter proxy] singleton (affecting all ODBCcontext objects):
useNetworkIndicator set to YES to run the spinner in the Status Bar when database threads are active
runningLateDelayInSeconds set to some delay after which a modal dialog should appear if db threads are still active
runningLateTitle title for the above 'running late' modal dialog
runningLateMessage message to appear in the above modal dialog

Connecting/Disconnecting a Data Source

• Connect - create an ODBCcontext connected to a Named-Data-Source on a given ODBC Router
+ [ODBCcontext contextWithDSN:(NSString *)dsn (example:MyGreatDatabase)
onRouter:(NSString *)ipAddressOrDNSofODBCrouter (example: odbc.mycompany.com)
ODBC_CALLBACK_ARGS]
NOTE: On mobiles, disconnect as quickly as possible, but do not re-connect too frequently (ie, cache data).
NOTE: If not disconnected, then a release sent to an [ODBCcontext] will block (while it internally disconnects).
 
• Disconnect - disconnect an ODBCcontext from its data source
- [ODBCcontext disconnect:ODBC_CALLBACK_ARGS]
NOTE: The first argument to this message is the object to receive the completion callback (or nil)

These properties are available on an ODBCcontext object after connect/disconnect
status if !nil, contains an NSString describing the failure status of the last ODBC operation

Running SQL Statements (CALL proc, SELECT, INSERT, UPDATE, DELETE, etc..)

• Run SQL - retrieve any rows as an NSArray of NSDictionary objects whose keys are the column names
- [ODBCcontext runStaticQuery:(NSString *)stringContainingSQLstatement
getArrayOfColumnDictionariesByRef:(NSMutableArray **)&myArray (will be set non-nil, if results are returned)
ODBC_CALLBACK_ARGS]
NOTE: Use this when you don't expect a large number of rows or columns to be returned.
NOTE: If you are new to Objective C:
This message optionally returns an NSArray whose entries are NSDictionary objects.
Think of each NSDictionary object as a PERL hash or PHP array.
The keys used to access each value in the NSDictionary are the exact column names from the database!
The values in an NSDictionary are always objects (NSString, UIImage, NSDate, NSNumber, NSData, ...)
([ODBCRouter.m] transparently maps the binary SQL data to and from objects for you.)
• Run SQL - retrieve any results as an NSArray of columns, each an NSArray of rows
- [ODBCcontext runStaticQuery:(NSString *)stringContainingSQLstatement
getArrayOfRowArraysByRef:(NSMutableArray **)&myArray (will be set non-nil, if results are returned)
ODBC_CALLBACK_ARGS]
NOTE: Use this when you expect many columns or more than a handful of rows to be returned.
NOTE: If you are new to Objective C:
This message optionally returns an NSArray containing an NSArray for each column
Each column's NSArray contains an object (NSString,UIImage,NSDate,NSNumber,NSData, ...) for each row
([ODBCRouter.m] transparently maps the binary SQL data to and from objects for you.)
These properties are available on an ODBCcontext object after running a SQL statement:
status if !nil, contains an NSString describing the status of the last query operation
columnDescriptors if results are available, contains an NSArray of ODBCcolumnDescriptor objects

- To preserve user bandwidth and stay responsive:
  • Avoid use of wildcard column specifiers (e.g., *) in stringContainingSQLstatement
  • Add a LIMIT, TOP or other query-modifier when a large result set may otherwise be returned.
  • CALL stored procedures rather than issuing multiple consecutive queries, where possible.
- Where practical, cache a local copy of frequently accessed data, but guard against mobile device loss:
  • Locally store credentials in the Keychain, never in your code, NSUserDefaults or in unprotected files
  • On iOS4+ locally store the user's sensitive data appropriately

Networking on a Mobile Device optional - but important

These APIs are typically called early in the appDelegate's application:didFinishLaunchingWithOptions:
• Require future ODBCcontexts to connect over a Carrier (cellular) data network
+ [ODBCRouter requireCarrierNetwork]
• Require future ODBCcontexts to connect over a Local (WiFi, Bluetooth, etc..) data network
+ [ODBCRouter requireLocalNetwork]
• Allow future ODBCcontexts to connect over any type of network (default)
+ [ODBCRouter requireAnyNetwork]

These APIs constrain and detect changes in the mobile network environment
• Return YES if device has any network connectivity or as required by the most recent requireXXXNetwork call
+ [ODBCRouter haveNetwork] - typically called prior to starting any new network operation throughout the app
Notifications:
Optionally register with [NSNotificationCenter defaultCenter] for kODBCRouterNetworkUp and kODBCRouterNetworkDown as desired. (These will only be sent between the time [ODBCRouter proxy] and [ODBCRouter dealloc] are called and only with respect to the currently required type of network.) These can be used to lock/unlock elements in the UI that might lead the user to directly or indirectly initiate a network operation (such as a SQL query).

Accessing Metadata optional - not used by most apps

• Retrieve an array of available Data Source Names and an array of their Descriptions from a given ODBC Router
- [ODBCRouter getFromRouter:(NSString *)ipAddressOrDNSofODBCrouter
TheDSNsByRef:(NSMutableArray **)dsnList
AndTheirDescriptionsByRef:(NSMutableArray **)descriptionsList
odbcStatusByRef:(NSString **)odbcStatus
ODBC_CALLBACK_ARGS]
• Retrieve an array of available Table/Catalog/Schema names in a given Data Source from a given ODBC Router
- [ODBCRouter getFromDSN:(NSString *)dsn
onRouter:(NSString *)ipAddressOrDNSofODBCrouter
TheTableCatalogsByRef:(NSArray **)tableCatalogs_or_nil
TheTableSchemasByRef:(NSArray **)tableSchemas_or_nil
TheTableNamesByRef:(NSArray **)tableNameList
odbcStatusByRef:(NSString **)odbcStatus
ODBC_CALLBACK_ARGS]
• Retrieve an array of Column names in a given Table/Catalog/Schema from a given ODBC Router
- [ODBCRouter getFromTable:(NSString *)table
inDSN:(NSString *)dsn
onRouter:(NSString *)ipAddressOrDNSofODBCrouter
inCatalog:(NSString *)catalog_or_nil
inSchema:(NSString *)schema_or_nil
TheColumnsByRef:(NSArray **)columnList
usingMicrosoftSyntax:(BOOL)useMicrosoftSyntax useful mainly for Access and Visual FoxPro data sources
odbcStatusByRef:(NSString **)odbcStatus
ODBC_CALLBACK_ARGS]
• Retrieve array of Rows from a given Column in a given Table/Catalog/Schema from a given ODBC Router
- [ODBCRouter getFromColumn:(NSString *)column inTable:(NSString *)table
inSchema:(NSString *)schema_or_nil
inCatalog:(NSString *)catalog_or_nil
inDSN:(NSString *)dsn
onRouter:(NSString *)ipAddressOrDNSofODBCrouter
TheRowsByRef:(NSArray **)rowList
AndTheirObjCTypeByRef:(NSString **)objCType
RowLimit:(NSUInteger)maxRowsToReturn_or_zeroForUnlimited
usingMicrosoftSyntax:(BOOL)useMicrosoftSyntax useful mainly for Access and Visual FoxPro data sources
odbcStatusByRef:(NSString **)odbcStatus
ODBC_CALLBACK_ARGS]


Backgrounding Support

Calls to the High-Level Objective C API return almost immediately, with the industry-standard Low-Level ODBC calls continuing on in the background to perform the requested operation. In this way, Cocoa Touch, spinners and other progress-indicators, will continue running and the app will remain responsive. When the requested database operation completes, if an app-specified callback routine was specified, it is invoked on the specified target and passed any specified object. Such a callback may then examine the completion status (aka ODBCcontext.status) and any returned result set, as well as perform other housekeeping, for example, sending a reloadData message to a UITableView that is backed by the returned result set. IMPORTANT: This mechanism for background threading retains compatibility from iOS 7 all the way back to iOS 3.1, which was sold on devices as recently as early 2010. (While old many old devices can be upgraded, many millions of those users will choose not to do so for reasons ranging from speed, lack of technical know-how or "look and feel".)

All ODBCRouter operations that run in the background accept "at least" these standard arguments (where they specify ODBC_CALLBACK_ARGS):
ODBC_CALLBACK_ARGS are
target:(id)target callback:(SEL)callback object:(id)object
target is the target object to receive the callback message
callback is the message to be sent, such as @selector(myRoutine:)
NOTE: remove trailing : if routine does not accept an argument
NOTE: if specified selector does not exist, no callback will be invoked and no error will be thrown
NOTE: if selector exists, it is always called on the app's Main thread
object is an optional argument to be passed to callback
NOTE: if an argument is specified, the message name (passed to @selector) must end in a :
all values may be nil if no callback is desired

Pass By Reference

Variables that you pick to receive any returned results are declared by your app as NSMutableArray * (with @property(retain)) and should always be accessed using the self. (or objectName.) prefix and reset to nil when not in use. These variables are passed by reference (meaning they are preceded with an & character) to runStaticQuery and others --the ODBC SDK will initialize them with new values if, and only if, results are returned.

User Interface Modality during Database Operations

Having all database operations performed in background threads is completely transparent to your code and you may only want to set the useNetworkIndicator property of the ODBCRouter object so that it spins the network-activity-indicator in the device's status bar during background operations. However, if your app is launching database operations as a direct result of user interaction and the user is truly waiting for results to come back from the database before they can proceed, then you may want to "lock" the user interface to prevent the user from re-initiating (or navigating away) before any database results can be received. This is trivial to accomplish using a convenience feature of the ODBCRouter object that allows you to specify how much time (in seconds) that a database operation may use before the ODBCRouter object presents a modal dialog (aka "blue pop") with your message and an activity-spinner until the database operation completes. This feature is controlled four ODBCRouter properties listed above (see Initialize).


Putting it all together, this code section demonstrates initializing (and using the optional "running late" user interface modality feature), using the optional features to honor the user's network-type selection (WiFi-only, Cellular+WiFi, ...), connecting to an ODBC Data Source (with some added code to ensure it is not done "too frequently"), running a query and getting back results (in one line of code), displaying the results and disconnecting. A second code section follows demonstrating how to request a list of available Data Source Names and Descriptions.

// for your class INTERFACE (.h) file:
// this all goes before your @interface declaration
#include "ODBCRouter.h"
#define kDSN                                    @"My SystemDSN"
#define kODBCRouterAddress                      @"odbc.mycompany.com"
#define scRequiredNetworkType                   @"scRequiredNetworkType"
#define scLastCloudTS                           @"scLastCloudTS"
#define scMinSecsBetweenCloudUpdateChecks       @"scMinSecsBetweenCloudUpdateChecks"
#define scRetryCloudIntervalInSecs              @"scRetryCloudIntervalInSecs"
// add to your class @interface definition (between { and } )
NSUserDefaults *config;
NSString *query;
NSMutableArray *myResults;
ODBCcontext *odbcContext;
// down at the bottom of the .h file add:
@property (retain) NSUserDefaults *config;
@property (retain) NSString *query;
@property (retain) NSMutableArray *myResults;
@property (retain) ODBCcontext *odbcContext;
- (void)receivedDSNs:(id)args;
- (void)updateConfigFromCloud;
- (void)queryDatabase:(id)args;
- (void)processResults;

// for your class IMPLEMENTAITON (.m) file:
@synthesize config,query,myResults,odbcContext;

// add these to your class 'dealloc' or 'viewDidUnload' method:
- (void)addTodealloc {
    self.config = nil;
    self.query = nil;
    self.myResults = nil;
    self.odbcContext = nil;
}

// add this one-time code to your class initialization, viewDidLoad or appDelegate
-(void) initMyDatabaseAccess {
    [ODBCRouter proxy]; // create the shared ODBC Router proxy singleton
   
    // optionally setup [ODBCRouter] to give UI feedback while it's running (by default it won't interact with user)
    [ODBCRouter proxy].useNetworkIndicator = YES; // spin in the Status Bar when database threads are active
    [ODBCRouter proxy].runningLateDelayInSeconds = 3.0f; // if db not finished in this many seconds, then blue-pop a spinner
    [ODBCRouter proxy].runningLateTitle = @"Working"; // with this title...
    [ODBCRouter proxy].runningLateMessage = @"My Company Database/odbc.mycompany.net"; // ..and this message

     // optionally initialize criteria for "haveANetwork" calls based on our stored user preferences
    self.config = [NSUserDefaults standardUserDefaults];
    if ([[config objectForKey:scRequiredNetworkType] integerValue] == kODBCRouterNetworkTypeAny) {
        [[ODBCRouter proxy] requireAnyNetwork]; // this is also the default if not called
    }
    else if ([[config objectForKey:scRequiredNetworkType] integerValue] == kODBCRouterNetworkTypeLocal) {
        [[ODBCRouter proxy] requireLocalNetwork];
    }
    else if ([[config objectForKey:scRequiredNetworkType] integerValue] == kODBCRouterNetworkTypeCarrier) {
        [[ODBCRouter proxy] requireCarrierNetwork];
    }

    // make sure our defaults for the example function 'updateConfigFromCloud' are set
    if (![config valueForKey:scMinSecsBetweenCloudUpdateChecks]) {  // minimum wait between successful cloud checkups
        [config setObject:[NSNumber numberWithDouble:3600.0] forKey:scMinSecsBetweenCloudUpdateChecks];
    }
    if (![config valueForKey:scRetryCloudIntervalInSecs]) {         // wait between unsuccessful cloud checkups
        [config setObject:[NSNumber numberWithDouble:300.0] forKey:scRetryCloudIntervalInSecs];
    }
}

// sample function to query the Cloud if it has been more than scMinSecsBetweenCloudUpdateChecks since
// last check, also shows how to use timers to retry operations that could fail while the user is moving around
// (networks fades...) and how to prevent hammering the shared server too much (e.g., if the user is
// somewhere the network quality is really poor)

- (void)updateConfigFromCloud {
    if (!config) {
        self.config = [NSUserDefaults standardUserDefaults];
    }
    if (    ![config valueForKey:scLastCloudTS] ||
        ![config valueForKey:scMinSecsBetweenCloudUpdateChecks] ||
        ([[config valueForKey:scLastCloudTS] respondsToSelector:@selector(timeIntervalSinceNow)] &&
         (([[config valueForKey:scLastCloudTS] timeIntervalSinceNow] * -1.0f) >
            [[config valueForKey:scMinSecsBetweenCloudUpdateChecks] doubleValue])))
    {
        if ([ODBCRouter proxy].haveNetwork) {
            // this starts a network connection to the data source with the given Data Source Name
            self.odbcContext = [ODBCcontext contextWithDSN:kDSN onRouter:kODBCRouterAddress
                        target:self callback:@selector(queryDatabase:) object:nil];
        }
        else { // no network is available, try again later
            NSTimer *t = [NSTimer timerWithTimeInterval:[[config valueForKey:scRetryCloudIntervalInSecs] doubleValue]
                            target:self selector:@selector(updateConfigFromCloud) userInfo:nil repeats:NO];
            [[NSRunLoop mainRunLoop] addTimer:t forMode:NSDefaultRunLoopMode];
        }
    }
}

// function to run a query on the Cloud database
- (void)queryDatabase:(id)args { // called when connection completes on the "main" thread
    if (!odbcContext.status) {
        self.query = @"SELECT a,b,c FROM myTable WHERE id=123";
        self.myResults = nil;
           
        // this runs the query on the data source
        [odbcContext runStaticQuery:self.query getArrayOfColumnDictionariesByRef:&myResults
                        target:self callback:@selector(processResults) object:nil];
    }
    else { // reschedule, if necessary
        NSLog(@"failed to connect DB: %@ (retrying in %f seconds)",
                    odbcContext.status, [[config valueForKey:scRetryCloudIntervalInSecs] doubleValue] );
        [odbcContext disconnect:nil callback:nil object:nil];
        NSTimer *t = [NSTimer timerWithTimeInterval:[[config valueForKey:scRetryCloudIntervalInSecs] doubleValue]
                            target:self selector:@selector(updateConfigFromCloud) userInfo:nil repeats:NO];
        [[NSRunLoop mainRunLoop] addTimer:t forMode:NSDefaultRunLoopMode];
    }
}

// function to process the result set returned from the Cloud
- (void)processResults { // called when the query completes on "main" thread
    BOOL erroredOut = NO;
    if (!odbcContext.status && myResults) {
        // here is the entire result set
        NSLog(@"query results (as an NSArray of one NSDictionary per row) %@",myResults);
       
        // here are the attributes (metadata) of each column
        NSLog(@"column descriptions (as an NSArray of one NSDictionary per column) %@",
                odbcContext.columnDescriptors);
       
        // how to access the data returned in the myResults:
        //  each row of 'myResults' is an NSDictionary with a "key" for each column name
        //  for example, to address the value for column named 'b' of row 0 in our result set:
        //      value = [[myResults objectAtIndex:0] valueForKey:@"b"]
        //  and if we wanted to 'copy' that value to a key named 'b' in our NSUserDefaults
         [config setObject:[[[myResults objectAtIndex:0] valueForKey:@"b"] copy] forKey:@"b"];

        // when we're done, delete the result set and remember this as the last time we updated from Cloud (scLastCloudTS)
        self.myResults = nil;
        [config setObject:[NSDate date] forKey:scLastCloudTS];
        [config synchronize]; // write our NSUserDefaults back to persistent storage
    }
    else {
        NSLog(@"error running query: %@",odbcContext.status);
        erroredOut = YES;
    }

    // this disconnects from the data source
    [odbcContext disconnect:nil callback:nil object:nil];

    if (erroredOut) { // reschedule, if something went wrong
        NSTimer *t = [NSTimer timerWithTimeInterval:[[config valueForKey:scRetryCloudIntervalInSecs] doubleValue]
                        target:self selector:@selector(updateConfigFromCloud) userInfo:nil repeats:NO];
        [[NSRunLoop mainRunLoop] addTimer:t forMode:NSDefaultRunLoopMode];
    }
}


// sample  (unrelated to prior) that retrieves a list of Data Source Names from the ODBC Router

// add to your class INTERFACE (.h) file
// add to your class @interface definition (between { and })
NSString *viewStatus;
NSMutableArray *odbcDSNs;
NSMutableArray *odbcDSNdescriptions;
// down at the bottom of the .h file add:
@property (retain) NSString *viewStatus;
@property (retain) NSMutableArray *odbcDSNs;
@property (retain) NSMutableArray *odbcDSNdescriptions;
- (void)receivedDSNs:(id)args;

// add to your class IMPLEMENTATION (.m) file
@synthesize viewStatus, odbcDSNs, odbcDSNdescriptions;

// add these to the class 'dealloc' or 'viewDidUnload' method:
- (void)moreThingsToAddToDealloc {
    self.viewStatus = nil;
    self.odbcDSNs = nil;
    self.odbcDSNdescriptions = nil;
}

// function to request list of available Data Source Names on the Cloud database
-(void) requestListOfDSNs {
    self.viewStatus = nil;      // this will receive an NSString containing a message if anything goes wrong
    self.odbcDSNs = nil;        // this will receive an NSMutableArray of Data Source Names on success
    self.odbcDSNdescriptions = nil// this will receive an NSMutableArray of Data Source Descriptions on success
   
    if ([ODBCRouter proxy].haveNetwork) { // there is a suitable network connection
        // ask the ODBC Router for list of Data Source Names and their Descriptions
        [ODBCRouter proxy].runningLateTitle = @"Retrieving Data Sources"; // if we run late...
        [[ODBCRouter proxy] getFromRouter:@"odbc.mycompany.net"  
                             TheDSNsByRef:&odbcDSNs
                AndTheirDescriptionsByRef:&odbcDSNdescriptions
                              odbcStatusByRef:&viewStatus
            target:self callback:@selector(receivedDSNs:) object:nil];
    }
    else {
        self.viewStatus = [NSString stringWithString:@"no network connection"];
        [self receivedDSNs:nil];
    }
}

- (void)receivedDSNs:(id)args { // called when DSNs (or an error) are available on "main" thread
    if (!viewStatus) { // no error
        NSLog(@"receivedDSNs: (%d rows)", [odbcDSNs count]);
        NSLog(@"here are the DSNs: %@", odbcDSNs);
        NSLog(@"here are their descriptions: %@",odbcDSNdescriptions);
        // [dsnTableView reloadData];  // reload some UITableView that pulls from odbcDSNs and odbcDSNdescriptions
    }
    else {  // something went wrong show the user
        NSLog(@"Can't Retrieve Data Source List: %@", viewStatus);
    }
}


© August Software, USA., 1990-2017. All Rights Reserved.