التعامل مع SQL

  كود:
function PopLBFromDB()
  --Select all entries found in the accounts table
  tbReturn = SQLite.QueryToTable(db, "SELECT * FROM accounts");
 
  --Clear all items from the listbox
  ListBox.DeleteItem("ListBox1", -1);
  if tbReturn and tbReturn.Rows > 0 then
      for i,v in pairs(tbReturn.Data) do
        ListBox.AddItem("ListBox1", tbReturn.Data[i]["name"], tbReturn.Data[i]["ID"]);
      end
  end
end
function AddtoDB()
  local strAcc = Input.GetText("I-Account");
  local strLog = Input.GetText("I-Login");
  local strPass = Input.GetText("I-Password"); 
  --Check to see that the above entries are valid
  if strAcc ~= "" and strLog ~= "" and strPass ~= "" then
      --Insert new account information
      SQLite.Query(db, "insert into accounts(name, login, password) values('".. strAcc.."',' "..strLog.."','"..strPass.."')", nil);
      --Clear the Inputs
      ClearInputs()
  else
      Dialog.Message("Error", "Please provide an Account name, Login and Password before trying to add an entry"); 
  end
end

function updateDB(x)
  local strAcc = Input.GetText("I-Account");
  local strLog = Input.GetText("I-Login");
  local strPass = Input.GetText("I-Password"); 
  --Check to see that the above entries are valid
  if strAcc ~= "" and strLog ~= "" and strPass ~= "" then
      --Insert new account information
      SQLite.Query(db, "UPDATE accounts SET name = '".. strAcc.."' , login='"..strLog.."' , password = '"..strPass.."' WHERE name = '"..x.."'", nil);         
      --Clear the Inputs
      ClearInputs()
  else
      Dialog.Message("Error", "Please provide an Account name, Login and Password before trying to add an entry"); 
  end
end

function ClearInputs()
  Input.SetText("I-Account", "");
  Input.SetText("I-Login", "");
  Input.SetText("I-Password", "");
end

 كود:
PopLBFromDB()

 كود:
-Open the Database, if the file does not exist it will be created by this command.
db = SQLite.Open(Shell.GetFolder(SHF_MYDOCUMENTS).."\\accounts.db");
--Create the table that will store our account information.
--This will fail if the table alread exists.
SQLite.Query(db, "CREATE TABLE accounts(ID integer primary key, name text, login text, password text, category integer)", nil);

 كود:
--Close the open database.
SQLite.Close(db);

 كود:
AddtoDB();
PopLBFromDB();

 كود:
xSel = ListBox.GetSelected("ListBox1");
nData = ListBox.GetItemText("ListBox1", xSel[1]);   
SQLite.Query(db, "DELETE FROM accounts WHERE name = '"..nData.."'");-- Borramos datos de la BD
PopLBFromDB()
ClearInputs()

 كود:
xSel = ListBox.GetSelected("ListBox1");
nData = ListBox.GetItemText("ListBox1", xSel[1]); 
updateDB(nData);
PopLBFromDB();

 كود:
sql = "select * from accounts";
tbResults = SQLite.QueryToTable(db, sql);
for i=1, ListBox.GetCount("ListBox1") do
if ListBox.IsItemSelected("ListBox1", i) then
j=i
break;
end
end

Input.SetText("I-Account",tbResults.Data[j]["name"]);
Input.SetText("I-Login", tbResults.Data[j]["login"]);
Input.SetText("I-Password", tbResults.Data[j]["password"]);