Not sure there are many that connect to MS SQL to execute queries, stored procedures, etc, but our Healthsystem has many different MS SQL based databases we use within the Interoperability environment for various reasons.
With the push to moving from on-prem to the Cloud we ran into some difficulties with our SQL Gateway connections and knowing how to config them to use Microsoft Entra for Active Directory Authentication.
The Certification Team of InterSystems Learning Services is currently developing an InterSystems ObjectScript Specialist certification exam, and we are reaching out to our community for feedback that will help us evaluate and establish the contents of this exam.
I’m facing issues with replicating data from my Caché 2016 database to a PostgreSQL database. I need to handle around 300 data updates per minute, and whenever certain tables are modified, those changes must be reflected in other databases.
I need to use a context variable in my code block for the table to query. When I use :content.table, it does not work. When using the literal, it does. How do I get the :context to work?
I have a little problem, I need to diplay the data based on input status (first time and after)
this is the data:
and this what I need to display
the notes become new because it is the first time that the data has been inputed, and it will become old if we have the data before (2nd data, 3rd data the notes will become old).
I tried executing the SQL JSON_TABLE query with large JSON string(more than 200000 characters) and I got the below error. I'm curious about this under the hood workflow and how does it reach reaches MAXSTRING.
Here at InterSystems, we often deal with massive datasets of structured data. It’s not uncommon to see customers with tables spanning >100 fields and >1 billion rows, each table totaling hundred of GB of data. Now imagine joining two or three of these tables together, with a schema that wasn’t optimized for this specific use case. Just for fun, let’s say you have 10 years worth of EMR data from 20 different hospitals across your state, and you’ve been tasked with finding….
Auditing is a critical capability ensuring the security of your server, and for quite a while ago we have included the ability to audit SQL statements run on the server.
With v2024.3 out already we're providing finer-grained options for defining these events to audit.
I’m currently experimenting with the SQL Gateway Connection, CREATE FOREIGN SERVER, and the THROUGH command (IRIS Documentation). To speed up my tests, I’ve combined several DemoDataSets in a Docker container and would like to automate the creation of SQL Gateway Connections using code.
I am attempting to create a Foreign Server/Table so I can pull some information in from MS SQL server via JDBC connection, but as soon as I create the Server/Table and logout the Foreign Server disappears.
CREATE FOREIGN SERVER Epic.Clarity
FOREIGN DATA WRAPPER JDBC CONNECTION 'MS-EpicClarity'
My IRIS instance is connected to a Postgres database using SQL Gateway and linked tables.
One of these tables is projected to the Patient class. I want to select a record from this table by ID and convert it to a FHIR resource using the %ExistsId and %OpenId methods.
I noticed that if I call these two methods from the console, the record is always found.
Like many others probably find themselves, we were stuck doing live data mapping in our Interface Engine that we really didn't want to do, but had no good alternative choice. We want to only keep mappings for as long as possibly needed and then purge expired rows based upon a TTL value. We actually had 4 use cases for it ourselves before we built this. Use cases:
I’m using EnsLib.SQL.OutboundAdapter in my InterSystems Ensemble operation to execute an insert statement in Sql Server Management Studio 20. While inline queries (e.g., constructing the query string with _ concatenation) work fine, I'm facing issues when trying to use parameterized queries.
First time trying to use Foreign Tables/Servers instead of Linked Tables...
Within the SQL Editor inside of the Managment Portal, or connecting through DBeaver JDBC how we can see what Foreign Servers have been defined? Is there a way to query and verify structure of the Foreign Server connection to know that we are building the correct Foreign Tables?
I attempted to create my first Foreign table but it failed when I tried to query the tables because it said the table could not be found. But when I sign into the Database via SQL Management Studio, I can see the table.
The rise of Big Data projects, real-time self-service analytics, online query services, and social networks, among others, have enabled scenarios for massive and high-performance data queries. In response to this challenge, MPP (massively parallel processing database) technology was created, and it quickly established itself. Among the open-source MPP options, Presto (https://prestodb.io/) is the best-known option. It originated in Facebook and was utilized for data analytics, but later became open-sourced.
The goal is to get data (from half a thousand to 3-4 thousands lines) from DB, calculate standart deviation then use it as logical condition in analyzer.
For example IF std > custom_value = show_the_result ELSE null
There is a STDDEV(MDX) method used in Analyzer but it is a measure and it can not be used as logical condition (correct me if i am wrong)
I am using a JDBC connection to MS SQL server to execute a stored procedure to select data and bring it into InterSystems as a EnsLib.SQL.Snapshot. I loop through the EnsLib.SQL.Snapshot using a while loop, but I also want to iterate through the Columns within that Row to do logic.
Is there a way to iterate through the Columns of the current Row of the EnsLib.SQL.Snapshot so I can apply logic/rules for further processing?
I'm playing with some anayltic queries against FHIR server tables. The HSFHIR_X0002_S_Patient.addressCity table contains a lot of cities which names contain german charachers such as ä, ö and ü.
The following query works fine:
select value from HSFHIR_X0002_S_Patient.addressCity
But this one converts city names to uppercase, and characters with umlauts are lost, so instead of "Köln" or "München" I see KOLN and MUNCHEN:
I HAVE A NEW SQL CLASS THAT DISPLAYS ENTRIES BASED ON THE FIRST 2 NODES OF A GLOBAL. I FOUND OUT THAT THE CLASS ALLOWS FOR AN ADDITIONAL NODE(s) TO BE INSERTED IN THE "User Specification Node:" along the delimiter and the Piece in the NewStorage Map1 for "ModifyDDDD" shown below. it is not working.
By default, the order of columns in a table is determined automatically by the system. To change the order, explicitly set the order for each property using the property keyword SqlColumnNumber when defining the class.
A customer today asked whether we could offer anything like the nice lineage charts that dbt offers, based on SQL view metadata. They are very much aware of our dbt-iris adapter, but were asking in the context of their landing area, which combines foreign tables and views, ahead of possibly using dbt for onward transformations.
By update.png)
Open Exchange app.png)
.png)
.png)
.png)