8

Inserting data into the sqlite database with the Corona SDK simulator

 3 years ago
source link: https://www.codesd.com/item/inserting-data-into-the-sqlite-database-with-the-corona-sdk-simulator.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Inserting data into the sqlite database with the Corona SDK simulator

advertisements

I can query data in an SQLlite database using the corona SDK simulator but can not insert or delete data. I am running the latest public release of the Corona SDK. Here is the code. It compiles fine but the insert command simply will not work. Here is the code for the main.lua:

local sqlite3 = require "sqlite3"

local function setUpDatabase(dbName)

local path = system.pathForFile( dbName, system.DocumentsDirectory )
local file = io.open( path, "r" )

if ( file == nil ) then
    -- copy the database file if doesn't exist
    local pathSource     = system.pathForFile( dbName, system.ResourceDirectory )
    local fileSource     = io.open( pathSource, "r" )
    local contentsSource = fileSource:read( "*a" )

    local pathDest = system.pathForFile( dbName, system.DocumentsDirectory )
    local fileDest = io.open( pathDest, "w" )
    fileDest:write( contentsSource )

    io.close( fileSource )
    io.close( fileDest )
end

local gameDB = system.pathForFile(dbName, system.DocumentsDirectory)
local dbNew = sqlite3.open( gameDB )

return dbNew

end

function loadData()
    local sql = "select * from projects"
    local projects = {}

    for a in db:nrows(sql) do
        projects[#projects+1] =
        {
        id = a.id,
        name = a.name,
        category = a.category,
        rating = a.rating
        }
    end

    return projects
end

function insertData(n, c, r)
    local sql = "insert into projects (name, category, rating) values ('" .. n .. "', '" .. c .. "', " .. r .. ")"
   db:exec(sql)
end

function deleteData(id)
    local sql = "delete from projects where id = " .. tostring(id)
    db:exec(sql)
end

function updateData(id, col, v)
    local sql = "update projects set " .. col .. " = '" .. v .. "' where id = " .. tostring(id)
    db:exec(sql)
end

db = setUpDatabase("mydatabase.sqlite")
insertData("Horse Crazy", "Game", 2)

deleteData(3)
--updateData(4, "name", "Ralph")
--updateData(4, "category", "Dog")
--updateData(4, "rating", 4)

local data = loadData()

for x = 1, #data do
    print (data[x].id, data[x].name, data[x].category, data[x].rating)
end

Just figured out after having same problem for a while. You have to replace this line

local sql = [[delete from projects WHERE ID = ']]..id..[[';]]
db:exec(sql)

make sure id is a number otherwise simply make it a number by adding this before first line:

id=id*1

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK