LiveCode Forums

Code: Select all

function dbData iHandle, iEntry
     global conID
     -- Step 1: Check for Close Connection Request
     if iHandle is empty then
          revCloseDatabase conID
          put the result into temp
          if the result is empty then
               return "ok"
          else
               else return the result
          end if
          put empty into conID
     end if
     -- Step 2: Build Full Path and SQLite file name
     set itemDel to slash  -- start to build current path for SQlite DB
     put the effective filename of this stack into realpath  --gets current path
     delete last item of realpath --removes .livecode file name on this stack
     put  realpath & "/System.data" into pathNdb -- adds SQLite file name to path
     -- Step 3: Open Connection -- close if all values are empty
     put revopendatabases("sqlite",,,,) into dbIDs --gets a list of open databases
     if not (conID is among the items of dbIDs) then  -- needs new connection id
          put revOpenDatabase("sqlite", pathNdb,,,,,,) into conID
          put the result into temp
          if the result is not a number then
               answer "ERROR=" & conID
          end if
     end if
     -- Step 4: Retrieve Data if Entry is empty
     put "'" & iHandle & "'" into iHandle -- add single quotes
     put "'" & iEntry & "'" into iEntry  -- required for SQL data
     if iEntry ="''" then  -- two single quotes is a request to retrieve data
          put "SELECT Entry FROM control WHERE Handle="  & iHandle into tSQL
          --revExecuteSQL conID, tSQL
          put revdb_querylist(,,conID,tSQL) into ptext
          if "revdberr" is in ptext then
               return "ERROR=" & ptext
          end if
          return ptext  -- valid a valid entry
     end if
     -- Step 5: Create Table
     put  "CREATE TABLE control (Handle text primary key, Entry text);" into tsql
     revExecuteSQL conID, tSQL
     put the result into view -- for debuging
     if "already exists" is in the result then
          -- good
     end if
     -- Step 6: Try to Insert New Row with SQL Insert statement
     put "INSERT INTO control (Handle, Entry)"  into tSQL
     put "VALUES (" & iHandle & ", " & iEntry & ");" after tSQL
     revExecuteSQL conID, tSQL
     put the result into view -- for debuging
     if "is not unique" is in the result then -- row already exits
          -- then go on to update row
     else if the result is 1 then
          return "ok"
     else
          return "ERROR=" & the result  --- exit with error
     end if         
     -- Step 7: Try to Update the Row Instead
     put "UPDATE control SET Entry=" & iEntry &  " WHERE Handle="  & iHandle into tSQL
     revExecuteSQL conID, tSQL
     put the result into view -- for debuging
     if the result is not 1 then
          return "ERROR=" & the result  --- exit with error
     end if         
     return "ok"
end dbData