Adding a column to an existing SQLite database

[glenn9]

Hi everyone,

I've been trying to learn how to use SQLite databases over the last few days and so far have managed to create a table and add some data, including via variables, but more by 'recipe' than complete understanding so far, but its working!

However, I'm now struggling to add columns to an existing database.

I've looked up the SQL syntax:

Code: Select all

ALTER TABLE table
ADD COLUMN column_definition;

but I'm not sure how to implement into my LC script:

Code: Select all

put "CREATE TABLE test4 (Col1 char(50), Col2 char(50))"  into tSQL
  revExecuteSQL gConID,tSQL

I've tried various unsuccessful permutations such as:

Code: Select all

put "CREATE TABLE test4 (Col1 char(50), Col2 char(50))" & "ALTER TABLE test4 ADD COLUMN (Col5 char(50))" into tSQL

but no success so far!

Grateful for any hints!

Kind regards,

Glenn

[Klaus]

Hi Glenn,

ALTER TABLE works with existing TABLEs, but your script creates a new table and tries to alter it immediately.
And since that TABLE already exists, you get an SQL error.

Try this:

Code: Select all

...
## Since these are actually two commands we need two lines here:
put "ALTER TABLE test4" & CR & "ADD COLUMN Col5 char(50)" into tSQL
...

Whenever I need to work with databases, I always refresh my poor SQL knowledge here: https://www.w3schools.com/sql/


Best

Klaus

P.S.
I could also explain this in german.
Nudge, nudge!

[glenn9]

Thanks Klaus, now understand the logic error in trying to ALTER whilst Creating a database.

I guess having Created the database, where would the ALTER code need to be inserted to action the adding of a column?

so far I've tried adding some data to the database (so it now 'exists'), and then, through a button mouseup action, to fire the ALTER command but sadly I get the error message that the column which I had hoped to have added, hasn't!

Kind regards,

Glenn

(Re german - I'm not brave enough - likely to blamier myself!)

[Klaus]

Hi Glenn,

Thanks Klaus, now understand the logic error in trying to ALTER whilst Creating a database.

great!

I guess having Created the database, where would the ALTER code need to be inserted to action the adding of a column?

Well, it depends!
Hard to say without knowing your project.

Two years ago I had been mentoring a complete nebie and after 6 month he had an app in the Apple iOS and Google store with database etc...!
But after the first delivery of the app, he noticed that he needed another, not yet present column in the database.
So I wrote a script which checks the existing database "on preopenstack" for that column and added it if neccessary.
I can look for that script and post it here if you like.

so far I've tried adding some data to the database (so it now 'exists'), and then, through a button mouseup action, to fire the ALTER command but sadly I get the error message that the column which I had hoped to have added, hasn't!

Could you please post your script?

(Re german - I'm not brave enough - likely to blamier myself!)

I was just referring to your posting in the german forum.


Best

Klaus

[glenn9]

Hi Klaus,

Many thanks for your help again.

Stack now attached!

I think my problem is that I'm unsure of where to place the code

Code: Select all

ALTER TABLE table
ADD COLUMN column_definition;

(and in the correct format) to get LC/SQLite to add the column!

[Klaus]

Hi Glenn,

script of your "Add column" button:

Code: Select all

on mouseup
   ## You also need to add the DATATYPE to the ALTER command as in your previous examples!
   
   ## put "ALTER TABLE test4 ADD COLUMN diagnosis(50)" into tSQL
   put "ALTER TABLE test4" & CR & "ADD COLUMN diagnosis varchar(50)" into tSQL
   answer tSQL
   ## You also need to EXECUTE this SQL! 8-)
   ## But only ONCE or you get another SQL error, see my last posting for checking the need of the ALTER command!
   
   ## put "INSERT INTO test4(diagnosis)" & " VALUES('test')" into tSQL
   ## No concatenation with & neccessary here!
   put "INSERT INTO test4(diagnosis) VALUES('test')" into tSQL
   put revdb_execute(gConID,tSQL) into tTmp
   answer tTmp
end mouseup

Best

Klaus

[glenn9]

Hi Klaus,

Thank you so much, have now successfully added my first column!!

I think this will now help me to explore more of the SQLite commands, and how to use them within LC.

Thank you again,

Kind regards,

Glenn