ObjectScript-Over-ODBC

Downloads15
Subscribe
0
Bookmark
2
This application is not supported by InterSystems Corporation. Please be notified that you use it at your own responsibility.
Details
Releases
Reviews
Screenshots
Issues
Videos
Articles

What's new in this version

fix docker

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 rccEX.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 rccEX.Xcmd('set %y=$d(^rcc)','%y')  
ok: 10  

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

[SQL]USER>>select rccEX.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 rccEX.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

Rating
4.5 (1)
Category
Technology Example
Works with
CachéEnsembleInterSystems IRIS
Tags
Info
Version
1.0.4
Last updated
2022-12-05
Repository
Open
Documentation
Open
License
Link