M E R Goulding Software Development

Some handy little general database functions and commands I wrote for an app I’m working on. They need some extra error handling but they are good for getting up and running quickly.

function getTable pTable
   local tReturn,tSQL
   put "SELECT * FROM "&pTable into tSQL
   put revDataFromQuery(tab ,cr ,sDB ,tSQL) into tReturn
   if tReturn contains "revdberr" then return ""
   set the itemDel to tab
   return tReturn
end getTable

function getTableRecord pTable,pKey,pID
   local tReturn,tSQL,tQuery
   put "SELECT * FROM "&pTable&" WHERE "&pKey&" = "&pID into tSQL
   put revQueryDatabase(sDB,tSQL) into tQuery
   if not revdb_iseof(tQuery) then
      revMoveToFirstRecord tQuery
      repeat for each item tColumn in revDatabaseColumnNames(tQuery)
         put unSQLString(revDatabaseColumnNamed(tQuery,tColumn)) into tReturn[tColumn]
      end repeat
   end if
   revCloseCursor tQuery
   return tReturn
end getTableRecord

command deleteTableRecord pTable,pKey,pID
   local tReturn,tSQL,tQuery
   put "DELETE FROM "&pTable&" WHERE "&pKey&" = "&pID into tSQL
    revExecuteSQL sDB,tSQL
end deleteTableRecord

on updateTableRecord pTable,pKey,pID,pRecordA
   local tSQL,tField
   put "UPDATE "&pTable&" SET" into tSQL
   repeat for each line tField in keys(pRecordA)
      if pRecordA[tField] is a number then
          put " "&tField&"="&pRecordA[tField]&"," after tSQL
      else
         put " "&tField&"="&sqlString(pRecordA[tField])&"," after tSQL
      end if
   end repeat
   delete char -1 of tSQL
   put " WHERE "&pKey&"="&pID into tSQL
   revExecuteSQL sDB,tSQL
end updateTableRecord