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 
) but note that in newer versions (IRIS, for instance), you can make use of the %JSON.Adaptor to do something like:
{
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.
Hey, I don't work in InterSystems anymore - NOT MY PROBLEM
While I agree with what a lot of people said here - you probably should use the the expanded syntax - it doesn't exactly address the fact that it is much quicker (or FEELS much quicker) to type the abbreviations. Especially when I'm in the coding groove and trying to get stuff done. The fact is your IDE can can expand all the commands. My take is this: let your developers do whatever makes their job easier. It's easier to write shorthand, but read longhand - make a tool that converts shorthand to longhand and put that into your source control.
Oh no! I did miss it!!! Sorry John!
Maybe I should edit the title to say 16 years!!!
That's why I like to post stuff like this. 60 seconds of work that can save you hours of frustration are tricks worth knowing about.
In the time it took me to write this @Alexander Koblov also mentioned that this is a "JOIN with no condition" - he's absolutely right.
@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.
Compile your routine/class with the 'k' flag (eq. /keepsource). Otherwise the ObjectScript compiler throws away the .int code.
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)?
Your IDKEY is your ID field, so it is a unique identifier that also becomes the subscript of the global you use to store your data. A Primary Key is any unique field (or combination of fields). Having it be primary key is the same as it being unique, as far as I know.
Changing your Identity property to have MINVAL=1 should make your class bitmap eligible. If that doesn't work, please contact the WRC.
The easiest solution here is to link the table from another namespace. Only takes a second and you don't need to screw around getting the storage right (note: do NOT start editing storage defs if you can avoid it - that is not an easily maintained solution). This solution does not work if you have to JOIN the two tables together (we call this a heterogeneous JOIN), however. The next best solution is to write your own class query as @Stephen Canzano & @Michael Smart have recommended. It's a little work up front but you can make some easy changes, such as creating a temp table and populating it with data from the other namespace or using a process private global.
Great article! I especially liked the part where you mentioned me! :-D
For what situation(s) should a user go to this page? For login? After login? When they've been timed out?
I think what you want to do is have a custom login page that automatically redirects you to a default landing page. But that's not configurable, you need to write that logic into your login page. But you can put your custom login page into the Web Application menu.
Please open a case with the WRC ( wrc.intersystems.com or email: support@intersystems.com ) who can help get this fixed in the product if it is still not working.
I do know a way to add permissions on a table that doesn't exist yet - so I'm adding on to Pravin's answer.
GRANT SELECT ON SCHEMA SQLUser TO MyRole
Then any table added to the SQLUser schema will be accessible to the role "MyRole" when that table is added. Since Schemas (or schemata) are how we split up tables into more generic categories, this is a nice way to also spit up your permissions.
So I spent 10 years working in Caché product support and this has popped up before. But I've only ever seen it on development systems when folks are creating, deleting, and updating classes at a much higher rate than would be acceptable on a production machine. And I'm relatively sure it was all new development. I think it ends up being an artifact of new development and the first creation of your class structures, though I've never been able to find what caused it in the past.
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)
Joe Gazillo! Good to hear from you!
I think that code should work back to 2016.2. Basically after the change to our JSON objects. To work on 2016.1 you'd have to change %Push() to $push(). Earlier versions, you'd have to something with the %ZEN.Auxiliary.jsonProvider to get that to work.
Can you not edit answers that are "accepted"? Because my code is wrong and needs updating.
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
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?
Sorry, perhaps I need to give a little more background than exactly none :-)
The way Atelier connects to Caché (eq. Ensemble, HealthShare, IRIS) is via a webserver. Atelier connects to the webserver and the webserver connects to Caché. What you need to do is set up the webserver to allow HTTPS connections. To do this, you could use the Private Apache that we ship with Caché (eq. ...), or you could install a full version of Apache or IIS to do this. Alternatively, you could set up a webserver local to your machine.
Honestly, you might be better off opening a WRC case (wrc.intersystems.com or support@intersystems.com) and one of us would be happy to help you in all the gory details. There are lots of questions to be asked here such as what attack vectors are you protecting against and where your server, webserver, and Atelier are all installed..
Did you enable HTTPS on your webserver? If so, that typically means a new port (default: 443), which you would need to add to your connection. If not, do you know how to enable HTTPS on your webserver?
The answer to your question is 'almost never'. If you run TuneTable on your class/table then you will be required to do a purge so the optimizer can pick up the changes. Aside from that, the Caché engine should handle all purges automatically. If you change a class and compile it, that compilation should trigger a purge of the appropriate cached queries. If you are finding that it does not, then please contact the WRC so we can get this fixed.
If ever you are looking for generalized information, you can always look at learning.intersystems.com as well as the documentation (docs.intersystems.com). There's a lot of good videos there that you can use to help get going. If you have any specific questions there are plenty of us who would be happy to answer them.
Hey Laura!
OK so if Frozen Query Plans was your problem (and it's a good hypothesis), then unfreezing them all gets you back to the old behavior (you'll need to recompile and purge cached queries). There were some problems with the 'old' dynamic SQL classes, but I think those were solved, so you shouldn't have any problems going forward. So let's talk about this a bit.
What are Frozen Query Plans?
If you've ever upgraded Caché and had a critical query degrade in performance, you'll agree this technology is pretty cool. What it does, is to lock down your query execution plan during upgrades (or it can be user [you're the user] initiated).
Benefits: No bad surprises during upgrades
Drawback: No benefits from core improvements to SQL engine
Is it a problem to Unfreeze all plans?
No! Of course not. You will absorb some risk, but if you usually don't have problems then you will continue to not usually have problems. If you unfreeze all plans you'll need to compile your application and/or purge cached queries. To do this you can execute the following in your application namespace:
w $SYSTEM.SQL.FreezePlans(0,1,,.err)
d $SYSETM.SQL.Purge()
You can obviously look this up in the docs for more information. If you have embedded SQL queries those would need to be recompiled.
Dynamic SQL Implementation
We (InterSystems) recommend using %SQL.Statement in lieu of %ResultSet for dynamic SQL. When you ask if you should change, the answer is 'yes'. However, this only fixes your problem because when you use %SQL.Statement it ends up being a slightly different query according to our Frozen Query Plan implementation. It fixes your problem, but not the root of the problem. %SQL.Statement is also fine to use for 'older' code. Of course, if you don't want to change your code ("if it ain't broke"), that's understandable.
SELECT * queries
Running a SELECT * query is a bit of an edge case. It is frequently better to list out your fields so your are only showing your users the data they are interested in. Indeed, you can only run into this problem if it is a SELECT * query. In general, list out your fields.
So that's that. If you have any further questions, feel free to let me know :-)