Hi, I am unsure how to remove this restriction; when I am performing dynamic SQL using ##class(%SQL.Statement).%ExecDirectNoPriv(, .query, args...)
It works fine, but the moment I add specific properties from the persistent class I am performing the select on into the WHERE clause, I get: ERROR #5540: SQLCODE: -99 Message: User UnknownUser is not privileged for the operation. Despite using %ExecDirectNoPriv, I've tried with prepared statement as well, exact same situation.
I have an IRIS persistent class with a %Stream property whose value is a JSON object. I'd like to use a SQL trigger to pull some value out of the JSON and persist it in another property, for easy querying and indexing. See below for a minimal example:
I have a general question about HealthShare Provider Directory using Code Tables on disk vs Cache SQL Tables. Why is Provider Directory not using the Cache SQL Tables within the IRIS platform?
I was wondering if someone could help me. In the past I have been able to call external Stored Procedures through a SQL Outbound Connection and have them return me the EnsLib.SQL.Snapshot to use within a BPL to extract data.
But this time instead of using a SQL Outbound BO to make the Stored Procedure call, I decided to create a Linked Stored Procedure through the %JDBC_Server to point to the Stored Procedure out on MS SQL.
However, I am struggling to get the code just right to return the Column value from the Linked Stored Procedure.
Our software commonly returns a full result set to the client and we use the DataTables plugin to display table data. This has worked well, but at datasets grow larger, we are trying to move some of these requests server-side so the server handles the bulk of the work rather than the client. This has had me scratching my head in so many ways.
I'm hoping I can get a mix of general best practice advice but also maybe some IRIS specific ideas.
My query that I am running on my Custom SQL Inbound Service has columns that are larger than the typical string length. How do I enlarge the SQL Snapshot Column limitations
I have the need to query an external database and write the result set/snapshot to an internal %Persistent [ DdlAllowed ] table that I built. I have built inbound SQL Services before and write them externally to replace SSIS jobs, but how would querying a database via a Service and writing the data to an internal table work?
Can I just take the inbound query structure and write it to the class file of the internal table in a DTL? If so, what would be the Target? Or does this need to be done within a BPL as a Code block?
I've created a method in my File Service to do a cleanup for every file load. Currently, I've set it to delete data when LastUpdated date is greater than maxdate. However, I want to do a cleanup for every new file load. Any suggestions or advice on how to do this? Thanks!
I am currently experiencing frustration with trying to Authenticate an Active Directory account through JDBC as the Hospital System moves from OnPrem SQL Server to using Azure SQL Server with Microsoft Entra Authentication.
Microsoft cannot give me a straight answer of what is required from a JDBC standpoint to authenticate from a Linux environment.
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?
So i want to use the INSERT OR UPDATE command so i can update a COUNTER for a given name:
INSERT OR UPDATE myTable SET name='Omer', counter = counter + 1;
as you can see with the above code - if the row is non-existent then we get an error because COUNTER is NULL! I tried the following to fix this but all have failed:
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).
Hello! So my question is quite simple, Do the different data models of Intersystems all support the ACID properties? I assume that for the SQL data model implementation it does, But does it also work for global (i.e the hierarchical data model)?
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.
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 have a business service which is responsible for some batch operations with an SQL table. The process is generally slow but it is possible to scale the performance using multithreading and/or parallel processing and logical partitioning (postgres):
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.
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'
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.