[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
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
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.
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>This is not a WSDL. it isn't even clean XML
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.
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
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
Sorry I saw your FOR loop only when the reply was out already
So you are not hurt by First-Touch effect anyhow.
As it is always the same object even buffer size should have no influence.
is there any other load on the server that could have influence ?
A rule of thumb:
Global buffer size ~50% of physical memory (set it manually !)
- enable / enforce large pages
- run it in a loop.
- the first OPEN has to fetch the whole global structure from disk, all following load from Global buffers
an Excellent Reference. you got the point.
for speed: If don't want to run a benchmark indirection isn't bad
as in this case: functionality counts
I fully agree with your concerns. Especially related to parallel processing and sharding.
And modern code and design will never need this.
But there are millions of lines of old code out in the field that require these dirty tricks to survive.
And YES! You have to examine very carefully what you do.
It's a little bit like mountaineering:
Most take the cable car, some climb with ropes and a lot of fancy equipment. While a few free climbers use nothing than their body.
You have to understand the risks and to take your decision.
BINGO!
You got my point. Of course, you should be careful with the storage not to mix it up. Therefore the class method to keep all related globals in sync.
CLASSPARAMETERS are static and apply to class compile time.
You refer to it by ..#paramname in your class code
https://cedocs.intersystems.com/latest/csp/docbook/Doc.View.cls?KEY=RCOS_fparameter
see section References to Other Class Members
For Multiline SQL you may do it his way:
no solution for coloring inside a String
on "html to pdf converter freeware" google gave me 3 270 000 hits.
Why reinventing the wheel.
Place your HTML into a local file and let someone else do the dirty job.
There are enough solutions for a call-out to trigger the conversion.
Sorry, it didn't work with postgreSQL.
The error changed to a gateway error.
You can map stored procedures over the Gateway but not SQL functions:
I assume the same is happening also with Oracle.
Though it seems nonsense to me you can fake SQLUSER.ROW_NUMBER
for that purpose like this:
In your namespace create this class to simulate ROW_NUMBER()
#1) I'd suggest to increase the size of your LockTab as suggested by @Vic Sun
#2) There is a class in %SYS . ^SYS.Lock that could be useful for multiple Lock deletes.
and as I just learned to adjust LockTab size (SetMaxLockTableSize)
https://cedocs.intersystems.com/latest/csp/documatic/%25CSP.Documatic.cls?PAGE=CLASS&LIBRARY=%25SYS&CLASSNAME=SYS.Lock
#3) ^LOCKTAB from terminal command line in %SYS also shows the Locktab
check Locktable in the management portal to find if some other process has locked the table.
by do ##class(my.class).%UnlockExtent(0,1) you just can release yourr own LOCKs
I'm not an expert on Oracle and have no chance to test. Just an idea
Instead of ROWNUM the function ROW_NUMBER() should pass the syntax check as it is not a column identifier.
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm
So a possible workaround could be to have a VIEW on Oracle including ROWNUM as a column
like SELECT ROWNUM as row, * from whatever_table
and then map the View instead of the original table just for this purpose.
Just as a side note.
mixing Caché tables with external tables (e.g. in a JOIN) is not possible.
%VID is a Caché internal workaround for subqueries to hide the fact that ROWNUM or ROW_NUMBER wasn't implemented
https://cedocs.intersystems.com/latest/csp/docbook/Doc.View.cls?KEY=RSQL_C189621
You can't mix it with external database access with internal features
instead, use the RowNumber implementation of your external DB (they vary by product)
https://www.w3schools.com/sql/sql_top.asp
https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver15
You just can win. The workload moves to the external DB and you transfer fewer records
So I'd suggest involving WRC to check the sources where the double translation comes from.
(probably since ever)
just an idea to understand:
what do you see if your .stream is a %Stream.GlobalBinary ?
see this example to reproduce and explain that there is an unnecessary conversion on the way
.png)
as you showed in your example
"text": "Condição de pagamento sujeito a análise de crédito: "
@Rubens Silva
That sounds to me like double encoding.
I'd suggest using a HEX Editor (e.g. PSpad) to examine your files.
UTF-8 means that some characters have more than 8 bit.
By converting an already converted string you may get those strange effects.
And you found the way to avoid this already yourself.
I just tested a variable parameter list in SQL. It works: (simplified example)
with SQL:
so you can generate your JSON straght as ClassMethd
With 2013.* it is not built-in, but you can mimic it:
every Classmethod can be projected as SQLprocedure.
I'm not sure if you can pass a variable list of arguments from SQL.
But this is the basic mechanic behind it