Date intersection revisited
bobs at twft.com
Thu Jan 12 11:24:09 CST 2012
Oh cool! Well then of course I would have to conditionally switch the query depending on the type of database I was using. The technique I am employing however precludes that to some degree. I am storing queries in properties using placeholder delimiters. Anything that comes between two percentage signs gets replaced with the value of it at runtime. This means I can insert a variable in a query like %theStartDate% and then programmatically replace it at runtime. It also means I can put things like %theRecordA["building"] & theRecordA["roomnumber"]% in the stored query and it will evaluate to the concatenated value at runtime. It is turning out to be a really powerful method, but I want to keep the stored queries from becoming too complex.
I could also have used CONCAT(%theRecordA["building"]%,%theRecordA["roomnumber"%) I suppose, but there are lots of Livecode statements I can use that have no equivalent in SQL, and by using Livecode statements whenever I can I am not database type dependent. The goal is to give developers in the application framework I am developing in parallel to the app I am coding, a way to "hook" into their own code when building queries without having to hard code them.
On Jan 11, 2012, at 7:55 PM, Pete wrote:
> You can do this with sql functions too Bob. The sqlite date function has a
> modifier parameter, eg date(2011-1-11,'+ 1 DAY). MYSQL has the DATE_ADD
> and DATE_SUB functions which do the same thing.
More information about the use-livecode