drop column for sqlite [CODE]

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
Emily-Elizabeth
Posts: 101
Joined: Mon Jan 03, 2022 7:10 pm

drop column for sqlite [CODE]

Post by Emily-Elizabeth » Fri Mar 24, 2023 4:02 am

Due to the version of the SQLite external, it doesn't support the SQL drop column command, so here is a workaround for anyone else that needs it. Code is in the public domain.

Code: Select all

command DBDropColumnForSQLite databaseID, tableName, columnName
   try
      if (_DBIsOpen(databaseID)) then
         local sql
         put "select sql from sqlite_master where type = 'table' and name ='" & tableName & "'" into sql
         local createTableSQL
         put revDataFromQuery(,, databaseID, sql) into createTableSQL
         local itemNumber
         put itemOffset(columnName, createTableSQL) into itemNumber
         if (itemNumber > 0) then  // if it is 0 then deleting a column that wasn't saved to the table
            // create the temporary table
            delete item itemNumber in createTableSQL
            if (char -1 of createTableSQL <> ")") then put ")" after createTableSQL
            replace tableName with tableName & "_backup" in createTableSQL
            replace "CREATE TABLE" with "CREATE TEMPORARY TABLE" in createTableSQL
            revExecuteSQL databaseID, createTableSQL
            // insert the data from the original table into the temporary table
            local columnNames
            put revDatabaseColumnNames(databaseID, tableName) into columnNames
            put itemOffset(columnName, columnNames) into itemNumber
            delete item itemNumber in columnNames
            put "INSERT INTO " & tableName & "_backup SELECT " & columnNames & " FROM " & tableName into sql
            revExecuteSQL databaseID, sql
            // drop the original table
            put "DROP TABLE " & tableName into sql
            revExecuteSQL databaseID, sql
            // recreate the original table (dbdMainTable)
            replace "CREATE TEMPORARY TABLE" with "CREATE TABLE" in createTableSQL
            replace tableName & "_backup" with tableName in createTableSQL
            revExecuteSQL databaseID, createTableSQL
            // insert the data back into the main table
            put "INSERT INTO " & tableName & " SELECT " & columnNames & " FROM " & tableName & "_backup" into sql
            revExecuteSQL databaseID, sql
            // delete the temporary table
            put "DROP TABLE " & tableName & "_backup" into sql
            revExecuteSQL databaseID, sql
         end if
      end if
   end try
end DBDropColumnForSQLite

j9v6
Posts: 77
Joined: Tue Aug 06, 2019 9:27 am
Location: U.K.
Contact:

Re: drop column for sqlite [CODE]

Post by j9v6 » Tue Sep 26, 2023 7:33 pm

Nice! Thanks for sharing :)

Emily-Elizabeth
Posts: 101
Joined: Mon Jan 03, 2022 7:10 pm

Re: drop column for sqlite [CODE]

Post by Emily-Elizabeth » Tue Sep 26, 2023 9:10 pm

You're welcome.

Post Reply

Return to “Databases”