SQLClient: A Native Microsoft SQL Server Library for iOS

SQL Server

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 these ads

About Martin Rybak

I am a New York area software developer and MBA with 10+ years of experience on the Microsoft stack. Over the past few years I have also expanded into iOS development using native Objective-C. I architect and develop full-stack web applications, iOS apps, database systems, and backend services.

105 responses to “SQLClient: A Native Microsoft SQL Server Library for iOS

  1. Seems interesting although a few things come to mind if you plan to build future support. Improved handling of data types are one. I will put this in my sandbox. Thanks for the effort.

  2. Thank you Martin. Just one question, will iOS apps utilising this approach be ok with- and be accepted by Apple’s approval for the app store?

  3. Bron

    How would you go about getting the results into something that you can refer to after the session has disconnected? I have been playing with the example code, I can connect to a test DB and NSLog shows the contents fine, but the variables disappear after the disconnect. I tried adding into my own array, and that doesn’t appear to work as I expected. I’m pretty new to IOS programming, so I might be missing something obvious here.

    • Hi Bron, the easiest way would be to simply declare a NSArray with the __block keyword before the code that invokes SQLClient:
      __block NSArray* data;
      Then inside the completion block of the execute command, you grab its results array:
      data = results;
      You can then access and process the data array after the execute command is done.

      • Bron

        This doesn’t seem to work, the information within data disappears when results does. Trying to manipulate anything is impossible because ^(NSArray* results) appears to be strong.

      • Bron, I updated the demo app to show how to process results outside of the execute: method. The execute: method is asynchronous, so any code you place after that method will be executed before the database results come back, which is probably why you see no data. The proper place to process results is inside the callback block, or by calling another method from inside the callback block. Give it a shot!

      • Bron

        Thanks Martin,

        Very much appreciated!

  4. Hi Martin, the server component in iSQL helped get around problems of IP firewalls, such as Azure SQL where mobile clients cannot access the server directly, but if hitting a web service it would not be a problem. However, iSQL does not play well with Azure either :(

    • Agreed, accessing SQL Server raw over the internet is generally not a good idea. This library is ideal for LAN (or VPN) solutions. For Azure, your best bet would be to use the Azure iOS SDK to communicate with Azure Mobile Services. Alternatively, you could use the OData4ObjC library which contains native support for querying Windows Azure tables.

      • Thanks, I ended up getting iSqlServer to work in the end, but it’s only a short term solution until the full web services get built. I bought all the source from Mobile Foo before they closed ;) Mobile Services don’t play nice with existing Azure SQL DB, it can be done, but you need to change the owner name from dbo. to {mobileservicename}. There are lots of caveats when migrating to Azure :(
        Thanks again for the reply.

  5. David Thompson

    Hi Martin, thank you for the code, works great. One question, I would like to perform multiple queries after a connection is made. When I attempt to perform my second [client execute:… I get an error.
    If I disconnect and reconnect it works fine. Once connected, how do you perform multiple [client execute…..?

    • Hi David, you are correct. If you try to execute multiple commands as below, it will fail with “Attempt to initiate a new Adaptive Server operation with results pending.” This is because the calls are asynchronous, and they both run at the same time. This will FAIL:

      __block NSArray* data1;
      __block NSArray* data2;
      [client execute:@"SELECT * FROM Employee" completion:^(NSArray* results) {
      data1 = results;
      }];
      [client execute:@"SELECT * FROM Customer" completion:^(NSArray* results) {
      data2 = results;
      }];

      There are 2 ways around this. The most efficient way is to simply put multiple statements into the same SQL command string, separated by semicolons. Each table will be its own array inside the results array:

      __block NSArray* data1;
      __block NSArray* data2;
      [client execute:@”SELECT * FROM Employee; SELECT * FROM Customer” completion:^(NSArray* results) {
      data1 = results[0];
      data2 = results[1];
      }];

      Another way is to nest the second query inside the first’s completion block. This will insure that they occur sequentially, one at a time:

      __block NSArray* data1;
      __block NSArray* data2;
      [client execute:@”SELECT * FROM Employee” completion:^(NSArray* results) {
      data1 = results;
      [client execute:@”SELECT * FROM Customer” completion:^(NSArray* results) {
      data2 = results;
      [client disconnect];
      }];
      )];

      Hope this helps!

      • David Thompson

        Hi Martin,
        Thank you for the response, I forgot about the worker thread still running SQL query. I ended up implementing a call back that triggers on the completion of the prior SQL query, this keeps only 1 query active at a time.
        Regards,

  6. David Thompson

    Hi Martin,
    In process of trying to create provisioning file for iTunes and I am coming across this error:
    /Users/davidthompson/Library/Developer/Xcode/DerivedData/PlexCall_Trak-bykjirxxzwxamlapyyvuodaquhlg/Build/Intermediates/PlexCall Trak.build/Release-iphoneos/PlexCall Trak.build/Script-5B0FC8A1180DDBFE00DF4EFE.sh: line 11: /usr/bin/appledoc: No such file or directory

    Looks like the a script is trying to locate appledoc ?
    I remember you mentioned that you were using appledoc to document the SQL interface. I’m sorry if this is not related but do you think it is?

  7. Edgar

    Hello Martin,
    I’m new to iOD development, however, I have been using C and C++ and C# for about 15 years. How come i’m unable to connect to a remote SQL Server using ‘server.com\instance’ or even ‘0.0.0.0\instance’, user name and password and database are correct. I was able to read the error as server is not in the config file??

    If possible can you tell me what i’m doing wrong?

    Thanks, and thank you for a great post!!
    Ed

  8. David Thompson

    Hi Martin,
    Not sure, but if you are embedding the connection in a string, you will need 0.0.0.0\\instance
    Just a thought…

  9. Ram

    Hi Martin,

    I am using your sql clinet library. I have a global function for sql connection. And I am getting this warning
    /Users/macbookpro13/Desktop/Traffic_Department_Saudi/Traffic_Department_Saudi/Traffic_dep_Util.m:18:20: Incompatible pointer types assigning to ‘NSObject *’ from ‘Class’
    How I resolve this warning?

    Thanks

    • Ram, not sure what’s going on without seeing more code, but it sounds like you are trying to assign a class to an object pointer.

      • Ram

        Hi Martin,
        in .h class

        #import
        #import “SQLClient.h”

        @interface Traffic_dep_Util : NSObject

        +(NSArray *)sql_connection:(NSString *)sql_query;
        @end

        and in .m class
        #import “Traffic_dep_Util.h”
        #import “SQLClient.h”

        @implementation Traffic_dep_Util

        +(NSArray *)sql_connection:(NSString *)sql_query{
        NSArray *result_array=[[NSArray alloc] init];
        SQLClient* client = [SQLClient sharedInstance];
        client.delegate = self;
        [client connect:@”” username:@”” password:@”” database:@”” completion:^(BOOL success) {
        if (success)
        {

        [client execute: sql completion:^(NSArray* results) {
        NSLog(@”%@”,results);
        NSArray* anArray = [results objectAtIndex:0];
        result_array=[anArray copy];
        [client disconnect];

        }];
        }
        }];

        return result_array;
        }

        #pragma mark – SQLClientDelegate

        //Required
        – (void)error:(NSString*)error code:(int)code severity:(int)severity
        {
        NSLog(@”Error #%d: %@ (Severity %d)”, code, error, severity);
        [[[UIAlertView alloc] initWithTitle:@”Error” message:@”Please check Internet connection.” delegate:nil cancelButtonTitle:@”OK” otherButtonTitles:nil, nil] show];
        }

        //Optional
        – (void)message:(NSString*)message
        {
        NSLog(@”Message: %@”, message);
        }

        @end

        I have design this class for global function. So that I can use single function for all class.

        Now please sort this issue.

      • You are trying to assign client.delegate = self but you are inside a class method so there is no notion of “self”.

  10. David Thompson

    Martin,
    How do you change the default port from 1433?

    • David Thompson

      Hi Martin,
      Just wanted to add: I see its server:port or server\instance but what about server\instance:port
      Doesn’t seem to work…

      • David Thompson

        Martin,
        Found solution/format. Also found some strange behavior of the SQL client.
        1. If you want to use a different port from default with instance, format is: 0.0.0.0:1433\instance (remember to use double \\ if embedding connection string)
        2. When using the Verizon network with iPhone, I found that 0.0.0.0\instance does not work. When using Wi-Fi it works fine but unless I define the default port of 1433 it will not work on the Verizon network. Example:
        A) 0.0.0.0\instance
        B) 0.0.0.0:1433\instance

        A will only work when the connection is made with Wi-Fi
        B will work on either

        On the Xcode simulator and using Wi-Fi as my connection, either A or B works. This behavior is 100% duplicatable.

      • Verizon is likely blocking port 1433. You should use Wifi or VPN if over cellular.

      • David, unfortunately this is a limitation in the FreeTDS library: http://freetds.schemamania.org/userguide/freetdsconf.htm

      • David Thompson

        Hi Martin,
        Thanks for the interaction, know we are all busy. Verizon blocking port 1433 was my first suspicion, strange thing is if I define the port 1433 it then works! That is strange. So, if I attempt with 0.0.0.0\instance on Verizon, it does not work. If I attempt with 0.0.0.0:1433\instance on Verizon, it works! Just wanted to let others know. Strange…

        Now that I am using a larger server connection string “www.website.com:1433\instance I am getting a “Name too long for LOGINREC field” but even with the error, all works. I will filter the error and ignore.

  11. Hey Martin Happy New Year!! I hope 2014 brings you a lot of creative ideas and success…I have a question, I’m messing around with your wrapper, I came around a few minor issues but it seems to be working, however, when I put the server’s IP and login credentials etc, it seems to try to connect, and even the credentials get accepted (if I change them I get a second error), however, I get a “General SQL Server error: Check messages from the SQL Server), and when I go to the SQL Server machine (VMWare Fusion virtual machine), I don’t see any related errors, the machine is a 32 bit Windows 2008 SP2, with SQL Server 2008 SP2….Any ideas? Have your encountered a similar error?

    Thanks,

    Eduardo.

    • Martin I was able to find the login msg from the Xcode Client:

      Login succeeded for user ‘test1′. Connection made using SQL Server authentication. [CLIENT: 172.16.67.1]

      So apparently is connecting….but the error still persists

  12. Thanks for your reply Martin, I implemented the message method and was able to connect to a regular DB. Sorry for asking newbie questions but I’m still getting familiar with all this IOS Dev, I’m trying to connect to a Linked Server on SQL Server, and I don’t have a clue on how to implement the OPENQUERY method within FreeTDS, or perhaps I just haven’t had the time to read all the documentation, so have you ever tried doing this, is this possible with FreeTDS and your wrapper?

    Again, Many Thanks

    Ed.

  13. Hi Martin,

    Thanks for this, it’s made it incredibly simple to read and write to a mssql database. It’s solved a big rad ache for me!

    One quick question, does this only work on ios7? It works perfectly on the simulator on 6.1 and on my iPad running ios 7. But on my iphone running 6.1.2 it crashes and I get an error to do with missing symbols? Something mentioning libSystem.B.dylib? I’m in te office now so can’t get the exact error. If you run your sample on an ios device running ios 6 you’ll see it.

    Thanks

  14. Nick

    Martin, I’ve been working with SQLClient for a couple weeks now, and I’ve run into a couple issues. First issue, if ANY column would return a NULL value, I get none of the results. The exact same query done directly on the database works fine (showing all the rows/columns, some are null), but through SQLClient, it returns an empty table, no rows, no columns, etc. Additionally, with text fields in my database, I can’t retrieve more than 256 characters (without a really hacky work-around in the SQL query to turn 1 row into multiple rows, each containing a 256-character section of the text). Is there a solution to allow me to get more than 256 characters without this annoying work around?

  15. Hi Martin,

    How do you implement an OpenQuery using your wrapper?

    Thanks

    • From what I understand, OPENQUERY works by specifying OPENQUERY as the table name in the “from” clause, so as long as everything is configured correctly on the server, it should be executed like any other query:

      SELECT * FROM OPENQUERY(remotelinkedservername, 'SELECT * FROM dbo.Users' )

  16. Thanks for your reply, indeed it could be executed like any query and I have an OpenQuery sentence that I execute from Jdbc in OSX without issues, however when I try using the wrapper I get an error, so my question is, in the DB field what should I put?

  17. I finally made it work using Execute (‘select * from TABLE”‘)at LinkedServer by enabling the RPC Out option. For some reason it didn’t like the OPENQUERY command.

  18. Ram

    Hello Martin,

    I am getting a big problem. From database i am getting arabic text like as ?????????. So please tell me how get text in proper format means if text in english then get in english or if in arabic then get in arabic?

    Thanks

  19. Ram

    Hi Martin,
    I am getting this also
    2014-01-18 19:01:05.412 Saudi_Traffic_Department[1173:60b] Error #2403: Some character(s) could not be converted into client’s character set. Unconverted bytes were changed to question marks (‘?’) (Severity 4)

  20. Ram

    Hi Martin,
    Can we use this sql connection code as a function and then use that function for all class in a single project? Means create as a globql function.

    • Sure, just remember that it is a singleton and that its delegate property must always point to an NSObject instance implementing the SQLClientDelegate protocol (in order to receive error messages).

  21. Ram

    Hello Martin,

    Today I am testing my app and suddenly I got some crashes. And when I went through the device log then I got these reasons:-

    Exception Type: EXC_BAD_ACCESS (SIGSEGV)
    Exception Subtype: KERN_INVALID_ADDRESS at 0x71431413
    Triggered by Thread: 6

    and
    Thread 6 Crashed:
    0 Saudi_Traffic_Department 0x0025e8f4 tds_dstr_zero + 24
    1 Saudi_Traffic_Department 0x00256880 tds_free_login + 24
    2 Saudi_Traffic_Department 0x0023a2b2 dbloginfree + 98
    3 Saudi_Traffic_Department 0x0016c6c6 -[SQLClient connectionSuccess:] (SQLClient.m:285)
    4 Saudi_Traffic_Department 0x0016b96a __59-[SQLClient connect:username:password:database:completion:]_block_invoke (SQLClient.m:124)
    5 Foundation 0x2fe218a4 -[NSBlockOperation main] + 128
    6 Foundation 0x2fe11aa0 -[__NSOperationInternal _start:] + 768
    7 Foundation 0x2feb5968 __NSOQSchedule_f + 56
    8 libdispatch.dylib 0x39d284b2 _dispatch_async_redirect_invoke + 106
    9 libdispatch.dylib 0x39d297d4 _dispatch_root_queue_drain + 220
    10 libdispatch.dylib 0x39d299c0 _dispatch_worker_thread2 + 52
    11 libsystem_pthread.dylib 0x39e53dfc _pthread_wqthread + 296
    12 libsystem_pthread.dylib 0x39e53cc0 start_wqthread + 4

    So the reason of that you can see in 3 and 4 point.
    How can I stop this crashing?

    Thanks

  22. Pingback: Open Source iOS Objective-C Wrapper For Working With Microsoft SQL Server Databases

  23. Pingback: SQLClient: A Native Microsoft SQL Server Library for iOS |  VishalDharmawat - iPhone and iPad Development

  24. John

    Hi Martin,

    Do you have an example of using an Insert Command with your wrapper? I’m having trouble with multiple inserts inside a for loop. It has something to do with a pointer no longer being referred to after I close the connection after the first loop and it causes a EXC_BAD_ACCESS memory fault at the dbloginfree(login) cleanup. Keep in mind since i have the execution inside a for loop, the connection opens and closes more than once.

    Example code below:

    [client2 connect:@”my server” username:@”xxxx” password:@”xxxxxx” database:@”xxxxxxxxxx” completion:^(BOOL success) {
    if (success)
    {
    for (int i = 0; i < iterator; i++)
    {
    if (i == 0)
    {
    factor = 0;
    }
    else
    {
    factor = 77*i;
    }
    NSLog(@"%d cycle through the for loop", i);

    [client2 execute:[NSString stringWithFormat:@"INSERT INTO Units(CustomerNumber, UnitType, UnitNumber, Make, Year, Odometer, PlateNumber, InspectionDate, Store, PersonEntering, SteerTireSize, DriveTireSize, TrailerTireSize, WheelType, SteerTreadType, DriveTreadType, TrailerTreadType, LeftSteerTrd, RightSteerTrd, LeftSteerPSI, RightSteerPSI, LFODriveTrd, LFIDriveTrd, RFIDriveTrd, RFODriveTrd, LFODrivePSI, LFIDrivePSI, RFIDrivePSI, RFODrivePSI, LRODriveTrd, LRIDriveTrd, RRIDriveTrd, RRODriveTrd, LRODrivePSI, LRIDrivePSI, RRIDrivePSI, RRODrivePSI, LROTagTrd, LRITagTrd, RRITagTrd, RROTagTrd, LROTagPSI, LRITagPSI, RRITagPSI, RROTagPSI, LFOTagTrd, LFITagTrd, RFITagTrd, RFOTagTrd, LFOTagPSI, LFITagPSI, RFITagPSI, RFOTagPSI, LFOTrailerTrd, LFITrailerTrd, RFITrailerTrd, RFOTrailerTrd, LFOTrailerPSI, LFITrailerPSI, RFITrailerPSI, RFOTrailerPSI, LMOTrailerTrd, LMITrailerTrd, RMITrailerTrd, RMOTrailerTrd, LMOTrailerPSI, LMITrailerPSI, RMITrailerPSI, RMOTrailerPSI, LROTrailerTrd, LRITrailerTrd, RRITrailerTrd, RROTrailerTrd, LROTrailerPSI, LRITrailerPSI, RRITrailerPSI, RROTrailerPSI) VALUES (\"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\")", testFillerArray[0+factor], testFillerArray[1+factor], testFillerArray[2+factor], testFillerArray[3+factor], testFillerArray[4+factor], testFillerArray[5+factor], testFillerArray[6+factor], testFillerArray[7+factor], testFillerArray[8+factor], testFillerArray[9+factor], testFillerArray[10+factor], testFillerArray[11+factor], testFillerArray[12+factor], testFillerArray[13+factor], testFillerArray[14+factor], testFillerArray[15+factor], testFillerArray[16+factor], testFillerArray[17+factor], testFillerArray[18+factor], testFillerArray[19+factor], testFillerArray[20+factor], testFillerArray[21+factor], testFillerArray[22+factor], testFillerArray[23+factor], testFillerArray[24+factor], testFillerArray[25+factor], testFillerArray[26+factor], testFillerArray[27+factor], testFillerArray[28+factor], testFillerArray[29+factor], testFillerArray[30+factor], testFillerArray[31+factor], testFillerArray[32+factor], testFillerArray[33+factor], testFillerArray[34+factor], testFillerArray[35+factor], testFillerArray[36+factor], testFillerArray[37+factor], testFillerArray[38+factor], testFillerArray[39+factor], testFillerArray[40+factor], testFillerArray[41+factor], testFillerArray[42+factor], testFillerArray[43+factor], testFillerArray[44+factor], testFillerArray[45+factor], testFillerArray[46+factor], testFillerArray[47+factor], testFillerArray[48+factor], testFillerArray[49+factor], testFillerArray[50+factor], testFillerArray[51+factor], testFillerArray[52+factor], testFillerArray[53+factor], testFillerArray[54+factor], testFillerArray[55+factor], testFillerArray[56+factor], testFillerArray[57+factor], testFillerArray[58+factor], testFillerArray[59+factor], testFillerArray[60+factor], testFillerArray[61+factor], testFillerArray[62+factor], testFillerArray[63+factor], testFillerArray[64+factor], testFillerArray[65+factor], testFillerArray[66+factor], testFillerArray[67+factor], testFillerArray[68+factor], testFillerArray[69+factor], testFillerArray[70+factor], testFillerArray[71+factor], testFillerArray[72+factor], testFillerArray[73+factor], testFillerArray[74+factor], testFillerArray[75+factor], testFillerArray[76+factor]] completion:^(NSArray* results) {

    [client2 disconnect];
    }];

    }

    }

    }];

    • Hi John, there’s a couple of things going on here. First, you are calling [client2 disconnect] in the completion block of your execute statement, but then not reconnecting so the connection is lost for the next iteration of the loop. Second, even if you remove that line, this code will fail because only one [client2 execute:] can be executed at a time. As it stands, they will all fire before the first one has a chance to return. My suggestion would be to either nest each execute command in its previous call’s completion block, or to concatenate the INSERT statements into a single SQL command, separated by a semicolon. Hope this helps.

      • John

        Hi Martin,

        Thanks for your feedback and all of your hard work. I am working diligently on trying your suggestions, to include BULK INSERT since I am simply breaking up a .txt file comprised of comma delimited values with newlines at the end. I’d prefer to do one insert statement as you suggested, but since I’d need to insert up to 100 rows, not sure what the syntax would be on the client2:execute string.

        If I were to create a simple execute statement such as:

        [client2 execute: [NSString stringWithFormat:@”INSERT INTO Units(CustomerNumber, UnitType) VALUES (\”%@\”, \”%@\”)”, testFillerArray[0+factor], testFillerArray[1+factor]]];

        Row 1 would populate no problem, where I get lost is writing the command to insert row 2 and beyond.

        You suggested concatenating the INSERTS with a semicolon, but again there seem to be some syntax issues I don’t understand on the client2 execute portion. I think I’m really close, just not a lot of SQL code out there to refer to in Objective-C. Any hint on the syntax of the execute statement(in regards to it being dynamic) would be huge.

        Thanks for all the help you’ve already extended, your code has been working great for me on SELECTS! :)

        ~John

      • John

        Martin,

        After many trials and tribulations, the nesting option worked for me. It’s not very elegant since I can’t loop, but it works…code snippet example:

        if (counter == 77)
        {

        [client2 connect:@”xxxxxx” username:@”xxxxxx” password:@”xxxxxx” database:@”xxxxxx” completion:^(BOOL success) {
        if (success)
        {
        [client2 execute:insertCommand1 completion:^(NSArray* results)
        {
        //Disconnect needs to be inside the inner most nested execute command
        [client2 disconnect];
        }];

        }

        }];
        }
        else
        {
        NSLog(@”2 unit test underway!!”);
        [client2 connect:@”xxxxxx” username:@”xxxxxx” password:@”xxxxxx” database:@”FleetUnitInfo” completion:^(BOOL success) {
        if (success)
        {
        [client2 execute:insertCommand1 completion:^(NSArray* results)
        {

        [client2 execute:insertCommand2 completion:^(NSArray* results)
        {
        //Disconnect needs to be inside the inner most nested execute command
        [client2 disconnect];
        }];

        }];
        }

        }];

        }

        Thanks again for your help!

        John

      • John

        Ugh, i forgot to change my ip address and pwd/usr for the 2nd connect. Would you mind moderating that for me please? Thanks!

        John.

  25. Peter Kristensen

    Great work with this SQL client. It is really helping me out.
    Unfortunately I am getting an error and I can’t seem to work out why.

    2014-02-10 14:39:15.912 TM Partner[1866:70b] Connection opened
    2014-02-10 14:39:16.014 TM Partner[1866:1303] *** Terminating app due to uncaught exception ‘Delegate must be set to an NSObject that implements the SQLClientDelegate protocol’

    • Peter, your class must implement the following SQLClientDelegate method:

      - (void)error:(NSString *)error code:(int)code severity:(int)severity

      • Peter Kristensen

        I don’t know why my question got so short all of a sudden. But I wrote that I already had implemented that method.
        The header:
        @interface TMPService : NSObject

        The implementation:
        @implementation TMPService

        //Required
        – (void)error:(NSString *)error code:(int)code severity:(int)severity

        {
        NSLog(@”Error #%d: %@ (Severity %d)”, code, error, severity);
        }

        If I remove the error method the compiler says: “Method ‘error:code:serverity:’ in protocol not implemented”

        Am i forgetting something else?

      • Peter Kristensen

        The header:
        @interface TMPService : NSObject

      • Peter Kristensen

        Okay it removes the “” when I post…
        The header:
        @interface TMPService : NSObject **SQLClientDelegate**

  26. Justin Boyd

    Thanks so much for this! Was hoping you could answer something for me.

    All the examples I’ve seen are just of printing results to NSLog.

    I would like to get the array of results out of the block and into my next view controller and have no idea on where to start. Should I change the execute blocks in SQLClient to start passing back values? Or is this something to do with overwriting the callback queue. I’m so lost.

    Obviously a newbie question, I’m sure. Thanks again.

  27. Ram

    Hello Martin,

    I am getting crashes again in this library. I have also create a issue at github. So please sort out this ASAP. I am at last stage of app.

    Thanks
    Ram

  28. Marco Luques

    Hello Martin,

    Thank you,

    You code works very well in ios7

    It will help-me so much.

  29. Francisco

    Hello Martin,

    I have an app that executes a query every 5 seconds, at some point of executing a query it crashes by a “bad access error” in the methods connection failure and connection success, it appears that at some point the login variable gets freed and the bad access exception is thrown crashing my app. I haven’t been able to track this error to a possible fix.
    Another thing to mention, is that this happens on the iOS simulator, i have not been able to test the app in a real device.

    Regards,
    Francisco.

  30. Ram

    Hi Martin,
    Please reply on Github for sunarc post

  31. Nick

    Martin, using your code as an example, I’ve rebuilt this project from scratch and solved a few of the issues I’ve run into with your library. My library allows for multiple objects to be instantiated, and allows multiple concurrent connections. Please take a look: https://github.com/nhgrif/SQLConnect

    • That is awesome! I’ve been swamped at my startup but I look forward to incorporating your contributions soon! Cheers.

    • Heiko

      Hi Nick,

      have you also solved the 256 character text limitation?

      • Nick

        Not exactly. When I try grabbing a text column, it limits me to 256 characters. If I grab a varchar column, I’m limited to the number of characters the varchar column is defined as. The best I’ve been able to do regarding this is CAST(someTextColumn as varchar(8000)) which lets me grab the first 8000 characters. I will continue to look for better solutions in the Objective-C code, but for right now, I’m resorting to SQL-magic.

    • Alix

      I have an issue EXC_BAD_ACCESS on dbconnect(login,…) on the first time i call [client connect]
      Do you have any suggestion on this?

  32. Serg

    Very nice, thanks for efforts to put everything together. I been searching for something like this for quite a while.

  33. heikobr@web.de

    Hi,

    this is a great thing. I have tried a bit with the project. But I am getting an error when the text in a field is bigger than 256 characters. Is there any solution for this?

  34. Heiko

    Hi Nick,

    thank you very much. The CAST(someTextColumn as varchar(8000)) works great. But how do I write data back in the same way? So I need to convert a nsstring to varchar(8000). How can I do this?

    Thank you

  35. Mat

    Hey Nick,

    Thanks for putting this together, it help me out a lot especially since I’m a beginner. I just had one question. By “native” do you mean that a user will not be able to connect to the sql server if they are not connected to a specific network? I am asking this because I created an iOS app for my company that has an internal network and I was successfully able to connect to their MS SQL server on my machine and retrieve data. However, when I tested the app on a device with a different wi-fi network, the app could not connect. Does this mean that I need to create a web service layer in order to achieve connection from any network?

    Any help would be much appreciated.

    Thank you!

  36. Alix

    Hello Martin,

    I run your sample and get error on line:
    //Connect to database server
    if ((connection = dbopen(login, [self.host UTF8String])) == NULL)

    Thread EXC_BAD_ACCESS (code = 1, address = 0x0)3:

    would you help me investigate?

  37. Serg

    Ok, IMHO time to put some salt here. I found this stuff because I was searching the means to make an application in iOS that can communicate with MS SQL. I was not aware that free TDS stuff exists (relatively new to i-developing) and thought that author actually compile something that allow connect to MS SQL from iOS. But this is not the case – author simply “wrap” existing TDS libraries to ugly Objective-C implementation. So now instead of using native C string (only means to communicate to TDS libraries) one has to continuously confer them to ugly NSString keeping in mind that some still has to be true C string. Moreover because everything encapsulated it became incredibly difficult to share result. Not only that – things happens asynchronously, so you cannot have access to results unless you doing very tricky programming. Yes, demo code looks really slick, but may I ask author what REAL advantage of all this encapsulation in REAL word? I an afraid only burden. I use all this Objective-C cr@p only where I need it – i.e. interface with user. All my other stuff written in C and lets me tell you it is much easier to do so, debug so and it resulted faster and smaller code. Your approach of course may be different and you may enjoy doing everything in “pure” Objective-C just for sack of it.
    I still like thank author to put this together – as least it allow me to find what I need. But I totally disagree with approach and prefer to use DB library as it meant to be use – i.e. in C environment. Everything under my own control, I can open connection and left it open, I can fetch rows whenever I can and I can use any datatypes simply and nicely without any restriction. Reference for those who interested is here http://technet.microsoft.com/en-us/library/aa936982(v=sql.80).aspx

    Sorry for negative post and good luck in your encapsulation.

  38. Sterling Hackley

    For everyone dealing with the 256+ character limitations, another “solution” is you can override the column size for a specific data type in SQLClient.m. It’s not the best solution but it can work for individual cases.

    On line 201 in SQLClient.m after:

    if (pcol->type != SYBCHAR && pcol->type != SYBTEXT)
    pcol->size = dbwillconvert(pcol->type, SYBCHAR);

    you can add:

    if(pcol->type == SYBIMAGE) //Or SYBBINARY, SYBVARBINARY, etc
    pcol->size = 8000;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 25 other followers

%d bloggers like this: