New post
AllAccepted answers
Dan Pasco · Mar 24, 2022

There is a project that will be available soon (don't ask me to define "soon") that will allow the Java programmer to load and compile sources from the local system into an IRIS Server. The IRIS Server does not need to be running on the same system where the files are located.

This example is slightly old as the current implementation of load() returns a list of items loaded and compile() returns a list of items compiled. There are load implementations that accept directories, individual files, any Java streamable, and also JAR files.

SourceLoader sourceLoader = new SourceLoader(connection);
Path path = Paths.get("path/to/samples/cls/Sample");
sourceLoader.load(path, null);
sourceLoader.compile(true);
Dan Pasco · Feb 22, 2022

When not using EXTENTSET mapping, all indexes are stored by default in a single global. To keep the index structures separate the first subscript of the index global, by default, is the index name. This creates two conditions that impact performance negatively. Firstly, the index name subscript creates a longer reference for each index key. That leaves less room for index key subscripts and longer references consume resources. Secondly, the size of the index global is increased and the number of index key values per block is reduced. Fewer key values per block means more blocks read during query execution and a less efficient global cache. The index name subscript is essentially "noise".

Using EXTENTSET mapping removes the need for an index identifying subscript (the global is the index).

Dan Pasco · Feb 22, 2022

First of all, the global names are not random but are based on a hash algorithm to reduce the length of the global name and to reduce the probability of global name collisions. This is the default global name assignment when USEEXTENTSET is true. The benefits of using EXTENTSET mapping are many but primarily, the size of indexes is reduced substantially making index filing is faster. Queries using indexes are also likely faster with USEEXTENTSET mapping.

With EXTENTSET, the storage default global is used as a base value for the set of globals used by the extent. Each index, including the master map/master data index (MDI) - also known as the "DATALOCATION", is the base value (the EXTENTLOCATION) plus ".n" where "n" is a number computed when mapping globals to indexes. The master map/MDI always is ".1". In the original post, the DEFAULTGLOBAL setting overrides the hash computation of the EXTENTLOCATION.

Compare the index reference for a simple name index on Sample.Person between USEEXTENTSET = 1 and USEEXTENTSET = 0. ^Sample.PersonI("PersonNameIndex","DOE, JOHN Q", 100) vs ^Sample.Person.2("DOE, JOHN Q", 100).

The developer has to choose whether to use conveniently named globals or better performance.

Note that not all class definitions/tables will benefit from USEEXTENTSET mapping. But many will.

Dan Pasco · Jan 12, 2022

Yes, of course "inverse" - sorry.

Persistent vs RegisteredObject - not a problem but you are calling a simple class method so we don't need any super class. I used this implementation for the IRIS Class:

Class Utils.CSW1JavaFunctions
{
    ClassMethod IrisReturn(user = "user", pass = "pass") As %Stream.GlobalBinary
    {
         try {
             set cswStream=##class(%Stream.GlobalBinary).%New()
             set cswReturn = {"user":(user), "pass":(pass) }
             do cswReturn.%ToJSON(cswStream)
             return cswStream
         } catch exc {
             write !,"Caught Exception on server: ", exc.AsSQLMessage()
         }
    }
}

 

 

And this is a crude hack at the Java code - the anonymous InputStream class could use more work but it does run for this simple example. I'll leave the rest of the InputStream coding to you.

package utils;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.intersystems.jdbc.*;

import java.io.*;
import java.sql.SQLException;


public class Reader {
    public static final String CACHE_CLASS_NAME = "Utils.CSW1JavaFunctions";

    public IRISConnection connection;
    public IRIS iris;

    public Reader(IRISConnection connection) throws SQLException {
        this.connection = connection;
        this.iris = IRIS.createIRIS(connection);
    }
    public static void main(String[] args) throws SQLException {
        IRISDataSource dataSource = new IRISDataSource();
        dataSource.setServerName("localhost");
        dataSource.setPortNumber(51776);
        dataSource.setDatabaseName("USER");
        dataSource.setUser("_SYSTEM");
        dataSource.setPassword("SYS");
        IRISConnection connection = (IRISConnection) dataSource.getConnection();
        Reader reader = new Reader(connection);
        try {
            JsonNode jsonNode = reader.execute("IrisReturn", "java", "jpass");
            System.out.println(jsonNode.toString());
        } catch (Exception exc) {
            exc.printStackTrace();
        }
    }

    public JsonNode execute(String method, Object... args) throws Exception {
        ObjectMapper mapper = new ObjectMapper();
        JsonNode jsonNode = null;
        try {
            IRISObject data  = (IRISObject) iris.classMethodObject(CACHE_CLASS_NAME, method, args[0], args[1]);
            InputStream is = new InputStream() {
                byte[] buffer;
                int pos = 0;
                int len = -1;

                @Override
                public int read() throws IOException {
                    if (pos >= len) {
                        getBuffer();
                    }
                    if (len == -1) {
                      return -1;
                    }
                    return buffer[pos++];
                }

                void getBuffer() {
                    pos = 0;
                    IRISReference readLen = new IRISReference(3200);
                    String string = (String) data.invoke("Read", readLen);
                    if (readLen.getLong() == -1) {
                        buffer = null;
                        len = -1;
                    } else {
                        buffer = string.getBytes();
                        len = buffer.length;
                    }
                }
            };

            jsonNode = (JsonNode) mapper.readTree(is);
            return jsonNode;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;
    }
}

Running this produces this output:

/usr/lib/jvm/adoptopenjdk-11-hotspot-amd64/bin/java -javaagent:/home/...
{"user":"java","pass":"jpass"}

Process finished with exit code 0

Dan Pasco · Jan 12, 2022

Interesting! I didn't see which version of InterSystems IRIS you are using but you might try a couple of things:

First, don't return a status value from your method. Instead return a %Stream.GlobalBinary instance. If you still want a status, get it some other way. I recommend just throwing an exception - IRIS Native should handle that okay. The object value you get back will be a proxy object that should allow you to read the stream. In our documentation you might search for "reverse proxy objects".

If get some time, I will try to build a sample of doing this.

Dan Pasco · Jan 4, 2022

I like :sql as a way to launch the shell - so that means this alias should be renamed to something else!!

USER>:sql "select top 2 * from person"
do $system.SQL.Execute("select top 2 * from person").%Display()
id    name    ssn    dob    home_street    home_city    home_state    home_zip
1    Willeke,Thelma K.    934-46-2099    18948    308 Madison Blvd    Bensonhurst    HI    71501
2    Nathanson,Will P.    771-91-1008    37359    9664 Second Place    Jackson    VA    15837

2 Rows(s) Affected
Dan Pasco · Jul 8, 2021

I didn't count the number of question marks but I assume you've verified that it matches the number of columns.

Also, there are limitations on the number of command line arguments you can pass but that number is rather high. I don't recall exactly what it is but it is certainly limited. At one time we established 16 as the maximum number of arguments. As I recall, we did nothing to impose an arbitrary limit but ObjectScript itself does have a limit.

My next questions are meant to establish where the error is reported. You can prepare a dynamic SQL statement and then execute it separately and multiple times. Is the error reported during prepare or during execute?

PS: (edited):

Sorry, I see now your code. You are using a HS Adapter to execute this statement. That leads me to a different question - can you prepare and execute this statement as a Dynamic SQL Statement?

set statement = ##class(%SQL.Statement).%New()

set status = statement.%Prepare(.tSQL)

If status is okay (1) then execute it - set result = statement.%Execute( <your parameter values go here>)

Then check result.%SQLCODE.

In the meantime, I will take a look at the Adapter (not my area but I can still look) to see if there is some limitation.

Dan Pasco · Jul 6, 2021

I agree with Herman - to a point. Leave the IDKEY index alone and just accept the system assigned idkey. In most cases this is the best policy. But - there are potentially several "candidate keys", all can be defined - or not, but one can be chosen to be the "primary key". In that case, define the key not as unique but as "primarykey". Some tooling works better with a defined primary key. It does not interfere with the IDKEY unless it is also defined as the "idkey".

Dan Pasco · Jul 6, 2021

Hi Jonathan,

I am very interested in this problem (I wrote the early version of dynamic statement parameter binding). My first question is simple - can you execute this statement using embedded SQL? If you can then we need to dig further to discover the correct error (some errors get masked as syntax when there is a different problem).

Thanks,

Dan

PS:

Of course, you will have to supply values for ? to test compile this as an embedded statement (:hv1, :hv2... can work) - or - can you try this using an SQL utility or as a ODBC/JDBC statement?

Dan Pasco · Apr 28, 2021 
    set java = $system.external.getJavaGateway()
    do java.addToPath("/home/myhome/mariadb-java-client-2.7.2.jar")
    set mariads = java.new("org.mariadb.jdbc.MariaDbDataSource","jdbc:mariadb://myhost:3306/SAMPLES")
    set mariaconn = mariads.getConnection("myuser","secret")
    set pstmt = mariaconn.prepareStatement("select * from person limit 5")
    set jdbcresult = pstmt.executeQuery()
    set jdbcmetadata = jdbcresult.getMetaData()
    for i=1:1:jdbcmetadata.getColumnCount() {
        write jdbcmetadata.getColumnName(i),$char(9)
    }
    while jdbcresult.next() {
        write !,$increment(rowcnt),$char(9)
        for i=1:1:jdbcmetadata.getColumnCount() {
            write jdbcresult.getString(i),$c(9)
        }
    }
    write !!!,"OR I COULD JUST USE MY ALREADY IMPLEMENTED RESULT SET RENDERER FROM ANOTHER JAVA PROJECT",!!!
    do java.addToPath(..#EXTERNALLIBPATH)
    set ers = java.new("external.test.ExternalResult",pstmt.executeQuery())
    do ers.renderTable()
    do mariaconn.close()
    do java.disconnect()

And the results:

USER>do ##class(external.test.MariaJDBC).communityDemo()
name    ssn    dob    home_street    home_city    home_state    home_zip    
1    Basile,Molly M.    452-57-8033    1994-06-02    1153 First Street    Xavier    SD    98033    
2    Cooke,Howard F.    131-62-3894    2017-12-09    5172 Washington Place    Zanesville    NE    44980    
3    Donaldson,Phil R.    480-79-5019    1990-01-23    4429 Elm Street    Miami    WA    67638    
4    Eisenstien,Michael D.    655-11-6334    1948-08-14    4676 Elm Avenue    Reston    KY    52729    
5    Faust,Mo E.    772-42-3921    2018-01-10    826 Maple Avenue    Youngstown    OH    37180    

OR I COULD JUST USE MY ALREADY IMPLEMENTED RESULT SET RENDERER FROM ANOTHER JAVA PROJECT

┌─────────────────────┬───────────┬──────────┬─────────────────────┬──────────┬──────────┬────────┐
│name                 │ssn        │dob       │home_street          │home_city │home_state│home_zip│
├─────────────────────┼───────────┼──────────┼─────────────────────┼──────────┼──────────┼────────┤
│Basile,Molly M.      │452-57-8033│1994-06-02│1153 First Street    │Xavier    │SD        │98033   │
├─────────────────────┼───────────┼──────────┼─────────────────────┼──────────┼──────────┼────────┤
│Cooke,Howard F.      │131-62-3894│2017-12-09│5172 Washington Place│Zanesville│NE        │44980   │
├─────────────────────┼───────────┼──────────┼─────────────────────┼──────────┼──────────┼────────┤
│Donaldson,Phil R.    │480-79-5019│1990-01-23│4429 Elm Street      │Miami     │WA        │67638   │
├─────────────────────┼───────────┼──────────┼─────────────────────┼──────────┼──────────┼────────┤
│Eisenstien,Michael D.│655-11-6334│1948-08-14│4676 Elm Avenue      │Reston    │KY        │52729   │
├─────────────────────┼───────────┼──────────┼─────────────────────┼──────────┼──────────┼────────┤
│Faust,Mo E.          │772-42-3921│2018-01-10│826 Maple Avenue     │Youngstown│OH        │37180   │
└─────────────────────┴───────────┴──────────┴─────────────────────┴──────────┴──────────┴────────┘