Single Record Updates Only???

[Bob Sneidar]

Hi all. 

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. 


Bob Sneidar
IT Manager
Logos Management
Calvary Chapel CM