How the match "-ERR no reply" in Lua script when Dbh query returns zero rows in FreeSWITCH?

Question

The PostgreSQL table:

mydb=# table phone_numbers;

 pn_id | user_id | phone_number
-------+---------+--------------
     1 |       2 | 5550001111
     4 |       2 | 5552223333

Given the Lua script below,

conn_string =
 "pgsql://hostaddr=1.2.3.4"                 ..
 " dbname=mydb"                             ..
 " user=postgres"                           ..
 " password=postgres"                       ..
 " options='-c client_min_messages=NOTICE'" ..
 " application_name='myapp'"

dbh = freeswitch.Dbh(conn_string)

assert(dbh:connected())
freeswitch.consoleLog("INFO", "lua script: connected to DB")

q =
  "SELECT user_id, phone_number " ..
    "FROM phone_numbers "         ..
    "WHERE phone_number = '5552223333'"

dbh:query(q, function(row)

  freeswitch.consoleLog("INFO", "log from dbh:query callback")

  for column_name, row_val in pairs(row) do
    stream:write(string.format("%5s : %s\n", column_name,  row_val))
  end

end)

dbh:release()

calling it in fs_cli results in

freeswitch@server> lua test.lua
user_id : 2
phone_number : 5552223333

[INFO] switch_cpp.cpp:1443 lua script: connected to DB
[INFO] switch_cpp.cpp:1443 log from dbh:query callback

On the other hand, when using a query that wouldn't return any rows, such as

q =
  "SELECT user_id, phone_number " ..
    "FROM phone_numbers "         ..
    "WHERE phone_number = '1234567890'"

then an "error" is returned and the dbh:query() callback is not even called:

freeswitch@server> lua test.lua
-ERR no reply

[INFO] switch_cpp.cpp:1443 lua script: connected to DB

Putting "error" in quotes, because it doesn't seem to behave as one; at least, I tried pcall but no joy.

Matching the -ERR no reply result (when the query results in zero rows) would be important so that the call could be hung up in that case.

Workaround

Just for the record, I figured out a workaround by tweaking the SQL query using EXISTS or COALESCE as they always provide a return value that can be matched from the script, but I'm sure there's a better way. For example:

q = 
  "SELECT COALESCE("        ..
    "SELECT user_id, phone_number "     ..
    "FROM phone_numbers "               ..
    "WHERE phone_number = '1234567890'" ..
    ", '0'" ..
  ")"

dbh:query(q, function(row)   
  if row.coalesce == "0" then
    freeswitch.consoleLog("INFO", "zero results")         
  end
end)

byoungdale · Accepted Answer · 2019-06-25 01:41:22Z

0

The callback will not run if there are no results from the query. But, you can set a local variable inside of the callback, so you know if the callback ran or not (aka if your query had results) As an example:

q = 
  "SELECT COALESCE("        ..
    "(SELECT phone_number " ..
      "FROM phone_numbers " ..
      "WHERE phone_number = '" .. ani .. "')" ..
    ", '0'" ..
  ")"

local got_results
dbh:query(q, function(row)   
  got_results = true
  -- whatever else you need to do
end)   

if not got_results then
  freeswitch.consoleLog("INFO", "zero results") 
  -- do what you need to with zero results
end

answered Jun 25, 2019 at 1:41

byoungdale

1813 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

toraritte Over a year ago

I updated my question, because it was prone to misinterpretation. I know how to save the callback results, but my problem is that when there are no results from the SQL query, then the callback isn't even called.

Collectives™ on Stack Overflow

How the match "-ERR no reply" in Lua script when Dbh query returns zero rows in FreeSWITCH?

Workaround

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Workaround

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related