(Editor - The following functions and handlers have been useful to me when writing code from Revolution that manipulates the database structure. I hope you get use out of them as well.)

DBFindField

This function is used to determine in what table(s) there is a field with a specific name. Here's an example of finding all the tables where there is a field called "UserLink":

put DBFindField("UserLink")

And here's the function (it assumes that you have stored the connection ID to MySQL in the global gDBRef):

function DBFindField pFieldName
  put revdb_query(gDBRef,"SHOW TABLES") into tTables
  put "" into tRetVal
  set the itemDel to tab
  repeat for each line tTable in tTables
    put revdb_query(gDBRef,"SHOW COLUMNS FROM" && tTable) into tList
    delete line 1 of tList  -- removes table titles
    repeat for each line tField in tList
      if item 1 of tField = pFieldName then
        put tTable & cr after tRetVal
      end if
    end repeat
  end repeat
  delete char -1 of tRetVal
  return tRetVal
end DBFindField

DBFieldExists

This function is used to determine whether or not a field exists in a specific table. This is useful when you want to add a field to the table programmatically, but want to make sure the field isn't already there:

put DBFieldExists("UserLink","Contacts")

And here's the function:

function DBFieldExists pFieldName,pTableName
  put revdb_query(gDBRef,"SHOW TABLES") into tTables
  if lineOffset(pTableName,tTables) <> 0 then
    put revdb_query(gDBRef,"SHOW COLUMNS FROM" && tTable) into tList
    delete line 1 of tList  -- removes table titles
    return lineOffset(cr & pFieldName & tab,cr & tList) <> 0
  else
    return "Error: Table '" & pTableName & "' does not exist."
  end if
end DBFieldExists

SQLDate

This function is used to convert a valid Revolution date (or date and time) into the format needed for a MySQL DATE (or DATETIME) field:

put SQLDate("10/6/06")
--> 2006-10-06

put SQLDate("10/6/06 2:24 PM")
--> 2006-10-06 14:24:00

And here's the function:

function SQLDate pDateTime
  -- pDateTime could be a date only, or a date and time, so check first
  put the twelvehourtime into tCurr12HrTime
  set the twelvehourtime to false
  if ":" is not in pDateTime then
    put false into tHasTime
    put pDateTime into tDate
    convert tDate to long date
  else
    put true into tHasTime
    put word 1 of pDateTime into tDate
    convert tDate to long date
    put word 2 to 3 of pDateTime into tTime
    convert tTime to long time
    set the itemDel to ":"
    if item 1 of tTime < 10 then
      put "0" before tTime
    end if
  end if
  put last word of tDate into tYear
  convert tDate to short date
  set the itemDel to "/"
  put item 1 of tDate into tMonth
  put item 2 of tDate into tDay
  if tMonth < 10 then put 0 before tMonth
  if tDay < 10 then put 0 before tDay
  set the twelvehourtime to tCurr12HrTime
  put tYear & "-" & tMonth & "-" & tDay into tDate
  if tHasTime then  
    return (tDate && tTime)
  else
    return tDate
  end if
end SQLDate

Enjoy!

Posted 10/16/2006 by Ken Ray