Use the MySQL client library in iOS project

July 9th, 2010

A code snippet shows the MySQL client library used in an iPhone project. You may see how the library was built in my last post.

MySQL C API Documentation

#import "mysql.h"

- (void)viewDidLoad {
    [super viewDidLoad];

    // Uncomment the following line to display an Edit button in the navigation bar for this view controller.
    // self.navigationItem.rightBarButtonItem = self.editButtonItem;

	rows = [[NSMutableArray alloc] initWithCapacity:0];

	MYSQL *sql = mysql_init( NULL );
	mysql_options(sql, MYSQL_READ_DEFAULT_GROUP, "LibMySQL");
	sql = mysql_real_connect(sql, "www.iosplace.com", "iospla5_db2gt", "*", "iospla5_db2", 3306, NULL, 0);
	if (sql != NULL) {
		int level = self.navigationController.viewControllers.count;
		if (level == 1) {
			self.title = @"Movies";
			mysql_query(sql, "select Name from Movie");
		}
		else if (level == 2) {
			char queryStr[1024];
			strcpy(queryStr, "select Material.Name, Url from Material \
				   join Movie on (Movie.id=Material.Movie_id) \
				   where Movie.Name=\"");
			strcat(queryStr, [self.title UTF8String]);
			strcat(queryStr, "\"");
			mysql_query(sql, queryStr);
		}

		MYSQL_RES *qResult = mysql_store_result(sql);
		if (qResult != NULL) {
			MYSQL_ROW row;
			while (row = mysql_fetch_row(qResult)) {
				if (level == 1) {
					[rows addObject:[NSArray arrayWithObjects:
									 [NSString stringWithCString:row[0] encoding:NSISOLatin1StringEncoding],
									 nil]];
				}
				else if (level == 2) {
					[rows addObject:[NSArray arrayWithObjects:
									 [NSString stringWithCString:row[0] encoding:NSISOLatin1StringEncoding],
									 [NSString stringWithCString:row[1] encoding:NSISOLatin1StringEncoding],
									 nil]];
				}
			}
			mysql_free_result(qResult);
		}
		mysql_close(sql);
		mysql_server_end();
	}
}

23 Responses to “Use the MySQL client library in iOS project”

  1. Christian says:

    Hello,

    Great posts – I have been able to successfully compile the libmysql.dylib and was able to compile your TestApp without any issues. I am however having an issue integrating the compiled libmysql.dylib into an iPhone project.

    I believe I have added the framework correctly, but I keep get an error when attempting to import mysql.h?

    I am sure I missed a simple step and will thump my head on my desk when I see what the issue was, but any suggestions would be greatly appreciated.

    Thanks again for the great posts.

  2. Wei says:

    A common problem when import any c header file in a .m file is that the source code is compiled as objective-c and the compiler doesn’t recognize some c syntax. You may give the source file .mm extension, which tells the compiler it’s mixed c and objective-c. And/Or select the source file, click on the ‘Info’ button (File->Get Info), under the General tab, verify that ‘sourcecode.cpp.objcpp’ is selected from the File Type drop down list.

    If that’s not it, tell us what’s the first error you got.

  3. AugSoft Tom says:

    I just posted on your other thread about the library build, but I wanted to post here again to ask how can we would be able to sell copies of Oracle’s MySQL client library embedded into our apps through Apple’s AppStore without paying Oracle an up-front fee and/or an additional fee for each copy? Doesn’t Oracle’s license for MySQL client libraries prohibit unpaid use by closed source commercial applications? ..and entangling those MySQL proprietary C API calls into your app sort of renders your code in a way that would be hard to change if they pulled the app and demanded compensation.

    Our solution was to create a free ODBC library for iOS that talks to server-side ODBC drivers installed on a central Windows PC (or VM). This means the stock MySQL ODBC drivers and libraries are only be installed on a central PC (as they are with any website) and are not being distributed with each copy of the app. We charge a one-time fee about the same as a TV set for the Windows background service (called ODBC Router) that allows the server-side ODBC drivers to be used remotely by the Apple mobile devices, Macs, Linux web servers and Windows boxes. More info at http://ODBCrouter.com/

  4. Rid says:

    Thanks for your help! Must it add all the head files to the project while using the SQL library?

  5. Rid says:

    And another question, how to use traditional chinese in sql query and get it in return result? thx.

  6. Wei says:

    @Rid
    You typically just include mysql.h where most commonly used function prototypes defined.
    I guess you can use any language in sql query as long as the encoding of the query matches your database setup, typically that would be unicode with utf8 encoding. I don’t have concrete experience on this though.

  7. Rid says:

    @Wei
    Infos Show that some files not found when I build the xCode project using cMake in Terminal.And then I successfully use the project including all the head files to build the sql library.However,It have many head files missing errors in “mysql.h” if I just include the “mysql.h”. And It work when I include all the head files.

  8. Wei says:

    @Rid
    If the compiler says a header file missing, that means it cannot find it on any of your specified searching path. You set the header searching path in the build settings panel in Xcode. You may refer to the Xcode help for more info. If add all the head files in the project works, then that’s fine. It’s really just giving the compiler a clue where to find those files. It doesn’t effect the build result.

  9. AugSoft Tom says:

    Hey folks, Apple has reportedly just complied with a GPL “Takedown Request” for the popular “VLC for iPhone and iPad” app brought by Rémi Deni-Courmont (not the official VideoLAN Client project!) who alleged the App Store licenses aren’t compatible with GPL projects. This is a landmark case being widely reported by all the tech blogs because of its implications. In this case, it probably points to a major reason why MySQL on iOS will poison your app, even if the app is given away completely for free (and advertising-free) and even when the legal objection does not originate from the project responsible for the code. The concern is that the GNU freedom people may scan the app binaries from the App Store and find the tell-tale entry points that reveal it to contain the GPL code and then create legal problems for the developer and Apple. Disclaimer: Our company has started producing a free (and royalty-free) ODBC API for iOS which ties Apple’s devices into our server-based ODBC driver system which, although inexpensive (like a TV set-like), is not free. That said, not on here spreading FUD, just not wanting to see anyone committing unintentional civil violations. More info here: http://stackoverflow.com/questions/1260591/about-mysql-gpl-and-lgpl

  10. Brian says:

    thanks for your good posting. I have a few questions. I successfully compiled the MySQL as above messages. and then, I created new project in xcode with View-based application. also, add more lines above example to testViewController.m. Ofcourse, I verified the sourcecode.cpp.objcpp in already and changed the variable to connect the MySQL like hostname, user , password etc.

    After build and run, I got a message “row was not declared in this scope.”

    If you don’t mind, Please let me know what should i do more?

    thanks.

  11. Lalith says:

    Will Apple Approve an app which uses the mysql api ?? has anyone tried it by any chance ? :?:

  12. ZHANG says:

    it is wrong in the place “mysql_init(NULL)”,it is break the app.
    Can you email your demo code to me? Thanck you!

  13. Mikael says:

    It doesn’t work well for me. Although the initial load works just fine, I keep getting the following nasty message:

    objc[893]: Object 0x445dc0 of class __NSCFString autoreleased with no pool in place – just leaking – break on objc_autoreleaseNoPool() to debug and crashes my very small app.

    I’ve definitely isolated the cause to mysql, which is loaded through a static .a library. Any tips before I start digging into the code?

  14. Bernard says:

    I see the exact problem as @Mikael. NSAutoReleasePool errors after a call to mysql_init().

    If you replace the libmysqlclient.a with a pre-built binary downloaded from the MySQL web site (the MySQLConnector/C page), or even one you can build yourself using the full MySQL 5.5.21 source (cd /pathToMySQL5.5.21Source;cmake -G Xcode;tweak my_net.h, my_global.h and my_config.h;xcodebuild -project mysql.xcodeproj -target mysqlclient -configuration Release -sdk macosx10.7 -mmacosx-version-min=10.7 ONLY_ACTIVE_ARCH=NO ARCHS=”i386 x86_64″), mysql_init() doesn’t crash. So the Cocoa code itself is OK.

    mysql_init() only crashes with the libmysqlclient.a built from the MySQLConnector/C source, on either iOS or MacOSX. So there’s something still missing from the build process described here.

  15. For those who are getting error about library being crashed just after init time of library, just befora screen loads, it’s because a BUG amoung CMAKE, library and LION 10.7, just remove all the references about pthreads on the source of CMake Check List.txt and all will be fine, I made my own library for release and debug, and everything is running fine now, except for sometimes crashing on BLOB retrieval, but it only few times ocurring, and I’m still trying to debug, if someone want to help and share informations, please, contact me.

  16. Kostapppas says:

    can you make more explicite instructions on how we remove all the references about pthreads on the source of CMake Check List.txt ?.

    I also crash after the mysql_init.

  17. Arni says:

    Hi. After compiling library and testing the code I got error:
    Lost connection to MySQL server at ‘reading initial communication packet’, system error: 54
    What it’s mean?
    Thanks.

  18. darkfire says:

    I’ve followed the way (or at least guessed the way) that Josenildo did for the pthread, and created two static libraries (one for simulator and one for device), both work. Just dunno why when I made them as a fat library, the NSAutoReleasePool error appeared again. but anyway, I can still separate the linkage of these two libraries when building the app.

    So, you may also tried my method.
    1. “Tweak” the my_net.h, my_global.h and my_config.h as described.

    2. In “my_config.h” line 292, comment the #define THREAD 1, as we are not going to use pthread:
    /*#define THREAD 1*/

    And to skip the compilation errors, do the followings:
    3. In “mf_keycache.c”, add a #ifdef block for line 1784-1814:
    #ifdef THREAD
    /* ……… */
    thread= my_thread_var;
    thread->opt_info= (void *) hash_link;
    :
    :
    goto restart;
    #endif

    4. In “lf_alloc-pin.c”, comment line 191:
    /* el->stack_ends_here= & my_thread_var->stack_ends_here; */

    5. In “waiting_threads.c”, comment line 312:
    /*pthread_mutex_init(&rc->lock.mutex, MY_MUTEX_INIT_FAST);*/

    6. do the “xcodebuild” as described for iphoneos5.1 and iphonesimulator5.1.

    7. Add the “libmysqlclient_simulator.a” or “libmysqlclient_device.a” to the iOS app project (depends on running in simulator or device).

    That’s it, try it out and good luck !!

  19. Greetings! Quick question that’s entirely off topic. Do you know how to make your site mobile friendly? My website looks weird when browsing from my apple iphone. I’m trying to find a
    template or plugin that might be able to fix this issue.

    If you have any recommendations, please share. Thanks!

  20. Felix B. says:

    Hi everybody,

    i got this error code.
    Can you help me?

    dyld: Library not loaded: libmysql.16.dylib
    Referenced from: /Users/gicom/Library/Application Support/iPhone Simulator/5.1/Applications/D596A114-C8F3-4938-A5BF-EF686D581CB1/Zeiterfassung_1.0.app/Zeiterfassung_1.0
    Reason: image not found

  21. Josenildo Matos says:

    So, I finnaly made a nutshell for doind the fat library, and I’m uploading it right now, soon I’ll post here the link for download both versions Devel and Release in Fat Library for use with any version you need without change library when you compile for simulator or device.

  22. Felix B. says:

    Whrere did you have post this link?

  23. Felix B. says:

    Why it is so damn diffelcult to bind this library? Isn’t there a easier way to do this?

RSS feed for comments on this post. And trackBack URL.

Leave a Reply