Reading from Database question

John Tregea john at debraneys.com
Sun Nov 11 16:31:20 EST 2007


Hi Dave,

I had a memory of being able to get one record from an SQL database. I just looked it up and you could use it something like this. 

Open your database connection then put the following SQL into a variable that you then execute with the revDataFromQuery statement using the variable.
SELECT TOP serial_id, name, details, done_flag
FROM my_database
WHERE done_flag = '0'

If you want to walk down the database table one record at a time you could add a field (to the database) that acts as a flag (e.g. done_flag). Each time you get the top record (where done_flag = 0) you know you haven't processed it before. 
Use the above SELECT statement and store the serial_id value in a variable (like current_serial)
Do your processing in Rev then use another simple sql statement to
UPDATE my_database SET done_flag = '1' WHERE serial_id = current_serial

Once you complete the last record and update it, the SELECT TOP statement will return empty.
If you want to reuse the set of records over and over then use

UPDATE my_database SET done_flag = '0';

and all your records are back available again.

Or if you wanted to process each once a day make the done_flag field a date field and put the date you process it into the field. Then you can SELECT TOP from my_database WHERE done_flag < today;

Hope this gets you started.

Regards

John




More information about the use-livecode mailing list