User comments | InterSystems Developer Community

Vitaliy Serdtsev · Jan 31, 2023 go to post

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 go to post

Instead of $ZPARENT, try using $system.Context.WorkMgr().MasterJob

See your local documentation:
MasterJob^2022.1ParentJob^2022.2

PS: This is done differently for Caché.

Vitaliy Serdtsev · Jan 31, 2023 go to post

See the answers here.

In addition there are a few comments:

you can use extrinsic function:
- ExtrinsicFunctions
- An extrinsic Caché ObjectScript function call operating on a database column
you can use stored function: Defining and Using Stored Procedures
you can encrypt the entire database, not just individual fields: Using Encrypted Databases
you can restrict access to encrypted/sensitive fields to privileged users only: GRANT column-privilege

Vitaliy Serdtsev · Jan 31, 2023 go to post

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 go to post

See Deploying code to production site especially the section Limitations for DeployToFile() and InstallFromFile()

Vitaliy Serdtsev · Jan 25, 2023 go to post

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 go to post

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.

Vitaliy Serdtsev · Jan 25, 2023 go to post

SFN - system file number

Vitaliy Serdtsev · Jan 24, 2023 go to post

If you select the text with the mouse, you will see spaces on the right in the second "Output".
In general, I think that such details should be enclosed in quotation marks.

Vitaliy Serdtsev · Jan 24, 2023 go to post

Ok. If the nuances of formatting spaces and line feed are unimportant, then can yet shorten Robert's code:

size = 40

ClassMethod Build(f As %Integer)
{
 s a="#" f i=f:-1:1 w !?i,a s a=a_"##"
}

Vitaliy Serdtsev · Jan 23, 2023 go to post

It is necessary to take into account the following points:

according to the documentation should be

// add the columns to export
Do mgr.ColumnNames.Insert("Closed")
Do mgr.ColumnTypes.Insert("N")
   
Do mgr.ColumnNames.Insert("DocumentType")
Do mgr.ColumnTypes.Insert("S")
   
Do mgr.ColumnNames.Insert("StatusCode")
Do mgr.ColumnTypes.Insert("N")
   
Do mgr.ColumnNames.Insert("StatusLastUpdated")
Do mgr.ColumnTypes.Insert("TS")

StringQuote only affects when escaping the corresponding characters, for example:
- Set mgr.StringQuote = $c(34) // double quotes
```
a"b -> "a""b"
```
- Set mgr.StringQuote = $c(39) // '
```
a'b -> 'a''b'
```
DateFormat and TimeFormat are applicable only for fields of type D and T, respectively, but you have a field of type TS
TimeStampFormat is applicable only for import, but not for export

Vitaliy Serdtsev · Jan 23, 2023 go to post

I hope that Eduard will be formulate the conditions of the task more carefully in the future.

PS: Robert, you shouldn't have rushed to remove your solution.

In any case, your solution was the best.

Vitaliy Serdtsev · Jan 23, 2023 go to post

You are absolutely right.
There should be no discrepancies in the description of the task and in the code for checking the solution.

Vitaliy Serdtsev · Jan 23, 2023 go to post

I suggest waiting for an answer from @Eduard Lebedyuk, the author of the discussion, who knows how really should be.

Vitaliy Serdtsev · Jan 23, 2023 go to post

Perhaps the author wanted to test us for attentiveness ?

Vitaliy Serdtsev · Jan 23, 2023 go to post

I'm looking at the output validation code, not Output, where there are no spaces at all on the right. It is a pity that there is confusion because of this!

Class codeGolf.test.Pyramid

Class codeGolf.test.Pyramid Extends %UnitTest.TestCase
{

ClassMethod openDisplay(Output filename As %String)
{
  Set dir=##class(%SYSTEM.Process).CurrentDirectory()
  Set filename=dir_"temp.txt"
  Open filename:("NRW"):5
  USE filename
}

ClassMethod getResult(filename As %String, Output list As %ListOfDataTypes)
{
  Set list = ##class(%ListOfDataTypes).%New()
  Set file = ##class(%File).%New(filename)
  Set sc = file.Open( "R" )
  If $$$ISERR(sc) quit

  While 'file.AtEnd {
    Do list.Insert(file.ReadLine())
  }
  Do file.Close()
  Do ##class(%File).Delete(filename)
}

Method TestSimple()
{
  Do ..openDisplay(.filename)
  Do ##class(codeGolf.Pyramid).Build(3)
  CLOSE filename
  Do ..getResult(filename, .result)
  Do $$$AssertEquals(result.GetAt(1), "  #  ")
  Do $$$AssertEquals(result.GetAt(3), "#####")
}

Method TestComplex()
{
  Do ..openDisplay(.filename)
  Do ##class(codeGolf.Pyramid).Build(40)
  CLOSE filename
  Do ..getResult(filename, .result)
  Do $$$AssertEquals(result.GetAt(1),  "                                       #                                       ")
  Do $$$AssertEquals(result.GetAt(23), "                 #############################################                 ")
  Do $$$AssertEquals(result.GetAt(40), "###############################################################################")
}

}

Vitaliy Serdtsev · Jan 23, 2023 go to post

size = 50

ClassMethod Build(f As %Integer)
{
 s a="#" f i=f:-1:1 w ?i-1,a,?f*2-1,! s a=a_"##"
}

PS: if you look at the class codeGolf.test.Pyramid, you can see that the format of the output data for f=3 should be as follows:

"  #  "
" ### "
"#####"

So be careful.

Vitaliy Serdtsev · Dec 21, 2022 go to post

Your example does not take into account which element is selected.

For example, if you select the last element, then the list wins instead of the delimited string.

s L=1e4,N=1e5,a="1",b=$lb(1)
f i=2:1:L s a=a_"_"_i,b=b_$lb(i)

s t=$zh
f i=1:1:N s c=$p(a,"_",L)
w $zh-t," s.",!  

s t=$zh
f i=1:1:N s c=$li(b,L)
w $zh-t," s.",!

Vitaliy Serdtsev · Dec 21, 2022 go to post

I'll put in my five cents too.

Firstly, Apache for Windows is installed very easily: you just need to unpack the archive and, if necessary (this is done once), register the service.
Secondly, it is not difficult to find the Apache distribution for Windows, for example here: Apache Haus Downloads
Thirdly, I always remove PWS and use my own Apache, so I welcome removing PWS from the IRIS distribution

Vitaliy Serdtsev · Dec 1, 2022 go to post

It is not entirely clear that you are using Caché or IRIS 2021.1:

#Caché
I have two tables: one a local table using cache and the other is via an SQL gateway..

Product version: IRIS 2021.1

The presence/absence of restrictions on the execution of heterogeneous queries depends on this:

Vitaliy Serdtsev · Nov 14, 2022 go to post

DELETE %NOCHECK FROM nodes

Vitaliy Serdtsev · Nov 11, 2022 go to post

size = 79

ClassMethod Order(a As %String) As %String
{
 s r=a f i=1:1:5e5{s s=$p(a," ",i),w=$tr(s,1e20/17),$p(r," ",$tr(s,w))=w} q r
}

Vitaliy Serdtsev · Oct 24, 2022 go to post

Have you looked at the type of fields in the metadata?
For me, the INTEGER type (4) is returned for ID, and the BIGINT type (-5) is returned for %ID.

Try

SELECT id, x, y, z FROM some_table order by %id

and

d rs.%GetMetadata().%Display()

What are the default values for AdaptiveMode, AllowRowIDUpdate, IdKey^CPF, PKEY_IS_IDKEY ?

Try disabling AdaptiveMode.

Vitaliy Serdtsev · Oct 24, 2022 go to post

There is a faster way without opening the object:

Class dc.test Extends %Persistent
{

ClassMethod Test()
{
  
  s classname="dc.test", storagename="Default"
  w $$$defMemberKeyGet(classname,$$$cCLASSstorage,storagename,$$$cSDEFdatalocation)
}

Storage Default
{
<Data name="testDefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
</Value>
</Data>
<DataLocation>^abc.1</DataLocation>
<DefaultData>testDefaultData</DefaultData>
<IdLocation>^dc.testD</IdLocation>
<IndexLocation>^dc.testI</IndexLocation>
<StreamLocation>^dc.testS</StreamLocation>
<Type>%Library.CacheStorage</Type>
}

}

Result:

^abc.1

Vitaliy Serdtsev · Oct 20, 2022 go to post

Here are two ways:

s s="12162,CHAPTER I,Certain infectious and parasitic diseases (A00-B99),003 (A20-A28),Certain zoonotic bacterial diseases,A28,""Other zoonotic bacterial diseases, not elsewhere classified"",A28,""Other zoonotic bacterial diseases, not elsewhere classified"",N,N,N,N,N,,,,,,,,,,G"
d ##class(%DeepSee.TermList).%ParseCSVRecord(s,.arr1)
zw arr1

w !

s list=$$CSVtoList^%occLibrary(s)

d ##class(%ListOfDataTypes).BuildValueArray(list,.arr2)
zw arr2

Take a look at the class methods %SQL.Util.Procedures

Vitaliy Serdtsev · Oct 19, 2022 go to post

Decoding Base64 PDF File

Vitaliy Serdtsev · Oct 19, 2022 go to post

If there was a built-in function for expanding the list into a table, then the solution would be even shorter.

An example of such a function:

Class dc.golf.Kata [ Abstract ]
{

Query split(
  s As %VarString,
  delim As %VarString) As %Query(ROWSPEC = "word:%String") [ SqlProc ]
{
}

ClassMethod splitExecute(
  ByRef qHandle As %Binary,
  s As %VarString,
  delim As %VarString) As %Status
{
  d ##class(%ListOfDataTypes).BuildValueArray($lfs(s,delim),.qHandle)
  s qHandle=0
  q $$$OK
}

ClassMethod splitFetch(
  ByRef qHandle As %Binary,
  ByRef Row As %List,
  ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = splitExecute ]
{
  s d=$o(qHandle(qHandle),1,v)
  i d="" { s AtEnd=1 } else { s Row=$lb(v),qHandle=d }
  q $$$OK
}
}

Now you can do a variety of things with the string, for example:

select min(length(word)) "min",length(word) len,word from dc_golf.Kata_split('bitcoin take over the world maybe who knows perhaps',' ')

Result:

min	len	word
3	7	bitcoin
3	4	take
3	4	over
3	3	the
3	5	world
3	5	maybe
3	3	who
3	5	knows
3	7	perhaps

Vitaliy Serdtsev · Oct 19, 2022 go to post

This code can be reduced by two more characters.

Vitaliy Serdtsev · Oct 7, 2022 go to post

There are no such methods, because the size of the stream (file) can exceed the maximum length of the string, which at the moment is 3641144, so the type of string (%String, %VarString, %xsd.base64Binary, etc.) will not always be able to hold all the data.

But also it is not difficult to read the stream into a line:

ClassMethod StreamToStr(ByRef stream As %Stream.Object) As %String
{
  s s=""
  d stream.Rewind()
  while 'stream.AtEnd {
    s s=s_stream.Read($$$MaxLocalLength)
  }
  q s
}

PS: above I have given methods, some of which are available in older versions of Caché.

Vitaliy Serdtsev · Aug 19, 2022 go to post

See documentation for $SYSTEM.SQL.QueryToTable(): THIS METHOD IS DEPRECATED. Refer to $SYSTEM.SQL.Schema.QueryToTable instead.^2021.1

Am I correct in saying that a create table as select is strictly as COPY operation of an existing table.

Yes, see CREATE TABLE table AS SELECT query copies column definitions and column data from an existing table (or tables) into a new table based on the specified SELECT query.^2022.1

To clarify, I used the $SYSTEM command to create table from query, because I kept getting errors with create table as select (didn't like my AS statement). What could have gone wrong there?
Benjamin De Boe

For that purpose, the CREATE TABLE .. AS SELECT .. syntax is offered by several other databases, and will appear in IRIS SQL soon. As a matter of fact, a developer is working on that command this very week :-)

Because this feature has appeared since version IRIS 2022.1 (see link above).

User statistics