Can't insert TEXT into SQLITE table

Question

Can't insert TEXT into SQLITE table

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

I'm having a heck of a time inserting a string into a SQLLITE table in my iPhone app. I've tried several different methods, and always the app just crashes and exits with no error msg in the log. I'm very much a noob, can someone please help me out, I feel like this shouldn't be so hard! :(

Here's the code:

//create db

var db = Ti.Database.open('happyClassDB');

//create tables

db.execute('CREATE TABLE IF NOT EXISTS room (roomID INTEGER PRIMARY KEY, rows INTEGER, columns INTEGER, seats INTEGER, roomName TEXT)');

//insert dummy data

db.execute('INSERT INTO room (rows,columns,seats,roomName) VALUES (?,?,?,?)',4,5,20,'room name');

2 Answers

Your Answer

Think you can help? Login to answer this question!

Accepted Answer

Accepted Answer

David

The error table room has no column named roomName occurs because the database already exists on your system but does not contain the roomName column. Hence, this would not have happened on my system when I tested the code. I have modified it, and the following should work in your environment:

var db = Ti.Database.open('happyClassDB');
 
db.execute('DROP TABLE IF EXISTS room');
db.execute('CREATE TABLE IF NOT EXISTS room (roomID INTEGER PRIMARY KEY, rows INTEGER, columns INTEGER, seats INTEGER, roomName TEXT)');
// for demonstration purposes, this will drop and recreate the room table
// before inserting the following records, to ensure roomID is unique, 
// and hence insert does not fail
 
db.execute('INSERT INTO room (roomID,rows,columns,seats,roomName) VALUES (?,?,?,?,?)',1,4,5,20,'Honeymoon Suite');
db.execute('INSERT INTO room (roomID,rows,columns,seats,roomName) VALUES (?,?,?,?,?)',2,4,5,20,'Royal Suite');
db.execute('INSERT INTO room (roomID,rows,columns,seats,roomName) VALUES (?,?,?,?,?)',3,4,5,20,'Presidential Suite');
 
 
var queryRS = db.execute('SELECT roomID,roomName FROM room');
while (queryRS.isValidRow())
{
  var roomID = queryRS.fieldByName('roomID');
  var roomName = queryRS.fieldByName('roomName');
  Ti.API.info('Room with ID ' + roomID + ' is called ' + roomName);
  queryRS.next();
}
queryRS.close();
db.close();

Regarding your assumption:

I omitted this field because it's primary key, shouldn't
it auto-populate with the next incremental integer

...no, the definition of a primary key is one that simply uniquely identifies a record in a relational database table; nothing more. The feature you mean is AUTOINCREMENT. Hence:

var db = Ti.Database.open('happyClassDB');
 
db.execute('DROP TABLE IF EXISTS room');
db.execute('CREATE TABLE IF NOT EXISTS room (roomID INTEGER PRIMARY KEY AUTOINCREMENT, rows INTEGER, columns INTEGER, seats INTEGER, roomName TEXT)');
// for demonstration purposes, this will drop and recreate the room table
// before inserting the following records, to ensure roomID is unique, 
// and hence insert does not fail
 
db.execute('INSERT INTO room (rows,columns,seats,roomName) VALUES (?,?,?,?)',4,5,20,'Honeymoon Suite');
db.execute('INSERT INTO room (rows,columns,seats,roomName) VALUES (?,?,?,?)',4,5,20,'Royal Suite');
db.execute('INSERT INTO room (rows,columns,seats,roomName) VALUES (?,?,?,?)',4,5,20,'Presidential Suite');
 
var queryRS = db.execute('SELECT roomID,roomName FROM room');
while (queryRS.isValidRow())
{
  var roomID = queryRS.fieldByName('roomID');
  var roomName = queryRS.fieldByName('roomName');
  Ti.API.info('Room with ID ' + roomID + ' is called ' + roomName);
  queryRS.next();
}
queryRS.close();
db.close();

Hope this helps

Answer 2

Hi David

Your INSERT statement is failing because you have omitted the roomID mandatory field. You should see an error generated in the Titanium Developer info log to reflect this mistake. Try this:

var db = Ti.Database.open('happyClassDB');
db.execute('CREATE TABLE IF NOT EXISTS room (roomID INTEGER PRIMARY KEY, rows INTEGER, columns INTEGER, seats INTEGER, roomName TEXT)');
 
db.execute('DELETE FROM room');
// for demonstration purposes, this will delete all data in the room table
// before inserting the following record (to ensure roomID is unique, 
// and hence insert does not fail)
 
db.execute('INSERT INTO room (roomID,rows,columns,seats,roomName) VALUES (?,?,?,?,?)',1,4,5,20,'room name');

Hope this helps