New post
AllAccepted answers
Vitaliy Serdtsev · Mar 7, 2023 
CREATE PROCEDURE DeleteSpecimenTwo
  (IN Specno VARCHAR(50))
LANGUAGE OBJECTSCRIPT
{
NEW SQLCODE,%ROWID,%ROWCOUNT
#SQLCOMPILE SELECT=LOGICAL

&sql(DELETE FROM PCONT WHERE PSPECNO = :Specno)
If $Get(%sqlcontext)'=$$$NULLOREF Set %sqlcontext.%SQLCODE=SQLCODE,%sqlcontext.%ROWCOUNT=%ROWCOUNT
Quit:SQLCODE<0

&sql(DELETE FROM PSPEC WHERE PSPECNO = :Specno)
If $Get(%sqlcontext)'=$$$NULLOREF Set %sqlcontext.%SQLCODE=SQLCODE,%sqlcontext.%ROWCOUNT=%ROWCOUNT
}
Vitaliy Serdtsev · Feb 20, 2023 
#include %occInclude

n alg,algInfo

f alg="RSA-OAEP","A256KW","A256CBC-HS512","A256GCM" {
  s algInfo=##class(%Net.JSON.JWA).GetAlgInfo(alg)
  w alg," = ",$s(algInfo'="":algInfo.%ToJSON(),1:$system.Status.GetErrorText($$$ERROR($$$UnsupportedJOSEAlg,alg))),!
}

Output:

RSA-OAEP = {"alg":"RSA-OAEP","kty":"RSA","use":"enc","key_ops":["wrapKey","unwrapKey"],"keysize":2048,"hashsize":0}
A256KW = {"alg":"A256KW","kty":"oct","use":"enc","key_ops":["wrapKey","unwrapKey"],"keysize":256,"hashsize":0}
A256CBC-HS512 = {"alg":"A256CBC-HS512","kty":"oct","use":"enc","key_ops":["encrypt","decrypt"],"keysize":256,"hashsize":512}
A256GCM = Error #9723: Unsupported JOSE algorithm: A256GCM

Since this algorithm is not supported, it remains either to try to modify the GetAlgInfo method or to work directly with the OpenSSL library or similar.

Vitaliy Serdtsev · Feb 17, 2023

Try working directly with the private key file, for example:

#include %msql

s f=##class(%Stream.FileBinary).%New()
s f.Filename="С:\your_private_key.pem"
s privateKey=f.Read($$$MaxLocalLength)
 
s myString  = "text to sign"
s signedTxt = ##class(%SYSTEM.Encryption).RSASHASign(256, $zcvt(myString,"O","UTF8"), privateKey)
zw signedTxt

This code works for me.

Vitaliy Serdtsev · Feb 13, 2023

There are a couple of points:

  • could you please publish the source code of the class, and not the SQL script? The fact is that the command 
    CREATE INDEX BITMAP_INDEX_ST_SEARCH_VISIT_BLOOD_TYPE_CODE ON SQLUser.ST_SEARCH_VISIT (BLOOD_TYPE_CODE)
    creates a regular index, not a bitmap index as expected. I'm afraid my code may not match yours.
  • I created a test class with test data on Caché 2018.x and IRIS 2023.1FT and I have your query executed very quickly. Details below.
 
User.STSEARCHVISIT.cls

Generating 6000000 records

USER>d ##class(User.STSEARCHVISIT).Fill(6e6)
OK
explain select count(distinct by(VISIT_SERIAL_NO,HQ_ORG_CODE) 1) from ST_SEARCH_VISIT

<plans>
  <plan>
    <sql>
      select count(distinct by(VISIT_SERIAL_NO,HQ_ORG_CODE) 1) from ST_SEARCH_VISIT /*#OPTIONS {"DynamicSQL":1} */
    </sql>
    <cost value="29794316"/>
    Call module B. Output the row.
    <module name="B" top="1">
      Read index map ST_SEARCH_VISIT.IdxVisitorg, looping on %SQLUPPER(VISIT_SERIAL_NO), %SQLUPPER(HQ_ORG_CODE), and ID. For each row: Check distinct values for %SQLUPPER(HQ_ORG_CODE) and %SQLUPPER(VISIT_SERIAL_NO) using a temp-file. For each distinct row: Accumulate the count([value]).
    </module>
  </plan>
</plans>

select count(distinct by(VISIT_SERIAL_NO,HQ_ORG_CODE) 1) from ST_SEARCH_VISIT

Row count: 1 Performance: 2.8704 seconds 6003078 global references 36016391 lines executed

explain select count(1) from (select distinct by (VISIT_SERIAL_NO,HQ_ORG_CODE) VISIT_SERIAL_NO,HQ_ORG_CODE  from ST_SEARCH_VISIT)

<plans>
  <plan>
    <sql>
      select count(1) from (select distinct by (VISIT_SERIAL_NO,HQ_ORG_CODE) VISIT_SERIAL_NO,HQ_ORG_CODE from ST_SEARCH_VISIT) /*#OPTIONS {"DynamicSQL":1} */
    </sql>
    <cost value="21000"/>
    Call module B. Output the row.
    <module name="B" top="1">
      Read index map ST_SEARCH_VISIT.IdxVisitorg, looping on %SQLUPPER(VISIT_SERIAL_NO) and %SQLUPPER(HQ_ORG_CODE). For each row: Call module I. Accumulate the count([value]).
    </module>
    <module name="I" top="1">
      Read index map ST_SEARCH_VISIT.IdxVisitorg, using the given %SQLUPPER(VISIT_SERIAL_NO) and %SQLUPPER(HQ_ORG_CODE), and looping on ID. For each row: Accumulate the [value].
    </module>
  </plan>
</plans>

select count(1) from (select distinct by (VISIT_SERIAL_NO,HQ_ORG_CODE) VISIT_SERIAL_NO,HQ_ORG_CODE from ST_SEARCH_VISIT)

Row count: 1 Performance: 0.0269 seconds 3079 global references 17711 lines executed

Vitaliy Serdtsev · Jan 31, 2023

Is there any harm in putting in a massive number into the len parameter of the Read() method?

You cannot read more than 3641144 characters from a stream to a string, as this is the limit for long strings: String Length Limit

From the documentation for the Read() method:

If no len is passed in, ie. 'Read()' then it is up to the Read implementation as to
how much data to return. Some stream classes use this to optimize the amount of
data returned to align this with the underlying storage of the stream.

So you can write like this:
Set inMsg = %request.Content.Read($$$MaxLocalLength)

Macros are defined in %msql.inc:

#define MaxStringLength 3641144
#define MaxLocalLength $zutil(96,39)
Vitaliy Serdtsev · Jan 31, 2023

See the answers here.

In addition there are a few comments:

Vitaliy Serdtsev · Jan 31, 2023

there are a few comments:

  • you can write shorter:
    Property CarNumber As %String(TRUNCATE = 1) [ SqlComputeCode = {s {*}=$s(Clear:$$Decrypt({CAR_Number}),1:{CAR_Number})}, SqlComputed, SqlFieldName = CAR_Number ];
  • you can make a separate calculated field for the decrypted value, for example:
    Property CarNumber As %String(TRUNCATE = 1) [ SqlFieldName = CAR_Number ];

Property CarNumberDecr As %String(TRUNCATE = 1) [ Calculated, SqlComputeCode = {s {*}=$s(Clear:$$Decrypt({CAR_Number}),1:{CAR_Number})}, SqlComputed, SqlFieldName = CAR_NumberDecr ];
  • where do you get the value of the Clear variable (see above)?
  • you can create your own data type for encrypted strings and override the corresponding methods there: How Data Type Classes Work

    In this case, you will not have to clutter up your queries with unnecessary encryption/decryption operations.

Vitaliy Serdtsev · Jan 25, 2023

There are a couple of points:

  • what's wrong with users, roles, privileges, applications, etc.? 
    %SYS>do ^SECURITY
 
 
1) User setup
2) Role setup
3) Service setup
4) Resource setup
5) Application setup
6) Auditing setup
8) SSL configuration setup
9) Mobile phone service provider setup
10) OpenAM Identity Services setup
11) Encryption key setup
12) System parameter setup
13) X509 User setup
14) KMIP server setup
15) Exit
 
Option? 12
 
1) Edit system options
2) Edit authentication options
3) Edit LDAP options
4) Display system options
5) Export All Security settings
6) Import All Security settings
7) Exit
 
Option? 5
 
Export ALL security records? Yes => Yes
 
Warning: Before importing SSL configurations into a different configuration
the same certificate directories and certificate files must exist in
the new configuration, otherwise the import will fail.
 
Export to file name SecurityExport.xml =>
Parameters? "WNS" =>
Confirm export of selected security records to SecurityExport.xml? No => yes
 
Exported x Application security records
Exported x DocDB security records
Exported x Event security records
Exported x KMIPServer security records
Exported x LDAPConfig security records
Exported x OpenAMIdentityService security records
Exported x PhoneProvider security records
Exported x Resource security records
Exported x Role security records
Exported x SQLPrivileges security records
Exported x SSLConfig security records
Exported x Service security records
Exported x System security records
Exported x User security records
Exported x X509Credential security records
Exported x X509Users security records
 
Export complete
 
1) Edit system options
2) Edit authentication options
3) Edit LDAP options
4) Display system options
5) Export All Security settings
6) Import All Security settings
7) Exit
 
Option?
  • to transfer JDBC/ODBC settings, you can still use external tools, for example DbVisualizer or SQL Data Lens, where you can choose the data format (CSV, XML, JSON, Excel, etc.), the necessary fields and much more.
Vitaliy Serdtsev · Jan 25, 2023

Did you copy/paste the query correctly here?

Select Books.nam,Books.print,Books.Relativename,Books.Firstpage,Books.Lastpage,Books.trn,Books.lastissue,
Books.firstissue,Books.person,Books.author,Books.price,Books.cd,Books.ab,Books.pf,Books.ju,Books.er,
Books.qw,Books.qt,Books.mn,
Records.qw,Records.er,Records.ty,Records.ui,Records.op,Records."as",
OrderBooks.mn,OrderBooks.bv,
OrderRecords.sd,OrderRecords.fg,OrderRecords.hj,
Orders.lastdate

From SQLUser.Books Books

INNER JOIN SQLUser.Records Records ON Books.id=Recordsid
INNER JOIN SQLUser.OrderedBooks OrderBooks ON Books.id=OrderBooks.id
INNER JOIN SQLUser.OrderedRecords OrderRecords ON Books.id=OrderRecords
INNER JOIN SQLUser.Orders Orders ON Books.id=Orders.id

where ((Records.qw=OrderBooks.qw) and (Records.er is null))

In addition to tuning tables, I would first look at the query plan and, accordingly, the indexes involved in it.