All
Accepted answers
Kevin Chan · Dec 30, 2021 go to post

Yeah, the JVM encoding problem has been discovered internally and we're actively trying to correct it.

Kevin Chan · Dec 30, 2021 go to post

The %qparsets issue that you're seeing has been resolved internally and should be shipped as part of IRIS 2022.1.0

Kevin Chan · Dec 30, 2021 go to post

To resolve the issues with airlines.dat, do the following

Add to the JVM arguments of %Java Sever, "-Dfile.encoding=UTF-8" without the quotes

Edit airlines.dat and make the following changes

  • Replace "\N" which is a MySQL specific token with "" (i.e. the empty string). Due to specific interactions with InterSystem's JDBC clients with IRIS, "" is interpreted as NULL when inserted as a string. 
  • Replace "\\'" (the escaped apostrophe) with "'" (just the single apostrophe) as InterSystem's JDBC clients do not require the escape character (which would otherwise be required in SQL).

This will allow all 6162 rows be inserted.

Kevin Chan · Mar 29, 2021 go to post

Are you running $system.OBJ.Load("name of file") without specifying any qualifiers? If so, I believe the default qualifiers are "CUK" (compile, update only, keep source). You might want to try $system.OBJ.Load("name of file", "CK") and see if that helps.

Kevin Chan · Mar 25, 2021 go to post

You can run a query like SELECT * FROM someTable ORDER BY %ID and it will order by the row ID without including it as part of the result set. Ultimately we would need more information (like the query you're running) to provide additional answers, but ORDER BY %ID sounds like what you want to use.

Kevin Chan · Mar 17, 2021 go to post

$system.OBJ.IsUpToDate()might fit the bill for your first question.

set sc = $system.OBJ.GetClassList(.classes)
set cls="", outdated=""
for {
    set cls = $order(classes(cls))
    quit:(cls="")
    if '$system.OBJ.IsUpToDate(cls) set outdated = outdated _ $lb(cls)
}
for i=1:1:$listlength(outdated) {
    write $listget(outdated, i)
}

should work for your 2nd question (documentation here: https://docs.intersystems.com/irislatest/csp/documatic/%25CSP.Documatic…)

Kevin Chan · Oct 1, 2020 go to post

Create a simple %Status like

set status = $system.Status.Error(5001, "This is an error")

and if you do 

zwrite status
status="0 "_$lb($lb(5001,"This is an error",, ....."

I cut out a significant chunk (...) because it's not relevant.

You can see that the first character in status is "0", that is because if you try a unary operator

+status, -status, 'status

 It will evaluate to the integer 0 and effectively work as a boolean flag. The part that matters is after the "0" inside the $listbuild.

The first element of $listbuild is the error code, followed by the message.

Because you set Test to 0 and nothing else, Objectscript recognizes that as a failure condition but has absolutely no information to provide beyond that. The recommendation is that you properly assign Test as a fully qualified %Status or change it to a %String where its value is equal to 

$system.Status.GetErrorText(/* some %Status instance*/)
Kevin Chan · Sep 12, 2020 go to post

If you're using IRIS 2020.2.0 or above (both target and source instances must be ^2020.2.0), you can try to leverage %Net.DB.Iris. It's an implementation of IRIS Native for Java/.NET/Python/Node except for Objectscript. If there is enough interest for features like %Net.DB.Iris, @Bob Kuszewski would love to hear about it and communicate that interest over to the development teams!

Kevin Chan · Aug 14, 2020 go to post

I want to make sure, AppSeguimientoAnadidaReferenciaJARSexternos.jar  is included on the "Additional Paths".

I searched the Operation's XML definition and I didn't see it there.

Kevin Chan · Aug 14, 2020 go to post

What Java version are you using?

Can you provide the output of `jar -tf <name of your jar file>`?

NoClassDefFoundError is usually an indication that the classpath (as defined by the classloading operations performed) is not properly setup.

If your jar file isn't properly structured, that's the problem.

Kevin Chan · Aug 3, 2020 go to post

If you run "jar -tf" onto the jar file,  does the directory structure of the jar file match example/KafkaBusinessOperation?

Kevin Chan · Jul 15, 2020 go to post

SELECT %ODBCOUT(TO_DATE('5/8/2020','MM/DD/YYYY'))

will also work. 

Kevin Chan · Jul 14, 2020 go to post

The logfile will be in the working directory of wherever the JDBC process was launched, not necessarily your local working directory. If you're working from a Unix system, you can specify a full filepath. If you're working from Windows, then unfortunately, that will be an issue. A bug was recently fixed that allows the full filepath to be specified via the URL but that was only fixed recently in IRIS.

Kevin Chan · Jul 14, 2020 go to post

$system.SQL.Purge() is objectscript code. Sorry for assuming you were using IRIS Terminal.

If you're using JDBC, please edit your JDBC url to look like the following:

jdbc:IRIS://host:port/namespace/logfile

logfile will be written to the current directory of wherever the JDBC process in launched (i.e. the working directory of the JVM process).

Kevin Chan · Jul 14, 2020 go to post

%0AmBs1+1^%sqlcq.SMTKTUAT.cls498.1 is the label and offset of where in the cached query the error was thrown, but not the actual error itself.

How are you running the SQL query? ODBC? JDBC? Embedded? Dynamic? If ODBC or JDBC, do you have any logs?

Alternatively, try purging your cached queries ($ysstem.SQL.Purge()) and reattempt. There could be an issue with the cached query itself.

Kevin Chan · Jun 1, 2020 go to post

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?

Kevin Chan · May 14, 2020 go to post

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

Kevin Chan · Mar 19, 2020 go to post

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.

Kevin Chan · Mar 11, 2020 go to post

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

Kevin Chan · Jan 15, 2020 go to post

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.

Kevin Chan · Dec 30, 2019 go to post

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)
INSERT INTO testTable VALUES(1 NULL, NULL,4)

-------------------------

USER>set stmt = ##class(%SQL.Statement).%New()
USER>set query = 1, query(1) = "SELECT * FROM testTable"
USER>write stmt.%Prepare(.query)
1
USER>set rs = stmt.%Execute()
USER>write rs.%Next()
1
USER>write rs.%Get("Column B")
2
USER>write rs.%Next()
1
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

Kevin Chan · Nov 28, 2019 go to post

https://docs.intersystems.com/irislatest/csp/docbook/Doc.View.cls?KEY=GSQL_procedures
 

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)
Kevin Chan · Oct 29, 2019 go to post

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);
    pstmt.executeUpdate();
}