Looking up value in Cache Table via a Business Rule (Routing)

Question

Question

Scott Roth · Jan 7, 2021

#Business Rules #ObjectScript #Caché #Ensemble

I need another set of eyes to look at the following. Instead of a Data Lookup table, I wanted to try to lookup a value against a Cache table that I had build. So I thought I could write a function to run a simple SQL query against the Cache Table and return me a %Boolean value back to my routing rule. However today I found that it was not working properly.

Can someone take a look at the following method and verify that I am doing this correct?

index = column name

value = HL7 field that I am passing into the method

ClassMethod TecsysUnitExists(index As %String, value As %String) As %Boolean
{
  set ValidUnitID=""
  &sql(SELECT :index INTO :ValidUnitID FROM osuwmc_Tecsys.UnitReference WHERE :index = :value)
  if ValidUnitID'=""
  {
  quit 1
  }
  quit 0
  }
}

Thanks

Scott

Product version: Ensemble 2018.1

$ZV: Cache for UNIX (IBM AIX for System Power System-64) 2018.1.3 (Build 414U) Mon Oct 28 2019 11:24:02 EDT

Discussion (4)2

Log in or sign up to continue

Marc Mundt · Feb 5, 2021

%SQL.Statement's %Execute method returns a result set (%SQL.StatementResult) object, not a %Status.

You can check if any rows were returned by checking %ROWCOUNT or %Next() in the StatementResult.

0 0

score 0 · Answer 1 · 2021-01-07T10:01:29-05:00

If "index" is a column name in the table then it shouldn't have a colon at the front.

Also, it's best to check the SQLCODE in order to handle errors:

if SQLCODE < 0 {
   // Do something about an error
} else {
   quit myReturnValue
}

score 0 · Answer 2 · 2021-01-07T10:13:46-05:00

I didn't read your post carefully enough -- I see that you're passing the column name as a variable named "index". Embedded SQL doesn't allow using host variables (e.g. :myVar ) in place of an identifier:

A host variable cannot be used to specify an SQL identifier, such as a schema name, table name, field name, or cursor name. A host variable cannot be used to specify an SQL keyword.

The alternative would be to switch from embedded SQL to Dynamic SQL, which would allow you to manually construct your query string before executing it:

set myQuery="SELECT "_index_" FROM osuwmc_Tecsys.UnitReference WHERE "_index_" = ?"

"value" would then be passed as a parameter when executing the statement and you would use "%Get" in the result set to fetch the value for ValidUnitID.

score 0 · Answer 3 · 2021-02-05T13:15:33-05:00

I was never able to get this to work, and now I have another case where I need to do something similar as I have a linked table that is dynamic and need to use as a filter in a router. Can someone see what I might be doing wrong?

Thanks

Scott