All
Accepted answers
Jean Millette · Oct 15, 2025 go to post

Thank you for the links; I'll investigate the differences in NLS/locale settings between the systems.

I'm helping to debug an app that uses $c(254) and $c(255) as line and field terminators for parsing a large ASCII data file. I moved the relevant code and the data file to a system I use for general debugging and was surprised by the different behavior of those $c() calls, hence the question.

I'll post what I needed to reconfigure to get the "debugging" system to work like the original system.

Thanks again.

Jean Millette · Aug 15, 2025 go to post

Thanks all for the feedback and interesting code examples. Although it's twice as slow as the approach as @Julius Kavay's clever numbers-only approach, I'm going with the string manipulation approach after all. I find it easier to document and therefore easier for an ObjectScript newby to understand. (Newby asks: "What do all those numbers mean? ☺)

/// Round the supplied PosixTime to whole seconds (strip microseconds)
ClassMethod RoundPosixToSeconds(posix As %Library.PosixTime) As %Library.PosixTime
{

    // Get the String representation of the posix time
    set str = ##class(%Library.PosixTime).LogicalToTimeStamp(posix)

    // Strip the microseconds value and the decimal point
    set stripped = $E(str,1,19)
    
    // Reconstruct the PosixTime object from the stripped String representation
    set newposix = ##class(%Library.PosixTime).TimeStampToLogical(stripped)
    quit newposix
}

NOTE: That last comment was generated by Tabnine VS Code extension. Needs an edit. 

I also appreciate the "timing" code that Julius provided. Here are the stats (after I ended up using $E instead of $P. (Thank you @Robert Cemper !)

CRMBI>set posix = 1154669852181849976
CRMBI>while $zh#1 {} s t=$zh f i=1:1:1E6 { s x=##class(CRMBI.JSONSynced).RoundPosixToSeconds(posix) } w $zh-t
1.969733
CRMBI>while $zh#1 {} s t=$zh f i=1:1:1E6 { s x=##class(CRMBI.JSONSynced).RoundPosixToSeconds(posix) } w $zh-t
2.025097
CRMBI>while $zh#1 {} s t=$zh f i=1:1:1E6 { s x=##class(CRMBI.JSONSynced).RoundPosixToSeconds(posix) } w $zh-t
1.975352
CRMBI>while $zh#1 {} s t=$zh f i=1:1:1E6 { s x=##class(CRMBI.JSONSynced).PosixToTimeStamp(posix) } w $zh-t
1.057825
CRMBI>while $zh#1 {} s t=$zh f i=1:1:1E6 { s x=##class(CRMBI.JSONSynced).PosixToTimeStamp(posix) } w $zh-t
.92929
CRMBI>while $zh#1 {} s t=$zh f i=1:1:1E6 { s x=##class(CRMBI.JSONSynced).PosixToTimeStamp(posix) } w $zh-t
.9343
CRMBI>while $zh#1 {} s t=$zh f i=1:1:1E6 { s x=##class(CRMBI.JSONSynced).PosixToTimeStamp(posix) } w $zh-t
.941494
CRMBI>


Thank you all again

Jean Millette · Aug 14, 2025 go to post

I should have copied and saved its suggestion, but the DC AI offered another approach that also required some string manipulation. :-)

Jean Millette · Jun 3, 2025 go to post

Hi again Robert,

I also would have expected the plans for both 18 and 19 to be the same, but "quelle surprise!":

18: Beautiful, expected plan:

19: What? I must have messed something up in the evolution of this class to have such an expensive plan that produces incorrect results:

I'll investigate further to figure out how to address that second plan, but any input from you or others would be welcome.

Thanks and best regards.

Jean Millette · Jun 3, 2025 go to post

Thank you, Robert (great to hear from you)! I will give those a shot to see what's going on and potentially clear cached queries if nothing obviously problematic shows up in the plans.

Best regards, Jean

Jean Millette · Feb 13, 2025 go to post

Try concatenating the new item as follows:

set mylist = ""
for i = 1:1:5 {
    set item = "item"_i
    set mylist = mylist_$ListBuild(item)
}

The output is:

zw mylist
mylist=$lb("item1","item2","item3","item4","item5")
Jean Millette · Feb 7, 2025 go to post

It's been almost 9 years @Eduard Lebedyuk :-) Have ever figured out how to call a linked procedure when running a select for data from a linked table?

When I execute a select statement with only the call to the linked stored procedure, all works ok:

select CRMBI.getPlanningRate(11,2023)

Expression_1
0.94

1 row(s) affected

But if I try to make that same call within a query to another table in that same package, I get errors:

select ISCurrencyID, CRMBI.getPlanningRate(ISCurrencyID,2024) from CRMBI.CurrencyRate

  [SQLCODE: <-223>:<Gateway query Prepare() failed>]

  [%msg: <Connection 'CRMBILINKTABLES': Prepare(select T1.ISCurrencyID AS C1,(T1.ISCurrencyID...): SQLState: (42000) NativeError: [1] Message: [Iris ODBC][State : 42000][Native Code 1] [c:\intersystems\iris2021v1\bin\irisdb.exe] [SQLCODE: <-1>:<Invalid SQL statement>] [Location: <Prepare>] [%msg: < ) expected, , found ^SELECT T1 . ISCurrencyID AS C1 , ( T1 . ISCurrencyID ,>] >]

Other queries to the linked table, without the stored procedure, also works just fine:

select ISCurrencyID, USDRate, effectiveDate from CRMBI.CurrencyRate where ISCurrencyID = 11 and effectiveDate > '2022-12-31' and effectiveDate < '2023-01-31'
 

ISCurrencyID USDRate effectiveDate
11 0.9392000 2023-01-01

1 row(s) affected

 

Might be user error or a later message to the WRC, but thanks in advance if you have any ideas.

Jean Millette · Jan 17, 2025 go to post

Thank you Dmitry; I confirmed it works when the dynamic arrays are stored in variables:

%SYS>w $zv
IRIS for Windows (x86-64) 2024.1.1 (Build 347U) Thu Jul 18 2024 17:35:51 EDT
%SYS>set arr1 = [1,2,3]
 
%SYS>set arr2 = [6,5,4]
 
%SYS>zw arr1.addAll(arr2)
[1,2,3,6,5,4]  ; <DYNAMIC ARRAY>
 
%SYS>do arr1.addAll(arr2)
 
%SYS>zw arr1
arr1=[1,2,3,6,5,4,6,5,4]  ; <DYNAMIC ARRAY>
Jean Millette · Aug 14, 2024 go to post

I'm a big fan of the "old-school" debugging technique of embedding code to store interesting data into globals for later review after running the code to be debugged.

In ObjectScript, the command would look something like this:

Set ^mtempJJM("test", $i(^mtempJJM("test")) = "self=<"_self_">, "_name=<"_name_">"

And then from terminal, issuing this command:

zw ^mtempJJM("test")

One can do something similar in Python using the syntax described on the pages linked below (syntax on how to increment the global index is left to the reader 😀 ):

https://docs.intersystems.com/iris20242/csp/docbook/DocBook.UI.Page.cls…

Global Reference API | Using Embedded Python | InterSystems IRIS Data Platform 2024.2
 

Jean Millette · Jun 13, 2024 go to post

Thanks for the vote Ben! I wanted to test the response to this idea here first before posting to other spots. Will be posting to the appropriate MS Discussion Forums shortly (great idea!). Thanks again.

Jean Millette · Jun 11, 2024 go to post

Thank you @Anastasia Dyubaylo for posting this presentation from last year's Global Summit. I'm pleased to say that all continues to go "swimmingly" with our InterSystems IRIS Data Lake.

Jean Millette · Mar 11, 2024 go to post

Thank you Luis for the link and for offering to save me a click (I admit I did click so I could read the details :-)).

Best regards,

Jean

Jean Millette · Oct 16, 2023 go to post

Thank you David for writing this up.

Will the Schema-level GRANT command (e.g., GRANT SELECT ON SCHEMA SQLUser TO NS1_Read) also provide "SELECT" privileges to the Views and any Stored Procedures in the schema or only to the tables?

Thanks again

Jean Millette · Apr 28, 2023 go to post

An update on this...

I had assumed that all was configured properly on the Report Server, particularly with respect to the required connections and gateways as described here: https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cl…

I was mistaken. Also, we are running an older version of the report server (Version 1.12.7981.11681, from Sept. 2021), which does not provide the "New data source and gateway management experience" introduced in May, 2022.

We're planning an upgrade of our Power BI Report Server and backing SQL Server and I'll revisit the use of DirectQuery to IRIS with some of our reports after that upgrade.

Thanks again Carmen for the pointers and I'll update this post after getting this working as expected.

Jean Millette · Apr 27, 2023 go to post

Hi Carmen, thank you for the clarification on the "Import mode" recommendation and for the link to something to try. Unfortunately, although the report shows up in the right spot on the report server, when I try to "Manage" the "Data Sources", that link is greyed out:

 

I don't believe it's a folder/file permissions issue since I've saved other reports to this folder before.

The example in the link you sent me used Direct Query to the SQL Server for its data. Since report configuration information is also stored in SQL Server and my report has no explicitly-defined connection to SQL Server, could that be an issue?

I'm now looking at the backend SQL Server database where report information is stored for clues. I've found "rsInternalError" entries in the "....ExecutionLogStorage" table for each of the times I've tried to display the problem report from the server, but now looking for details of these errors.

I'll update this post with more details as I find them, but other ideas (how to enable the "Data sources" link?) are most welcome.

Thanks again,

Jean

Jean Millette · Apr 18, 2023 go to post

Thank you, Jeffrey. Yes, it would be good for completeness' sake and for making the "File->New File" less confusing. I'm good with the "right-click" in Explorer pane, etc. (got the same good "in-house" advice from @Pravin Barton )

Jean Millette · Oct 27, 2022 go to post

Thanks @Ben Spead! It was fun to do and hope it helps (in a small way) increase people's use/understanding of embedded python. 

Jean Millette · Apr 22, 2022 go to post

When does "tSC" get defined? This line might be the problem, causing the for loop to stop:

       If&nbsp;$$$ISERR(tSC) Quit 

(Comment out that line and watch the results appear :-))

Jean Millette · Apr 22, 2022 go to post

...and, it's a shot in the dark, but how about putting parenthesis around each condition?

(MessageName = :tMessageName) and (Identifier = :tIdentifier)

Jean Millette · Apr 20, 2022 go to post

The "repr(ex)" Python method also prints a bit more information about the exception:

        except Exception as ex:
            print("Exception: %s" % str(ex))
            print(repr(ex))

The "repr(ex)" Python method also prints a bit more information about the exception (e.g., the type of Error):

Exception: division by zero
ZeroDivisionError('division by zero')
Jean Millette · Apr 20, 2022 go to post

If the goal is to have the method return an instance of %Exception.PythonException, the method could create one with "_New(str) method, supplying the "ex" string as the "str" parameter. The "except" clause would then return the instance.

Not sure of the syntax, but it's likely something like this:

newex = iris.cls('%Exception.PythonException')._New(ex) 
return newex

If, instead of returning that exception, the method would rather "raise" the exception,  there are some helpful hints here:

https://stackoverflow.com/questions/2052390/manually-raising-throwing-a…

Jean Millette · Apr 18, 2022 go to post

How about this?

do ^%G

(I tried to paste a screen shot, but no luck)...will try again, but the above command works to get you to the "Global ^" prompt.