create database structure using .sql file and FMDB

I have a .sql file which I have exported from MySQL. I am trying to add that table structure into SQLite using FMDB framework but it is not executed and always return false. Here is the structure:

CREATE TABLE IF NOT EXISTS `admin` (
`admin_id` tinyint(4) NOT NULL AUTO_INCREMENT,
`first_name` varchar(30) NOT NULL,
`last_name` varchar(30) NOT NULL,
`email` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`date` datetime DEFAULT NULL,
`status` tinyint(2) NOT NULL,
PRIMARY KEY (`admin_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

What I am doing wrong here ?

Solution

That SQL doesn't conform to SQLite's CREATE TABLE syntax as documented online. If you look at the FMDB lastErrorMessage method (which is analogous to sqlite3_errmsg), it will report the specifics regarding the error.

Notably, the AUTO_INCREMENT syntax is not correct. Even if you fix that, the ENGINE and DEFAULT CHARSET options won't be recognized, either. At the very minimum, you'd have to try something like:

NSString *sql = @"CREATE TABLE IF NOT EXISTS `admin` ("
                "`admin_id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
                "`first_name` varchar(30) NOT NULL,"
                "`last_name` varchar(30) NOT NULL,"
                "`email` varchar(30) NOT NULL,"
                "`password` varchar(30) NOT NULL,"
                "`date` datetime DEFAULT NULL,"
                "`status` tinyint(2) NOT NULL"
                ")";

Or, given that SQLite won't understand some of those data types (though with SQLite's "dynamic type system", it doesn't really matter), you might want to replace varchar(30) with TEXT and tinyint(2) with INTEGER and datetime with whatever format you plan on storing dates. See Data Types discussion.