count(*) from a sql dynamic query
I have dynamic sql select query ,
Set resultset = ##class(%ResultSet).%New()
set sql = "SELECT COUNT(*) FROM "_tableName_" WHERE "_fieldName_">='"_fromDate_"' AND "_fieldName_"<='"_currentDate_"'"
SET SC = resultset.Prepare(sql)
SET SC = resultset.Execute(sql)
How can I print/view the count ?
Comments
%ResultSet in deprecated, use %SQL.Statement instead.
Never concatenate parameters to query text, use placeholders ("?").
Set sql = "SELECT COUNT(*) FROM "_tableName_" WHERE "_fieldName_">=? AND "_fieldName_"<=?"Set resultset = ##class(%SQL.Statement).%ExecDirect(,sql,fromDate,currentDate)
Do tResult.%Display()There are many examples in the documentation: Using Dynamic SQL
Points made by Enrico are 100% valid and you may need to consider them.
In your case, because your column is not named, you can use GetData(n) to get the count:
set sql = "SELECT COUNT(*) FROM "_tableName_" WHERE "_fieldName_">='"_fromDate_"' AND "_fieldName_"<='"_currentDate_"'"set resultset = ##class(%ResultSet).%New("%DynamicQuery:SQL")
set SC = resultset.Prepare(sql)
set SC = resultset.Execute(sql)
write resultset.GetData(1)Obviously don't forget to check the statuses.
set sql = "SELECT COUNT(*) FROM "_tableName_" WHERE "_fieldName_">='"_fromDate_"' AND "_fieldName_"<='"_currentDate_"'"
SET SC = resultset.Prepare(sql)
SET SC = resultset.Execute(sql)
Set recordsDeleted = resultset.GetData(1)
$$$TRACE("recordsDeleted="_recordsDeleted)
Tried this , its not displaying the count ;
Never concatenate parameters to query text, use placeholders ("?").
What's the status (SC) returned by the called methods?
In your code after each "SET SC=....:" add the line:
$$$LOGSTATUS(SC)
What's the status logged?
Status OK
and the cnt as empty (I am expecting the count)
-------------------------------
Set resultset = ##class(%ResultSet).%New("%DynamicQuery:SQL")
set sql = "SELECT COUNT(*) AS CNT FROM "_tableName_" WHERE "_fieldName_">='"_fromDate_"' AND "_fieldName_"<='"_currentDate_"'"
SET SC = resultset.Prepare(sql)
$$$LOGSTATUS(SC)
SET SC = resultset.Execute(sql)
$$$LOGSTATUS(SC)
if (resultset.GetData(1)>0)
$$$TRACE("cnt="_resultset.GetData(1))
-------------------------------
After executing your query, you need to move cursor and then you can get data:
SET SC = resultset.Prepare(sql)
SET SC = resultset.Execute()
if (resultset.Next(.SC)) {
quit:$$$ISERR(SC)
set cnt = resultset.Data("CNT")
}
SET SC = resultset.Close()Thanks , it worked.