There is apparently an internal-design-level distinction between UDL as an editor format and XML as an export format. We appear to have closed the case without clarifying the documentation on this point (apologies for this oversight), but I've reopened the case so we might review and reconsider the current behaviour more broadly. There's a number of additional flags like these, so it's possibly a larger change.
@Vitaliy Serdtsev is right in pointing to the resultset's metadata. That's where IRIS SQL registers column datatypes and he already pointed out that this is obviously not impacted by the presence of an ORDER BY.
The resultset object returns the SQL-side value to ObjectScript, but on that ObjectScript side, the datatype no longer matters as it isn't strongly typed and still 1="1". Therefore, I don't think this constitutes an error.
FWIW, the reason you're seeing this is that due to the ORDER BY clause we're picking up that id value from the index' subscript rather than from the master map.
Try the zremove (or zr in short) command without arguments.
Unfortunately we're not able to offer transactions across linked tables. For one thing, the remote database may use different transaction semantics. I will request this gets clarified in our documentation, as it's not called out right now as a limitation of linked tables.
Hi Jun, those sandboxes are controlled environments set up for specific courses. There isn't really a course corresponding to this demo / subject, so unfortunately no single-click-install this time.
Once you have a running instance of IRIS, setting it up through the commands described above shouldn't be that big a deal:
do $system.OBJ.ImportDir("/path/to/downloaded/isc-iknow-setanalysis","*.xml","c",,1)
do ##class(Demo.SetAnalysis.Utils).CreateRestWebApp()Thanks @Vitaliy Serdtsev
Indeed, that method will be deprecated shortly, in favour of CREATE TABLE AS SELECT, which is new in 2021.1 and part of the SQL standard. Both this command and the $SYSTEM utility method will create a physical copy of the data, so a real SQL table that's not kept in sync in the way a SQL view is. If the latter is what you need, don't bother with creating it as a table.
Note that in a more recent version we also support the CREATE OR REPLACE syntax for a bunch of additional statements (including CREATE FUNCTION, CREATE PROCEDURE, ...) and CREATE IF NOT EXISTS for things that actually contain data such as tables.
I'm not sure I understand which sandbox you're referring to?
yeah, while I appreciate @Renato Banzai 's creativity in the above article, I can't resist to make a plug for our %Stream classes that can take care of very large strings. Also, starting 2021.2, Stream data is compressed by default, which can easily save you 80% on storage for content such as XML. I have a couple of utilities here that complement this feature by allowing you to compress old stream data in-place.
Cool stuff! For this age-old iKnow demo I used import.io to scrape a bunch of hotel reviews from the web, but it was pretty crude and server-based, so impossible to include in the demo repo. I've heard of beautifulsoup before, but never considered revisiting that data sourcing piece of my demo now that we can use it directly through Embedded Python.
I have an example of a generic logging filter that takes this approach here: https://github.com/bdeboe/isc-sql-utils#generic-table-logging
I'm not sure what you mean with "linear cost pagination", but as soon as your query plan gets more complex than reading a single stream (and especially when sorting) there is upfront work before you can start reading the first row so any pagination of that final result simply cannot be linear.
That small disclaimer aside, "customizable filtering and sorting" sound like application-level features you'd apply on top of the result set returned by SQL and the %ScrollableResultSet class offers something along those lines. If you're looking for server-side support, IRIS SQL currently only supports the TOP clause, but we have support for LIMIT / OFFSET semantics queued for development.
Where Oracle uses the FROM DUAL pseudo-table, IRIS will just do without a FROM clause altogether for cases where you just want to select a single row of constant expressions. The following statement should work:
INSERT INTO mytable (column1, column2, column_n) SELECT expr1, expr2, expr_n UNION ALL SELECT expr1, expr2, expr_n;
As of IRIS 2021.1, we allow users to create their own aggregate functions. Perhaps there's a beautiful community contribution in there? :-)
You could build something simple where you just stuff all values in a temporary global (the name of which you pass as a state) and sort them (using $sortbegin/$sortend), maintaining a count and then in the FINALIZE method gather the actual median value.
Two caveats:
We'll be removing these annoyances in a future version
Oliver already pointed you at the key setting for this, but I thought I'd include a snippet from and link to the corresponding doc as well
If Source has a foreign key public RowID, and you want Destination to have the same foreign key relationship, you must define Destination with the ALLOWIDENTITYINSERT=1 parameter. When defining Destination as a persistent class, specify Parameter ALLOWIDENTITYINSERT=1;. When using CREATE TABLE to define Destination, specify %CLASSPARAMETER ALLOWIDENTITYINSERT=1. If a table is defined as ALLOWIDENTITYINSERT=1, this setting cannot be changed by the SetOption("IdentityInsert")
method.
That would be through calling %Next() right away. The way how our query code works is to walk through results and tell you when it runs out of rows to pass back.
Our online documentation has a feedback button that makes your comments immediately find their way to the doc writers. They really appreciate suggestions and usually turn them around quite quickly :-)
Have you taken a look at DataMove? DataMove supports live environments, ensuring any operations to the global (subscripts) being moved are propagated along to the new location without requiring downtime.
I understand it might not yet be available on the releases you currently have deployed, but would be interested in learning more about features that might be missing or generally experiences from doing this on production systems.
This is expected behaviour. for a SELECT statement, %Execute() (or %ExecDirect()) will set %SQLCODE to 0 if the statement was successfully executed and ready to return rows, or an error code if the execution went wrong. It's through the use of %Next() that it will figure out whether there are any rows left. The first %Next() call on such a resultset will return 0 and at the same time set %SQLCODE to 100. This behaviour is similar and perhaps even more visible in the case of %ROWCOUNT for SELECT statements, which increases with every call to %Next(). See also the docs for %Next() (second bullet)
The simple reason for this is that we're not building the whole result set upfront, but rather "get ready" to iterate through it with %Next() calls, as users may only be interested in the first few rows. Some call this a lazy execution model, but I prefer the term efficient :-). Of course, for DML statements such as UPDATE and DELETE, the %ROWCOUNT is known immediately upon execution, as there is no result set to scan through
Also interested to see submissions / ideas on handling really BIG globals. DataMove is a powerful feature for moving data from one database to another in a live environment, but it operates at a fairly low level. Adding a layer around it for easy archiving of table data, an interface to keep track of (potentially large numbers of) subscript-level mappings, etc.. would be examples of helpful utilities in the big global space.
FWIW, we have several roadmap items for IRIS in this area, but I won't go into detail on them as it's much more exciting and informative for us to learn about the problems you are seeing and the solutions you'd propose to them :-)
Two small notes:
you're right. I didn't properly say that with "array of" I actually meant the underlying STORAGEDEFUALT = "array" behaviour. So the combination that'll be newly supported in IRIS 2022.1 is STORAGEDEFAULT = "list" with SQLPROJECTION = "table" (and table/column)
FWIW, here's my warranty-free code to project list-style collection properties to a table: https://github.com/bdeboe/isc-sql-utils
what do you mean with "for object properties too"? If you're asking whether
Property Addresses as array of Sample.Address;
projects as a child table; yes it does. (and "list of" will support this too in 2022.1)
Sorry to hear you ran into this. The issue was also detected during recent internal testing and we're in the process of fixing it.
A little more documentation on the SQL projection of collection properties here.
Currently the SQLPROJECTION = table option is only available for "array of" properties. In 2022.1 we intend to make this option also available for "list of" properties.
If you're interested, I can dig up a script I wrote a while ago to create a class that projects a read-only view of your "list of" property. No warranty!
If upgrading is on the cards, our recent IRIS 2021.2 release has built-in connectors for S3 and more
This is indeed a new piece of ALTER TABLE syntax we introduced between 2020.1 and 2021.1, so nothing wrong with your 2020.1 instance
Can you be more specific about what you mean with "can't be deleted"? If this is a <PROTECT> error because they are mapped to ENSLIB (as part of enabling the namespace for Interop as Robert described), you can just ignore that error in a try / catch block.
I'm not sure about the general application context but if you need to go through the full list of globals for your app, it may be be better to query the globals that are in your application's database (directory) using %SYS.GlobalQuery:DirectoryList() rather than all the ones that are visible in the namespace using %SYS.GlobalQuery:NamespaceList() or ^$GLOBAL. That'll avoid the issues with ^EnsEDI.
Note that if you are actually using DeepSee or interop (or more generally rely on any features that may be writing their own globals), just looping through the global list for this kind of operations is very risky. Most built-in IRIS features (besides DeepSee and interop, that is) will write to globals prefixed with ^IRIS.*
hang on there. What I said on discord is only that the DDL nature of this command prevents it from supporting query parameters. We are looking into expression support inside the VALUES clause. You still have time to stress the importance of this in the survey ;-)
woops, I got that quite wrong indeed. I got confused by the mentions of TSQL earlier in the thread and mistook it for some other lock-related syntax that's only there for TSQL. you are correct: LOCK TABLEis part of IRIS SQL.
I'm not sure whether the error message you got would have further clues, but likely you're better off filing this with the WRC. Given that it works for embedded SQL, I'm suspicious it may have to do with the pre-parser for .NET, which does an initial parsing of the command on the client side.
Did you set the SQL dialect to MSSQL or Sybase? That LOCK TABLE command is not part of IRIS SQL. I'm not familiar with .NET myself, but found this older code sample (rename to IRISConnection()):
_tconn = new CacheConnection(connectionString);
_tconn.SQLDialect = "MSSQL";
_tconn.Open();