I'm struggling to insert into a table, and it is baffling me. The insert statement is now very simple (I was using a complex INSERT SELECT statement but I'm trying to narrow down the problem).
I would like to capture any NACK's that is sent back to the Operation. The Operation is already setup to "Save Replies/IndexNotOK's", but I would like to see if we can query Cache and pull those NACK's into an extract.
We need a web-based SQL tool to connect to Ensemble/Cache, that will offer more functionality than what the Management Portal offers. It needs to be web based, so that we can host it on a machine that has connectivity to the server via the superserver port. We (the devs and support) only have browser access to the environment via Remote Desktops, hence the requirement.
Has anyone gotten OmniDB to connect to Ensemble/Cache? If so, can you please advise on how to configure it?
Any other suggestions for such a tool are also welcome.
Recently our team have been getting requests to pickup a large amount of data from API nightly (e.g. using ODATA to loop through pages) and placing that into MS SQL databases.
My question is, what is considered the best practice to get data from API and route to external SQL database WITHOUT persisting any messages/traces etc?
Would like to know if there is an alternative or better way to paginate through a dataset using dynamic SQL than what I am using below. The problem is that as the potential pool of data gets larger, this code slows down to the point of not being useable. In analyzing each line of code below, it appears the slow down is related to the initial rset.%Next() iteration. Is there anything available which does not require a subquery/%VID such as a simple LIMIT/OFFSET?
Is there a way to find the median in Intersystems Cache SQL? I know it is not available as an aggregate function. Also in SQL Server I could try something like:
SELECT
(
(SELECT MAX(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score) AS BottomHalf)
+
(SELECT MIN(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score DESC) AS TopHalf)
) / 2 AS Median
However, there is no PERCENT Keyword in Cache as well. Any suggestions?
I'm very new to InterSystems Health Connect so may be doing something silly...
I'm trying to link to an Oracle DB view using the Link Table Wizard in the Management Portal. The wizard finds the view, but when I select it and click the 'next' button I get an error on the 'Select Columns' screen: 'ERROR #5534: Columns error' (and no columns show in the wizard). I've tried on several views and tables in the same DB but keep hitting the same error.
I have a linked procedure class and SQL gateway setup and I can't seem to get any response, status or error from using the stored procedure, I think it must be something big I'm missing to not even get a status.
Can someone see what my problem is please, any help would be very much appreciated.
A question has come up that I am not finding the answer for.... Does the daily purge process re-index EnsLib.HL7.SearchTable or other SQL tables? In looking at the purge process documentation I am not seeing anything that mentions EnsLib.HL7.SearchTable. Do we have to manually constantly re-index tables that we create? For example I created another search table based off of EnsLib.HL7.Search table, will I need to constantly watch this as it grows? How do tables get index, is there some kind of mechanism that automatically does it, or are we responsible for indexing tables ourselves?
I'm using an embedded SQL statement with a a cursor-based Embedded SQL query that uses host variables in the where clause, however, what I'm doing doesn't seem to work. Can anyone help?
I'd like to know if there are any issues if an index is inserted into a table without running the %BuildIndices() method.
It's important to note that data inserted before the index is not important for retrieval, so it's not a problem data inserted before the index don't show up in queries.
The reason why I'm asking this is that I'd like to avoid index reconstruction on big tables which I need to inser such index.
Why when I use a SQL on the Cache, the condition between "the expression" and "the expression", not permite to me, delete the all ID of this data class?
We are trying to track down the source of Orphaned messages and noticed that we are unable to query EnsLib.HL7.Message with any kind of WHERE or ORDER BY clauses in our SQL statement.
I know EnsLib.HL7.Message is a system table, but is there a way we can add additional Indexes to the table to make the query run better/faster without affecting the system?
I'm new to cache, come from an oracle and sql server background. In oracle and sql server I could write basically a stored procedure like script and pass it in as text to the command to execute.
Example:
Below would be the _sqltext
DECLARE @Id INT;
select id into @Id from something;
if @Id = 9
BEGIN
do something
END
The _sqltext would work in sql server, what would be the equivalent for cache for it work?
I want to have a script that can run from the usual unix, linux, or aix command line. It has to be able to get into an irissession and use set statements to get data using sql. It seems like I'm in a catch-22. When I use the irissession SERVER command at the command line, I can't run a script. When I put the irissession SERVER command in a script, it won't run anything in the script after that.
My goal for this script is to get this information and put it into a file which I can then parse.
Use case: small in-house hospital systems that query patient demographics via SQL. The new PAS being implemented in the near future will only support query/response via HL7. SQL access is available but intended for reporting so up to 24 hours behind.
Probably the easiest option is to set up a database in Ensemble and keep it up to date via standard ADT feed. This is going to have quite a large footprint and has a risk of getting out of sync.
I have a SQL query that I want to run against MS SQL from cache ensemble using the SQL outbound adapter. If I run this query direct from MS SQL Studio it take about 7 seconds MAX and returns about half a million rows. The row only contains one column it is a number all same size and if I run this query in the production in ensemble it takes for ever the production finishes without getting the response back. The same query run in cache outside the ensemble environment writing results to a file it returns results taking about 3minutes to complete.
Is there a way to use a class Parameter in embedded SQL without having to declare a local variable, in a similar way to how it is done with Property by preponing i% as described here. See my example:
I am experiencing a problem with an EDI process that uses a SQL Batch Service to connect to our DEV environment. However, when we point the EDI service to our TEST server, it errors out.
I have checked every single property on our TEST and DEV servers as well as the properties on the associated tables. They are identical. Nothing has changed in the SQL either.
By update