Hi,
the desired result from a
revExecuteSQL call is the number of records affected, so you better test for this -
revdberr is
not send for this command (neither for the
revDB_execute function equivalent)!
Besides, when working with remote databases it's essential to catch all & any errors. Doing this for each single query is nasty, so we better have one handler to catch all the database work. I have appended an example (it's a stripped down version of the one I use in my programs).
Parameters are easy:
- theDB: the database you want to connect to.
It's assumed that you have a custom property in your stack, named "cSQLCreds" - each line containing a 'theDB' as item 1, and then the 5 database credential parameters (as used in revOpenDatabase/ revDB_connect), all tab-delimited.
- StrSQL: The SQL string that shall be sent to the DB.
- theMode: 'GET' for SELECT queries, 'PUT' for action queries. If empty, 'GET' is used.
- theDatArr: In 'PUT' mode an array can be provided as data. If this param isn't empty, its contents will be used as data array for the query. Else the query will be run as plain-text. (for syntax, see documentation for revExecuteSQL)
Call it this way:
Code: Select all
on mouseUp
get runSQL("myDB", "SELECT * FROM t_test;","GET")
if it begins with "error" then
answer error it titled "What a mess!"
else
-- do whatever you want with 'em results ;-)
put it
end if
end mouseUp
It will return something starting with "error" when it fails, or the desired data (number of records affected in case of 'PUT').
Code: Select all
function RunSQL theDB, StrSQL, theMode, theDatArr
-- set up theMode defaults -> GET:
if theMode is not "PUT" then put "GET" into theMode
if (theDatArr is not empty) AND (theDatArr is not an array) then \
return "Error in RunSQL: theDatArr is NOT an array!" & theDatArr
set itemdel to tab -- get the login parameters:
put line lineOffset((theDB & tab), the cSQLCreds of this stack) \
of the cSQLCreds of this stack into myDBStr
if myDBStr is empty then return \
"Error in RunSQL: Found no database parameters for '" & theDB & "'!"
repeat with i = 2 to 6
do "put item i of myDBStr into Parm" & (i -1)
end repeat
put revdb_connect(Parm1,Parm2,Parm3,Parm4,Parm5) into MyDBID -- connect
if not (MyDBID is a number) then return \
"Error in RunSQL: Connecting to " & theDB & " failed, Reason:" & CR & MyDBID
switch theMode
case "GET" -- fetch query data
put revdb_querylist(tab,return,MyDBID,StrSQL) into myData
break
case "PUT" -- do an action query
if theDatArr is empty then -- as fulltext
put revdb_execute(MyDBID,StrSQL) into MyData
else -- or with parameterArray
put revdb_execute(MyDBID,StrSQL,"theDatArr") into MyData
end if
if not (myData is a integer) then -- we want a revdberr in case of failure
if not (myData begins with "revdberr") then put "revdberr," before myData
end if
break
end switch
get revdb_disconnect(MyDBID) -- disconnect
if MyData begins with "revdberr" then
return "Error in RunSQL: This query failed:" & CR & StrSQL & CR & \
"Reason:" & CR & myData
else
return Mydata -- return the result
end if
end RunSQL
You'll notice that I open a fresh connection for every 'runSQL' call - I don't trust in long standing db connections. This means, 'runSQL' shouldn't be used inside a longer repeat loop - but we don't torture our databases with such anyways, we use elaborate SQL instead, don't we? ;-)
Btw., this also should properly handle a timeout.
Have fun!