sql - 当 Dbh 查询在 FreeSWITCH 中返回零行时，如何在 Lua 脚本中匹配 "-ERR no reply"？

Question

PostgreSQL 表:

mydb=# table phone_numbers;

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

给定下面的 Lua 脚本，

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()

在 fs_cli 中调用它会产生

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

另一方面，当使用不返回任何行的查询时，例如

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

然后返回“错误”并且甚至没有调用 dbh:query() 回调:

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

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

将“error”放在引号中，因为它看起来不像一个；至少，我尝试了 pcall 但并不开心。

匹配 -ERR no reply 结果(当查询结果为零行时)很重要，以便在这种情况下可以挂断电话。

---

解决方法

仅作记录，我通过使用 EXISTS 或 COALESCE 调整 SQL 查询找到了解决方法，因为它们始终提供可以从脚本匹配的返回值，但我确信有更好的方法。例如:

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)    

Answer 1

如果查询没有结果，回调将不会运行。但是，您可以在回调内部设置一个局部变量，这样您就可以知道回调是否运行(也就是您的查询是否有结果)例如:

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