SQLite: Creating new Column?

[aidmm]

I've been trying to create a new column in a table I just created. I can't figure it out! I've read a couple different sites about SQL and SQLite... I've also read through the included documentation that came with the SQLite object. I can't figure out how to insert a column, though.

anyone know how?

thanks.

[DavidN]

I'm guessing that the SQLite object just uses SQL commands in strings, is that right?

ALTER TABLE table_name ADD column_name datatype

[aidmm]

When I try using ALTER TABLE, the following error shows up:

near "ALTER": syntax error


There are some SQLite commands that are ommited from the MMF object...

[DavidN]

SQL isn't particularly well known for its helpful error messages, so it could really be anything...

[Corentin]

I never tried, it but alter table should work. When you have such a problem, use any other tool (ie not a tool designed by you) to test if it works (i remember there was a list of such tools on SQlite website, maybe in the wiki).

I didn't try adding columns, but it's supported by SQLite it must be supported in SQLite Object too. Remember this when adding a column (from official documentation) :
* The column may not have a PRIMARY KEY or UNIQUE constraint.
* The column may not have a default value of CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.
* If a NOT NULL constraint is specified, then the column must have a default value other than NULL.

[aidmm]

This is a quote from the "SQL Features That SQLite Does Not Implement" page of the MMF SQLite object documentation:

"Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted."

To me it's confusing. I've tried entering the following in the sample app that comes with the object, but it doesn't work:

ALTER TABLE Person ADD City varchar(30)


It's looking to me like the ALTER TABLE feature is entirely unsupported.

[JSJ]

This appears to be true, ALTER TABLE is non functional. This is in the core SQLite engine, unfortunately. The SQLDB object embeds version 2.8.x of SQlite, so the documentation regarding ALTER TABLE must apply to version 3.1.x of SQlite only (this makes me think so http://www.sqlite.org/lang_altertable.html).

So, for now we have to live with this. I originally tried to build this object with version 3.1.x, but ran into massive errors because 3.1.x went multi-byte to support unicode languages, etc. So I had to fall back on 2.8.x to get it to compile with MMF SDK.

So best thing is to use a workaround:
Assuming current table TABLE1 with columns X, Y
you want TABLE1 to have columns X,Y,Z

Workaround:

// create a new table with all desired columns
1) CREATE TABLE TABLE2 (X,Y,Z)

// fill the new table with all data from the original, plus zero for the new column for all rows
2) INSERT INTO TABLE2 (X,Y,0) SELECT X,Y FROM TABLE1

// delete the original table
3) DROP TABLE TABLE1

// recreate the original table with the new columns
4) CREATE TABLE TABLE1 (X,Y,Z)

// copy data from the new table back to the original name
5) INSERT INTO TABLE1 SELECT X,Y,Z FROM TABLE2

This worked in my test, the SQL should be very close.

Hope this helps.
JSJ

[JSJ]

Sorry step 2 should be more like:

INSERT INTO TABLE2 SELECT (X,Y,0) FROM TABLE1

but I think you get the idea

[aidmm]

Thanks for the confirmation and the workaround, JSJ. I've tried to mess with the database object in MMF in the past, and since I knew nothing about database "stuff" I got nowhere. I saw your SQLite object and gave it a shot. The syntax was pretty easy to learn (for me) and I've made my first simple record-keeping app with it. Thanks for making this available!