CODEWITHSUNDEEP
androidsqldatabasesqlite
doorbash
doorbash

Reputation: 362

Error in SQlite syntax i dont know whats wrong

I wonder what's wrong with my sql syntax.Its working on mysql but for sqlite gives me error.

CREATE TABLE IF NOT EXISTS `points` (
                                     `id` int(11) NOT NULL AUTO_INCREMENT, 
                                     `name` varchar(50) CHARACTER SET latin1 NOT NULL, 
                                     `longitude` double NOT NULL, 
                                     `latitude` double NOT NULL, 
                                     `radius` double NOT NULL, 
                                     `image` blob NOT NULL, 
                                     PRIMARY KEY (`id`)
                                    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci AUTO_INCREMENT=4;

INSERT INTO `points` (`id`, `name`, `longitude`, `latitude`, `radius`, `image`) 
VALUES      (1, 'Isfahan, Iran', 51.678314, 32.65036, 0.064486, 
             0xffd8ffe000104a46494600010001009600960000fffe001f4c45414420546563686e6f6c6f6769657320496e632e2056312e303100ffdb008400100b0c0e0c0a100e0d0e1211101318281a181616183123251d283b343e3d3a34393841495d4f414558463839516f52586063696a693f4e737b72667a5d676964011112121815182f1a1a2f644339436464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464ffc4003000000203010000000000000000000000000001020304050101010101010100000000000000000000000102030405ffc20011

Upvotes: 1

Views: 1134

Answers (1)

nawfal
nawfal

Reputation: 73163

  1. It should be AUTOINCREMENT as one word.

  2. You dont require this statement: ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci AUTO_INCREMENT=4; which is MySQL specific. Further more, if you are setting the autoincrement value to start from 5, then you shouldn't be inserting an id of value equal to 1.

  3. You do not seem to have a closing bracket after the insert statement.

You should instead try: 

CREATE TABLE IF NOT EXISTS `points` (
                                     `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
                                     `name` varchar(50) CHARACTER SET latin1 NOT NULL, 
                                     `longitude` double NOT NULL, 
                                     `latitude` double NOT NULL, 
                                     `radius` double NOT NULL, 
                                     `image` blob NOT NULL
                                    );

INSERT INTO `points` (`id`, `name`, `longitude`, `latitude`, `radius`, `image`) 
VALUES      (1, 'Isfahan, Iran', 51.678314, 32.65036, 0.064486, 
             0xffd8ffe000104a46494600010001009600960000fffe001f4c45414420546563686e6f6c6f6769657320496e632e2056312e303100ffdb008400100b0c0e0c0a100e0d0e1211101318281a181616183123251d283b343e3d3a34393841495d4f414558463839516f52586063696a693f4e737b72667a5d676964011112121815182f1a1a2f644339436464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464ffc4003000000203010000000000000000000000000001020304050101010101010100000000000000000000000102030405ffc20011);

There could be more fixes required as well. Back tick though recognized by SQLite, is a MySQL specific quoting.

Upvotes: 3

Related Questions