Hello,
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.
Thanks.
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?
Thanks.
…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:
ALTER TABLE mData ADD COLUMN clm4 Binary;
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.
Hello,
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?
‘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’.
etc.
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: https://www.sqlite.org/datatype3.html
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.
Thanks for the reference. I had indeed looked at https://www.sqlite.org 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?
Thanks.
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).
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"
db:exec(sql)
end
[code]---------------------------------------------------------------------------------------
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
.exit
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 !
