All
Accepted answers
Eduard Lebedyuk · Jan 5, 2021 go to post

Great. This should also work:

cursor.execute('SELECT * FROM exp.MD5NoMatch(?,?,?)',('exchange', 'cplx','channel'))
Eduard Lebedyuk · Jan 4, 2021 go to post

JDBC (docs):

import pandas as pd
import jaydebeapi
cnxn=jaydebeapi.connect("com.intersystems.jdbc.IRISDriver","jdbc:IRIS://localhost:51773/Python",  ["dev", "123"], "/InterSystems/IRIS/dev/java/lib/JDK18/intersystems-jdbc-3.0.0.jar")
Data=pd.read_sql('SELECT 1',cnxn)
cnxn.close()

ODBC (docs):

import pandas as pd
import pyodbc
cnxn=pyodbc.connect(('DSN=ENSEMBLE(PYTHON);UID=dev;PWD=123'),autocommit=True)
Data=pd.read_sql('SELECT 1',cnxn)
cnxn.close()

I would much prefer to simply execute the debugged and more secure class queries that already exist and and have been precompiled.  

Sure, this works too:

SELECT * FROM Class.Query
Eduard Lebedyuk · Jan 4, 2021 go to post

If you don't know how to get any part of request/response/session the easiest way is to send your request and execute this code on your server:

set %response.ContentType = "html"
do ##class(%CSP.Utils).DisplayAllObjects()
quit $$$OK

It would output HTML page containing all required information.

I think %request.Get(name) is what you need.

Eduard Lebedyuk · Jan 2, 2021 go to post

That won't return accurate count unfortunately.

Consider a Text value like: ABCD ABC. It would be returned by iFind since ABC is in this text, however $Find would return 2 - incorrect number of ABC instances. More advanced iFind variants do additional text/term processing so I'd like to use it.

Additionally I know it's possible as there is highlight function so iFind can determine a number of search term occurrences in a string and highlight them, but I only need to return the number of occurrences.

I can highlight and count <b>,  but I wonder if a better solution exists.

Eduard Lebedyuk · Dec 17, 2020 go to post

Cubes rely heavily on bitmap indexes for them to run fast.

Primarily each fact in fact table must be accessible via bitmap index.

In the past Bitmap indices worked only with positive integers, but now there seems to be a %BID approach - a surrogate key essentially.

I think InterSystems BI should throw an error or generate a %BID or offer to generate a %BID if the fact class id is not a positive integer

Eduard Lebedyuk · Dec 17, 2020 go to post

I see you have random string IDs. Try with consecutive integer ids starting from 1.

Calling @Benjamin.DeBoe.

Eduard Lebedyuk · Dec 17, 2020 go to post

SELECT on INFORMATION_SCHEMA.TABLES and maybe some other tables from INFORMATION_SCHEMA schema.

Eduard Lebedyuk · Dec 10, 2020 go to post 
Zn "%SYS"
Set Ref = ##class(Config.NLS.Locales).OpenCurrent(.Sc)
Write "Locale name: ",Ref.Name, !
Do Ref.GetTables(.Tables)
Set Key = ""
For { Set Key = $O(Tables("XLT", Key)) Quit:Key=""  Write Key,!}
Eduard Lebedyuk · Dec 9, 2020 go to post

What would be best practice for data type assigned to the "Password" property and securing that password against prying eyes, both just browsing the global as well as via SQL? 

Do not store passwords at all. If you need to check passwords hash and salt them - there are enough functions in $System.Encryption to get by. The only exception is when you need credentials to auth against some external system, in that case

  1. Encrypt login/pass.
  2. Store them in a separate encrypted-at-disk DB with separate resource.
  3. Write API to switch into this DB and get your credential, ideally this API returns authenticated object and not the password itself.
  4. Check that it fails for app user.
  5. Use Privileged Routine Application to allow your app access to a separate DB.

Also check Managed Key Encryption - from the docs:

Data-element encryption for applications, also known simply as data-element encryption — A programmatic interface that allows applications to include code for encrypting and decrypting individual data elements (such as particular class properties) as they are stored to and retrieved from disk.

___________

Better yet, how can I prevent a specific property from being projected to SQL?

The best approach is not to store password at all. Still, there are several options:

  • Access management - if the user does not have access to the password column/table he can't access it. InterSystems IRIS supports CLS.
  • Internal - hides property from docs.
  • Private - hides property from SELECT * explorers, but it can still be explicitly referenced by name
  • Custom datatype - redefine getter to return data only to verified callers, for example check Security.Datatype.Password datatype implementation which returns *** instead of actual password value in ODBC context.
Eduard Lebedyuk · Dec 9, 2020 go to post

It's a popular question:

Can anyone tell me your use case? I encounter XML and JSON fairly often in integrations but it's always some specific schema, as defined by XSD for XML and plaintext for JSON (OpenAPI spec sometimes). So for me it's always XML ↔ Object ↔ JSON (where Object can be zero or more transformations between objects of different classes). But what's the case for a generic XML ↔ JSON with no regards for a particular schema? Clearly it's a popular and valid use case, and I want to know more about it.

Eduard Lebedyuk · Dec 8, 2020 go to post

Great article!

This is the part that would be fantastic if it were a pip package, especially if only for Linux, as AWS Lambda functions execute on Linux boxen. At the time of this commit, the api is not available via pip, but we are resourceful and can roll our own.

You can install NativeAPI using pip:

pip install https://github.com/intersystems/quickstarts-python/raw/master/Solutions/nativeAPI_wheel/irisnative-1.0.0-cp34-abi3-linux_x86_64.whl
Eduard Lebedyuk · Dec 7, 2020 go to post

It depends. Essentially Interoperability Productions take care of:

  • Parallelization
  • Queues / Async
  • Error management, mitigation, and recovery
  • After-error investigation (Visual Trace / Audit)
  • Unified overview of integration flows

For each integration or part of an integration you need to decide if you need these features and usually you  do. In that case all you need to do is to develop one or more Business Hosts containing the business logic and as long as they conform to Interoperability Production structure you would automatically get all the above mentioned benefits.

You pay for the convenience with the overhead for messages and queues.

In the cases where some (most) of these conditions are true:

  • external system (whatever it is) is reliable - downtime is a scheduled rarity
  • external system does not throw errors much
  • response time is stable and in the sync realm
  • interaction with the system is one simple flow
  • integration is extremely highload

You can choose to interface more directly.

Furthermore it's not Interoperability/no Interoperability, but rather a scale of how much you expose as Interoperability Hosts. In your example maybe having only a BO is enough and you can forego the BP?

Eduard Lebedyuk · Dec 3, 2020 go to post

I run tests programmatically like this:

ClassMethod runtest() As %Status
{
    set ^UnitTestRoot = basedir
    set sc = ##class(%UnitTest.Manager).RunTest(testdir, "/nodelete")
    quit sc
}

ClassMethod isLastTestOk() As %Boolean
{
    set in = ##class(%UnitTest.Result.TestInstance).%OpenId(^UnitTest.Result)
    for i=1:1:in.TestSuites.Count() {
        #dim suite As %UnitTest.Result.TestSuite
        set suite = in.TestSuites.GetAt(i)
        return:suite.Status=0 $$$NO
    }
    quit $$$YES
}
Eduard Lebedyuk · Nov 28, 2020 go to post

To be honest despite about my 7+ years of experience in exposing class methods as sql procedures I've yet to write a name of a resulting sql procedure correctly.

The trick I use is to open the list of procedures in SMP:

In this list I search for an already existing procedure with the same nesting level, copy it and change identifiers to my values.

That's how I wrote your query.

First I copied: %Atelier_v1_Utils.Extension_AfterUserAction

Then replaced %Atelier_v1_Utils with com_xyz_utils

Finally replaced Extension_AfterUserAction with Users_getRole

Eduard Lebedyuk · Nov 27, 2020 go to post

Try

SELECT
    id,
    com_xyz_utils.Users_getRole(id)
FROM users.users

Note that package is underscored.

Eduard Lebedyuk · Nov 27, 2020 go to post

Can you share some technical details?

One of the projects:

  • 1 Mirrored Data server
  • 4 Non-mirrored App Servers

They run Apache web servers.

Eduard Lebedyuk · Nov 27, 2020 go to post

I recommend InterSystems IRIS REST API backend with any js framework frontend.

10 000 simulatenous clients are easily achievable. I know of InterSystems IRIS based applications in productions with even higher number of concurrent users.