Hello. Currently, we are developing using Cache 2018 version. Our team is working on improving an existing legacy program so that it can also be used on the web.
Before asking my question, here is the development environment.
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 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 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:
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’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.
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:
Is there a programmatic way (in ObjectScript) to determine if a string is an SQL reserved word (aside from using embedded SQL to try to name a property with that string and look for the error :-))?
I need help with coming up with a SQL query that pulls only one value. I have a case where two providers share the exact same name. Each has a different NPI number and IdentityTypeId. I tried the below query - output is also below.
I have been trying to track down an issue we are seeing in our TEST environment with Memory usage.
We have Several BP's for years now that take a HL7 message, parse it apart, and make calls to a Custom EnsLib.SQL.OutboundAdapter to have it execute Insert/Select/Update/Delete stored procedures against a MS SQL Database via JDBC connection. We are using Microsoft's JDBC 12.2 driver to do this.
What we are seeing is that IRIS.WorkQueue globals are being defined for these calls but then the IRIS.WorkQueue is not being cleaned up and taking up large amounts of Memory.
By date
.png)
.png)
.png)