SQLite Plugin Question

[Jerryab]

I am trying to find out if a field exist in a table. Is there a simple way to do this ?

I am using reteset's plugin v3.7.2

[reteset]

here is a helper function to check whether a column exist in a table

function DoesColumnExist(db,strTable,strColumn)

        local q = string.format("SELECT %s FROM %s",strColumn,strTable)
		local stm = db:prepare(q)
		local bret = false
		
		if stm then
            
            if(stm:step() ) then
			   bret = true
		    end
		    
			stm:finalize()
	   end
	   return bret
end

and this is the usage of function :

local db = sqlite3.open_memory()

db:exec[[
  CREATE TABLE test (id INTEGER PRIMARY KEY, content TEXT);
  INSERT IGNORE INTO test VALUES (NULL, 'Hello World');
]]


local tblName = "test"
local clmnName = "content"

if (DoesColumnExist(db,tblName,clmnName)) then   
     Dialog.Message("Notice", "The Column : "..clmnName.." Does Exist In Table : "..tblName);
else
   Dialog.Message("Notice", "The Column : "..clmnName.." Does Not Exist In Table : "..tblName);
end

db:close()

you can also do similar queries over a special table sqlite_master that contains all tables and column names in a database , but it may be complex for you

so creating a statement with a desired query and checking for the error would do the job

for example "SELECT columnname-to-check-existance FROM table-to-be-looked-in"
if this query returns an error , it is probably there is no such column or table

[Jerryab]

Thanks, reteset.

I was busting my head trying to figure that out. Your code worked very well.

I now check for the field and if not exist I creat it. I did figure out adding a field to a table,
just had a time checking if it existed or not.

Thanks again. I really appreciate the help.

[telco]

Hi.. anyone can help me using SQLite3 to add a new column in existing Table?

[colc]

Hi telco ,
Don't know why you would want to add a col to existing table in DB as all inputs would just be NULL, unless you recode to get new values to and from new column
Easier to create new table

But here you go with this DEMO , check my avatar to get code
Customise DB Sqlite3.zip
Cheers

[telco]

can you share the code my friend?

my main problem is i already created the db and thinking what if later i would like to add new column, i dont want to re create the database.. so creating new column is the solution i am thinking.. t

thank you

[colc]

I did

But here you go with this DEMO , check my avatar to get code

Cheers

[daviz]

can you share the code my friend?

my main problem is i already created the db and thinking what if later i would like to add new column, i dont want to re create the database.. so creating new column is the solution i am thinking.. t

thank you

this is very useful for altering databases.

https://sqlitebrowser.org/