#SQL

Hi community.

I was wondering if it was possible to use something like EnsLib.SQL.InboundAdapter with tables in IRIS.

This library monitors when a record has been inserted into a table in an external database, so it requires a DSN to connect to that database.

My goal is to make a call to an external API that takes a long time, it could spend nearly an hour (or more) completing its processes, but I don't want to block the main process.

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.

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.

• IDE: IntelliJ
• Framework: Spring Boot, MyBatis
• DB Connection: JDBC (using the library provided by InterSystems)

Currently, we are successfully mapping global data through the %PERSISTENT class and able to query it with SQL. However, the problem is that the retrieved "Korean" data is all broken.

Hello Community,

I executed the below query in the %ZLANGC00.mac routine. It returns results when using dynamic SQL, but not with embedded SQL(returns 0). 

 &SQL(SELECT count(*) INTO :Cnt FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'")
 Write "SQL Table count : "_Cnt,!
 Set tResultSet = ##class(%SQL.Statement).%ExecDirect(,"SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'")
 Do tResultSet.%Display()

Thanks!

I have a table in the development and want to erase data from certain columns. There is info in the docs but not for human
This piece of code does not work

ALTER TABLE table DROP COLUMN column %DELDATA

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:

Class PAB.DebugStream Extends %Persistent
{

Property Contents As %Stream.GlobalCharacter;
Property msg As %String;
ClassMethod InsertRow()
{
    set stream = ##class(%Stream.GlobalCharacter).%New()
    $$$ThrowOnError(stream.Write({"msg":"hello world!"}))
    &sql(insert into PAB.

I am struggling with returning a SQL Query Result that may have multiple rows that can be searched.

 set query = "SELECT Facility FROM FROM osuwmc_EnterpriseDirDB.RelationshipMedCtrID WHERE OSUmedcenterID = ?"
 SET rset = ##class(%SQL.Statement).%New()
 SET qStatus = rset.%Prepare(query)
 SET rset = rset.%Execute($Get(ID))
 do rset.%Display()

I need to take the values that are returned and say search them for a single value. How would I go about returning the EnsLib.SQL.Snapshot into a Array or List for it to be searchable.

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.

 set result = ##class(EnsLib.SQL.Snapshot).

Hello,

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.

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):

select id, col1, col2, mod(row_number() over (), 4) as partition from some_table;

Thus, a partition index will be assigned to each table row. The idea is to create several instances of my business service using pooling (e.g. Pool Size = 4) so each business service instance will be responsible to hande rows belonging to a certain partition, e.g.

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?

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 

Class osuwmc.Epic.Clarity.

Hello,

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!

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 searched the docs and the different articles, It seems for example that here its implied that the different data models of Intersystems  DO indeed support the ACID properties and allow for safe insertion, deletion etc... in concurrent operations to the server that is.

Would love to get a clarification, Thx!

Hi Folks!

Have a very simple question, I hope :)

How can I make sure that user 'John' is already created in the IRIS system?

Preferabbly via SQL?

thanks a lot!

Hello,

It's possible to set a value to a "variable" in SQL Cache?

Like in SQl Server or Oracle?
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 am working with WRC, but we are both struggling to find the specific answer for JDBC. If I use my local desktop and JDBC to connect through DBeaver I don't have an issue.

Hello guys,

I need to tranform columns into rows or rows into columns.
I'm using only SQL Cache.

The code:

SELECT
    codRepresentante
    ,vendasAbril
    ,vendasAgosto
FROM
    Ped.MetasRepresen
WHERE ano = 2024

The columns: 

Can you help me?

Thanks

Hello everyone,

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.

So far, I’ve tried various approaches, including:

• Setting up an intermediary API,
• Using Azure Service Bus,
• Leveraging Caché Jobs,
• All of which rely on table triggers as the entry point.

However, each of these solutions has led to performance bottlenecks and system lockups.

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?

In tracking down some of our Orphaned message issues, I was wondering if setting our EnsLib.SQL.Snapshot variable equal to "" was the same as calling the .Clean() method on EnsLib.SQL.Snapshot? Are they the same?

should I be possibly using ##class(EnsLib.SQL.Snapshot).%Delete() instead?

Hello my Friends,

I have a question, I have a data like this

And I want to create a display like this, which I can put a status column beside the Item Column

The status become new, if there's no previous item, and if there's a same item after the first one, the status become Old

Does anyone could help me ?

I need to display it without subquery if possible

Thank You

Best Regards,

Steven Henry

Hi everyone,

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. 

(By the way, if you’re interested, the Docker container is available here: Demo DBs IRIS.)

To achieve this, I’m using SQL to create a connection by running a command like INSERT INTO %Library.sys_SQLConnection.

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'

logout, then run 

the documentation - Defining Foreign Tables | Using InterSystems SQL | InterSystems IRIS Data Platform 2024.3 mentions "A user that creates a foreign server must have the %MANAGE_FOREIGN_SERVER administrative privilege" but I am not finding that security setting to give to my role.

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. But, if I do the same from the FHIR Facade layer, the %OpenId method returns NULL.

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.

Hence the question: is there a way to do that?
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)

And there is a Embedded Python with numpy, but logic behind STDDEV(MDX) and numpy.

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?

Thanks

Scott

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:

select ac.value, count(ac.value) as cnt
  from HSFHIR_X0002_S_Patient.addressCity ac
  group by ac.value
  order by 2 desc

I'm using DBeaver with IRIS official JDBC driver.

Hello,

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 :-))?

Thank you,

Jean