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

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Jonathan Hunter
Hi Guys,

Im quite new to Lua and Im having an issue with extracting particular data where there a multiple rows in a database I want to deal with so was wondering if someone could help?

Basically using a function, Im extracting data from a Mysql table that has multiple rows, and its looping through the rows as expected.

Now I have setup a number of entries for Time of day and I want to stop the loop (exit it) as soon as there is a match, and Id like to know how to achieve this.

Basically I want to perform a query such as;

select priority,dayofmonth,month,year,dayofmonth from timeofday_new where todname='sales' order by priority;
+----------+------------+-------+------+------------+
| priority | dayofmonth | month | year | dayofmonth |
+----------+------------+-------+------+------------+
|        1 | 20         | 9     | 2015 | 20         |
|        2 | 14-17      | 9     | 2015 | 14-17      |


And Im doing this using;




local todnew_query = string.format("select priority,dayofmonth,month,year,dayofmonth from timeofday_new where todname='sales' order by priority;")
                                   assert (dbh:query(todnew_query,function(todresult)

Now with using the function it loops through the rows, in this case, priority 1 and 2 rows, but if I match on the first entry with an if statement, such as;


--------------------------------------------------
--------------------------------------------------------------------------------
---If year and month match, send to freeswitch.
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
         .....Exit here sending data.
      end
       
end
------------------------------------------------------------------------------
---End of loop to send all TOD options
end))

I want it to exit the loop/function on true and not return any of the other entries,so in affect matches on first priority entry and stops there.

Can this be done with the way Im trying to loop through multiple rows in a database table?

Many thanks

Jon

Reply | Threaded
Open this post in threaded view
|

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

Jonathan Goble
On Mon, Sep 21, 2015 at 5:03 PM, Jonathan Hunter
<[hidden email]> wrote:
> I want it to exit the loop/function on true and not return any of the other
> entries,so in affect matches on first priority entry and stops there.

You can use the "break" keyword to exit the innermost loop
immediately, or the "return" keyword to immediately exit the current
function, including all loops within that function (and possibly send
value(s) back to the caller, by "return <value>").

Note that when using "break", you immediately lose any local variables
whose scope is the loop itself, including the loop variables
themselves, so be sure to store the entry in another variable before
"break"ing.

Beyond that, your database code is all Greek to me. :-) (One of these
days, I should learn some SQL. Might help me with a personal project
I'd like to do eventually.)

Reply | Threaded
Open this post in threaded view
|

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

Jonathan Hunter
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))

As for  SQL, its easy compared to Lua coding for me at the moment :) 

Thanks again.

Jon

On Mon, Sep 21, 2015 at 10:13 PM, Jonathan Goble <[hidden email]> wrote:
On Mon, Sep 21, 2015 at 5:03 PM, Jonathan Hunter
<[hidden email]> wrote:
> I want it to exit the loop/function on true and not return any of the other
> entries,so in affect matches on first priority entry and stops there.

You can use the "break" keyword to exit the innermost loop
immediately, or the "return" keyword to immediately exit the current
function, including all loops within that function (and possibly send
value(s) back to the caller, by "return <value>").

Note that when using "break", you immediately lose any local variables
whose scope is the loop itself, including the loop variables
themselves, so be sure to store the entry in another variable before
"break"ing.

Beyond that, your database code is all Greek to me. :-) (One of these
days, I should learn some SQL. Might help me with a personal project
I'd like to do eventually.)




--
Jonathan Hunter
Technical Director /Telephony Developer
M:(+44) 7917 190 438

Reply | Threaded
Open this post in threaded view
|

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

Sean Conner
In reply to this post by Jonathan Hunter
It was thus said that the Great Jonathan Hunter once stated:

> Hi Guys,
>
> Im quite new to Lua and Im having an issue with extracting particular data
> where there a multiple rows in a database I want to deal with so was
> wondering if someone could help?
>
> Basically using a function, Im extracting data from a Mysql table that has
> multiple rows, and its looping through the rows as expected.
>
> Now I have setup a number of entries for Time of day and I want to stop the
> loop (exit it) as soon as there is a match, and Id like to know how to
> achieve this.
>
> Basically I want to perform a query such as;
>
> select priority,dayofmonth,month,year,dayofmonth from timeofday_new where
> todname='sales' order by priority;
> +----------+------------+-------+------+------------+
> | priority | dayofmonth | month | year | dayofmonth |
> +----------+------------+-------+------+------------+
> |        1 | 20         | 9     | 2015 | 20         |
> |        2 | 14-17      | 9     | 2015 | 14-17      |
>

  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?

  -spc


Reply | Threaded
Open this post in threaded view
|

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

Jonathan Goble
On Mon, Sep 21, 2015 at 6:17 PM, Jonathan Hunter
<[hidden email]> 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 <[hidden email]> 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?

Reply | Threaded
Open this post in threaded view
|

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

Sean Conner
It was thus said that the Great Jonathan Goble once stated:

> On Mon, Sep 21, 2015 at 6:17 PM, Jonathan Hunter
> <[hidden email]> 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:

  Well, actually, he *could* break out of it:

        okay,results = pcall(dbh.query,dbh,todnew_query,function(result)
          if criteria(result) then
            error(result)
          end)

  The error() is caught by pcall() and passes the "error" (which in this
case, isn't an error but the desired result) back to the caller.  If okay is
true, then db.query() ran to completion and didn't find anything, otherwise,
if okay if false, there's a result.  

  Hmm ... perhaps better would be:

        result,data = pcall(...)

would be better:

        if result then
          use(data)
        else
          error("the real error")
        end

  -spc (But I think this approach is ... um ... ugly ... )


Reply | Threaded
Open this post in threaded view
|

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

Alexey Melnichuk-2
In reply to this post by Jonathan Hunter

> local todnew_query = string.format("select
> priority,dayofmonth,month,year,dayofmonth from timeofday_new where
> todname='sales' order by priority;")
>                                    assert
> (dbh:query(todnew_query,function(todresult)

> Now with using the function it loops through the rows, in this
> case, priority 1 and 2 rows, but if I match on the first entry with an if statement, such as;

It  hard  to say if you do not say which lib you use. I assume you use
FS.  If  so just return any number > 0. Otherwise read the doc/source.
Or try return different values



--
С уважением,
 Alexey                          mailto:[hidden email]


---
Это сообщение проверено на вирусы антивирусом Avast.
https://www.avast.com/antivirus


Reply | Threaded
Open this post in threaded view
|

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

Jonathan Hunter
Hi Guys,

Thank you all so much for the response so far.

And yes I use the FreeSWITCH Database Handler (freeswitch.dbh) which allows connection from my lua script.

I can get results fine, its just ensuring as soon as I get a match based on month and  day of month that I break out of the function which is returning the results, as looking at previous freeswitch related posts I have seen;

You can pass a function as second argument to dbh:query, like so:

dbh = freeswitch.Dbh(odbc_dsn, odbc_user, odbc_pass)
dbh:query("select * from some_table", function(row)     -- here it  
loops through all rows
	for key, val in pairs(row) do                           -- here it  
loops through all columns
		stream:write(string.format("%25s : %s\n", key, val))
	end
	stream:write("\n")
end))

This will run the anonymous function for each row, where each  
iteration of the function will be passed a row in the form of a table.

Each row (table) will have the following syntax:

{ ["column_name_1"] = "row_value_1", ["column_name_2" = "row_value_2" }
So I thought it made sense to go to you Lua guru's to give me some pointers, as the documentation is none existent in terms of how you can control/break out of this function if you were to get a match.

Hope this makes sense!

Thanks again

Jon

On Tue, Sep 22, 2015 at 5:15 AM, Alexey Melnichuk <[hidden email]> wrote:

> local todnew_query = string.format("select
> priority,dayofmonth,month,year,dayofmonth from timeofday_new where
> todname='sales' order by priority;")
>                                    assert
> (dbh:query(todnew_query,function(todresult)

> Now with using the function it loops through the rows, in this
> case, priority 1 and 2 rows, but if I match on the first entry with an if statement, such as;

It  hard  to say if you do not say which lib you use. I assume you use
FS.  If  so just return any number > 0. Otherwise read the doc/source.
Or try return different values



--
С уважением,
 Alexey                          mailto:[hidden email]


---
Это сообщение проверено на вирусы антивирусом Avast.
https://www.avast.com/antivirus





--
Jonathan Hunter
Technical Director /Telephony Developer
M:(+44) 7917 190 438

Reply | Threaded
Open this post in threaded view
|

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

Tomás Guisasola-2
Hi Jonathan

I checked the docs at:

https://freeswitch.org/confluence/display/FREESWITCH/Lua+API+Reference

There I found that:

------------------------------------------------------------------
dbh:query("query", function()) takes the query as a string and an
optional Lua callback function that is called on each row returned by
the db.

     The callback function is passed a table representation of the
current row for each iteration of the loop.
     Syntax of each row is: { ["column_name_1"] = "value_1",
["column_name_2"] = "value_2" }.
     If you (optionally) return a number other than 0 from the
callback-function, you'll break the loop.
------------------------------------------------------------------

Note the last line.  As Alexey already told, it seems that you can
return "a number other than 0" to break the loop.  In that case you
should change your function:

------------------------------------------------------------------
assert (dbh:query(todnew_query,function(todresult)
...
        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?
           return 1    <<--- try returning a number other than 0 here
...
------------------------------------------------------------------

Regards,
Tomás

On 2015-09-22 04:50, Jonathan Hunter wrote:

> Hi Guys,
>
> Thank you all so much for the response so far.
>
> And yes I use the FreeSWITCH Database Handler (freeswitch.dbh) which
> allows connection from my lua script.
>
> I can get results fine, its just ensuring as soon as I get a match
> based on month and  day of month that I break out of the function
> which is returning the results, as looking at previous freeswitch
> related posts I have seen;
>
> You can pass a function as second argument to dbh:query, like so:
>
> dbh = freeswitch.Dbh(odbc_dsn, odbc_user, odbc_pass)
> dbh:query("select * from some_table", function(row) -- here it
> loops through all rows
>  for key, val in pairs(row) do -- here it
> loops through all columns
>  stream:write(string.format("%25s : %sn", key, val))
>  end
>  stream:write("n")
> end))
>
> This will run the anonymous function for each row, where each
> iteration of the function will be passed a row in the form of a table.
>
> Each row (table) will have the following syntax:
>
> { ["column_name_1"] = "row_value_1", ["column_name_2" = "row_value_2"
> }
>
> So I thought it made sense to go to you Lua guru's to give me some
> pointers, as the documentation is none existent in terms of how you
> can control/break out of this function if you were to get a match.
>
> Hope this makes sense!
>
> Thanks again
>
> Jon
>
> On Tue, Sep 22, 2015 at 5:15 AM, Alexey Melnichuk
> <[hidden email]> wrote:
>
>>> local todnew_query = string.format("select
>>> priority,dayofmonth,month,year,dayofmonth from timeofday_new
>> where
>>> todname='sales' order by priority;")
>>> assert
>>> (dbh:query(todnew_query,function(todresult)
>>
>>> Now with using the function it loops through the rows, in this
>>> case, priority 1 and 2 rows, but if I match on the first entry
>> with an if statement, such as;
>>
>> It hard to say if you do not say which lib you use. I assume you
>> use
>> FS. If so just return any number > 0. Otherwise read the
>> doc/source.
>> Or try return different values
>>
>> --
>> С уважением,
>> Alexey mailto:[hidden email]
>>
>> ---
>> Это сообщение проверено на вирусы
>> антивирусом Avast.
>> https://www.avast.com/antivirus [1]
>
> --
>
> Jonathan HunterTechnical Director /Telephony Developer
> M:(+44) 7917 190 438
> Email:[hidden email]
>
>
>
> Links:
> ------
> [1] https://www.avast.com/antivirus

Reply | Threaded
Open this post in threaded view
|

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

Jonathan Hunter
Hi Tomas,

Thank you and the other guys so much, that works a treat!!

Many thanks guys :)

Jon


ation of the loop.
    Syntax of each row is: { ["column_name_1"] = "value_1", ["column_name_2"] = "value_2" }.
    If you (optionally) return a number other than 0 from the callback-function, you'll break the loop.
------------------------------------------------------------------

Note the last line.  As Alexey already told, it seems that you can return "a number other than 0" to break the loop.  In that case you should change your function:

------------------------------------------------------------------
assert (dbh:query(todnew_query,function(todresult)
...
       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?
          return 1    <<--- try returning a number other than 0 here
...
------------------------------------------------------------------

Regards,
Tomás

On 2015-09-22 04:50, Jonathan Hunter wrote:
Hi Guys,

Thank you all so much for the response so far.

And yes I use the FreeSWITCH Database Handler (freeswitch.dbh) which
allows connection from my lua script.

I can get results fine, its just ensuring as soon as I get a match
based on month and  day of month that I break out of the function
which is returning the results, as looking at previous freeswitch
related posts I have seen;

You can pass a function as second argument to dbh:query, like so:

dbh = freeswitch.Dbh(odbc_dsn, odbc_user, odbc_pass)
dbh:query("select * from some_table", function(row) -- here it
loops through all rows
 for key, val in pairs(row) do -- here it
loops through all columns
 stream:write(string.format("%25s : %sn", key, val))

 end
 stream:write("n")
end))

This will run the anonymous function for each row, where each
iteration of the function will be passed a row in the form of a table.

Each row (table) will have the following syntax:

{ ["column_name_1"] = "row_value_1", ["column_name_2" = "row_value_2"
}

So I thought it made sense to go to you Lua guru's to give me some
pointers, as the documentation is none existent in terms of how you
can control/break out of this function if you were to get a match.

Hope this makes sense!

Thanks again

Jon

On Tue, Sep 22, 2015 at 5:15 AM, Alexey Melnichuk
<[hidden email]> wrote:

local todnew_query = string.format("select
priority,dayofmonth,month,year,dayofmonth from timeofday_new
where
todname='sales' order by priority;")
assert
(dbh:query(todnew_query,function(todresult)

Now with using the function it loops through the rows, in this
case, priority 1 and 2 rows, but if I match on the first entry
with an if statement, such as;

It hard to say if you do not say which lib you use. I assume you
use
FS. If so just return any number > 0. Otherwise read the
doc/source.
Or try return different values

--
С уважением,
Alexey mailto:[hidden email]

---
Это сообщение проверено на вирусы
антивирусом Avast.
https://www.avast.com/antivirus [1]

--

Jonathan HunterTechnical Director /Telephony Developer
M:<a href="tel:%28%2B44%29%207917%20190%20438" value="+447917190438" target="_blank">(+44) 7917 190 438
[hidden email]



Links:
------
[1] https://www.avast.com/antivirus



--
Jonathan Hunter
Technical Director /Telephony Developer
M:(+44) 7917 190 438