Single Record Updates Only???
bobs at twft.com
Fri May 28 19:45:35 EDT 2010
Well I think I've run across a bit of a limitation with the Rev Database API's. I had heard that you cannot send multiple SQL statements at a time, by separating them with semiColons. This is quite the bummer. When working with a large remote SQL database, sending a series of commands for the server to process is really the only way to work efficiently.
For instance, I have been trying to develop a paging system that will query and update a limited number of records, so as to avoid hitting the wall by inadvertently returning an extremely large data set in Revolution. So I build my SQL to look like this:
UPDATE devices SET sw_id="8",devicename="logos-it xserve-raid" WHERE sw_id="8";
UPDATE devices SET sw_id="11",devicename="logos-it xsan backup" WHERE sw_id="11";
UPDATE devices SET sw_id="12",devicename="logos-ftp" WHERE sw_id="12";
and so on for the number of records in the page, currently set to 100. I can paste that SQL directly into mySQL Workbench and it executes flawlessly. But when I use RevExecuteSQL I get an error at line 2. As soon as it sees the semi colon it chokes and doesn't even try to send the SQL to the database.
At this point, I am not sure if MySQL Workbench is simply parsing this SQL out one line at a time and doing what Rev would do. The result would seem to indicate that. Is there no way of updating more than one record at a time in a single UPDATE statement?
If so, I am wondering if this is something that can be addressed in a future update, or if there is perhaps another way of doing this. Repeated queries for each record are not gonna do it for me, otherwise I may as well concede that Revolution is NOT a good choice for manipulating a large amount of data in a remote SQL server. Maybe nothing is good at working with remote SQL servers, I don't know. It could be that SQL was never really made to be accessed remotely over the internet. <sigh>.
I can still work with it, I just have to forget about any kind of scalability as such.
Calvary Chapel CM
More information about the Use-livecode