Hi guys,
I know how you check if a table exists and create it if it doesn’t but I want to do the same thing with a column.
Thanks,
Ian [import]uid: 123492 topic_id: 35233 reply_id: 335233[/import]
create table if not exists TableName [import]uid: 50459 topic_id: 35233 reply_id: 140077[/import]
It’s not directly possible to check if a column exists, however you can issue a “pragma table_info(table name)” statement which will return one row for each column in the table.
For more info see:
http://www.sqlite.org/pragma.html [import]uid: 70847 topic_id: 35233 reply_id: 140079[/import]
Thanks for replying.
It’s not creating a table I’m looking at though - it’s creating a column within a table if that column doesn’t exist.
Anyway - I found the following code here - http://gamedevnation.com/game-development/look-for-existing-columns-in-a-sqlite-database-table/ - which works brilliantly. Thanks to Jay (http://gamedevnation.com/author/Jay/) who posted that page originally.
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 [import]uid: 123492 topic_id: 35233 reply_id: 140080[/import]
create table if not exists TableName [import]uid: 50459 topic_id: 35233 reply_id: 140077[/import]
It’s not directly possible to check if a column exists, however you can issue a “pragma table_info(table name)” statement which will return one row for each column in the table.
For more info see:
http://www.sqlite.org/pragma.html [import]uid: 70847 topic_id: 35233 reply_id: 140079[/import]
Thanks for replying.
It’s not creating a table I’m looking at though - it’s creating a column within a table if that column doesn’t exist.
Anyway - I found the following code here - http://gamedevnation.com/game-development/look-for-existing-columns-in-a-sqlite-database-table/ - which works brilliantly. Thanks to Jay (http://gamedevnation.com/author/Jay/) who posted that page originally.
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 [import]uid: 123492 topic_id: 35233 reply_id: 140080[/import]
That function is amazing, thanks for posting the solution you found keystagefun. This is just what I was looking for.
I was just about to paste that function too. I have used that function with great success for a long time. It is rock solid. Thanks much to Jay for contributing it. Here’s how I normally use that function and add the column if its missing. Hope this helps.
if not dbColumnExists( db, "tableName", "columnName") then local sql = "alter table tableName add column columnName columnType" db:exec(sql) endThat function is amazing, thanks for posting the solution you found keystagefun. This is just what I was looking for.
I was just about to paste that function too. I have used that function with great success for a long time. It is rock solid. Thanks much to Jay for contributing it. Here’s how I normally use that function and add the column if its missing. Hope this helps.
if not dbColumnExists( db, "tableName", "columnName") then local sql = "alter table tableName add column columnName columnType" db:exec(sql) end