SQLClient: A Native Microsoft SQL Server Library for iOS

One of things that surprised me in iOS is the lack of an open-source native library to connect to Microsoft SQL Server.  When googling the topic the usual comments are “why would you do that?” and “just use a web service wrapper.” Well, there are countless business reasons to access a SQL Server on a LAN: POS, data collection, reporting, etc. Further, it’s not always possible or practical to install a web service layer (REST, SOAP, OData) to act as an intermediary. Granted, it is generally much safer to do so, but it does add a layer of complexity (and latency).

There used to be a paid product that could connect iOS to SQL Server called iSQL SDK from mobile[foo], a RedGate subsidiary. However, both the product and the company appear to be shut down, and apparently relied on an additional server-side component. ODBC Router is another commercial option, but costs about $130. Fortunately, there is a mature open-source C library called FreeTDS, which uses the TDS (Tabular Data Stream) protocol to communicate with Microsoft SQL Server (and Sybase). Of course, iOS can run C programs, but I couldn’t find an Objective-C implementation of this library to make it easy to use with iOS.

So, I took the opportunity to write SQLClient, an Objective-C wrapper around the FreeTDS library to make it extremely simple to connect to SQL Server from iOS. I compiled the 0.91 version of FreeTDS for i386, armv7, armv7s, arm64, and x86_64 against the iOS 7.0 SDK using this build script, and followed this example C code to invoke the library. Here’s how to use my wrapper:

Usage

#import "SQLClient.h"

SQLClient* client = [SQLClient sharedInstance];
client.delegate = self;
[client connect:@"server:port" username:@"user" password:@"pass" database:@"db" completion:^(BOOL success) {
    if (success)
    {
      [client execute:@"SELECT * FROM Users" completion:^(NSArray* results) {
        for (NSArray* table in results)
          for (NSDictionary* row in table)
            for (NSString* column in row)
              NSLog(@"%@=%@", column, row[column]);
        [client disconnect];
      }];
    }
}];

//Required
- (void)error:(NSString*)error code:(int)code severity:(int)severity
{
  NSLog(@"Error #%d: %@ (Severity %d)", code, error, severity);
}

SQLClient requires a delegate that implements the SQLClientDelegate protocol. This protocol has only one required method, error, and is used to receive asynchronous error messages, complete with error code and severity. A second method, message, is not required and is used by the library to communicate non-critical messages.

The connect method accepts the usual database connection parameters. The host name can be passed in one of 3 ways (this is a limitation of the FreeTDS library):

• server
• server:port
• server\instance

SQLClient attempts to connect to the database server for a duration specified in the timeout property, which defaults to 5 seconds. If an error is encountered or the timeout is reached, the error method will be invoked on the delegate. The connected method can be called at any time to check connection status. After a successful connection, SQLClient is ready to execute SQL commands.

The execute method accepts a SQL string as its input and passes back an NSArray of results to its completion handler when done. This array contains the result tables for each query in the SQL command (non-queries such as inserts, updates, and deletes do not return any results). Each table is an NSArray of rows. Each row is an NSDictionary of columns where key=name and object=value. All values are of type NSString. To close the connection, simply call the disconnect method.

Remember to be mindful of memory utilization! Avoid returning large data sets. Also, results processing should be performed on a separate queue to prevent blocking the main UI thread.

Try it out! Open the Xcode demo project in Github. Or browse the class reference. I welcome your comments and feedback. Feel free to modify and fork. Enjoy!

Installation

CocoaPods is the preferred way to install this library.

1. Open a Terminal window. Update RubyGems by entering sudo gem update --system. Enter your password when prompted.
2. Install Cocoapods by entering sudo gem install cocoapods.
3. Create a file at the root of your Xcode project folder called Podfile.
4. Enter the following text: pod 'SQLClient', '~> 0.1.3'
5. In Terminal navigate to this folder and enter pod install.
6. You will see a new .xcworkspace file. Open this file in XCode to work with this project from now on.

Or you can install it manually:

1. Drag and drop the contents of the SQLClient/SQLClient/SQLClient folder into your Xcode project.
2. Select Copy items into destination group’s folder (if needed).
3. Go to Project > Build Phases > Link Binary With Libraries.
4. Click + and add libiconv.dylib.

Notes

Singletons. Personally I dislike singletons because they create “global” code that violates the principles of OOP, but in this case I had to because of a quirk in bridging C and Objective-C. The FreeTDS library communicates errors and messages asynchronously using callback functions. Unfortunately, C has no notion of objects so there is no reference to self. For the callbacks to access the SQLClient class (and its delegate), there needs to be a globally accessible pointer to an object instance. That means that we also have to restrict the class to a single instance…a legitimate use case for a singleton!

Concurrency. SQLClient performs connections and queries on a separate worker queue to prevent blocking the UI when called from the main queue. This worker queue is exposed via the workerQueue property and can be overridden. The completion block, however, is invoked on the same queue that first instantiates the singleton (usually the main queue). This is exposed via the callbackQueue property and can also be changed.

Credits

None of this would have been possible without the FreeTDS library, so many thanks to all its contributors.

FreeTDS
http://www.freetds.org

FreeTDS-iOS
https://github.com/patchhf/FreeTDS-iOS

FreeTDS example code in C
http://freetds.schemamania.org/userguide/samplecode.htm

About Martin Rybak

I am a New York area software developer and MBA with 10+ years of server-side experience on the Microsoft stack. I've also been a native iOS developer since before the days of ARC. I architect and develop full-stack web applications, iOS apps, database systems, and backend services.

View all posts by Martin Rybak →