User comments | InterSystems Developer Community

Vitaliy Serdtsev · Nov 1, 2018 go to post

$tr(str,",",$c(9))
or
$replace(str,",",$c(9))

Vitaliy Serdtsev · Nov 1, 2018 go to post

I was looking at the generated INT code for &sql(select lpad('1',4,'0') n)

Unfortunately, for COS at the moment I have not found an analogue LPAD^SQL.
Can you offer a documented LPAD^COS ?

Vitaliy Serdtsev · Oct 30, 2018 go to post

Once upon a time promised to make Server-Side JavaScript alongside with Caché ObjectScript and Caché Basic:

I found a JS Runtime Shell that uses Node.JS & esprima:

SAMPLES>d ##class(%CPT.JS.Runtime.Shell).Run()
 
JRS> ?
 
.L CLASS   - load javascript methods from CLASS into JS global namespace
.LV var,.. - load the given COS public vars into JS global namespace
 
.S FUNC  - list the source of the method which implements function FUNC
           - FUNC must be in the global namespace
           - the whole implementing method is shown, even for an inner function
           - this will also work for non-javascript methods
.M       - list MAC code from latest compilation
.I       - list INT code from latest compilation
.T       - show latest tree
 
.G       - list global symbols
 
.N CODE  - execute JS on node.js
.N       - execute the last-entered line of JS on node.js
 
.CONFIG ... - passed to %CPT.CalloutShell 'CONFIG' command
 
.SET ... - passed to %CPT.CalloutShell 'SET' command
.CLEAR ... - passed to %CPT.CalloutShell 'CLEAR' command
.SHOW ... - passed to %CPT.CalloutShell 'SHOW' command
 
!COS     - execute a line of COS
 
Anything else is interpreted as JS. :-
* if preceded by "=" it must be a single function definition
* otherwise it can be either a single expression or a sequence
  of statements separated by ";"s
 
Either way it will be compiled into a function body and :-
* if preceded by "==" it is stored as a JS global with the new function's name
* otherwise it will just be executed
 
\n can be used to represent a newline.
 
You can also take input from a file :-
 <FILE    - read JS from FILE and compile+execute it
 <=FILE   - read JS from FILE and compile+store it
 
The result of the execution is shown and also stored in global variable '_'.
 
For a global variable, set it without using 'var' (this will be fixed)


JRS> .N var obj = { name: "John", age: 30, city: "New York" }; JSON.stringify(obj);
{result: "{""name"":""John"",""age"":30,""city"":""New York""}"}

JRS> .N Math.random()
{result: .5670654247514904}

JRS> .N var fruits = ["Banana", "Orange", "Apple", "Mango"]; fruits.sort();
{result: ["Apple", "Banana", "Mango", "Orange", 1: "Apple", 2: "Banana", 3: "Mango", 4: "Orange"]}

JRS> q
 
SAMPLES>

Vitaliy Serdtsev · Oct 30, 2018 go to post

If you do universal LPAD, it is best $$lpad^%qarfunc, because for certain arguments may produce an incorrect result, for example:

#define lpad(%s,%len,%pad) $tr($j(%s,%len)," ",%pad)
   
s s="a b",len=8,pad="0"
   
w $$$lpad(s,len,pad),!
 ,$$lpad^%qarfunc(s,len,pad),!
 ,$$$lpad(s,len,s),!
 ,$$lpad^%qarfunc(s,len,s),!

Result:

USER>d ^test
00000a0b
00000a b
aaaaaaab
a ba a b

Vitaliy Serdtsev · Oct 29, 2018 go to post

Also LPAD^SQL

USER>d $system.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
 
The command prefix is currently set to: <>.
Enter q to quit, ? for help.
USER>>select lpad('1',4,'0') n
1.      select lpad('1',4,'0') n
 
n
0001
 
1 Rows(s) Affected

Vitaliy Serdtsev · Oct 29, 2018 go to post

The functions from %qarfunc are actively used in system classes, so I don't think ^%qarfunc will be removed in the near future, otherwise everything will stop working.

Vitaliy Serdtsev · Oct 29, 2018 go to post

See: Implied Namespace Mapping What Is Accessible in Your Namespaces

Vitaliy Serdtsev · Oct 23, 2018 go to post

USER>w $$lpad^%qarfunc("1",4,"0")
0001

Vitaliy Serdtsev · Oct 19, 2018 go to post

Try

ClientMethod changeParams() [ Language = javascript ]
{
  var query = zen('categorieBeneficiaire');
  var param1 = zen('catBParam1');
  var param2 = zen('catBParam2');

  param1.value='OS';
  param2.value='NSAL';
  query.refreshContents();
}

/// This client event, if present, is fired when the page is loaded.
ClientMethod onloadHandler() [ Language = javascript ]
{
  zenPage.changeParams();
}

Vitaliy Serdtsev · Oct 17, 2018 go to post

or
w $zcvt(x,"I","HTML")

Vitaliy Serdtsev · Oct 9, 2018 go to post

You don't need anything else to work with globals directly: Using the Globals API.

See the code examples in <install-dir>\dev\dotnet\samples\globals

Vitaliy Serdtsev · Oct 9, 2018 go to post

Another variants:

USER>k  s args=3,args(1)="-h",args(2)=21,args(3)="community.intersystems.com" do $system.OBJ.DisplayError(##class(%Net.Remote.Utility).RunCommandViaCPIPE("tracert",,.output,,900,.args)) w output

or

USER>k  s args=3,args(1)="-h",args(2)=21,args(3)="community.intersystems.com" do $system.OBJ.DisplayError(##class(%Net.Remote.Utility).RunCommandViaZF("tracert",,.output,,900,,.args)) w output

Vitaliy Serdtsev · Oct 9, 2018 go to post

See Handling of static data^EN or Обработка статики^RU.

In your case, is enough will be the standard class %CSP.StreamServer (see streamserve.csp from CSP Samples). And you can use it to download external files, for example: %25CSP.StreamServer.cls?FILE=blablabla

PS: by the way, it is not safe to transmit OID in open form, so pay attention to the method %CSP.Page:Encrypt(oid).

Vitaliy Serdtsev · Oct 2, 2018 go to post

Changed a bit and added new code:

/// d ##class(Scratch.test).ClassVsInst()
ClassMethod ClassVsInst(N = {1e6}) [ ProcedureBlock = 0, PublicList = st ]
{
   n p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, xClass, xInst, st
   
   s p1="пропоывшыщзшвыщшв"
    ,p2="гшщыгвыовлдыовдьыовдлоыдлв"
    ,p3="widuiowudoiwudoiwudoiwud"
    ,p4="прпроыпворыпворыпворыпв"
    ,p5="uywyiusywisywzxbabzjhagjЭ"
    ,p6="пропоывшыщзшвыщшв"
    ,p7="гшщыгвыовлдыовдьыовдлоыдлв"
    ,p8="widuiowudoiwudoiwudoiwud"
    ,p9="прпроыпворыпворыпворыпв"
    ,p10="uywyiusywisywzxbabzjhagjЭ"
    
    ,xClass="(args...) f i=1:1:N s sc=##class(Scratch.test).%1(args...)"
    ,xInst="(args...) f i=1:1:N s sc=st.%1(args...)"
   
   
   s st=##class(Scratch.test).%New()
   
   w $p($zv,"(Build"),!!
   
   d runClassmethod("dummyClass10", p1, p2, p3, p4, p5, p6, p7, p8, p9, p10)
    ,runMethod("dummyClass10", p1, p2, p3, p4, p5, p6, p7, p8, p9, p10)
    ,runMethod("dummyInst10", p1, p2, p3, p4, p5, p6, p7, p8, p9, p10)
    ,runClassmethod("dummyClass5", p1, p2, p3, p4, p5)
    ,runMethod("dummyClass5", p1, p2, p3, p4, p5)
    ,runMethod("dummyInst5", p1, p2, p3, p4, p5)
    ,runClassmethod("dummyClassNull")
    ,runMethod("dummyClassNull")
    ,runMethod("dummyInstNull")
   
    ,runX("dummyClass10",xClass, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10)
    ,runX("dummyInst10",xInst, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10)
    ,runX("dummyClass5",xClass, p1, p2, p3, p4, p5)
    ,runX("dummyInst5",xInst, p1, p2, p3, p4, p5)
    ,runX("dummyClassNull",xClass)
    ,runX("dummyInstNull",xInst)
  
runMethod(methodname,args...)
   n t,i,sc
   s t=$zh f i=1:1:N s sc=$method(st, methodname, args...)
   s t=$zh-t w methodname,?16,"total time = "_t,?38,"avg time = "_(t/N),!
   q
runClassmethod(methodname,args...)
   n t,i,sc
   s t=$zh f i=1:1:N s sc=$classmethod("Scratch.test", methodname, args...)
   s t=$zh-t w methodname_"*",?16,"total time = "_t,?38,"avg time = "_(t/N),!
   q
runX(methodname,x,args...)
   n t,i,sc
   s t=$zh
   x ($$$FormatText(x,methodname), args...)
   s t=$zh-t w "X"_methodname,?16,"total time = "_t,?38,"avg time = "_(t/N),!
   q
}

Result:

USER>d ##class(Scratch.test).ClassVsInst()
Cache for Windows (x86-64) 2018.1
 
dummyClass10*   total time = .328227  avg time = .000000328227
dummyClass10    total time = .27655   avg time = .00000027655
dummyInst10     total time = .259913  avg time = .000000259913
dummyClass5*    total time = .286983  avg time = .000000286983
dummyClass5     total time = .25666   avg time = .00000025666
dummyInst5      total time = .240312  avg time = .000000240312
dummyClassNull* total time = .274406  avg time = .000000274406
dummyClassNull  total time = .250926  avg time = .000000250926
dummyInstNull   total time = .234486  avg time = .000000234486
XdummyClass10   total time = .312917  avg time = .000000312917
XdummyInst10    total time = .264871  avg time = .000000264871
XdummyClass5    total time = .286985  avg time = .000000286985
XdummyInst5     total time = .238557  avg time = .000000238557
XdummyClassNull total time = .278684  avg time = .000000278684
XdummyInstNull  total time = .236815  avg time = .000000236815

Vitaliy Serdtsev · Sep 19, 2018 go to post

See %UTC. PS: also.

Vitaliy Serdtsev · Sep 17, 2018 go to post

Still can be so:

select relation_name tablename,
       %Library.SQLCatalog_SQLClassname(relation_name) classname

from %Library.SQLCatalog_SQLTables()

-- where relation_name [ 'aaa'

Vitaliy Serdtsev · Sep 14, 2018 go to post

I have a few comments.:

your code finds by the class name, not the table name, which is confusing
For example, for the next class, the method finds nothing:

Class dc.test Extends %Persistent [ SqlTableName = aaa ]
{
...
}
```
USER>d ##class(objectscript.findTable).test("aaa")
?
```
according to the documentation, your code uses the deprecated API:

proof
The Caché Library set of class definitions classes has been superseded by the %Dictionary package. The %Library classes described here are maintained for compatibility with existing applications.
New code should make use of the classes within the %Dictionary package.
in fact, the search is done elementary:
select ClassName from %Dictionary.ClassDefinition_ClassIndex() where ClassName [ 'test'

or

select ClassName from %Dictionary.ClassDefinition_ClassIndex() where nvl(SqlTableName,ClassName) [ 'aaa'

Vitaliy Serdtsev · Sep 14, 2018 go to post

Task Schedule (see Delete)
%SYS.Task.History:PurgeHistory()

Vitaliy Serdtsev · Sep 14, 2018 go to post

See

Vitaliy Serdtsev · Jul 26, 2018 go to post

Declaring Stream Properties You can store in the database not the files themselves, but only links to them. Thus, the size of your database will be small.
Managing Caché You can create a custom task on a schedule in which you need programmatic to clean up old files and then compress cache.dat (compact/truncate).

Vitaliy Serdtsev · Jul 26, 2018 go to post

$system.OBJ.Export()
E.g.

s all("blabla.MAC")=""
s all("blabla.INT")=""
s all("blabla.INC")=""
s all("blabla.OBJ")=""
s all("blabla.CLS")=""

; ...

d $system.OBJ.Export(.all,"C:\temp\all.xml")

Vitaliy Serdtsev · Jul 26, 2018 go to post

SELECT %ID,prop3 FROM mp.test WHERE %ID IN (
  SELECT * FROM (
    SELECT TOP ALL %ID FROM mp.test WHERE prop1='name' AND prop3>='1' AND prop3<='30' AND prop2='prop2' ORDER BY prop3 DESC
  ) WHERE %VID BETWEEN 1 AND 5
) -- ORDER BY prop3 DESC

Vitaliy Serdtsev · Jul 26, 2018 go to post

Working with Streams
E.g.

;s stream=##class(%GlobalBinaryStream).%New()
;d stream.Write("--------")
   
s file=##class(%Stream.FileBinary).%New()
s file.Filename="C:\temp\test.txt"
d file.CopyFromAndSave(stream)

Vitaliy Serdtsev · Jul 26, 2018 go to post

And if so?

s query = "select %ID,prop3 from mp.test where %ID in (SELECT * FROM (SELECT %ID FROM mp.test WHERE prop1='name' AND prop3>='1' AND prop3<='30' AND prop2='prop2') WHERE %VID BETWEEN 1 AND 5)"

Vitaliy Serdtsev · Jul 25, 2018 go to post

Could you provide a complete example similar to mine (code + data + queries with sorting and filtering) that you have a issue with?

How much all rows in the table? How much RAM? Which version $zv?

Vitaliy Serdtsev · Jul 20, 2018 go to post

There are one error in the Test5 method: t4case* -> t5case*

Vitaliy Serdtsev · Jul 20, 2018 go to post

See Using SSL to Connect for %Net.HttpRequest.

Vitaliy Serdtsev · Jul 20, 2018 go to post

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"
  q

But the quickest option is if/elseif/else, since here is used inline-call, and not external-call.

Vitaliy Serdtsev · Jul 19, 2018 go to post

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).

Vitaliy Serdtsev · Jul 19, 2018 go to post

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)

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.

User statistics