SQL Host Variables missing ?Contestant

Host Variables are a rather common programming feature in many implementations of SQL. 
A recent question in DC made me aware that in IRIS, Caché, Ensemble, ... 
host variables just exist within embedded SQL

>  You can supply host variables for Embedded SQL queries only. <

Related examples are included in the available Documentation

This is a description  for a workaround if you don't / can't use embedded SQL.
The basic idea is to use PPG (Process Private Globals)  to store the mimic of a host variable.
A major reason to use PPG is the fact that they get deleted automatically at the end of process.
Another idea is to avoid conflicts with local variable arrays and local versus global variable scope
In addition there is no need for an clean up activity.

In fact it is a set of 3 SQL Methods:

• HDEC to mimic SQL DECLARE @var and SQL SET @var 
• HGETV and HGETI  to return the variable value as VARCHAR or INT
  • As a side feature NULL is returned if the variable wasn't set of declared before
  • if required returning any other data type is rather easy
• HDEL is provided if there raises the unlikely need to delete a host variable

Class User.HostVar
{
ClassMethod GetV(name) As %String [ SqlName = HGETV, SqlProc ]
{
    quit $get(^||host($g(name,"%")))
}
ClassMethod GetI(name) As %Integer [ SqlName = HGETI, SqlProc ]
{
    quit $get(^||host($g(name,"%")))
}
ClassMethod Declare(name, val) As %Integer [ SqlName = HDEC, SqlProc ]
{
    set ^||host($g(name,"%"))=$g(val)
    quit $$$OK
}
ClassMethod Del(name) As %Integer [ SqlName = HDEL, SqlProc ]
{
    kill ^||host($g(name,"%"))
    quit $$$OK
}
}

My example contains 2 variants for implementation
The first is straight ObjectScript,
this variant is also available in IPM. 

The second is SQL based and can be implemented also over ODBC / JDBC 

CREATE or REPLACE
PROCEDURE HDEC(IN name VARCHAR(50), IN val VARCHAR(50)) RETURNS INT
  LANGUAGE OBJECTSCRIPT 
  { 	set ^||host($g(name,"%"))=$g(val)
	quit $$$OK
  } 

CREATE or REPLACE PROCEDURE
HGETV(IN name VARCHAR(50)) RETURNS VARCHAR(50)
  LANGUAGE OBJECTSCRIPT 
  {
 	quit $get(^||host($g(name,"%")))
  }

CREATE or REPLACE PROCEDURE
HGETI(IN name VARCHAR(50)) RETURNS INT
  LANGUAGE OBJECTSCRIPT 
  {
 	quit $get(^||host($g(name,"%")))
  }  

CREATE or REPLACE  
PROCEDURE HDEL(IN name VARCHAR(50)) RETURNS INT
  LANGUAGE OBJECTSCRIPT 
  { 	kill ^||host($g(name,"%"))
	quit $$$OK
  } 

Both variants are designed to be usable without a package prefix.
Therefore used package User (the default) mapping to default schema SQLUser in SQL.

During testing I detected that forcing a sequence of Setting and Consuming
this host variables can be a tricky exercise if you want to execute it inside
a single SQL statement.
I managed to provide a predictable sequence only using a CASE construct.

SELECT 
CASE
  WHEN HDEC('rcc',66) > 0  -- set host var 
   THEN HGETI('rcc')-5||' ** '||HGETV('rcc')
END  

Within a stored procedure you don't face this problem.

My example and also the provided package in Open Exchange were built
and tested on IRIS. Though the construct is rather basic and also runs
without any change on Caché, Ensemble, ... and it's derivates and
of course on all HEALTH* derivates of IRIS.

My only doubt is about a sharded database in combination with 
Process Private Globals. I had just no setup available for testing  this scenario. 

I don't assume that this proposal is the ultimate solution.
Specific situations may require a more sophisticated approach
or otherwise more simple constructions.

As mentioned earlier I see the big advantage to have a solution
that can be used over ans ODBC or JDBC access and doesn't
require coding with VSCode or Studio.

Video

GitHub

Implementation of Ideas Portal

 

Hoping for your votes.in contest.