Handy Handlers for MySQL |
|
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":
And here's the function (it assumes that you have stored the connection ID to MySQL in the global gDBRef):put DBFindField("UserLink")
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:
And here's the function:put DBFieldExists("UserLink","Contacts")
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:
And here's the function:put SQLDate("10/6/06") --> 2006-10-06 put SQLDate("10/6/06 2:24 PM") --> 2006-10-06 14:24:00
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