Maybe you should take a look at the class %ScrollableResultSet?
- Log in to post comments
All
Accepted answers
I didn't find in the documentation the line {InterSystems IRIS ODBC35}:
Try
Driver=InterSystems ODBC Driver;...or
DRIVER={InterSystems ODBC35};...- Log in to post comments
Try googling "ssl_error_syscall errno 10053". Perhaps some of the suggestions will help you.
- Log in to post comments
It's clearer now, this is the tricks of Excel, which convert the string 01022000 to the number 1022000. In your case, try changing the cell type from General to Text.
PS: when generating Excel reports, I use the Date cell type (short format DD.MM.YYYY), and from Caché/IRIS I return the %Date type. The components that I use already know how to translate the internal representation of the date 01.02.2000 from the Caché/IRIS format (58105) to Excel (36557) and vice versa.
- Log in to post comments
Can I see how you call TO_CHAR?
I have a leading zero displayed without problems:
SELECT %internal(to_date('01022000','DDMMYYYY')) -- 01022000 -> 58105
,TO_CHAR(58105,'DDMMYYYY') -- 58105 -> 01022000
- Log in to post comments
Try this:
Set objcontato=##class(Contatos.Amiguinho).%New() Do objcontato.MoradiaSetObjectId(3) Do objcontato.TrabalhoSetObjectId(2) Set ret=objcontato.%Save()
- Log in to post comments
size = 52
ClassMethod IsValid(s As %String) As %Boolean
{
1 s c=$a(s,$i(i))+1 g:1-$i(z,c=42-(c=41))&c 1 q 'z
}- Log in to post comments
You can make it even easier:
ClassMethod IsValid(s As %String) As %Boolean [ Language = python ]
{
import regex;return regex.match("^((?>[^()]|\((?1)\))*)$",s) is not None
}Unfortunately, RegExp ICU does not support recursive patterns, otherwise it would be possible to write
q $match(s,"^((?>[^()]|\((?1)\))*)$")
- Log in to post comments
No.
This code will not work in IRIS 2023.1 because changes have been made for security reasons: Improvements to how IRIS classes are generated and called
- Log in to post comments
Thanks for the comment. I tested only on the provided data. The second option requires improvement.
size = 72
ClassMethod IsValid(s As %String) As %Boolean
{
q $l(s,"(")=$l(s,")")&($f(s,"(")<$f(s,")"))&($f(s,")(")-$f(s,"()")<2)
}size = 69
ClassMethod IsValid(s As %String) As %Boolean
{
1 s c=$i(c,$case($e(s,$i(i)),"(":1,")":-1,"":-2,:0)) q:c<0 c=-2 g 1
}- Log in to post comments
size = 61
ClassMethod IsValid(s As %String) As %Boolean
{
q +##class(%iFind.Utils).TestSearchString($$$URLENCODE(s))
}- Log in to post comments
It's strange, according to approved method of calculating the length of the solution, your code shows a length of 62, not 61. Have you measured the length of your solution using this method?
- Log in to post comments
Query FilterBy(
Name As %String = "",
Age As %Integer = "") As %SQLQuery(CONTAINID = 1, SELECTMODE = "RUNTIME") [ SqlName = SP_Sample_Filter_By, SqlProc ]
{
SELECT TOP 5 ID, Name, Age, SSN FROM %ALLINDEX Sample.Person WHERE
(:Name IS NULL or Name %STARTSWITH :Name)
AND
(:Age IS NULL or Age >= :Age)
}
This kind of query ends up preventing Caché SQL compiler from optimizing using index based on each of the criteria made optional.What is your conclusion based on? If you check the plans of this query for different combinations of parameters, then the corresponding indexes are used (it is assumed that the table was previously configured via TuneTable).
select * from Sample.SP_Sample_Filter_By('s')
SELECT TOP 5 ID, Name, Age, SSN FROM %ALLINDEX Sample.Person WHERE
('s' IS NULL or Name %STARTSWITH 's')
AND
(NULL IS NULL or Age >= NULL)
select * from Sample.SP_Sample_Filter_By(,47)
SELECT TOP 5 ID, Name, Age, SSN FROM %ALLINDEX Sample.Person WHERE (NULL IS NULL or Name %STARTSWITH NULL) AND (47 IS NULL or Age >= 47)
select * from Sample.SP_Sample_Filter_By('s',47)
SELECT TOP 5 ID, Name, Age, SSN FROM %ALLINDEX Sample.Person WHERE
('s' IS NULL or Name %STARTSWITH 's')
AND
(47 IS NULL or Age >= 47)- Log in to post comments
Did a little test:
Class dc.test [ Abstract ]
{
ClassMethod GetObjectFieldValue(
pObject As %DynamicAbstractObject,
pField As %String) As %String
{
quit:pObject="" ""
quit:pField="" "" set tResponse=""
set tResponse = pObject.%Get(pField)
return tResponse
}
ClassMethod Test()
{
s p1={
"priority": "2",
"lastmodifieduser": "PORTAL",
"assignedto": "jdoe01 STAFF",
"status": "REVIEW",
"documentclass": "PATIENTCASE",
"versiontoken": "937486",
"departmentid": "999",
"patientcaseid": "105555424",
"providerusername": "jdoe01",
"internalnote": "firstMessage",
"subject": "first123",
"patientid": 9877654321,
"createduser": "PORTAL",
"description": "patient case - first123",
"documentroute": "MDP",
"documentsubclass": "PATIENTCASE_CLINICALQUESTION",
"documentsource": "PORTAL",
"createddate": "07/17/2023",
"lastmodifieddate": "07/17/2023"}
s p2="documentsubclass"
w ..GetObjectFieldValue(p1,p2)
}
}
Result:
USER>d ##class(dc.test).Test() PATIENTCASE_CLINICALQUESTION
- Log in to post comments
Your question has already been answered here: Dynamic SQL and Setting SQLCODE
- Log in to post comments
There is nothing specific to IRIS here, except for the connection string. The same code with minimal changes should work with other DBMS: Oracle, SQL Server, PostgreSQL. etc. The Internet is full of examples of how to insert streams into the database via ADO.
- Log in to post comments
I wrote a small test on VBS, but you can easily redo this code for your language. For details on working with streams for ADO, see the documentation.
Code on Visual Basic Script
adVarChar=200
adLongVarChar=201
adLongVarBinary=205
adParamInput=1
adCmdText=1
adExecuteNoRecords=128
Set cn=Createobject("ADODB.Connection")
cn.ConnectionString="DRIVER={InterSystems IRIS ODBC35}; SERVER=127.0.0.1; PORT=1972; DATABASE=USER; UID=_system; PWD=SYS;"
cn.open
Set cmd = Createobject("ADODB.Command")
with cmd
.ActiveConnection = cn
.CommandText = "CREATE TABLE dc.test(Name VARCHAR(50) NOT NULL,Notes TEXT,Photo IMAGE)"
.Execute ,,adCmdText + adExecuteNoRecords
.CommandText = "insert into dc.test(Name,Notes,Photo)values(?,?,?)"
.Parameters.Append .CreateParameter("pName", adVarChar, adParamInput, 50, "blablabla")
.Parameters.Append .CreateParameter("pNotes", adLongVarChar, adParamInput, 2147483647, ReadTextFile("C:\bigText.txt","Windows-1251"))
.Parameters.Append .CreateParameter("pPhoto", adLongVarBinary, adParamInput, 2147483647, ReadBinaryFile("C:\bigImage.png"))
.Execute ,,adCmdText + adExecuteNoRecords
end with
WScript.Echo "Succesfully!"
Function ReadBinaryFile(FileName)
Const adTypeBinary = 1
'Create Stream object
Dim BinaryStream
Set BinaryStream = CreateObject("ADODB.Stream")
'Specify stream type - we want To get binary data.
BinaryStream.Type = adTypeBinary
'Open the stream
BinaryStream.Open
'Load the file data from disk To stream object
BinaryStream.LoadFromFile FileName
'Open the stream And get binary data from the object
ReadBinaryFile = BinaryStream.Read
End Function
Function ReadTextFile(FileName, CharSet)
Const adTypeText = 2
'Create Stream object
Dim BinaryStream
Set BinaryStream = CreateObject("ADODB.Stream")
'Specify stream type - we want To get binary data.
BinaryStream.Type = adTypeText
'Specify charset For the source text (unicode) data.
If Len(CharSet) > 0 Then
BinaryStream.CharSet = CharSet
End If
'Open the stream
BinaryStream.Open
'Load the file data from disk To stream object
BinaryStream.LoadFromFile FileName
'Open the stream And get binary data from the object
ReadTextFile = BinaryStream.ReadText
End Function
- Log in to post comments
In my opinion, the documentation clearly explains the characteristics of such a property and its purpose, for example:
- To specify that this property has the characteristics of a multidimensional array
- That is, the property is also automatically Transient.
- Multidimensional properties are rare but provide a useful way to temporarily contain information about the state of an object.
- Log in to post comments
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
}- Log in to post comments
By the way, your example takes 47, not 48 characters
- Log in to post comments
48 (the same example, but with documented feature)
ClassMethod Check(w As %String) As %Boolean
{
f i=1:1:90{ret:$l($$$UPPER(w),$c(i))>2 0} q 1
}- Log in to post comments
52
ClassMethod Check(w As %String) As %Boolean
{
a s w=$zu(28,w,5),l=$l(w,$e(w,$i(i))) g:l=2 a q 'l
}48
ClassMethod Check(w As %String) As %Boolean
{
f i=1:1:90{ret:$l($zu(28,w,5),$c(i))>2 0} q 1
}- Log in to post comments
For an empty string (a=""), the <UNDEFINED> error occurs because the variable r is not defined.
- Log in to post comments
See String-to-Number Conversion and further.
w ("45" < "46") === 45 < 46
w ("45" > "46") === 45 > 46
w ("V45" < "V46") === 0 < 0
w ("V45" > "V46") === 0 > 0
w ("V45" <= "V46") === 0 <= 0
w ("V45" >= "V46") === 0 >= 0
w +"45" -> 45
w +"V45" -> 0- Log in to post comments
#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.- Log in to post comments
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.
- Log in to post comments
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
///
Class User.STSEARCHVISIT Extends %Persistent [ ClassType = persistent, DdlAllowed, Final, ProcedureBlock, SqlRowIdPrivate, SqlTableName = ST_SEARCH_VISIT ]
{
Property HQORGCODE As %Library.String(MAXLEN = 32) [ SqlColumnNumber = 2, SqlFieldName = HQ_ORG_CODE ];
Property HQORGNAME As %Library.String(MAXLEN = 32) [ SqlColumnNumber = 3, SqlFieldName = HQ_ORG_NAME ];
Property TENANTID As %Library.String(MAXLEN = 32) [ SqlColumnNumber = 4, SqlFieldName = TENANT_ID ];
Property GROUPPATIENTSN As %Library.String(MAXLEN = 32) [ SqlColumnNumber = 5, SqlFieldName = GROUP_PATIENT_SN ];
Property ORGPATIENTSN As %Library.String(MAXLEN = 32) [ SqlColumnNumber = 6, SqlFieldName = ORG_PATIENT_SN ];
Property NAME As %Library.String(MAXLEN = 64) [ SqlColumnNumber = 7 ];
Property SEXCODE As %Library.String(MAXLEN = 2) [ SqlColumnNumber = 8, SqlFieldName = SEX_CODE ];
Property SEXNAME As %Library.String(MAXLEN = 16) [ SqlColumnNumber = 9, SqlFieldName = SEX_NAME ];
Property BIRTHDATE As %Library.Date [ SqlColumnNumber = 10, SqlFieldName = BIRTH_DATE ];
Property MARRYCODE As %Library.String(MAXLEN = 2) [ SqlColumnNumber = 11, SqlFieldName = MARRY_CODE ];
Property MARRYNAME As %Library.String(MAXLEN = 16) [ SqlColumnNumber = 12, SqlFieldName = MARRY_NAME ];
Property IDENTIFYTYPECODE As %Library.String(MAXLEN = 5) [ SqlColumnNumber = 13, SqlFieldName = IDENTIFY_TYPE_CODE ];
Property IDENTIFYTYPENAME As %Library.String(MAXLEN = 32) [ SqlColumnNumber = 14, SqlFieldName = IDENTIFY_TYPE_NAME ];
Property IDENTIFYNUMBER As %Library.String(MAXLEN = 50) [ SqlColumnNumber = 15, SqlFieldName = IDENTIFY_NUMBER ];
Property BLOODTYPECODE As %Library.String(MAXLEN = 2) [ SqlColumnNumber = 16, SqlFieldName = BLOOD_TYPE_CODE ];
Property BLOODTYPENAME As %Library.String(MAXLEN = 12) [ SqlColumnNumber = 17, SqlFieldName = BLOOD_TYPE_NAME ];
Property MOBILE As %Library.String(MAXLEN = 50) [ SqlColumnNumber = 18 ];
Property MAILINGADDRESS As %Library.String(MAXLEN = 127) [ SqlColumnNumber = 19, SqlFieldName = MAILING_ADDRESS ];
Property VISITSERIALNO As %Library.String(MAXLEN = 64) [ SqlColumnNumber = 20, SqlFieldName = VISIT_SERIAL_NO ];
Property TABLEFLAG As %Library.String(MAXLEN = 1) [ SqlColumnNumber = 21, SqlFieldName = TABLE_FLAG ];
Property VISITTYPECODE As %Library.String(MAXLEN = 24) [ SqlColumnNumber = 22, SqlFieldName = VISIT_TYPE_CODE ];
Property VISITTYPENAME As %Library.String(MAXLEN = 64) [ SqlColumnNumber = 23, SqlFieldName = VISIT_TYPE_NAME ];
Property VISITDEPTCODE As %Library.String(MAXLEN = 32) [ SqlColumnNumber = 24, SqlFieldName = VISIT_DEPT_CODE ];
Property VISITDEPTNAME As %Library.String(MAXLEN = 64) [ SqlColumnNumber = 25, SqlFieldName = VISIT_DEPT_NAME ];
Property INOUTVISITNO As %Library.String(MAXLEN = 32) [ SqlColumnNumber = 26, SqlFieldName = IN_OUT_VISIT_NO ];
Property VISITTIME As %Library.PosixTime [ SqlColumnNumber = 27, SqlFieldName = VISIT_TIME ];
Property DISCHARGETIME As %Library.PosixTime [ SqlColumnNumber = 28, SqlFieldName = DISCHARGE_TIME ];
Property INHOSPITALTIME As %Library.PosixTime [ SqlColumnNumber = 29, SqlFieldName = IN_HOSPITAL_TIME ];
Property ICDCODE As %Library.String(MAXLEN = 20) [ SqlColumnNumber = 30, SqlFieldName = ICD_CODE ];
Property ICDNAME As %Library.String(MAXLEN = 64) [ SqlColumnNumber = 31, SqlFieldName = ICD_NAME ];
Property HEALTHCARDNO As %Library.String(MAXLEN = 32) [ SqlColumnNumber = 32, SqlFieldName = HEALTH_CARD_NO ];
Property HEALTHCARDTYPE As %Library.String(MAXLEN = 64) [ SqlColumnNumber = 33, SqlFieldName = HEALTH_CARD_TYPE ];
Property SURGCODE As %Library.String(MAXLEN = 20) [ SqlColumnNumber = 34, SqlFieldName = SURG_CODE ];
Property SURGNAME As %Library.String(MAXLEN = 64) [ SqlColumnNumber = 35, SqlFieldName = SURG_NAME ];
Property AGE As %Library.String(MAXLEN = 10) [ SqlColumnNumber = 36 ];
Property DISEASECODE As %Library.Integer(MAXVAL = 2147483647, MINVAL = -2147483648) [ SqlColumnNumber = 37, SqlFieldName = DISEASE_CODE ];
Property DISEASENAME As %Library.String(MAXLEN = 64) [ SqlColumnNumber = 38, SqlFieldName = DISEASE_NAME ];
Property LastChangedTime As %Library.PosixTime [ SqlColumnNumber = 39 ];
Property LastCreateTime As %Library.PosixTime [ SqlColumnNumber = 40 ];
Property GROUPORG As %Library.String(MAXLEN = 50) [ SqlColumnNumber = 41 ];
Parameter USEEXTENTSET = 0;
/// Bitmap Extent Index auto-generated by DDL CREATE TABLE statement. Do not edit the SqlName of this index.
Index DDLBEIndex [ Extent, SqlName = "%%DDLBEIndex", Type = bitmap ];
Index BITMAPINDEXSTSEARCHVISITBLOODTYPECODE On BLOODTYPECODE [ SqlName = BITMAP_INDEX_ST_SEARCH_VISIT_BLOOD_TYPE_CODE, Type = bitmap ];
Index BITMAPINDEXSTSEARCHVISITHQORGCODE On HQORGCODE [ SqlName = BITMAP_INDEX_ST_SEARCH_VISIT_HQ_ORG_CODE, Type = bitmap ];
Index BITMAPINDEXSTSEARCHVISITIDENTIFYTYPECODE On IDENTIFYTYPECODE [ SqlName = BITMAP_INDEX_ST_SEARCH_VISIT_IDENTIFY_TYPE_CODE, Type = bitmap ];
Index BITMAPINDEXSTSEARCHVISITMARRYCODE On MARRYCODE [ SqlName = BITMAP_INDEX_ST_SEARCH_VISIT_MARRY_CODE, Type = bitmap ];
Index BITMAPINDEXSTSEARCHVISITSEXCODE On SEXCODE [ SqlName = BITMAP_INDEX_ST_SEARCH_VISIT_SEX_CODE, Type = bitmap ];
Index BITMAPINDEXSTSEARCHVISITTABLEFLAG On TABLEFLAG [ SqlName = BITMAP_INDEX_ST_SEARCH_VISIT_TABLE_FLAG, Type = bitmap ];
Index BITMAPINDEXSTSEARCHVISITVISITDEPTCODE On VISITDEPTCODE [ SqlName = BITMAP_INDEX_ST_SEARCH_VISIT_VISIT_DEPT_CODE, Type = bitmap ];
Index BITMAPINDEXSTSEARCHVISITVISITTYPECODE On VISITTYPECODE [ SqlName = BITMAP_INDEX_ST_SEARCH_VISIT_VISIT_TYPE_CODE, Type = bitmap ];
Index INDEXSTSEARCHVISITBIRTHDATE On BIRTHDATE [ SqlName = INDEX_ST_SEARCH_VISIT_BIRTH_DATE, Type = index ];
Index INDEXSTSEARCHVISITDISCHARGETIME On DISCHARGETIME [ SqlName = INDEX_ST_SEARCH_VISIT_DISCHARGE_TIME, Type = index ];
Index INDEXSTSEARCHVISITGROUPPATIENTSN On GROUPPATIENTSN [ SqlName = INDEX_ST_SEARCH_VISIT_GROUP_PATIENT_SN, Type = index ];
Index INDEXSTSEARCHVISITIDENTIFYNUMBER On IDENTIFYNUMBER [ SqlName = INDEX_ST_SEARCH_VISIT_IDENTIFY_NUMBER, Type = index ];
Index INDEXSTSEARCHVISITINHOSPITALTIME On INHOSPITALTIME [ SqlName = INDEX_ST_SEARCH_VISIT_IN_HOSPITAL_TIME, Type = index ];
Index INDEXSTSEARCHVISITINOUTVISITNO On INOUTVISITNO [ SqlName = INDEX_ST_SEARCH_VISIT_IN_OUT_VISIT_NO, Type = index ];
Index INDEXSTSEARCHVISITMOBILE On MOBILE [ SqlName = INDEX_ST_SEARCH_VISIT_MOBILE, Type = index ];
Index INDEXSTSEARCHVISITNAME On NAME [ SqlName = INDEX_ST_SEARCH_VISIT_NAME, Type = index ];
Index INDEXSTSEARCHVISITORGPATIENTSN On ORGPATIENTSN [ SqlName = INDEX_ST_SEARCH_VISIT_ORG_PATIENT_SN, Type = index ];
Index INDEXSTSEARCHVISITVISITSERIALNO On VISITSERIALNO [ SqlName = INDEX_ST_SEARCH_VISIT_VISIT_SERIAL_NO, Type = index ];
Index INDEXSTSEARCHVISITVISITTIME On VISITTIME [ SqlName = INDEX_ST_SEARCH_VISIT_VISIT_TIME, Type = index ];
Index IdxMapOrg On HQORGCODE [ SqlName = IdxMapOrg, Type = index ];
Index LastChangedTimeIndex On LastChangedTime [ SqlName = LastChangedTimeIndex, Type = index ];
Index LastCreateTimeIndex On LastCreateTime [ SqlName = LastCreateTimeIndex, Type = index ];
Index idxGO On GROUPORG [ SqlName = idxGO, Type = index ];
Index INDEXPIPATIENTINFOHQORGCODE On (HQORGCODE, GROUPPATIENTSN) [ SqlName = INDEX_PI_PATIENT_INFO_HQ_ORG_CODE, Type = index ];
Index IdxGpsnorg On (GROUPPATIENTSN, HQORGCODE) [ SqlName = IdxGpsnorg, Type = index ];
Index IdxVisitorg On (VISITSERIALNO, HQORGCODE) [ SqlName = IdxVisitorg, Type = index ];
ClassMethod Fill(N = 50)
{
; bulk insert via SQL. This is convenient if USEEXTENTSET = 1
/*
d ..%KillExtent()
f i=1:1:N &sql(insert %NOLOCK %NOCHECK %NOINDEX %NOTRIGGER %NOJOURN into ST_SEARCH_VISIT(HQ_ORG_CODE,VISIT_SERIAL_NO)values(:i#4,:i#5))
*/
; bulk insert via globals
d DISABLE^%NOJRN
k ^User.STSEARCHVISITD,^User.STSEARCHVISITI
f i=1:1:N {
s $li(^User.STSEARCHVISITD(i),1)=i#4, ;HQ_ORG_CODE
$li(^User.STSEARCHVISITD(i),19)=i#5 ;VISIT_SERIAL_NO
}
s ^User.STSEARCHVISITD=N
d ENABLE^%NOJRN
w "insert - OK!",!
d ..%BuildIndices(,,,$$$NO)
w "BuildIndices - OK!",!
d $system.SQL.TuneTable("ST_SEARCH_VISIT",$$$YES)
d $system.OBJ.Compile($classname(),"cu-d")
w "OK",!
}
}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
- Log in to post comments
See for Caché 2018.1:
PS: It is a pity that again no one pays attention to the version of the product.
- Log in to post comments
For Caché: $$GroupJob^%SYS.WorkQueueMgr($system.Context.WorkMgr().MasterGroup)
- Log in to post comments
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)
- Log in to post comments