Having the jar in the same directory as the application may not be enough to ensure that the JAR is loaded into the vm.

How are you running the logstash application? Is it through a GUI or CLI? If CLI, can you paste (with any sensitive information redacted ofc) the command your running?

Use a Java Gateway if that is available to you.

There's a JDBC example (it uses InterSystems' JDBC driver, but the code can be replaced with the proper driver if you're familiar with JDBC since it users DriverManager)  under %Net.Remote.Java.Test in the JDBC method

Is this Dynamic or Embedded SQL?

I am trying to run this in the server management portal and it does not work with a stub table.

w $system.Status.GetErrorText(##class(SomeClass).IsNonNullCustomerID("1234"))

To unfreeze:

  • Do $SYSTEM.SQL.FreezePlans(0,1,,.Errors) // Unfreezes all SQL statement plans in the current namespace
  • Do $SYSTEM.SQL.FreezePlans(2,1,,.Errors) // Unfreezes all SQL statement plans in the current namespace that were marked Frozen/Upgrade.

Then execute $system.SQL.Purge()

That should unfreeze everything and delete all CQs.

The noted difference between %SQL.StatementResult.%Get() and %Library.ResultSet.%Get() is regarding the actual column names, not the values they contain.

Given that I am using InterSystems IRIS, but here is how I reproduced your example (I don't use a left-join, but they should behave the same)

CREATE TABLE testTable("Column A" INT, "Column B" INT, "Column C" INT, "Column D" INT)
INSERT INTO testTable VALUES(1,2,3,4)


USER>set stmt = ##class(%SQL.Statement).%New()
USER>set query = 1, query(1) = "SELECT * FROM testTable"
USER>write stmt.%Prepare(.query)
USER>set rs = stmt.%Execute()
USER>write rs.%Next()
USER>write rs.%Get("Column B")
USER>write rs.%Next()
USER>zwrite rs.%Get("Column B")

USER>do $system.Status.DisplayError(rs.%Get("Column E"))
DO $SYSTEM.Status.DisplayError(rs.%Get("Column E"))
<PROPERTY DOES NOT EXIST> *Column E,%sqlcq.USER.cls15

As you can see, %SQL.StatementResult.%Get() should only throw an error if you pass in an invalid column name such as "Column E" in the code snippet above.

I recommend that you check the %SQL.StatementMetadata to check if the columns exist in the first place.

USER>set rsmd = rs.%GetMetadata()
USER>set columns = $LB()
USER>for i=0:1:rsmd.columnCount-1 set $li(columns,i+1)=rsmd.columns.GetNext(i).colName

Once you have columns populated, you can use $LISTFIND to determine whether or not the column exists.

If you cannot do the above then you can wrap the use of %SQL.StatementResult.%Get() with a try-catch block as seen below:

while(rs.%Next()) {
     try {
         set var = rs.%Get(columnName)
     } catch {
         set var = ""

I do not recommend the try-catch fallback assignment


I believe this page explains why,  and I will also try my best to condense information:

  • Stored Procedures that return a single value are classified as Stored Functions. These can be SELECTed but not Stored Procedures.
  • Class Queries are SQL-Invokable-Routines (SIRs), but they have additional ClassMethods defined that allow it to be SELECTed. This is namely the <<name>>EXECUTE() and <<name>>FETCH() functions. This is reflected in the CREATE QUERY documentation that warns if EXECUTE() and FETCH() are not defined, SQLCODE -46 is thrown. ( https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_createquery)

If you have a java.sql.PreparedStatement and what seems to be a java.io.InputStream subclass (since you can call read() on it), why not utilize java.sql.PreparedStatement.setBinaryStream(int parameterIndex, InputStream x)? I am not sure which version of com.intersys.jdbc.CacheDriver you are using, but it is likely that your driver version has that API supported.

try (
    java.sql.Connection        cnxn  = DriverMasnager.getConnection(url, cnxnProps);
    java.sql.PreparedStatement pstmt = cnxn.prepareStatement(
            "INSERT INTO tableWithBlobField VALUES(?)");
    pstmt.setBinaryStream(1, binaryStreamFromRestAPI);