Re: Breaking out of a function loop when querying a database that returns multiple rows of data

[Jonathan Goble <jcgoble3@...>]

On Mon, Sep 21, 2015 at 6:17 PM, Jonathan Hunter
<jhunter@voxboxcoms.co.uk> wrote:
> HI Jonathan,
>
> Thanks for the reply!
>
> Could you just give me an idea of the code, as if I try break it says its
> outside of the loop, and return doesnt exit the function loop, as I presume
> my if statements are causing issues,
>
> An idea of my code is;
>
> assert (dbh:query(todnew_query,function(todresult)
> local t = os.date("*t");
> if (tostring(todresult.year) == tostring(t.year) and
> tostring(todresult.month) == tostring(t.month)) then
>        freeswitch.consoleLog("notice","Year and Month Match, next step is
> day of month\n")
>        if (tostring(todresult.dayofmonth) == tostring(t.day)) then
>          freeswitch.consoleLog("notice","This entry matches on Day of month
> too\n")
>           return     <<---tried return or break here, is this correct?
>         end
>         if (tostring(t.day) >= startday) and (tostring(t.day) <= endday)
> then
>           freeswitch.consoleLog("notice", "Today is in the time range\n")
>
>       end
>  end
> end))

By "loop", I assumed you meant a "for" loop in Lua. This is not a
"for" loop, thus "break" won't work. Also, this appears to be calling
the function separately on every table row produced by the query, so
"return" won't prevent it from processing others as well.

So you would need to either check the documentation for the database
wrapper library that you're using for a solution, or failing that,
figure out a solution in pure SQL, as suggested by Sean Conner between
your reply and this reply:

On Mon, Sep 21, 2015 at 6:28 PM, Sean Conner <sean@conman.org> wrote:
> What's wrong with this? (and please excuse the SQL, I'm not terribly
> familiar with it and you would generally need to either bind the values you
> want to search to a prepared statement or build the SQL on the fly)
>
>
>         t = os.date("*t")
>
>         SELECT prority /* and other required fields */
>         FROM timeofday_new
>         WHERE
>                 todname = 'sales'
>                 AND year = t.year
>                 AND month = t.month
>                 AND dayofmonth = t.day
>         ORDER BY priority
>
>   Let SQL do the work for you.  Or am I missing something?

Some quick Googling indicates that SQL offers "LIMIT" to limit the
results. So maybe add "LIMIT 1" to Sean's solution?