Replies by Robert Cemper for InterSystems Developer Community

Robert Cemper · May 20, 2020

As there is no default RANDOM() in Caché/IRIS SQL you have to write it yourself.
Create this class:

Class User.Henry { ClassMethod Random(maxval As %Integer = 2) As %Integer [ SqlName = RANDOM, SqlProc ] { quit $random(maxval) } }

Assuming your table has defautl ID (%Integer, MINVAL=1)

SELECT TOP 1 column FROM table
HAVING ID >= RANDOM(MAX(ID))

>= ensures that you just get an existing ID and don't drop on a deleted one

Of course, if you know in advance the highest existing ID (e.g. maxid) then RANDOM(maxid) will do it.

go to post

Robert Cemper · May 20, 2020

Extending the reply of @Vitaliy.Serdtsev
Basically whatever your variable holds could be a %String (also including binary values)
COS has a few check functions to interpret the content

$ISOBJECT
$ISVALIDDOUBLE
$ISVALIDNUM
$INUMBER
$LISTVALID
the whole block of $ZD,$ZDH,$ZDT,$ZDTH,$ZT,$ZTH
$MATCH

Further on the Pattern Match operator ? let you sort out pure text and binary, .....

All you have is just content. It is left up to you to decide what type it is.
You can find out by those functions what is NOT.

Only the data type classes decide to some limit what type it should be.

go to post

Robert Cemper · May 20, 2020

NO it is not possible:
In IRIS for Windows (x86-64) 2020.1 (Build 215U) it is compiled.
But at run time you get an SQLCODE=1 and an error message for a bad SQL statement.
Better compose an SQL string "SELECT name FROM "_table and process it with some ResultSet class,

The embedded code in the class we know from Cache is gone.
It all generates into a %scqlc.* class

go to post

Robert Cemper · May 18, 2020

If you don't have a standard ID (%Integer,MINVAL=1)
but something like ^<dataglobal>(BRANCH,ID) or similar than you might get your fast count by applying
The adopted Bitmap
Adopted Bitmaps example now on Open Exchange

ant iterate over the "BRANCHES"

go to post

Robert Cemper · May 17, 2020

If you run Tune Table on a regular base it updated EXTENTSIZE.
But this is then not exact but a close estimation depending on the frequency of run

go to post

Robert Cemper · May 17, 2020

In principle I'd share te suggestion of @Stephen Canzano;

with 2 minor additions: SELECT Count(ID) from <youtable> will you always lead to the
explicit or implicit Extent Index. (bitmap or standard)

set cls=##class(%Dictionary.CompiledClass).%OpenId("classname") set table=clsSqlTableName

This is important as there are a bunch of classes that have explicit defined table names.

In addition, it works also for table linked over SQL gateway

go to post

Robert Cemper · May 15, 2020

*.1.int shows us that this is generated from a class
%Stream.Object.... is a class from SYSLIB.
System Classes are typically only available in compiled format (.obj) You can't debug them.
Either skip them by <F10> or leave them by <SHIFT F11>

go to post

Robert Cemper · May 11, 2020

You might have a UNIQUE key on some of the other columns

I think your data is crap. (in the screenshot)
The ID you insert is totally different by structure than what you display from, table !!!

This one looks like a 3 level subscript (Server | CustID | ActivityCode)
I'd suggest not to try to insert ID at all. it is obviously generated

go to post

Robert Cemper · May 11, 2020

I'd suggest to UNSWIZZLE ( ~~ remove from memory) all children once you a done with an object.

it's described here:

https://docs.intersystems.com/iris20192/csp/docbook/DocBook.UI.Page.cls?KEY=GOBJ_relationships

go to post

Robert Cemper · May 11, 2020

[SQLCODE: <-131>:<After Insert trigger failed>]

[%msg: <21003,Cannot INSERT "NIActivityCode" because number is already used.>]

this simply means that you try to insert something that is unique and already present.
so check against your table definition for uniqueness and next the data you want to insert.

Start with ID first.

It is definitely a data issue and not related to syntax

go to post

Robert Cemper · May 11, 2020

INSERT INTO ccms.NIActivityCode (ID, CustID, ActivityCode, Name, ShortName, CreateDate, Type, Server)
SELECT '1||1||102', '1', '102', 'Test1', 'test1', '2020-01-01 10:37:00', '4', '1'
UNION ALL
SELECT '1||1||103', '1', '103', 'Test2', 'test2', '2020-01-01 10:37:00', '4', '1'
UNION ALL
SELECT '1||1||104', '1', '104', 'Test3', 'test3', '2020-01-01 10:37:00', '4', '1'
~~UNION ALL~~

UNION ALL adds to the previous Select.

ALL means all columns

go to post

Robert Cemper · May 11, 2020

This is just a wrong format.
the example you pointed to simulates an INSERT..... SELECT... construct
with no VALUES keyword !!

see docs: Insert Query Results: INSERT with SELECT

the SELECT combines single values with a UNION ALL clause.
All standard SQL.

go to post

Robert Cemper · May 10, 2020

An example as you see in namespace SAMPLES SOAP.Demo.cls .
First, of anything else, it is an XML document.

<?xml version="1.0" encoding="UTF-8"?>
<definitions xmlns="http://schemas.xmlsoap.org/wsdl/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:mime="http://schemas.xmlsoap.org/wsdl/mime/" xmlns:s="http://www.w3.org/2001/XMLSchema" xmlns:s0="http://tempuri.org" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" targetNamespace="http://tempuri.org">
    <types>
        <s:schema elementFormDefault="qualified" targetNamespace="http://tempuri.org">
            <s:import namespace="http://tempuri.org/ByNameDataSet" schemaLocation="http://localhost:57772/csp/samples/SOAP.ByNameDataSet.cls?XSD"/>
            <s:import namespace="http://tempuri.org/QueryByName_DataSet" schemaLocation="http://localhost:57772/csp/samples/SOAP.Demo.QueryByName.DS.cls?XSD"/>
            <s:element name="AddInteger">
                <s:complexType>
                    <s:sequence>
                        <s:element minOccurs="0" name="Arg1" type="s:long"/>
                        <s:element minOccurs="0" name="Arg2" type="s:long"/>
                    </s:sequence>
                </s:complexType>
            </s:element>
            <s:element name="AddIntegerResponse">
                <s:complexType>
                    <s:sequence>
                        <s:element name="AddIntegerResult" type="s:long"/>
                    </s:sequence>
                </s:complexType>
            </s:element>
            <s:element name="DivideInteger">
                <s:complexType>
                    <s:sequence>
                        <s:element minOccurs="0" name="Arg1" type="s:long"/>
                        <s:element minOccurs="0" name="Arg2" type="s:long"/>
                    </s:sequence>
                </s:complexType>
            </s:element>
            <s:element name="DivideIntegerResponse">
                <s:complexType>
                    <s:sequence>
                        <s:element name="DivideIntegerResult" type="s:long"/>
                    </s:sequence>
                </s:complexType>
            </s:element>
            <s:element name="FindPerson">
                <s:complexType>
                    <s:sequence>
                        <s:element minOccurs="0" name="id" type="s:string"/>
                    </s:sequence>
                </s:complexType>
            </s:element>
            <s:element name="FindPersonResponse">
                <s:complexType>
                    <s:sequence>
                        <s:element name="FindPersonResult" type="s0:Person"/>
                    </s:sequence>
                </s:complexType>
            </s:element>
            <s:complexType name="Employee">
                <s:complexContent>
                    <s:extension base="s0:Person">
                        <s:sequence>
                            <s:element minOccurs="0" name="Title">
                                <s:simpleType>
                                    <s:restriction base="s:string">
                                        <s:maxLength value="50"/>
                                    </s:restriction>
                                </s:simpleType>
                            </s:element>
                            <s:element minOccurs="0" name="Salary">
                                <s:simpleType>
                                    <s:restriction base="s:long">
                                        <s:maxInclusive value="100000"/>
                                        <s:minInclusive value="0"/>
                                    </s:restriction>
                                </s:simpleType>
                            </s:element>
                            <s:element minOccurs="0" name="Notes" type="s:string"/>
                            <s:element minOccurs="0" name="Picture" type="s:base64Binary"/>
                        </s:sequence>
                    </s:extension>
                </s:complexContent>
            </s:complexType>
            <s:complexType name="Person">
                <s:sequence>
                    <s:element name="Name" type="s:string"/>
                    <s:element name="SSN" type="s:string"/>
                    <s:element minOccurs="0" name="DOB" type="s:date"/>
                    <s:element minOccurs="0" name="Home" type="s0:Address"/>
                    <s:element minOccurs="0" name="Office" type="s0:Address"/>
                    <s:element minOccurs="0" name="Spouse" type="s0:Person"/>
                    <s:element minOccurs="0" name="FavoriteColors" type="s0:ArrayOfFavoriteColorsItemString"/>
                    <s:element minOccurs="0" name="Age" type="s:long"/>
                </s:sequence>
            </s:complexType>
            <s:complexType name="Address">
                <s:sequence>
                    <s:element minOccurs="0" name="Street">
                        <s:simpleType>
                            <s:restriction base="s:string">
                                <s:maxLength value="80"/>
                            </s:restriction>
                        </s:simpleType>
                    </s:element>
                    <s:element minOccurs="0" name="City">
                        <s:simpleType>
                            <s:restriction base="s:string">
                                <s:maxLength value="80"/>
                            </s:restriction>
                        </s:simpleType>
                    </s:element>
                    <s:element minOccurs="0" name="State">
                        <s:simpleType>
                            <s:restriction base="s:string">
                                <s:maxLength value="2"/>
                            </s:restriction>
                        </s:simpleType>
                    </s:element>
                    <s:element minOccurs="0" name="Zip">
                        <s:simpleType>
                            <s:restriction base="s:string">
                                <s:maxLength value="5"/>
                            </s:restriction>
                        </s:simpleType>
                    </s:element>
                </s:sequence>
            </s:complexType>
            <s:complexType name="ArrayOfFavoriteColorsItemString">
                <s:sequence>
                    <s:element maxOccurs="unbounded" minOccurs="0" name="FavoriteColorsItem" nillable="true" type="s:string"/>
                </s:sequence>
            </s:complexType>
            <s:element name="GetByName">
                <s:complexType>
                    <s:sequence>
                        <s:element minOccurs="0" name="name" type="s:string"/>
                    </s:sequence>
                </s:complexType>
            </s:element>
            <s:element name="GetByNameResponse">
                <s:complexType>
                    <s:sequence>
                        <s:element name="GetByNameResult" type="s0:DataSet"/>
                    </s:sequence>
                </s:complexType>
            </s:element>
            <s:complexType name="DataSet">
                <s:sequence>
                    <s:element ref="s:schema"/>
                    <s:any/>
                </s:sequence>
            </s:complexType>
            <s:element name="GetDataSetByName">
                <s:complexType>
                    <s:sequence>
                        <s:element minOccurs="0" name="name" type="s:string"/>
                    </s:sequence>
                </s:complexType>
            </s:element>
            <s:element name="GetDataSetByNameResponse">
                <s:complexType>
                    <s:sequence>
                        <s:element name="GetDataSetByNameResult" type="s0:ByNameDataSet"/>
                    </s:sequence>
                </s:complexType>
            </s:element>
            <s:complexType name="ByNameDataSet">
                <s:sequence>
                    <s:any namespace="http://tempuri.org/ByNameDataSet"/>
                </s:sequence>
            </s:complexType>
            <s:element name="GetListByName">
                <s:complexType>
                    <s:sequence>
                        <s:element minOccurs="0" name="name" type="s:string"/>
                    </s:sequence>
                </s:complexType>
            </s:element>
            <s:element name="GetListByNameResponse">
                <s:complexType>
                    <s:sequence>
                        <s:element name="GetListByNameResult" type="s0:ArrayOfPersonIdentification"/>
                    </s:sequence>
                </s:complexType>
            </s:element>
            <s:complexType name="ArrayOfPersonIdentification">
                <s:sequence>
                    <s:element maxOccurs="unbounded" minOccurs="0" name="PersonIdentification" nillable="true" type="s0:PersonIdentification"/>
                </s:sequence>
            </s:complexType>
            <s:complexType name="PersonIdentification">
                <s:sequence>
                    <s:element minOccurs="0" name="ID" type="s:string"/>
                    <s:element name="Name" type="s:string"/>
                    <s:element name="SSN" type="s:string"/>
                    <s:element minOccurs="0" name="DOB" type="s:date"/>
                </s:sequence>
            </s:complexType>
            <s:element name="LookupCity">
                <s:complexType>
                    <s:sequence>
                        <s:element minOccurs="0" name="zip" type="s:string"/>
                    </s:sequence>
                </s:complexType>
            </s:element>
            <s:element name="LookupCityResponse">
                <s:complexType>
                    <s:sequence>
                        <s:element name="LookupCityResult" type="s0:Address"/>
                    </s:sequence>
                </s:complexType>
            </s:element>
            <s:element name="Mission">
                <s:complexType>
                    <s:sequence/>
                </s:complexType>
            </s:element>
            <s:element name="MissionResponse">
                <s:complexType>
                    <s:sequence>
                        <s:element name="MissionResult" type="s:string"/>
                    </s:sequence>
                </s:complexType>
            </s:element>
            <s:element name="QueryByName">
                <s:complexType>
                    <s:sequence>
                        <s:element minOccurs="0" name="name" type="s:string"/>
                    </s:sequence>
                </s:complexType>
            </s:element>
            <s:element name="QueryByNameResponse">
                <s:complexType>
                    <s:sequence>
                        <s:element name="QueryByNameResult" type="s0:QueryByName_DataSet"/>
                    </s:sequence>
                </s:complexType>
            </s:element>
            <s:complexType name="QueryByName_DataSet">
                <s:sequence>
                    <s:any namespace="http://tempuri.org/QueryByName_DataSet"/>
                </s:sequence>
            </s:complexType>
        </s:schema>
    </types>
    <message name="AddIntegerSoapIn">
        <part name="parameters" element="s0:AddInteger"/>
    </message>
    <message name="AddIntegerSoapOut">
        <part name="parameters" element="s0:AddIntegerResponse"/>
    </message>
    <message name="DivideIntegerSoapIn">
        <part name="parameters" element="s0:DivideInteger"/>
    </message>
    <message name="DivideIntegerSoapOut">
        <part name="parameters" element="s0:DivideIntegerResponse"/>
    </message>
    <message name="FindPersonSoapIn">
        <part name="parameters" element="s0:FindPerson"/>
    </message>
    <message name="FindPersonSoapOut">
        <part name="parameters" element="s0:FindPersonResponse"/>
    </message>
    <message name="GetByNameSoapIn">
        <part name="parameters" element="s0:GetByName"/>
    </message>
    <message name="GetByNameSoapOut">
        <part name="parameters" element="s0:GetByNameResponse"/>
    </message>
    <message name="GetDataSetByNameSoapIn">
        <part name="parameters" element="s0:GetDataSetByName"/>
    </message>
    <message name="GetDataSetByNameSoapOut">
        <part name="parameters" element="s0:GetDataSetByNameResponse"/>
    </message>
    <message name="GetListByNameSoapIn">
        <part name="parameters" element="s0:GetListByName"/>
    </message>
    <message name="GetListByNameSoapOut">
        <part name="parameters" element="s0:GetListByNameResponse"/>
    </message>
    <message name="LookupCitySoapIn">
        <part name="parameters" element="s0:LookupCity"/>
    </message>
    <message name="LookupCitySoapOut">
        <part name="parameters" element="s0:LookupCityResponse"/>
    </message>
    <message name="MissionSoapIn">
        <part name="parameters" element="s0:Mission"/>
    </message>
    <message name="MissionSoapOut">
        <part name="parameters" element="s0:MissionResponse"/>
    </message>
    <message name="QueryByNameSoapIn">
        <part name="parameters" element="s0:QueryByName"/>
    </message>
    <message name="QueryByNameSoapOut">
        <part name="parameters" element="s0:QueryByNameResponse"/>
    </message>
    <portType name="SOAPDemoSoap">
        <operation name="AddInteger">
            <input message="s0:AddIntegerSoapIn"/>
            <output message="s0:AddIntegerSoapOut"/>
        </operation>
        <operation name="DivideInteger">
            <input message="s0:DivideIntegerSoapIn"/>
            <output message="s0:DivideIntegerSoapOut"/>
        </operation>
        <operation name="FindPerson">
            <input message="s0:FindPersonSoapIn"/>
            <output message="s0:FindPersonSoapOut"/>
        </operation>
        <operation name="GetByName">
            <input message="s0:GetByNameSoapIn"/>
            <output message="s0:GetByNameSoapOut"/>
        </operation>
        <operation name="GetDataSetByName">
            <input message="s0:GetDataSetByNameSoapIn"/>
            <output message="s0:GetDataSetByNameSoapOut"/>
        </operation>
        <operation name="GetListByName">
            <input message="s0:GetListByNameSoapIn"/>
            <output message="s0:GetListByNameSoapOut"/>
        </operation>
        <operation name="LookupCity">
            <input message="s0:LookupCitySoapIn"/>
            <output message="s0:LookupCitySoapOut"/>
        </operation>
        <operation name="Mission">
            <input message="s0:MissionSoapIn"/>
            <output message="s0:MissionSoapOut"/>
        </operation>
        <operation name="QueryByName">
            <input message="s0:QueryByNameSoapIn"/>
            <output message="s0:QueryByNameSoapOut"/>
        </operation>
    </portType>
    <binding name="SOAPDemoSoap" type="s0:SOAPDemoSoap">
        <soap:binding transport="http://schemas.xmlsoap.org/soap/http" style="document"/>
        <operation name="AddInteger">
            <soap:operation soapAction="http://tempuri.org/SOAP.Demo.AddInteger" style="document"/>
            <input>
                <soap:body use="literal"/>
            </input>
            <output>
                <soap:body use="literal"/>
            </output>
        </operation>
        <operation name="DivideInteger">
            <soap:operation soapAction="http://tempuri.org/SOAP.Demo.DivideInteger" style="document"/>
            <input>
                <soap:body use="literal"/>
            </input>
            <output>
                <soap:body use="literal"/>
            </output>
        </operation>
        <operation name="FindPerson">
            <soap:operation soapAction="http://tempuri.org/SOAP.Demo.FindPerson" style="document"/>
            <input>
                <soap:body use="literal"/>
            </input>
            <output>
                <soap:body use="literal"/>
            </output>
        </operation>
        <operation name="GetByName">
            <soap:operation soapAction="http://tempuri.org/SOAP.Demo.GetByName" style="document"/>
            <input>
                <soap:body use="literal"/>
            </input>
            <output>
                <soap:body use="literal"/>
            </output>
        </operation>
        <operation name="GetDataSetByName">
            <soap:operation soapAction="http://tempuri.org/SOAP.Demo.GetDataSetByName" style="document"/>
            <input>
                <soap:body use="literal"/>
            </input>
            <output>
                <soap:body use="literal"/>
            </output>
        </operation>
        <operation name="GetListByName">
            <soap:operation soapAction="http://tempuri.org/SOAP.Demo.GetListByName" style="document"/>
            <input>
                <soap:body use="literal"/>
            </input>
            <output>
                <soap:body use="literal"/>
            </output>
        </operation>
        <operation name="LookupCity">
            <soap:operation soapAction="http://tempuri.org/SOAP.Demo.LookupCity" style="document"/>
            <input>
                <soap:body use="literal"/>
            </input>
            <output>
                <soap:body use="literal"/>
            </output>
        </operation>
        <operation name="Mission">
            <soap:operation soapAction="http://tempuri.org/SOAP.Demo.Mission" style="document"/>
            <input>
                <soap:body use="literal"/>
            </input>
            <output>
                <soap:body use="literal"/>
            </output>
        </operation>
        <operation name="QueryByName">
            <soap:operation soapAction="http://tempuri.org/SOAP.Demo.QueryByName" style="document"/>
            <input>
                <soap:body use="literal"/>
            </input>
            <output>
                <soap:body use="literal"/>
            </output>
        </operation>
    </binding>
    <service name="SOAPDemo">
        <port name="SOAPDemoSoap" binding="s0:SOAPDemoSoap">
            <soap:address location="http://localhost:57772/csp/samples/SOAP.Demo.cls"/>
        </port>
    </service>
</definitions>

go to post

Robert Cemper · May 10, 2020

This is not a WSDL. it isn't even clean XML

go to post

Robert Cemper · May 9, 2020

It looks like a broken WSDL you get.
Download it from your SOAP source into a local file and validate it against an XML checker.
it could be your own or any public available service.
Google is your friend to find it.

go to post

Robert Cemper · May 8, 2020

Just to complete this issue.
My final solution is a VM with Ubuntu 18.04 + Docker (as suggested by @Rich Taylor ) .
After some troubles, I moved to a Docker VOLUME as advised by @J. Hemdal .

Docker Volumes are typically buried in a multilevel hard to remember directory tree.
An easy to remember symlink solved that.
And it works fine in all variations. Full durability and lightweight.
Thanks for your contributions.

go to post

Robert Cemper · May 7, 2020

Hi Tani, ( @Netanel Frankel )

I've seen Salva's article and tried it. But I always ended with some graphic version.
Downloading an Ubuntu Server kit looked good in the beginning.
But when it asked me to initialize all 127GB of my only Harddrive I got scared and stopped it.

go to post

Robert Cemper · May 7, 2020

Thank you @Rich Taylor !
I was sure not to be the first. My story was more a warning not to spend to much time on it.
My workaround was an after-midnight-hack. Nobody around and I want this beast to go.
You know: "Once engineer, ever engineer! "
But I'm already working on suggestion #2. I have just to clean the virtual disk from old junk.

Best wishes, stay save!

go to post

Robert Cemper · May 5, 2020

you didn't tell us the Namespace. Assuming USER.
you have to take care that the Database for USER is part of your durable environment.
after restart of your container any thing not in the durable environment (e.g IRISTEMP) is gone

go to post

Robert Cemper · May 4, 2020

First, check if the Caché SERVICE (in win sense) gets started as account With enough privileges

Next, check that the installation directory and all sub dir are accessible with all right from this account

Eventually, consider a reinstall / Update with enough ADMINISTRATOR rights