New post
AllAccepted answers
Robert Cemper · May 11, 2018

I'd recommend JAVA, JDBC Cache, SQL it's a strong and fast combination with strong support,

VB6 is far out of date
VisM is a remainder from before Caché times.

So it's rather an issue of compatibility, maintenance, support  than performance

Objects vs. SQL is depending on what you try to achieve.
Object access is can be an advantage for large and complex objects that you need as a whole item.
As you mention VB6 + VisM the original author probably wasn't very comfortable with SQL at his time.
 

Robert Cemper · May 10, 2018

Welcome to the Caché world!

There is NO limit on commands to be executed !
Don't worry about the number. SQL is consuming a lot of commands.
And Class Queries in %SYS by its nature may even consume more than standard SQL queries.
Just during a quick check of your code example I passed 700k.

A general hint on programming style:
Pls. try to avoid Dot Style and use curly brackets instead. 
This is a very old notation. Not as old as Hieroglyphs, but almost smiley

What I mean. instead of

  I Rset.GetData(1) = Pid D
    . w !,Rset.GetData(1)," ",ClientIPAddress," ",Pid
            //Set rs=$SYSTEM.Process.Terminate(Pid)
            //If rs'=1 {
            //    Write "Terminate Fialure "_" Pid "_Pid,!
            //}
            //Else {
            //    W "Sucess",!
        //    }
    //    }

use 

  I Rset.GetData(1) = Pid {
     w !,Rset.GetData(1)," ",ClientIPAddress," ",Pid
            //Set rs=$SYSTEM.Process.Terminate(Pid)
            //If rs'=1 {
            //    Write "Terminate Fialure "_" Pid "_Pid,!
            //}
            //Else {
            //    W "Sucess",!
        //    }
    //    }
    }

 This makes it much easier for the audience to follow your code, and it is actual coding style,
 

Robert Cemper · May 9, 2018

Congratulations!

docs say:

 Sends any buffered output out the TCP connection; that is, executes a TCP SEND operation on the data in the output buffer.
If the data is compressed (/GZIP) stream data, *-3 sends the data without marking the compression endpoint.

I interpret this: 
up to some limit, it is sent somehow automatically
and the eventual rest needs to be pushed out manually or gets never sent or is to late.

???  surprise

Robert Cemper · May 9, 2018

- you have

Parameter CONNECTION = "canreg,NOCREATE";

- so the name of your Gateway definition is canreg 
- you have no storage for streams defined.
Strange but might be an issue of Caché version version issue.
in 2016.2 it just doesn't compile without.
- more strange is this:

Index MainIndex On null [ IdKey, PrimaryKey ];

The property null doesn't exist in your class.
Without a property null  it doesn't compile for me.

adding manually the missing Storage section and 

Property null As %Integer;

let it compile for me. 

Though this may be a temporary fix I distrust your Link Wizard.
Eventually, you should contact WRC  to investigate the reason.

Robert Cemper · May 7, 2018

I finally got the code to compile with no errors.
When I run it, the code will insert a row in the table "global" in cache.
However, it isn't inserting the information in the external database.

From what you describe you have generated a Local Table
as there is a GLOBAL and you write to your local DB. As you have seen.

If you use Link Table Wizzard to create it, then there is just no global.

The correctly generated class looks similar to this:

/// Generated by the Link Table wizard on 2018-05-07 23:20:46.
Note that you can access the data in this class only when the external database is accessible.
Class GTY.Person Extends %Library.Persistent [. . . . , StorageStrategy = GSQLStorage ]
{
/// Specifies details for the SQL Gateway Connection that this class uses
Parameter CONNECTION = ". . . . . . ,NOCREATE";
.......

Storage GSQLStorage
{
<StreamLocation>^####S</StreamLocation>
<Type>%CacheSQLStorage</Type>
}

}

If you do not see the bolded text in your class,
it is not generated by Link Table Wizard and
you are in the wrong Class / Table with no access to he external table.

Robert Cemper · May 7, 2018

There is a bunch of useful articles you find here searching for CSV.

I just picked one out of them: 
https://community.intersystems.com/post/how-read-csv-file-contents-objects-cache

In a Windows environment, you may also use an ODBC driver (DSN) and handle
Excel as what it is: As a Table using the SQLgateway as you would to with other DBs

You just have to take care to get the bits matching  (32/64). More details:
https://support.office.com/en-us/article/overview-of-connecting-to-importing-data-c0ad7aec-ff1a-4e48-9c21-dc18a102433f
 

Robert Cemper · May 3, 2018

as Jeff mentioned: the Shadowed DB is Read Only.

Execution of some code in a remote instance of Caché is independent of Mirror or not

  • Either pack your code (or its call) into a ClassMethod with a return value.
    mark the Method as  [SqlProc ]
    trigger it by SELECT my.procedure()
  • Or wrap it into some WebService ( REST, SOAP) and execute it that way.