#SQL

I need to reproduce a table (in fact a view, but let's start with a simple table 😊) in IRIS as the currently used in Oracle.

I need to expose/project one column to JDBC as CLOB, in my class I have the corresponding property defined as:

Property GlobStream As %Stream.GlobalCharacter;

In JDBC this column is projected as LONGVARCHAR, this is compliant with the documentation but I need to project it as CLOB.

In addition, I'm no expert in JDBC but wit seems that LONGVARCHAR has a Maximum Length 32,700 characters in JDBC, not quite enough for an arbitrary stream.

Hi communauty

I've two columns in my table and the type must be interval of integer values for example : age must be interval between [2 and 6] years and wigth between [10 and 30 ] Kg , how can i define them when creating my table?

thank's

Why do these clauses affect SQL performance?

select ID from some_table where row_status in ('I','U') order by ID limit 5 - makes the query infinite select top 10 ID from some_table where row_status in ('I','U') order by ID - the same select ID from some_table where row_status in ('I','U') order by ID - is fast

Actually there are no rows in the table having row_status 'I' or 'U'.

Hello everyone,

My team lead mentioned that users can sometimes create globals directly without associating them with tables or classes. In this case, these globals would not be accessible via SQL.

Is this correct? Can a global exist independently in this way, and if so, is there a specific approach to access or manage them without SQL?

Hi folks!

Is there any way to know the text of the SQL queries executed against the IRIS server?

My case: I have an SQL class query with a few parameters and and some combination of values causes unexpected results.

It'd be great if I could just copy the query from some log of queries done vs my IRIS the server. Cannot find it easily, though. Any advice?

New to using Analytics and using Dashboards. We have this Report, SQL Query that lists out the Activity per Data Source in Health Share Provider Directory. Instead of running it as a report, because it takes a while to run, was wondering if there is a way to do this as a Dashboard instead.

How can I take the SQL from this report and create a Dashboard instead?

Hello,

We are accessing an InterSystems Cacha database from Microsoft SQL Server using a linked server over ODBC (ODBC35). Queries are executed using OPENQUERY.

On the SQL Server side, there is a linked server property called Query Timeout. By default it is set to 0 (no timeout). We are considering setting it to 15 seconds and would like to understand how Cache behaves in this scenario.

I have a Photo that is stored as an VARBINARY within MS SQL. I would like to Select the data into a EnsLib.SQL.Snapshot, is that possible? I would take it out of the Snapshot and send it to a %Stream so it can be written out to a file.

Defining my first REST API within InterSystems using iris-rest-Api-template as a basis and I am seeing if someone could provide me some guidance to see if I can make it work.  

In some of my other posts, I have been trying to come up with a way for our Enterprise Application Development team which works with .Net to build Applications to make a REST call to our instance of InterSystems to query some of the Cache Tables we have defined. 

Using the iris-rest-api-template, I have created the osuwmc.DataLookup.REST.Base.cls

Class osuwmc.DataLookup.REST.Base Extends %CSP.

There is a Master Table within IRIS that I am populating from Epic but want to share it with our Enterprise Application Development Team (Web). As a test I was able to use _SYSTEM from postman to execute the following.

POST /api/atelier/v1/xxxx/action/query HTTP/1.1
Host: xxxxxxxx
Content-Type: application/json
Authorization: ••••••
Cookie: CSPSESSIONID-SP-443-UP-api-atelier-=00f0000000000AKyLjBfUvU$MpFD8UT8y$EoNKNw1ixZeXN4_Q; CSPWSERVERID=hzZAT5rb
Content-Length: 86

{"query": "SELECT * FROM osuwmc_Epic_Clarity.

Hi developers!

There is a neat feature of ObjectScript classes - Query element, which allows you to write in a clear SQL (without any & or ()), pass parameters to it and call it from ObjectScript as do QueryNameFunc() or via Call SQLProcedureName via SQL, .e.g.

Query MyQuery(p as %String) as %SQLQuery [SQLProc]

{ 

SELECT * FROM MyTable 

WHERE Name=:p

}

All works fine, but when I tried to use the same for a DELETE statement see the following error:

SELECT expected, DELETE found ^ DECLARE Q1 CURSOR FOR DELETE

Is the Query element for SELECT only? What am I doing wrong? )

I want to extract data from IRIS (Health Share Provider Directory) and write the data out to a file that I can use within an external system. Using EnsLib.SQL.Service.GenericService do I need to supply Credentials if I am querying IRIS locally? I have always used JDBC to connect externally when creating Services and Operations to read/write SQL data from external Databases, but never internally. Normally I would just use sql or code to pull the data within a Process or DTC. But in this case, I want the Service to drive the workflow, and query the data from Local Tables

Could someone explain how and why a HL7 ACK be showing up as a Orphaned message when I run the following SQL...
 

SELECT HL7.ID,HL7.DocType,HL7.Envelope,HL7.Identifier,HL7.MessageTypeCategory,HL7.Name,HL7.OriginalDocId,HL7.ParentId, HL7.TimeCreated
FROM EnsLib_HL7.Message HL7
LEFT JOIN Ens.MessageHeader hdr
ON HL7.Id=hdr.MessageBodyId
WHERE hdr.MessageBodyId IS NULL

I am trying to find the problem code that is causing the Orphaned messages, and an ACK showing up seems kind of Odd. While we do have Archive IO/Trace on, and Index NOT OK's set why would they show up as Orphaned messages?

Hi everyone,
I'm getting prepared to take the following certification exam: "InterSystems IRIS Development Professional".

Can you give some advice on how to prepare (aside from the official course page: https://www.intersystems.com/certifications/intersystems-iris-development-professional/ )?
Do you have examples of quiz questions that simulate the real exam or any material that helped you getting prepared?

Thanks you in advance,

Arber

Hello!

I'm trying to create some foreign tables to a PostgreSQL database. In some cases, columns with certain datatypes cannot be consumed by IRIS and the following error is thrown:

 [SQLCODE: <-237>:<Schema import for foreign table did not return column metadata>]

  [%msg: <Unkown data type returned by external database>]

For example: serial4 typed ID columns are typical examples. Is it possible, what's the best way of resolving these datatypes, which- seemingly- don't have proper JDBC metadata mappings?

Hello all,

I'm trying to build a cube based on a linked table but seems that IRIS is not able to do it :O

Long story short, I have a linked table in IRIS that sources a Microsoft SQL table (using standard linked feature from the portal). It works fine, I can access it using SQL as many other times. On top of that, I've created in DeepSee (ok, Analytics) a cube that uses this class as source. It compiles correctly, no errors given. When I build it with 100 records, all goes well and using Analyzer I can see results.

Existing cube deleted.

Hi community.
I have a query:

SELECT
nameField,
dateField,
anotherDateField
FROM
(      
SELECT
MIN(someDate) as dateField,       
nameField,
anotherDateField
FROM $$$SOURCE
WHERE $$$RESTRICT               
GROUP by someOtherField    
)
WHERE dateField >= anotherDateField

This query should filter the data by the minimum value of the somDate field, but it doesn't. It displays all values ​​together, regardless of the external filter. The exact same query (without the $$$ tokens, of course) works fine in a regular SQL runtime.
My guess is that the $$$RESTRICT does this

WHERE source.

Hi Team, 

Can I please check if anyone has built a simple web interface for maintaining custom SQL lookup class.   

We have a simple persistent class in HealthShare which is used for storing Pathology test codes. Test codes in this lookup class is used for message filtering and applying additional logic when processing pathology results/orders. 

We want to make this class available to external users from pathology (not the usual management portal users) to maintain so that they can add/edit/delete test codes as required.

Is there a way to create a table for the results of a SQL statement ?

Example: Consider the below query and results.

Select ID,Name,DOB,Gender from EMS.EMS

RESULT 

Hi Community,
I’m trying to execute a directory query in InterSystems IRIS using %SQL.Statement, but encountering an unexpected error.

Details:
The following command confirms that the directory exists:

Set dirPath="\\MYNETWORK_DRIVE\DFS-Shared_Product\GXM"
Write ##class(%File).DirectoryExists(dirPath)

It returns 1, meaning the path is valid and accessible.

However, when I try to execute this SQL query:

Set File=##Class(%SQL.Statement).%New()
Set Status=File.%PrepareClassQuery("%File","FileSet")
Set Result=File.%Execute(dirPath)
If Result.%SQLCODE {
    Write Result.

We currently have Business Operation that we built to use the EnsLib.SQL.OutboundAdapter so we can make Microsoft SQL Server Stored Procedure calls. The BO is attached to a Java Gateway Service.

Some of our MS SQL Databases have moved from being OnPrem to Azure Cloud. We have started seeing where we are receiving errors on the BO saying that we cannot connect to the Azure Database, but we never receive a Disconnect from the Azure Database.

I am trying to help another group within our organization access a SQL Table that I have created to populate Epic Department Data within our environment and came across the ability to use SQL Seach REST Interface using iKnow.

However, I am having issues trying to get it to work via POSTMAN before I hand off the solution...

the POST URL... https://<servername>/api/iKnow/latest/TESTCLIN/table/osuwmc_Epic_Clarity.DepartmentMaster/search

where osuwmc_Epic_Clarity.DepartmentMaster is the table

In the

Hello,

I am currently maintaining a legacy application that was developed in ObjectScript and accesses data by working directly with globals.
For a new program, however, I plan to access the same global data through SQL-mapped classes.

I have two questions regarding this approach:

1. If I define indexes in the SQL-mapped class, what potential impact (if any) could this have on the existing legacy application that directly accesses the globals?