ObjectScript-Over-ODBC

Every now and then you may encounter a situation where for various reasons
ODBC is the only option to access a remote system. Which is sufficient as long as you need to examine or change tables.
But you can’t directly execute some commands or change some Global.

Special thanks @Anna Golitsyna for inspiring me to publish this.

This examples provides 3 Methods projected as SQLprocedure that enable this if other ways of access are blocked.
Typically by some firewall.

SQLprocedure Ping() returns Server::Namespace::$ZV and allows to check the connection
SQLprocedure Xcmd(<commandline>,<resultvar>) executes the command line you submit and returns the result
that you deposit in a variable that you named.
SQLprocedure Gset(<global>,<subscript>,<value>,<$data>) allows you to set or delete a global node

• <global> is a GlobalName in the remote namespace including leading carret; e.g. ‘^MyGlobal’ (sql quoted!)
• <subscript> stands for the complete subscript including parenthesis ;e.g. ‘(1,3,“something”,3)’ (sql quoted!)
• <$data> controls if you set the Global Node or execute a ZKILL on it; e.g. 1, 11 to set, 0,10 to ZKILL
  As you may guess by the name this is especially useful during a Global copy.
  The procedure Gset is designed to make use of Global Scanning described earlier.
  Combined, they allow a Global copy across any ODBC connection.

Installation:

• On the remote system you need the class provided with this article in OpenExchange
• On the local (source) system you need to define the procedures as Linked SQL Procedures
  SMP>System>SQL> Wizards>Link Procedure
  at that time you local package name is defined (in the examples I used zrccEX)
• If you want to run the Global copy you also need to install the
  Global Scanning class from OEX
  (It is just for comfort)

Examples:

USER>do $system.SQL.Shell()   
SQL Command Line Shell  
[SQL]USER>>select zrcc_EX.Ping()  
Expression_1  
cemper9::CACHE::IRIS for Windows (x86-64) 2020.1 (Build 215U) Mon Mar 30 2020 20:14:33 EDT  

Check existence of Global ^rcc

[SQL]USER>>select zrcc_EX.Xcmd('set %y=$d(^rcc)','%y')  
ok: 10  

Set some value to ^rcc4(1,“demo”,3,4)

[SQL]USER>>select zrcc_EX.Gset('^rcc4','(1,"demo",3,4)','this is a demo',1)  
Expression_1  
ok: ^rcc4(1,"demo",3,4)  

Do a global copy from ^rcc2 to ^rcc4.
First show ^rcc2

USER>>select reference,value,"$DATA" from rcc_G.Scan where rcc_G.scan('^rcc2',4)=1  
Reference       Value   $Data  
 ^rcc2                  10  
(1)             1       1  
(2)             2       11  
(2,"xx")                10  
(2,"xx",1)      "XX1"   1  
(2,"xx",10)     "XX10"  1  
(2,"xx",4)      "XX4"   1  
(2,"xx",7)      "XX7"   1  
(3)             3       1  
(4)             4       11  
(4,"xx")                10  
(4,"xx",1)      "XX1"   1  
(4,"xx",10)     "XX10"  1  
(4,"xx",4)      "XX4"   1  
(4,"xx",7)      "XX7"   1  
(5)             5       1  
16 Rows(s) Affected  

Now run the copy to remote global

[SQL]USER>>select zrcc_EX.Gset('^rcc4',reference,value,"$DATA")  from rcc_G.Scan where rcc_G.scan('^rcc2',4)=1  
Expression_1  
ok: ^rcc4  
ok: ^rcc4(1)  
ok: ^rcc4(2)  
ok: ^rcc4(2,"xx")  
ok: ^rcc4(2,"xx",1)  
ok: ^rcc4(2,"xx",10)  
ok: ^rcc4(2,"xx",4)  
ok: ^rcc4(2,"xx",7)  
ok: ^rcc4(3)  
ok: ^rcc4(4)  
ok: ^rcc4(4,"xx")  
ok: ^rcc4(4,"xx",1)  
ok: ^rcc4(4,"xx",10)  
ok: ^rcc4(4,"xx",4)  
ok: ^rcc4(4,"xx",7)  
ok: ^rcc4(5)  
 16 Rows(s) Affected  

Article in DC

Demo Server SMP
Demo Server WebTerminal

Code Quality