count(*) from a sql dynamic query

Question

Question

Krishnaveni Kapu · Mar 27

#Caché #InterSystems IRIS for Health #Ensemble #InterSystems IRIS

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 ?

Discussion (8)3

Log in or sign up to continue

Laura Blázquez ... · Mar 28

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

0 0

score 1 · Answer 1 · 2025-03-27T20:11:33-04:00

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

score 1 · Answer 2 · 2025-03-28T02:36:12-04:00

Vitaliy Serdtsev · Mar 28

There are many examples in the documentation: Using Dynamic SQL

1 0

score 0 · Answer 3 · 2025-03-28T04:27:54-04:00

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.

score 0 · Answer 4 · 2025-03-28T06:39:52-04:00

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 ;

score 0 · Answer 5 · 2025-03-28T06:46:45-04:00

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?

score 0 · Answer 6 · 2025-03-28T07:54:37-04:00

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

-------------------------------

score 0 · Answer 7 · 2025-03-28T10:55:43-04:00

Krishnaveni Kapu · Mar 28

Thanks , it worked.

0 0