All
Accepted answers
Robert Cemper · Apr 14, 2021 go to post

A little bit of cosmetics in your printf could make it JSON formatted.

 print(f'{{"subscript":{subscript},"value":"{value}"}}')

which should result in a nice conveniant JSON object

{
  "subscript":1,
  "value":"2.16.840.1.113883.3.86ISCInterSystems Corporation"
  }

Robert Cemper · Apr 14, 2021 go to post

try:

 Property alternateId As %String(%JSONNULL = 1);

  {
        "alternateId": null ,
        "benefitPlanId": "FLSN4444", 

Robert Cemper · Apr 14, 2021 go to post

If you get back large results sets that you use for further processing PyODBC will be better suited.
But for a small number of values, the overhead at both ends to service ODBC structures may not pay off
since both ends have to get their internal structure in to  ODBC and out of it.
I don't have measured the difference so this is just a guess:
- for the typical embedded SQL returning < 1..10 rows a MethodCall might be more efficient.
This doesn't prevent you from using and tuning an SQL SELECT isolated in IRIS environment. 
In any case, the transfer between PY and IRIS is the slowest piece.
The less data you transport the faster the action is completed.
And transport in blocks wins over isolated pieces in loops.

Robert Cemper · Apr 13, 2021 go to post

try to use a username  with privileged %ALL in Health Share    
to distinguish between network problems and access right problems  

Robert Cemper · Apr 13, 2021 go to post

That's happening due to problems in the network or system stop/restart.
normally it recovers without manual interventions and without data loss.
levels 0 and 1 signal it's an info / warning.

Robert Cemper · Apr 12, 2021 go to post

create a calculated property and it looks like any other one

Property MyId As %Integer [ Calculated, SqlComputeCode = { set {*}={%%ID}}, SqlComputed ];

Robert Cemper · Apr 12, 2021 go to post

this looks strange to me   LITERAL ('PERSON') expected, IDENTIFIER (Person) found

with not much hope:
does CAST(TableA.ID AS INTEGER)   instead of TableA.ID make a difference?

Robert Cemper · Apr 12, 2021 go to post

indeed by CUSTOM_MyQuery(TableA.ID) you just pass 1 parameter

but   LinkedFundsByPerson(TableA.ID,1) might work
as the signature expects 2 params some related code is generated
even if the second param isn't used at all.
You can test  the Stored Procedure interactive from SMP>SQL>storedProcedures 
and you should be asked for 2 params

Robert Cemper · Apr 11, 2021 go to post

did you consider using some of the standard MACROS available in Ensemble.inc ?

  • $$$TRACE
  • $$$LOGSTATUS
  • $$$LOGERROR
  • $$$LOGWARNING
  • $$$LOGALERT
  • $$$LOGINFO
  • ....
Robert Cemper · Apr 11, 2021 go to post

@Chris Bransden 
Without knowing the definition of CUSTOM_MyQuery(par) it's no possible to answer.
The error message indicates that a literal is expected but indeed TableA.ID is a column reference and you feed a whole resultset instead of a single value

My interpretation: You want to see the rows  found by   CUSTOM_MyQuery()
which is indeed a classical inner join. 

So what is the result returned by  SELECT * FROM CUSTOM_MyQuery(??)  ?

You may try this transformation  that does the same in principle

SELECT *  FROM TableA
WHERE 0<(SELECT count(*) FROM CUSTOM_MyQuery(TableA.ID))

Robert Cemper · Apr 10, 2021 go to post

GREAT!
>>unless perhaps the right person at InterSystems is reading this and gets the change put into the products. <<
My hope is with you!

But even if you place a Prodlog / Change Request it might be a matter of years [plural] to see this in production. 
 Always assuming the "priests of the security grail"  don't oppose. wink

Robert Cemper · Apr 10, 2021 go to post

There is a bunch of auto-generated methods that might be useful:
https://community.intersystems.com/post/useful-auto-generated-methods

especially this one from @Eduard Lebedyuk 
#############################################################

But with PropertySetObjectId you can expedite things

set person = ##class(Person).%New()
set companyId = 123
do person.EmployedAtSetObjectId(companyId)

The main advantage is that company object doesn't have to be opened.

#############################################################

Robert Cemper · Apr 10, 2021 go to post

an example of a self-referencing 1:1 relationship
it is also characterized by our "implicit join" feature  ->

Class Sample.Person Extends (%Persistent, %Populate)  {
 - - - 
 Property Spouse As Sample.Person
 Index sp On Spouse [ Unique ]; // to verify 1:1    
 - - -
 }
Robert Cemper · Apr 10, 2021 go to post

YES ! but not it's addressed explicitly

  • the trivial one by self-restricting 1:many  to a single link
  • inheriting of classes is a 1:1 parent-child- relationship  (sample.person<-sample.employee)
  • the normal property -> object reference (sample.employee->sample.company)
Robert Cemper · Apr 9, 2021 go to post

There is some 'silent' period during the night.
Actually, a snapshot (~380 GB) is taken during a Freeze-Thaw window, Which is not really RO.

Robert Cemper · Apr 8, 2021 go to post

the format ./IOTABLE="CP1252" applies only using the OPEN command

$ZCONVERT and %Stream.FileCharacter  just use "CP1252" just by the name

Robert Cemper · Apr 8, 2021 go to post

In namespace %SYS you have a utility NLS that shows your installed conversion table and its short names.

%SYS>d ^NLS
2) Select defaults
2) I/O tables
Items marked with (*) represent the locale's original default
 I/O table              Current default
---------------------  --------------------
1) Process             RAW (*)
2) Cache Terminal      UTF8 (*)
3) Other terminal      UTF8 (*)
4) File                RAW (*)
5) Magtape             RAW (*)
6) TCP/IP              RAW (*)
7) System call         RAW (*)
8) Printer             RAW (*)
 
I/O table: 4
 
 1) RAW (*)                              2) UTF8
 3) UnicodeLittle                        4) UnicodeBig
 5) CP1250                               6) CP1251
 7) CP1252                               8) CP1253
 9) CP1255                              10) CP437
11) CP850                               12) CP852
13) CP866                               14) CP874
15) EBCDIC                              16) Latin2
17) Latin9                              18) LatinC
19) LatinG                              20) LatinH
21) LatinT

So you see the shortnames but no Latin1  but CP1252 which is almost identical.
the related problem is described here:
 https://www.i18nqa.com/debug/table-iso8859-1-vs-windows-1252.html

"ISO-8859-1 (also called Latin-1) is identical to Windows-1252 (also called CP1252) except for the code points 128-159 (0x80-0x9F). ISO-8859-1 assigns several control codes in this range. Windows-1252 has several characters, punctuation, arithmetic and business symbols assigned to these code points."

and Encoding Problem: ISO-8859-1 vs Windows-1252
So you should check what your customer really does (some hide the fact they use Windows)
The appropriate table can be used in

  • $ZCONVERT(),
  • ##class(%Stream.FileCharacter ) property TranslateTable
  • OPEN command parameter /IOTABLE=
Robert Cemper · Apr 6, 2021 go to post

I just verified:

  • Download an Ubuntu Docker image
  • Install a Caché Ububtu distribution
  • tar + gzip the image and you are part of the game with 550 MB only 
Robert Cemper · Apr 5, 2021 go to post

If this record is 

Property Record as %STRING;

you can use 2 calculated properties 

/// true if Header/Trailer
Property HeaderTrailer as %Boolean [Calculated,SqlComputed,
          SqlComputeCode = { set {*} = $extract({Record},1,20)?20" "  }  ];  
/// make integer, 0 for Header / Trailer
Property RecordType as %Integer  [Calculated,SqlComputed,
          SqlComputeCode = { set {*} = $extract({Record},21,23)\1  }  ];