Hey Juan,

So at a guess - Intersystems is doing the thing where they just take whatever error you return, and tack on their own error on top of it.  In some cases they return all errors, and in others they just pick off the first one of them and return that (GetOneError or something similar).  This is a bug (in my opinion) - you should open a WRC ticket and have them fix it.  They might also be able to give you a workaround.


Good luck!

WHO DARES SUMMON ME!

Oh, hi Evgeny!  Great to see you, hope you're well!

For everyone else, Evgeny is right, of course (because he's quoting me laugh) but note that in newer versions (IRIS, for instance), you can make use of the %JSON.Adaptor to do something like:

Class Test.DoesItJSON Extends (%RegisteredObject, %JSON.Adaptor)
{
Property aString As %String;
Property anInteger As %Integer;
}

The do something like:

s obj=##class(Test.DoesItJSON).%New()
s obj.aString="Hello"
s obj.anInteger=123

d obj.%JSONExportToString(.jsonstring)
zw jsonstring
    jsonstring="{""aString"":""Hello"",""anInteger"":123}"

This is better for creating new classes (again in later versions than you're on, you need to use the jsonProvider or the altJsonProvider) that you think you're going to want to serialize into JSON.

@Vitaliy.Serdtsev  - you were right to summon me here!

SQL optimization is a HUGE topic (as has been mentioned) but InterSystems products are VERY good at JOIN-ing large tables.  Here's the issue, you're NOT doing a JOIN, you're doing a Cartesian Product.  Your query:

select o.col1, o.col2, op.partnum, op.amount
from orders o join orderpositions op
where o.orderDate > $H-1000

Is saying to JOIN EVERY order with EVERY order position over the last 3 years (give or take).  As you are not restricting how you're JOIN-ing the two tables together, there's not much we can do to optimize this.  That said, when bringing up these issues (and you should! either we help you write better code or we fix problems in our code!) you should provide your table definition and a query plan.  I think if you look at the query plan (which you can get from the management portal) you'll see that the problem is you're missing your JOIN condition (something like ON op.Order = o.ID, if I were to take a guess).  If that's not the case and this is the query you want to be running, please send over your class definitions by doing:

d $SYSTEM.OBJ.Export("User.orders.cls,User.orderpositions.cls","C:\Temp\KyleWantsThis.xml")

and I'll be happy to look at this further.  Of course, you are also very welcome to open up an issue with InterSystems Support - best support in the industry, and I'll bet a kidney on that statement.


 

To my knowledge you cannot do this on the client side, however it is easy enough to write a stored procedure to take in whatever input you want (say, a JSON object, or a list-of-lists, or some other data type) and use that to parse-and-INSERT on the server side. 

I'm a bit confused here - granting the user SELECT privileges on those tables didn't allow your customer's ODBC client to work?  It sounds like %ALL is working as designed (if you have %ALL you own the system and can therefore see everything).  What does the client tool look like when you grant just SELECT access to certain tables or schemas?  Can you try a different ODBC client to see how that behaves (I like WinSQL)? 

OK you seem to have 3 questions here:
 

1) IRIS or Caché: If you're working with new the answer is IRIS.  IRIS is our "latest and best" and you'll be best off going there.

2) Which IDE: Doesn't matter.  Atelier is an Eclipse based IDE, which is nice if you're used to Eclipse.  I haven't touched VSCode so I can't speak for that.  Studio is our home-grown IDE.  Choose one, I'm not sure it matters at all, as long as you're comfortable using it.
 

3) How to deploy: In IRIS we have containers for deployment, so if you want to go that direction, great.  However, you might be more comfortable working on your own machine, which you can also do and bring in containerization later (if it makes sense).  Remember, using containers is a tool, and it's not ALWAYS the right tool for the job.

Good luck!  And if you have questions about building your application be sure to let us know!

This typically happens when you have some corruption of the Extent information for your namespace.  You'll want to delete the nodes for digi.packet in ^oddEXT and ^rINDEXEXT (in the "G" and "U" nodes, I believe).  You can try running ##class(%ExtentMgr.Util).DeleteExtentDefinition("digi.packet") but I'm not sure if that will get it all the way done.


The easiest thing to do might be to export all your classes and import them into a fresh namespace (as you noted this works in many other places).  If you want more step-by-step guidance or any root cause analysis, I recommend contacting InterSystems Support (support@intersystems.com)

Class Def

--------------
Class User.JSONify Extends %Persistent

{

Property JSONData as list of %String;

 

ClassMethod MakeJSONArray(list) as %DynamicArray [sqlname="MakeJSONArray", sqlproc]

{

                q:(('$listvalid(list))|| (list="")) ""

                set jsonarray=[]

                for i=1:1:$LL(list)

                {

                                d jsonarray.%Push($ListGet(list,i))

                }

                q jsonarray

}


}

--------------

Data Population
---------------

USER>s obj=##class(User.JSONify).%New()

 

USER>s json="{""Name"":""Kyle""}"

 

USER>w obj.JSONData.Insert(json)

1

USER>s json="{""Name"":""Evgeny""}"

 

USER>w obj.JSONData.Insert(json)   

1

USER>w obj.%Save()

1


-----------------
Query

-----------------

3.            select JSONData,MakeJSONArray(JSONData) from JSONify

 

JSONData             Expression_2

$lb("{""Name"":""Kyle""}","{""Name"":""Evgeny""}")           17@%Library.DynamicArray

 

1 Rows(s) Affected

statement prepare time(s)/globals/lines/disk: 0.0507s/37476/174342/0ms

          execute time(s)/globals/lines/disk: 0.0003s/2/598/0ms


-----------------

Extra Proof
-----------------
USER>s sql="select JSONData,MakeJSONArray(JSONData) from JSONify"

 

USER>s rs=##class(%SQL.Statement).%ExecDirect(,sql)

 

USER>w rs.%Next()

1

USER>s json=rs.%GetData(2)

 

USER>w json

16@%Library.DynamicArray

USER>w json.%Get(0)

{"Name":"Kyle"}

USER>w json.%Get(1)

{"Name":"Evgeny"}


Not 100% on this design, but getting it to work isn’t bad.  You’ll need extra error checking crap in any REAL code, obviously (for instance, I do ZERO checking to see if what is in the list is valid JSON).

Cheers,

Kyle

Edit: Adding parens around list="" as is proper when writing ObjectScript code.  Due to ObjectScript's left-to-right evaluation, it is important to always wrap your conditions in parens or risk getting some unexpected results!!!

AVG exists in 2015.1.  If you want the median you might have to calculate that yourself within a function.  Do you need an SQL-only solution?