Creating database - Syntax error

You must Login before you can answer or comment on any questions.

I'm trying to build a database, I'm really struggling though, I keep getting the following error:

[ERROR] invalid SQL statement. Error Domain=com.plausiblelabs.pldatabase Code=3 "An error occured parsing the provided SQL statement." UserInfo=0x99567d0 {com.plausiblelabs.pldatabase.error.vendor.code=26, NSLocalizedDescription=An error occured parsing the provided SQL statement., com.plausiblelabs.pldatabase.error.query.string=CREATE TABLE IF NOT EXISTS menus (menu_id INTEGER,section VARCHAR(255),dish VARCHAR(255),price NUMERIC NOT NULL,original_id INTEGER,PRIMARY KEY  (menu_id));CREATE TABLE IF NOT EXISTS pubs (pub_id INTEGER,name VARCHAR(255),location VARCHAR(255),distance NUMERIC,geoX NUMERIC,geoY NUMERIC,original_id INTEGER,PRIMARY KEY  (pub_id));CREATE TABLE IF NOT EXISTS staff (staff_id INTEGER,name VARCHAR(255),job VARCHAR(255),desc TEXT(1000),original_id INTEGER,section VARCHAR(255),PRIMARY KEY  (staff_id));, com.plausiblelabs.pldatabase.error.vendor.string=file is encrypted or is not a database}  in -[TiDatabaseProxy execute:] (TiDatabaseProxy.m:186)
[ERROR] Script Error = invalid SQL statement. Error Domain=com.plausiblelabs.pldatabase Code=3 "An error occured parsing the provided SQL statement." UserInfo=0x99567d0 {com.plausiblelabs.pldatabase.error.vendor.code=26, NSLocalizedDescription=An error occured parsing the provided SQL statement., com.plausiblelabs.pldatabase.error.query.string=CREATE TABLE IF NOT EXISTS menus (menu_id INTEGER,section VARCHAR(255),dish VARCHAR(255),price NUMERIC NOT NULL,original_id INTEGER,PRIMARY KEY  (menu_id));CREATE TABLE IF NOT EXISTS pubs (pub_id INTEGER,name VARCHAR(255),location VARCHAR(255),distance NUMERIC,geoX NUMERIC,geoY NUMERIC,original_id INTEGER,PRIMARY KEY  (pub_id));CREATE TABLE IF NOT EXISTS staff (staff_id INTEGER,name VARCHAR(255),job VARCHAR(255),desc TEXT(1000),original_id INTEGER,section VARCHAR(255),PRIMARY KEY  (staff_id));, com.plausiblelabs.pldatabase.error.vendor.string=file is encrypted or is not a database}  in -[TiDatabaseProxy execute:] (TiDatabaseProxy.m:186) (unknown file)
The code I'm using to do this is:
db = Titanium.Database.open('db');
 
db.execute('CREATE TABLE IF NOT EXISTS menus ('+
  'menu_id INTEGER,'+
  'section VARCHAR(255),'+
  'dish VARCHAR(255),'+
  'price NUMERIC NOT NULL,'+
  'original_id INTEGER,'+
  'PRIMARY KEY  (menu_id)'+
');'+
''+
'CREATE TABLE IF NOT EXISTS pubs ('+
  'pub_id INTEGER,'+
  'name VARCHAR(255),'+
  'location VARCHAR(255),'+
  'distance NUMERIC,'+
  'geoX NUMERIC,'+
  'geoY NUMERIC,'+
  'original_id INTEGER,'+
  'PRIMARY KEY  (pub_id)'+
');'+
''+
'CREATE TABLE IF NOT EXISTS staff ('+
  'staff_id INTEGER,'+
  'name VARCHAR(255),'+
  'job VARCHAR(255),'+
  'desc TEXT(1000),'+
  'original_id INTEGER,'+
  'section VARCHAR(255),'+
  'PRIMARY KEY  (staff_id)'+
');');
Any help is greatly appreciated. Been running about in circles with this for a while now.

Thanks

3 Answers

Separate your CREATE statements into individual db.execute() commands. Also, some of these data types do not exist in SQLite, such as VARCHAR, NUMERIC, etc.

Check out the Working with SQLite Databases Guide to get more info on data types and how to structure your statements.

— answered 11 months ago by Adam Paxton
answer permalink
4 Comments

  • Still the same I'm afraid. Please see amended code below:

    db = Titanium.Database.open('db');
 
db.execute('CREATE TABLE IF NOT EXISTS menus ('+
  'menu_id INTEGER PRIMARY KEY,'+
  'section TEXT,'+
  'dish TEXT,'+
  'price REAL,'+
  'original_id INTEGER'+
');');
 
db.execute('CREATE TABLE IF NOT EXISTS pubs ('+
  'pub_id INTEGER PRIMARY KEY,'+
  'name TEXT,'+
  'location TEXT,'+
  'distance REAL,'+
  'geoX REAL,'+
  'geoY REAL,'+
  'original_id INTEGER'+
');');
 
db.execute('CREATE TABLE IF NOT EXISTS staff ('+
  'staff_id INTEGER PRIMARY KEY,'+
  'name TEXT,'+
  'job TEXT,'+
  'desc TEXT,'+
  'original_id INTEGER,'+
  'section TEXT'+
');');
    I'm surprised that SQLite doesn't have VARCHAR, as I took that from SQLite's website and documentation directly. I guess the devices/Ti must have a different implementation.

    Thanks for the response, hope you can help further.

    — commented 11 months ago by Mike Griffiths

  • I'm really confused here. I've striped it right back with the following code:

    db.execute('CREATE TABLE menus ('+
  'menu_id INTEGER '+
');');
    Which gives me this error:
    [ERROR] invalid SQL statement. Error Domain=com.plausiblelabs.pldatabase Code=3 "An error occured parsing the provided SQL statement." UserInfo=0x8d729b0 {com.plausiblelabs.pldatabase.error.vendor.code=26, NSLocalizedDescription=An error occured parsing the provided SQL statement., com.plausiblelabs.pldatabase.error.query.string=CREATE TABLE menus (menu_id INTEGER );, com.plausiblelabs.pldatabase.error.vendor.string=file is encrypted or is not a database}  in -[TiDatabaseProxy execute:] (TiDatabaseProxy.m:186)

    — commented 11 months ago by Mike Griffiths

  • This works for me, testing on iOS Simulator 5.1, Ti SDK 2.1.0.GA: 

    db = Titanium.Database.open('db');
 
db.execute('DROP TABLE IF EXISTS menus');
db.execute('DROP TABLE IF EXISTS pubs');
db.execute('DROP TABLE IF EXISTS staff');
 
db.execute('CREATE TABLE IF NOT EXISTS menus ('+
  'menu_id INTEGER PRIMARY KEY,'+
  'section TEXT,'+
  'dish TEXT,'+
  'price REAL,'+
  'original_id INTEGER'+
');');
 
db.execute('CREATE TABLE IF NOT EXISTS pubs ('+
  'pub_id INTEGER PRIMARY KEY,'+
  'name TEXT,'+
  'location TEXT,'+
  'distance REAL,'+
  'geoX REAL,'+
  'geoY REAL,'+
  'original_id INTEGER'+
');');
 
db.execute('CREATE TABLE IF NOT EXISTS staff ('+
  'staff_id INTEGER PRIMARY KEY,'+
  'name TEXT,'+
  'job TEXT,'+
  'desc TEXT,'+
  'original_id INTEGER,'+
  'section TEXT'+
');');
 
var tables = db.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;");
 
while (tables.isValidRow()){
    Ti.API.info(tables.fieldByName('name'));
    tables.next();
}
tables.close();
 
db.close();
    I am checking the tables using the sqlite_master table as explained here.

    As for supported datatypes, I am looking at this doc.

    — commented 11 months ago by Adam Paxton

  • Show 1 more comment

I have fixed the issue. This is caused by a old erroneous database being present on the device. Previously I had tried loading a db and called it 'db', so when creating a new database Appcelerator/SQLite reports errors withe SQL statements, when in reality the issue is with the database itself rather than the code.

I have just renamed the database and everything works as expected.

I have fixed the issue. This is caused by a old erroneous database being present on the device. Previously I had tried loading a db and called it 'db', so when creating a new database Appcelerator/SQLite reports errors withe SQL statements, when in reality the issue is with the database itself rather than the code.

I have just renamed the database and everything works as expected.

Your Answer

Think you can help? Login to answer this question!