go to post Vitaliy Serdtsev · Jul 20, 2018 Can also speed up, abandoning the indirect: s:'$d(addr3) addr3(1)=1, (addr3(2),addr3(8),addr3(9))=289, addr3(3)=3 d $case(addr3(value),1:t3case1,289:t3case289,3:t3case3,:t3case) ##; w !,r q t3case1 s r="Case 1" q t3case289 s r="Case 2,8-9" q t3case3 s r="Case 3" q t3case s r= "Case default" qBut the quickest option is if/elseif/else, since here is used inline-call, and not external-call.
go to post Vitaliy Serdtsev · Jul 19, 2018 So, in order. There is no limit to perfection. Better then d:(v=2)!(v=8)!(v=9) t3case289 q There are one error in the Test2 method: s:'$d(addr) addr(1)=1, (addr(2),addr(8),addr(9))=289, add(3)=3 There are error in the Test3 method: d:v=1 t3case1 Q ;will work in any way d:v=2 t3case289 Q d:v=8 t3case289 Q d:v=9 t3case289 Q d:v=3 t3case3 Q d t3case Q Speed will depend on many factors: RAM size, number of conditions (the more of them, the slower the lasts of them will be executed and more RAM is required).
go to post Vitaliy Serdtsev · Jul 19, 2018 So, in order. - The third article mentions a few options of which some are not available in Cache, such as LIMIT and OFFSET. Caché has analogues - TOP N and %vid, which with more than replace the LIMIT/OFFSET. In the second link this is discussed in detail. - And the 4th talks about using LIMIT and OFFSET which are, again, not available in Cache. The essence of the article is to replace query SELECT user_id, external_id, name, metadata, date_created FROM users ORDER BY user_id ASC LIMIT 50 000 000, 10 000; --- 5 000th page * 10 000 page size 10 000 rows in set (40.81 sec)to SELECT user_id, external_id, name, metadata, date_created FROM users WHERE user_id > 51 234 123 --- value of user_id for 50 000 000th record ORDER BY user_id ASC LIMIT 10 000 10 000 rows in set (0.03 sec)Eventhough I am only asking for the first 100, there is still a major performance hit when executing the first rset.%Next() due to, what I assume is, the code trying to find the 100 records I am requesting out of the 1 mil records. In this case, the following query is sufficient: SELECT TOP 100 prop FROM table WHERE prop=?-The second suggestion is what I am doing above. Using %VID with a subquery is just too slow when dealing with large datasets. Is there an index for "prop"? You tuned the table? I have all works quickly: Class dc.test Extends %Persistent { Index iprop On prop; Property prop As %String; ClassMethod Fill( Nrow = {1e6}, Npage = {1e3}) { d DISABLE^%NOJRN ,..%KillExtent() s time=$zh ,^dc.testD=Nrow f id=1:1:Nrow { s p=id-1\Npage+1 ,v=id-1#Npage+1 ,val=p_":"_v ,^dc.testD(id)=$lb("",val) ,^dc.testI("iprop",$$$SQLUPPER(val),id)="" } w "(Fill) time = ",$zh-time," s.",!! zw:Nrow<=50 ^dc.testD,^dc.testI d ENABLE^%NOJRN ,$system.SQL.TuneTable($classname(),$$$YES) ,$system.OBJ.Compile($classname(),"cu-d") } ClassMethod Query( q As %TinyInt = 1, prop As %String, rownum1 As %Integer, rownum2 As %Integer) { s sql(1)="select *,%vid from (select %ID,prop from dc.test where prop %startswith ?) where %vid between ? and ?" ,sql(2)="select *,%vid from (select %ID,prop from %ignoreindex iprop dc.test where prop like ?) where %vid between ? and ?" ,sql(3)="select *,%vid from (select top ? %ID,prop from %ignoreindex iprop dc.test where prop like ? order by %ID desc) order by %vid desc" ,time=$zh ,rs=$s(q=3:##class(%SQL.Statement).%ExecDirect(,sql(q),rownum1,prop), 1:##class(%SQL.Statement).%ExecDirect(,sql(q),prop,rownum1,rownum2)) i 'rs.%SQLCODE { while rs.%Next() { /* s id=rs.%Get("ID") ,prop=rs.%Get("prop") w id," ",prop,! */ d rs.%Print() } } w "(",$lts($lb(q,prop,rownum1,rownum2)),") time = ",$zh-time," s.",!! } /// d ##class(dc.test).Test() ClassMethod Test() { d ..Fill() ,..Query(1,"1000:",111,111+16) ,..Query(2,"%12%",111,111+16) ,..Query(1,"1",111984,111984+16) ,..Query(2,"%12%",39584,39584+16) ;# slow (last 17) ,..Query(3,"%12%",17,"") ;# fast (last 17) } }Result: USER>d ##class(dc.test).Test() (Fill) time = 1.277645 s. 999111 1000:111 111 999112 1000:112 112 999113 1000:113 113 999114 1000:114 114 999115 1000:115 115 999116 1000:116 116 999117 1000:117 117 999118 1000:118 118 999119 1000:119 119 999120 1000:120 120 999121 1000:121 121 999122 1000:122 122 999123 1000:123 123 999124 1000:124 124 999125 1000:125 125 999126 1000:126 126 999127 1000:127 127 (1,1000:,111,127) time = .084489 s. 5128 6:128 111 5129 6:129 112 5212 6:212 113 5312 6:312 114 5412 6:412 115 5512 6:512 116 5612 6:612 117 5712 6:712 118 5812 6:812 119 5912 6:912 120 6012 7:12 121 6112 7:112 122 6120 7:120 123 6121 7:121 124 6122 7:122 125 6123 7:123 126 6124 7:124 127 (2,%12%,111,127) time = .091251 s. 999984 1000:984 111984 999985 1000:985 111985 999986 1000:986 111986 999987 1000:987 111987 999988 1000:988 111988 999989 1000:989 111989 999990 1000:990 111990 999991 1000:991 111991 999992 1000:992 111992 999993 1000:993 111993 999994 1000:994 111994 999995 1000:995 111995 999996 1000:996 111996 999997 1000:997 111997 999998 1000:998 111998 999999 1000:999 111999 1000000 1000:1000 112000 (1,1,111984,112000) time = .66504 s. 999121 1000:121 39584 999122 1000:122 39585 999123 1000:123 39586 999124 1000:124 39587 999125 1000:125 39588 999126 1000:126 39589 999127 1000:127 39590 999128 1000:128 39591 999129 1000:129 39592 999212 1000:212 39593 999312 1000:312 39594 999412 1000:412 39595 999512 1000:512 39596 999612 1000:612 39597 999712 1000:712 39598 999812 1000:812 39599 999912 1000:912 39600 (2,%12%,39584,39600) time = 1.946264 s. 999121 1000:121 17 999122 1000:122 16 999123 1000:123 15 999124 1000:124 14 999125 1000:125 13 999126 1000:126 12 999127 1000:127 11 999128 1000:128 10 999129 1000:129 9 999212 1000:212 8 999312 1000:312 7 999412 1000:412 6 999512 1000:512 5 999612 1000:612 4 999712 1000:712 3 999812 1000:812 2 999912 1000:912 1 (3,%12%,17,) time = .089032 s.
go to post Vitaliy Serdtsev · Jul 18, 2018 See: %ScrollableResultSet Is there any way of paginate SQL Query in Caché? comment Efficient pagination of a table with 100M records PS: need to replace the heading on "Speedup SQL pagination"
go to post Vitaliy Serdtsev · Jul 17, 2018 And what you are not satisfied with the native classes %Stream.FileBinaryGzip, %Stream.FileCharacterGzip?
go to post Vitaliy Serdtsev · Jul 17, 2018 Use %MVR For example: Class dc.test Extends %Persistent { Index ibar On bar; Property bar As %String(COLLATION = "MVR"); ClassMethod Test() { d ..%KillExtent() &sql(insert into dc.test(bar) select '00123AB' union select '12345AB') d $system.SQL.TuneTable($classname(),$$$YES), $system.OBJ.Compile($classname(),"cu-d") f args="00123AB","123AB" d ##class(%SQL.Statement).%ExecDirect(,"select * from dc.test where bar=?",args).%Display() w !! } }Result: USER>d ##class(dc.test).Test() ID bar 1 00123AB 1 Rows(s) Affected ID bar 1 00123AB 1 Rows(s) Affected
go to post Vitaliy Serdtsev · Jul 17, 2018 test.mac: #include %systemInclude #define getServState(%serviceName) ##class(%Activate.GenericObject).GetObject("winmgmts:\\.\root\cimv2").InvokeMethod1("Get",$$$FormatText("Win32_Service.Name=%1",$$$quote(%serviceName))).GetProperty("State") f s="wudfsvc","WSearch" w s,": ",$$$getServState(s),!Result: USER>d ^test wudfsvc: Stopped WSearch: Running
go to post Vitaliy Serdtsev · Jul 16, 2018 Components Installed by Setup Type: IRIS, Caché Unfortunately, Perl/Python Binding supported only on the x86-32 bit platform, but IRIS is only supported for x86-64.
go to post Vitaliy Serdtsev · Jul 16, 2018 I think you need to override not the COLLATION, and methods DisplayToLogical/LogicalToDisplay and/or LogicalToStorage/StorageToLogical. See Defining Data Type Classes
go to post Vitaliy Serdtsev · Jul 6, 2018 Contents of a Script File: Script files are line oriented; there is no line-continuation convention. Each line is separate from any other. Lines beginning with a semicolon are considered comments. You can use blank lines liberally to improve readability. Normally, invalid lines are ignored. Script commands may be preceded by spaces and/or tabs.
go to post Vitaliy Serdtsev · Jul 6, 2018 Example of converting a CSV to JSON file and then reading from a JSON file without creating intermediate classes/tables: ; CSV -> JSON s rowtype = "name VARCHAR(50),year INTEGER,amount NUMERIC(9,2),date DATE", fileIn = "C:\Temp\import.csv", fileOut = "C:\Temp\export.json", fileExp=##class(%Stream.FileBinary).%New(), obj=##class(%Document.Object).%New() d obj.loadResultSet(##class(%SQL.Statement).%ExecDirect(,"call %SQL_Util.CSV(,?,?)",.rowtype,.fileIn),"res"), fileExp.LinkToFile(fileOut), obj.%ToJSONStream(fileExp), fileExp.%Save() ; Read JSON s fileImp=##class(%Stream.FileBinary).%New() d fileImp.LinkToFile(fileOut) s obj = ##class(%DynamicAbstractObject).%FromJSON(fileImp) d obj.%ToJSON() w !,"obj.res.%Size() = ",obj.res.%Size() ,!,obj.res."0"."""date"""Result: C:\Temp\import.csv: car,2000,100.51,27.10.2016, phone,2003,65.8,15.01.2017, USER>d ^test {"res":[{"\"date\"":"27.10.2016","amount":100.51,"name":"car","year":2000},{"\"date\"":"15.01.2017","amount":65.8,"name":"phone","year":2003}]} obj.res.%Size() = 2 27.10.2016
go to post Vitaliy Serdtsev · Jun 26, 2018 TEST1(STATUS=1) N MSG X ("(IN,OUT) S OUT=$S(IN:""HELLO"", 1:""GOODBYE"")",STATUS,.MSG) W !,MSG Q TEST2(STATUS=1) PUBLIC { X ("(IN,OUT) S OUT=$S(IN:""HELLO"", 1:""GOODBYE"")",STATUS,.MSG) W !,MSG }
go to post Vitaliy Serdtsev · Jun 25, 2018 Try the following example of uploading multiple files at once, showing the progress for each file you upload (without form, submit, iframe, jQuery, flash, java, reloading/redrawing the page): <!DOCTYPE html> <html> <head> <title>Upload multiple files using XMLHttpRequest</title> <style type="text/css"> .ok { color:green; } #dropZone { width: 360px; height: 125px; border: dashed 2px #ccc; background-color: #fefefe; color: #ccc; text-align: center; padding: 125px 0 0 0; } </style> </head> <body onload="onloadHandler()"> <div id="dropZone">Drag and drop files here or use the button below</div> <div> Select files <input type="file" id="fileToUpload" onchange="fileSelected(document.getElementById('fileToUpload').files)" multiple="multiple" /> </div> <button type="button" title="Clearing the queue" onclick="clearList();">Clear</button> <button type="button" title="Upload files to the server" onclick="upFile();">Upload</button> <div id="holder"></div> <script language="javascript"> function clearList() { fileQueue=[]; document.getElementById('holder').innerHTML=''; document.getElementById('fileToUpload').value=''; } function fileSelected(files){ var holder = document.getElementById('holder'); for (var i = 0; i < files.length; i++) { var file = files[i]; var fileSize = 0; if (file.size > 1024 * 1024) fileSize = (Math.round(file.size * 100 / (1024 * 1024)) / 100).toString() + 'Mbyte'; else fileSize = (Math.round(file.size * 100 / 1024) / 100).toString() + 'Kbyte'; var divInfo = document.createElement('div'); divInfo.innerHTML=file.name+' ('+file.type+') - '+fileSize; holder.appendChild(divInfo); var divProgN = document.createElement('div'); divProgN.id='progressNumber'+i; divProgN.innerHTML='%'; holder.appendChild(divProgN); var prog = document.createElement('progress'); prog.id='progressValue'+i; prog.max='100.0'; prog.value='0'; holder.appendChild(prog); fileQueue.push({i:i,file:file}); } } function upFile() { while (fileQueue.length > 0) { var item=fileQueue.pop(); uploadFile(item.file,item.i); } } function onloadHandler() { if (typeof FileReader == "undefined") alert('Sorry, your browser does not support File API, so this demo will not work correctly'); fileQueue = new Array(); uploadFile = function (file, i) { var xhr = new XMLHttpRequest(), upload = xhr.upload, fd = new FormData(); fd.append('fUpload', file); upload.addEventListener('progress', function (evt) { if (evt.lengthComputable) { var percentComplete = Math.round(evt.loaded * 100 / evt.total); document.getElementById('progressNumber'+i).innerHTML = percentComplete.toString() + '%'; document.getElementById('progressValue'+i).value = percentComplete; } else { document.getElementById('progressNumber'+i).innerHTML = 'Cannot calculate'; } }, false); upload.addEventListener('load', function (ev) { var c=document.getElementById('progressNumber'+i); c.className='ok'; c.innerHTML='OK'; }, false); upload.addEventListener('error', function (ev) {alert('An error occurred while trying to upload the file.');}, false); upload.addEventListener('abort', function (ev) {alert('The upload was cancelled by the user or the browser reset the connection.');}, false); xhr.open('POST',window.location.href); xhr.setRequestHeader('Cache-Control', 'no-cache'); xhr.setRequestHeader('X-Requested-With', 'XMLHttpRequest'); xhr.send(fd); } dropZone=document.getElementById('dropZone'); dropZone.addEventListener('dragenter', function(ev){ ev.stopPropagation(); ev.preventDefault(); }, false); dropZone.addEventListener('dragleave', function(ev){ ev.stopPropagation(); ev.preventDefault(); this.style['backgroundColor'] = '#FEFEFE'; this.style['borderColor'] = '#CCC'; this.style['color'] = '#CCC'; }, false); dropZone.addEventListener('dragover', function(ev){ ev.stopPropagation(); ev.preventDefault(); this.style['backgroundColor'] = '#F0FCF0'; this.style['borderColor'] = '#3DD13F'; this.style['color'] = '#3DD13F'; }, false); dropZone.addEventListener('drop', function(ev){ ev.stopPropagation(); ev.preventDefault(); this.style['backgroundColor'] = '#FEFEFE'; this.style['borderColor'] = '#CCC'; this.style['color'] = '#CCC'; fileSelected(ev.dataTransfer.files); }, false); } </script> <script language="Cache" method="OnPreHTTP" arguments="" returntype="%Boolean"> #dim stream As %CSP.BinaryStream=%request.GetMimeData("fUpload") i $IsObject(stream) { // make with the resulting file useful work ;s ^tmp($i(^tmp),"filename")=stream.FileName ;s ^tmp($i(^tmp),"filesize")=stream.Size q $$$NO } q $$$YES </script> </body> </html>
go to post Vitaliy Serdtsev · Jun 22, 2018 Use %UTC instead of %TimeStamp In this case, all conversions will be performed automatically. Simple example: Class dc.test Extends %Persistent [ Final ] { Property CreationTime As %TimeStamp [ InitialExpression = {$ZDateTime($ZTimeStamp, 3, 1, 2)} ]; Property utc As %UTC [ InitialExpression = {##class(%UTC).NowUTC()} ]; /// d ##class(dc.test).Test() ClassMethod Test() { d ..%KillExtent() &sql(insert into dc.test default values) zw ^dc.testD w ! ; %SelectMode = DISPLAY d ##class(%SQL.Statement).%ExecDirect(##class(%SQL.Statement).%New(2),"select * from dc.test").%Display($c(9,9)) w ! ; %SelectMode = ODBC d ##class(%SQL.Statement).%ExecDirect(##class(%SQL.Statement).%New(1),"select * from dc.test").%Display($c(9,9)) w ! ; %SelectMode = LOGICAL d ##class(%SQL.Statement).%ExecDirect(##class(%SQL.Statement).%New(0),"select * from dc.test").%Display($c(9,9)) } }Result: USER>d ##class(dc.test).Test() ^dc.testD=1 ^dc.testD(1)=$lb("2018-06-22 14:58:11.61","2018-06-22 14:58:11.618") note that the database stores the same values ID CreationTime utc 1 2018-06-22 14:58:11.61 2018-06-22 17:58:11.618 1 Rows(s) Affected ID CreationTime utc 1 2018-06-22 14:58:11.61 2018-06-22 17:58:11.618 1 Rows(s) Affected ID CreationTime utc 1 2018-06-22 14:58:11.61 2018-06-22 14:58:11.618 1 Rows(s) AffectedSimilarly, you can play with the query in the Portal or any ODBC/JDBC client. PS: don't forget about SQL Functions: %internal()/%external(), %odbcin()/%odbcout().
go to post Vitaliy Serdtsev · Jun 22, 2018 The documentation strongly recommends against doing so: Exercise caution when comparing local time and UTC time
go to post Vitaliy Serdtsev · Jun 22, 2018 Useful links: SQL and NLS Collations Collations in Caché This issue dares relatively simply: Create the required collation tables. Fortunately, for some languages, they are already included and can be taken as a basis. In complex cases, you can contact to WRC. Check their list and status: USER>zn "%SYS" %SYS>d ^COLLATE Status Number Abbrev Name ---------- ------ ------ ---------------------- Built-in 0 OANS ISM Pre-6.2 Built-in 1 ANSI ISM 6.2->6.4 Built-in 2 COBR Ipsum/Cobra Built-in 3 DTMC DTM-compatible Built-in 4 CBR2 Ipsum/Cobra-2 Built-in 5 UNIC Cache standard Not loaded 10 GER1 German1 Not loaded 11 POR1 Portuguese1 Not loaded 12 POL1 Polish1 Not loaded 13 GER2 German2 Not loaded 14 SPA1 Spanish1 Not loaded 15 DAN1 Danish1 Available 16 CYR1 Cyrillic1 Not loaded 17 GRE1 Greek1 Not loaded 18 CZE1 Czech1 Not loaded 19 CZE2 Czech2 Not loaded 20 POR2 Portuguese2 Not loaded 21 FIN1 Finnish1 Not loaded 22 JAP1 Japanese1 Not loaded 24 POL2 Polish2 Not loaded 27 FRE1 French1 Not loaded 28 FIN2 Finnish2 Available 29 HUN1 Hungarian1 Available 30 GER3 German3 Not loaded 31 POL3 Polish3 Not loaded 32 SPA2 Spanish2 Not loaded 33 DAN2 Danish2 Not loaded 34 GRE2 Greek2 Not loaded 35 FIN3 Finnish3 Not loaded 36 LIT1 Lithuanian1 Available 37 CYR3 Cyrillic3 Not loaded 38 SLO1 Slovenian1 Not loaded 39 SLO2 Slovenian2 Not loaded 40 TUR1 Turkish1 Not loaded 41 DAN3 Danish3 Available 42 UKR1 Ukrainian1 Available 43 CYR4 Cyrillic4 Not loaded 44 CZE3 Czech3 Not loaded 46 MAL1 Maltese1 Not loaded 48 MAL2 Maltese2 Not loaded 49 SPA4 Spanish4 Not loaded 50 SLO1 Slovak1 Not loaded 51 SPA5 Spanish5 Not loaded 52 FIN4 Finnish4 Not loaded 53 CZE4 Czech4 Built-in 128 OSTR ISM Pre-6.2 string Built-in 129 NSTR ISM 6.2->6.4 string Built-in 133 USTR Cache standard string Available 144 CYR1S Cyrillic1 string Available 157 HUN1S Hungarian1 string Available 158 GER3S German3 string Available 165 CYR3S Cyrillic3 string Available 170 UKR1S Ukrainian1 string Available 171 CYR4S Cyrillic4 string load the new collation tables to your current locale for Storage make mapping your indexes in separate globals with own collation Profit! Here are two small examples: Class dc.test1 Extends %Persistent { Index iDE On DescDE As EXACT; Index iHU On DescHU As EXACT; Index iRU On DescRU As EXACT; Property DescDE As %String; Property DescHU As %String; Property DescRU As %String; ClassMethod LoadColTables() [ Private ] { s r=$$$NO try { $$$AddAllRoleTemporaryInTry n $namespace s $namespace="%SYS" $$$ThrowOnError(##class(Config.NLS.Tables).Load("deuw","COL","German3")) $$$ThrowOnError(##class(Config.NLS.Tables).Load("hunw","COL","Hungarian1")) $$$ThrowOnError(##class(Config.NLS.Tables).Load("rusw","COL","Cyrillic4")) ;d ^COLLATE ; for check only s r=$$$YES }catch(ex) { w ex.DisplayString(),! } q r } /// d ##class(dc.test1).Test() ClassMethod Test() { q:'..LoadColTables() d ..%KillExtent() &sql(insert into dc.test1(DescDE,DescHU,DescRU) select 'Straßer','csak','Прохор' union all select 'Straster','comb','Павел' union all select 'Strasser','cukor','Пётр') zw ^dc.test1D w !,"---- Before (for all indexes by default = 'Cache standard') --------",!! d print w !,"---- After (each index has its own collation) --------",!! d ..%PurgeIndices() d $system.OBJ.DisplayError(##class(%GlobalEdit).Create(,"dc.test1.iDE",##class(%Collate).DisplayToLogical("German3"))) ,$system.OBJ.DisplayError(##class(%GlobalEdit).Create(,"dc.test1.iHU",##class(%Collate).DisplayToLogical("Hungarian1"))) ,$system.OBJ.DisplayError(##class(%GlobalEdit).Create(,"dc.test1.iRU",##class(%Collate).DisplayToLogical("Cyrillic4"))) ,..%BuildIndices(,$$$NO,$$$YES,$$$NO) ,$system.SQL.TuneTable($classname(),$$$YES) ,$system.OBJ.Compile($classname(),"cu-d") ,print q print zw ^dc.test1.iDE,^dc.test1.iHU,^dc.test1.iRU w ! f lng="DE","HU","RU" d ##class(%SQL.Statement).%ExecDirect(,$$$FormatText("select %ID,Desc%1 from dc.test1 order by %exact(Desc%1)",lng)).%Display() w !! q } Storage Default { <Data name="test1DefaultData"> <Value name="1"> <Value>%%CLASSNAME</Value> </Value> <Value name="2"> <Value>DescDE</Value> </Value> <Value name="3"> <Value>DescHU</Value> </Value> <Value name="4"> <Value>DescRU</Value> </Value> </Data> <DataLocation>^dc.test1D</DataLocation> <DefaultData>test1DefaultData</DefaultData> <IdLocation>^dc.test1D</IdLocation> <Index name="iDE"> <Location>^dc.test1.iDE</Location> </Index> <Index name="iHU"> <Location>^dc.test1.iHU</Location> </Index> <Index name="iRU"> <Location>^dc.test1.iRU</Location> </Index> <IndexLocation>^dc.test1I</IndexLocation> <StreamLocation>^dc.test1S</StreamLocation> <Type>%Library.CacheStorage</Type> } }Result: USER>d ##class(dc.test1).Test() ^dc.test1D=3 ^dc.test1D(1)=$lb("","Straßer","csak","Прохор") ^dc.test1D(2)=$lb("","Straster","comb","Павел") ^dc.test1D(3)=$lb("","Strasser","cukor","Пётр") ---- Before (for all indexes by default = 'Cache standard') -------- ^dc.test1.iDE("Strasser",3)="" ^dc.test1.iDE("Straster",2)="" ^dc.test1.iDE("Straßer",1)="" ^dc.test1.iHU("comb",2)="" ^dc.test1.iHU("csak",1)="" ^dc.test1.iHU("cukor",3)="" ^dc.test1.iRU("Павел",2)="" ^dc.test1.iRU("Прохор",1)="" ^dc.test1.iRU("Пётр",3)="" ID DescDE 3 Strasser 2 Straster 1 Straßer 3 Rows(s) Affected ID DescHU 2 comb 1 csak 3 cukor 3 Rows(s) Affected ID DescRU 2 Павел 1 Прохор 3 Пётр 3 Rows(s) Affected ---- After (each index has its own collation) -------- ^dc.test1.iDE("Strasser",3)="" ^dc.test1.iDE("Straßer",1)="" ^dc.test1.iDE("Straster",2)="" ^dc.test1.iHU("comb",2)="" ^dc.test1.iHU("cukor",3)="" ^dc.test1.iHU("csak",1)="" ^dc.test1.iRU("Павел",2)="" ^dc.test1.iRU("Пётр",3)="" ^dc.test1.iRU("Прохор",1)="" ID DescDE 3 Strasser 1 Straßer 2 Straster 3 Rows(s) Affected ID DescHU 2 comb 3 cukor 1 csak 3 Rows(s) Affected ID DescRU 2 Павел 3 Пётр 1 Прохор 3 Rows(s) Affected Class dc.test2 Extends %Persistent { Index iDE On (Lng, Descr As EXACT); Index iHU On (Lng, Descr As EXACT); Index iRU On (Lng, Descr As EXACT); Property Lng As %String; Property Descr As %String; /// d ##class(dc.test2).Test() ClassMethod Test() { d ..%KillExtent() ,$system.OBJ.DisplayError(##class(%GlobalEdit).Create(,"dc.test2.iDE",##class(%Collate).DisplayToLogical("German3"))) ,$system.OBJ.DisplayError(##class(%GlobalEdit).Create(,"dc.test2.iHU",##class(%Collate).DisplayToLogical("Hungarian1"))) ,$system.OBJ.DisplayError(##class(%GlobalEdit).Create(,"dc.test2.iRU",##class(%Collate).DisplayToLogical("Cyrillic4"))) &sql(insert into dc.test2(Lng,Descr) select 'DE', 'Straßer' union select 'DE', 'Straster' union select 'DE', 'Strasser' union select 'HU', 'csak' union select 'HU', 'comb' union select 'HU', 'cukor' union select 'RU', 'Прохор' union select 'RU', 'Павел' union select 'RU', 'Пётр') zw ^dc.test2D,^dc.test2.iDE,^dc.test2.iHU,^dc.test2.iRU d $system.SQL.TuneTable($classname(),$$$YES) ,$system.OBJ.Compile($classname(),"cu-d") ,print q print w ! f lng="DE","HU","RU" { s idx=$replace("iDE,iHU,iRU","i"_lng,"_i"_lng) d ##class(%SQL.Statement).%ExecDirect(,$$$FormatText("select * from %IGNOREINDEX %1 dc.test2 where Lng=? order by %exact(Descr)",idx),lng).%Display() w !! } q } Storage Default { <Data name="test2DefaultData"> <Value name="1"> <Value>%%CLASSNAME</Value> </Value> <Value name="2"> <Value>Lng</Value> </Value> <Value name="3"> <Value>Descr</Value> </Value> </Data> <DataLocation>^dc.test2D</DataLocation> <DefaultData>test2DefaultData</DefaultData> <IdLocation>^dc.test2D</IdLocation> <Index name="iDE"> <Location>^dc.test2.iDE</Location> </Index> <Index name="iHU"> <Location>^dc.test2.iHU</Location> </Index> <Index name="iRU"> <Location>^dc.test2.iRU</Location> </Index> <IndexLocation>^dc.test2I</IndexLocation> <StreamLocation>^dc.test2S</StreamLocation> <Type>%Library.CacheStorage</Type> } }Result: USER>d ##class(dc.test2).Test() ^dc.test2D=9 ^dc.test2D(1)=$lb("","DE","Straßer") ^dc.test2D(2)=$lb("","DE","Straster") ^dc.test2D(3)=$lb("","DE","Strasser") ^dc.test2D(4)=$lb("","HU","csak") ^dc.test2D(5)=$lb("","HU","comb") ^dc.test2D(6)=$lb("","HU","cukor") ^dc.test2D(7)=$lb("","RU","Прохор") ^dc.test2D(8)=$lb("","RU","Павел") ^dc.test2D(9)=$lb("","RU","Пётр") ^dc.test2.iDE(" DE","Strasser",3)="" ^dc.test2.iDE(" DE","Straßer",1)="" ^dc.test2.iDE(" DE","Straster",2)="" ^dc.test2.iDE(" HU","comb",5)="" ^dc.test2.iDE(" HU","csak",4)="" ^dc.test2.iDE(" HU","cukor",6)="" ^dc.test2.iDE(" RU","Павел",8)="" ^dc.test2.iDE(" RU","Прохор",7)="" ^dc.test2.iDE(" RU","Пётр",9)="" ^dc.test2.iHU(" DE","Strasser",3)="" ^dc.test2.iHU(" DE","Straster",2)="" ^dc.test2.iHU(" DE","Straßer",1)="" ^dc.test2.iHU(" HU","comb",5)="" ^dc.test2.iHU(" HU","cukor",6)="" ^dc.test2.iHU(" HU","csak",4)="" ^dc.test2.iHU(" RU","Павел",8)="" ^dc.test2.iHU(" RU","Прохор",7)="" ^dc.test2.iHU(" RU","Пётр",9)="" ^dc.test2.iRU(" DE","Strasser",3)="" ^dc.test2.iRU(" DE","Straster",2)="" ^dc.test2.iRU(" DE","Straßer",1)="" ^dc.test2.iRU(" HU","comb",5)="" ^dc.test2.iRU(" HU","csak",4)="" ^dc.test2.iRU(" HU","cukor",6)="" ^dc.test2.iRU(" RU","Павел",8)="" ^dc.test2.iRU(" RU","Пётр",9)="" ^dc.test2.iRU(" RU","Прохор",7)="" ID Descr Lng 3 Strasser DE 1 Straßer DE 2 Straster DE 3 Rows(s) Affected ID Descr Lng 5 comb HU 6 cukor HU 4 csak HU 3 Rows(s) Affected ID Descr Lng 8 Павел RU 9 Пётр RU 7 Прохор RU 3 Rows(s) Affected Important: if you remove "As EXACT" in the index, the examples will not work correctly for the German language, because: Capital ẞ
go to post Vitaliy Serdtsev · Jun 15, 2018 Add the RenderDirectlyOnTimeout property in the report: Property RenderDirectlyOnTimeout As %ZEN.Datatype.boolean(ZENURL = "$RENDERDIRECTLYONTIMEOUT") [ InitialExpression = {$$$NO} ];Then you will get the following error: ERROR #5001: Cannot contact server on port %1.or ERROR #5001: Received Control-C or there was a fatal error.
