jjSQL handlers

[Josh Mellicker]

I am working on a library of handlers that I hope will make the  
process of creating a Revolution client that interacts with a remote  
MySQL database as easy and quick to code as possible, and eventually  
add some elements making working with Rev similar to FileMaker.

One primary goal with these is to make them simple to understand and  
use.

I know many of you have similar libraries, just wanted to share this  
and see if I can get a few people interested in collaborating and  
extending this or a similar library in an organized group with a road  
map and shared collaboration space. I have lots of ideas but my  
coding is s-l-o-w!


I'm just getting started, here's what I have so far:

________________________________________________________________________ 
________________________

1. A handler that UPDATES a MySQL row:

ON jjSQLupdate tRowID, tIDcolumnName, tmySQLtableName -- plus more  
parameters - (the data)
     put "UPDATE `tmySQLtableName` SET newData WHERE `tIDcolumnName`  
= tRowID LIMIT 1" into theSQL
     replace "tRowID" with q(tRowID) in theSQL
     replace "tIDcolumnName" with tIDcolumnName in theSQL
     replace "tmySQLtableName" with tmySQLtableName in theSQL
     put (the paramcount - 3)/2 into numOfFlds2Update
     put 4 into x
     REPEAT numOfFlds2Update times
         IF x > 4 THEN put comma after tNewData
         put bq(param(x)) & " = " & q(param(x+1)) after tNewData
         put x+2 into x
     END REPEAT
     replace "newData" with tNewData in theSQL
     doThisSQL theSQL
END jjSQLupdate

________________________________________________________________________ 
________________________

jjSQLupdate how-to:

the jjSQLupdate handler needs several parameters:

tRecordID - this is the row ID (record ID) of the MySQL record to update

tIDcolumnName - this is the name of the ID column (field) of the  
table to be updated. Often this will be simply "id", but in other  
cases it might be "user_id" or "userID" or "projectID" etc. This is  
almost always an auto-incremented, primary key of a table.

tmySQLtableName - this is the name of the mySQL table

data - after these first three, this handler needs the data to be  
updated, in the format "columnName", data (identical to the LibURL  
formatting)

________________________________________________________________________ 
________________________

jjSQLupdate Example:

Let's say your MySQL table is called "users", your ID column is  
called "userID", and what you want to update is the user's favorite  
color, which has changed from "blue" to "chrome". The MySQL favorite  
color column is called "favColor".

-- first, set up variables

put the tID of the owner of me into tRecordID
put "userID" into tIDcolumnName
put "users" into tmySQLtableName
put fld "favColor" into favColor

-- now, do it
jjSQLupdate tRecordID, tIDcolumnName, tmySQLtableName, "favColor",  
favColor

That's all there is to it! A one-liner!

________________________________________________________________________ 
________________________


2. A handler that DELETES a MySQL row:

ON jjSQLdelete tRecordID, tIDcolumnName, tmySQLtableName
     put "DELETE FROM `tmySQLtableName` WHERE `tIDcolumnName` =  
tRecordID" into theSQL
     replace "tmySQLtableName" with tmySQLtableName in theSQL
     replace "`tIDcolumnName`" with tIDcolumnName in theSQL
     replace "tRecordID" with tRecordID in theSQL
     doThisSQL theSQL
END jjSQLdelete

(pretty self-explanatory)

________________________________________________________________________ 
________________________


3. A handler that INSERTS a MySQL row:

ON jjSQLinsert tmySQLtableName
     put "INSERT INTO `tmySQLtableName` (theColumnNames) VALUES  
(theData)" into theSQL
     replace "tIDcolumnName" with tIDcolumnName in theSQL
     replace "tmySQLtableName" with tmySQLtableName in theSQL
     put (the paramcount - 1)/2 into numOfFlds
     put 2 into x
     REPEAT numOfFlds times
         IF x > 2 THEN put comma after tColNames
         IF x > 2 THEN put comma after tNewData
         put bq(param(x)) after tColNames
         put q(param(x+1)) after tNewData
         put x+2 into x
     END REPEAT
     replace "theColumnNames" with tColNames in theSQL
     replace "theData" with tNewData in theSQL
     doThisSQL theSQL
END jjSQLinsert

This one is like the update one except it doesn't need to know the  
rowID column name, or the record ID

________________________________________________________________________ 
________________________


The above handers depend on some other handlers:


ON doThisSQL theSQL
     put getConnectID() into dbID
     IF dbID is empty THEN answer "error : could not connect"
     revExecuteSQL dbID, theSQL
     IF the result is not a number THEN
         answer "Woops! A doThisSQL database error: " & cr & theSQL &  
cr & the result
     ELSE
         set the latestStatusReport of stack "greenbongo" to  
"executed: '" & theSQL & " : ready"
     END IF
END doThisSQL

FUNCTION getConnectID
     IF the dbID of this stack is empty THEN
         --try to connect
         put "dave" into dbUser
         put "swordfish" into dbPass
         put "rev_test" into dbName
         put "revcoders.org" into dbAddr
         put revOpenDatabase("MySQL",dbAddr,dbName,dbUser,dbPass)  
into dbresult
         IF dbresult is a number THEN
             put dbresult into the dbID of this stack
             return dbresult
         ELSE -- connection failed
             beep
             answer dbResult
             return ""
         END IF
     ELSE -- we were connected already
         return the dbID of this stack
     END IF
END getConnectID

FUNCTION q a
     return quote & a & quote
END q

FUNCTION bq a
     return "`" & a & "`"
END bq