Hi everyone,
can someone tell me if it is possible to edit RowSets returned by Database.TableColumns to change the structure of a database table? For example the column type/name? Or is this only possible for RowSets that return the data of a table?
Thanks
it may be easier to do this using executeSQL and sql commands like ALTER TABLE
you can add or drop columns, but to modify works only with certain databases engines.
Yes, and in particular, for SQLite, ALTER TABLE is quite limited, and the column type has little meaning. Might be useful to know what Martin actually wants to do.
I‘m coding a little Database Editor. I tried to use RowSet.EditRow with Database.TableColumns but it doesn’t work. Any other suggestions?
?
I do not found that in the LR.
Ans RecordSet is deprecated (not API2).
Sorry, RowSet.EditRow.
For which database? MySQL? SQLite?
For all Database Engines supported by Xojo 
That’s why I tried to use RowSet.Edit-/SaveRow.
I’ve never used either of those. I only use SelectSQL and ExecuteSQL, and use SQL statements to select or update rows. Where SQLite is concerned, you may want to look at:
https://www.sqlite.org/datatype3.html
to understand what SQLite does with types (TL;DR is: not much). as for modifying the schema, you are limited as shown here:
https://www.sqlite.org/lang_altertable.html
where the same TL;DR applies as above.
To be able to use RowSet.EditRow, the table MUST have a primary key. Then after you do the SELECT statement to get the Rowset, if the RowSet variable name is ‘rs’, you need to call
rs.EditRow
// Update the item information
rs.Column ("Item").StringValue = ItemField.Text ' This line is an example
...
// Update the record in the database
rs.SaveRow
Don’t forget to do the appropriate error checking.
Remember, the table must have a primary key or EditRow won’t work.
As far as I know, you cannot change the column name/definition via EditRow, only the row contents. If you want to change the database schema, you must use ALTER TABLE.
Thanks. I know I can use ALTER TABLE to rename a table or column. But how can I change the column type?
Depends on the database. For sqlite, you can’t change the column directly. To change the name or type, you have to create a temporary table, copy the orginal contents into it, drop the column, add the column back with the new name/type, then copy the contents back and delete the temp table. Other databases are easier, but they’re all different.
I use SQLiteManager from SQLabs and it do all the copy to temp and renamed etc automatically