Adding new fields to existing database

I would like to add one field to an existing database, but I dont know how to do it.
At present, when the app is launched the first time, it creates the following tables:

myDataBase.SQLExecute “create table mNames (vNames varchar, ID integer NOT NULL PRIMARY KEY)”
myDataBase.SQLExecute "create table mData (nameID varchar, clm0 varchar, clm1 varchar, clm2 varchar, " + _
“clm3 varchar, ID integer NOT NULL PRIMARY KEY)”

Now I would like to add a new field after “clm3 binary” of the second table, let us call it “clm4 binary”.
How to add it? And I would add it in such a way as to avoid adding “clm4 binary” each time the app is launched.

Normally, databases have a schemaversion. You can do something like

if schemaversion = 1 then
update table here
schemaversion = 2
end if

and so on. If your app doesn’t have that. Do a select on the new field. If that doesn’t work you know that you need to add the field.

To change a table check out “alter table”. See

After adding the following code I populate the listbox:

if listBox1.ColumnCount = 9 then//new column and datafiels needed
majoDataBase.SQLExecute “ALTER TABLE mData”
majoDataBase.SQLExecute “ADD clm4 binary”
listBox1.ColumnCount = 10
end if

The columns gets added, but adding a new record or modifying an old record I get a database error: clm14 doe not exists.
I even tried to alter the table when in the code that adds a new record.
Any idea?

I would put the alter table and add column on the same sqlexecute to be effective ?

majoDataBase.SQLExecute “ALTER TABLE mData ADD clm4 binary”
majoDataBase.SQLExecute “ALTER TABLE mData ADD COLUMN clm4 binary”

still result in the same error.

it seems you are using sqlite database, then binary does not exist you may use blob instead

Did you copy paste that error message?
Because it says clm14 instead of clm4.

[quote=236683:@Marco Hof]Did you copy paste that error message?
Because it says clm14 instead of clm4.[/quote]
good point …

…and in your first post you say that your Create query looks like this:

[quote]myDataBase.SQLExecute "create table mData (nameID varchar, clm0 varchar, clm1 varchar, clm2 varchar, " + _
“clm3 varchar, ID integer NOT NULL PRIMARY KEY)”[/quote]
and then you say:

There is no clm3 binary.
If you want to add an extra column that is the same as clm3, add another varchar field.

ALTER TABLE mData ADD COLUMN clm4 Varchar;

or for a Binary column:


However, if this is SQLite instead of MySQL (or similar), there is no Binary type (and Varchar’s are actually Text types). As Jean-Yves said, maybe you mean Blob instead.

first of all, I’m really sorry for some confusion I created when I mixed up some data, like clm14 instead of clm4 and so on. I guess I was under some strain.

Everything now works OK. Again, the fault was mine, since I foolishly attempted to add the column before connecting to the database.

As for using binary, the database is a SQLiteDatabase, where it seems binary is included. Or am I wrong?

Thank you all.

‘Officially’, SQLite uses only a few datatypes.
Integer, Real, Text Blob and Numeric (if I remember correctly).

If you do a create/alter table, it will cast many common datatypes you know from more traditional SQL DB’s.

So datatypes like SmallInt, BigInt TinyInt will all be handled as SQLite datatype ‘Integer’.
Datatypes like Text, Char, Varchar etc. all become SQLite datatype ‘Text’.
Doubles and floats become ‘Reals’
Boolean, Decimal, Dates etc. go all into ‘Numerics’.

In MySQL, Binary and Blob datatypes are different (mainly on the size and how they’re stored internally).
You didn’t get an error when specifying the column as a Binary so SQLite most likely recognized the name and made it a Blob.

No, I dint get any error using binary. Actually I used it since I saw it listed in recordSet.ColumnType.
But I realize that the recordSet.ColumnType list is not limited to SQLite

Here is some info on the SQLite datatype:
They call the whole casting thing ‘Type Affinity’. So whatever you throw at it, you don’t have to worry about it because it stores it internally in it’s own simple way. :slight_smile:

Thanks for the reference. I had indeed looked at but somehow I had missed the particular topic.
Now, if you dont mind, I have another question that I think is only related to aesthetics:

adding the new column, it is, of course, positioned after the last column (…, ID integer NOT NULL PRIMARY KEY, clm4 binary)"

and when a new user creates the new database, the code now is as follow:
myDataBase.SQLExecute "create table mData (nameID varchar, clm0 varchar, clm1 varchar, clm2 varchar, " + _
“clm3 varchar, ID integer NOT NULL PRIMARY KEY, clm4 binary)”

I’d have liked to have something like: (… clm3 varchar, clm4 binary, ID integer NOT NULL PRIMARY KEY)"
So, is it only a matter of aesthetics, and so to be left as it is? Or is there anything I should do?

I follow this conversation and I have a question…

If you have to read previous data base files (without this newly added column):

how do you deal with the two formats ?

I have myself to add two columns to an existing data base [SQLIte] and will have to make something to read previously created db files. On the original design, I already think at that and placed a “file version” column (set to 1).

Then you need to rename the old table, create a new table and copy the data back.

Here are the steps:

@Emile Schwarz

I found this in my snippets. Maybe it helps

A function to test if the column exists:

function dbColumnExists(dbase, tbl, col) local sql = "select * from "..tbl.." limit 1;" local stmt = dbase:prepare(sql) local tb = stmt:get_names() local found = false for v = 1, stmt:columns() do print(tb[v]); if tb[v] == col then found = true; print("FOUND "..tb[v]); end end return found end
and then add a column if not:

  if not dbColumnExists( db, "tableName", "columnName") then

                local sql = "alter table tableName add column columnName columnType"

[quote=236782:@Marco Hof]Then you need to rename the old table, create a new table and copy the data back.

Here are the steps:[/quote]
you also can edit directly the sqlmaster table - it’s easier IMHO.

BEGIN; --start a transaction; this ensures that your changes won’t be written until you COMMIT
.schema --this will show you your current schema
PRAGMA writable_schema=1; --this enables you to edit the schema
SELECT * FROM sqlite_master; --show the raw data SQLite saves related to your schema
UPDATE sqlite_master SET SQL=REPLACE(SQL, ‘county’, ‘county_id’) WHERE name=‘people’; --simple string replace
.schema --verify CREATE TABLE is correct
UPDATE sqlite_master SET SQL=‘CREATE INDEX people_county_id_index on people(county_id)’ WHERE name=‘people_county_index’; --update index SQL
UPDATE sqlite_master SET name=‘people_county_id_index’ WHERE name=‘people_county_index’; --update index name
.schema --verify index is correct
SELECT * FROM sqlite_master; --double-check all raw data once more
PRAGMA writable_schema=0; --disable editing the schema
COMMIT; --save the changes to the database


Thanks all.

Nota: each year, I’ve make a backup of the DB, remove one year (the last one) in the current file and rename it (to the new - current - year).

don’t know what’s in your db but I have a 200000 records table in sqlite with no speed problem at all
(all the sells that are made in my office since 1991 …)
I dont like to archive records, it’s always more difficult to find something after !