#SQL

Hi, we are trying to run a dynamically generated Oracle Insert SQL script using Do $SYSTEM.SQL.DDLImport("Oracle",""," sql.txt",[Error Log]) to insert multiple rows into a Linked Table within HealthConnect to an Oracle database.

An interesting pattern around unique indices came up recently (in internal discussion re: isc.rest) and I'd like to highlight it for the community.

As a motivating use case: suppose you have a class representing a tree, where each node also has a name, and we want nodes to be unique by name and parent node. We want each root node to have a unique name too. A natural implementation would be:

Class DC.Demo.Node Extends %Persistent
{

Property Parent As DC.Demo.

I'm getting wrapped around the axle with CAST and CONVERT and can't seem to find a way to do this (short of adding a stored procedure wrapping $zdt($zdth(posix,-2),3), which I'm refusing to do on principle, because there has to be some way to make this work).

Any ideas?

I am calling a stored procedure over an ODBC connection and every time I call it there are several warnings written to the log event {Found no Parameter 1 (used as 1) for query}.I seem to be getting this on every query executed and that seems to happen a number of times the query parameters are per query and its filling up my disc.

1) Is there a way to suppress these warnings as the query seems to be executed and data written to the database?

Hi,

I'm trying to load some data into IRIS using LOAD DATA in the IRIS terminal however I am getting blocked by this error. I have read the documentation on this page trying to set up a gateway connection but get the same Connection cannot be established error.

Say I have a persistent class in IRIS with an optional property EmailOptIn:

Class Person Extends %Persistent
{
Property Name As %String;
Property EmailOptIn As %Boolean;
}

I later realize that I'm doing a lot of null-checking on this property where I shouldn't need to. The solution is to make this a required property:

Class Person Extends %Persistent
{
Property Name As %String;
Property EmailOptIn As %Boolean [ Required ];
}

When I make this change I'll need to update all the existing data to set a reasonable default where it is null.

Hey Community,
I have an scenario, please advise or suggest possible ways to solve it.
So i have a persistent class to test results of a diagnostic laboratory. The properties are 

• Patient name
• Type of Test
• Order ID

Hi I've got a service setup to query a sql server via ODBC.

I've got all the parameters etc setup and everything is ok

My Query is

SELECT TOP 1
CI.ClientID, 
Gender, 
DateOfBirth, 
MaritalStatus AS MartialStatusCode,
(SELECT CodeDescription FROM GenMaritalStatus WHERE Code =  MaritalStatus) AS Martial,
Ethnicity AS EnthnicityCode,
(SELECT CodeDescription FROM GenEthnicity WHERE Code = Ethnicity) AS Ethnicity,
Nationality AS NationalityCode,
(SELECT CodeDescription FROM GenNationality WHERE Code = Nationality) AS Nationality,
DateOfDeath,
CN.Surname AS Surname,
 ISNULL (CN.GivenName1, '')
 + ISNULL( CN.

I have a table with a Varchar(max) column that I have created via the HealthShare SQL portal, and I see that in it's underlying class that column corresponds to a  %Stream.GlobalCharacter

When I try to do a text search on that column (referenced as cd.Code in the example below) I get an error

select * 
from DevTools_CodeAnalysis_tables.Code cd
inner join DevTools_CodeAnalysis_tables.MethodData mtd
on charindex( mtd.qualifiedRef,  cd.Code) > 0

It produces the error

 Scalar function CHARINDEX (arg2) not supported for stream fields

How do I search it?
 

Hi all

I'm struggling to insert into a table, and it is baffling me. The insert statement is now very simple (I was using a complex INSERT SELECT statement but I'm trying to narrow down the problem).

Insert statement:

INSERT INTO Phu_Replay_Schema.

Hey Community, 

Don't miss the latest videos on InterSystems Developers YouTube channel:

⏯ IntegratedML Update from the Field

In the good old days (tm) determining the size of the data, streams, and indices for a class/table was easy - you just ran %GSIZE and check D, S, and I globals respectively.

However, nowadays sharding, optimized global names, and indices in separate globals produce %GSIZE output looking like this:

            Global Size Display of /irissys/data/IRIS/mgr/irisshard/
                              1:35 PM  Dec 02 2020

          IRIS.Msg       1     IRIS.MsgNames       1     IRIS.SM.Shard       1
       IS.DGoWeK.1   24359       IS.DGoWeK.2       3       IS.DGoWeK.3    2810
       IS.DGoWeK.

My personal problem is the use of DBeaver as a migration tool.
Especially as one of the strengths of IRIS ( and also Caché) before is the availability of the
SQLgateways that allow access to any external Db as long as for them an access usinig 
JDBC or ODBC is available. So I extended the package to demonstrate this.

I would like to capture any NACK's that is sent back to the Operation. The Operation is already setup to "Save Replies/IndexNotOK's", but I would like to see if we can query Cache and pull those NACK's into an extract.

Is this possible?

Thanks

Scott

Hi Guys,

So I've been following this guide in using a %Library.ResultSet with a  ClassName / QueryName as described in the first example.

https://docs.intersystems.com/irislatest/csp/documatic/%25CSP.Documatic…

The code I've got so far doesn't work and is as follows:

set rs=##class(%ResultSet).%New()
set rs.ClassName="GMECC.DocmanConnect.Tables.vwNewGPs"
set rs.QueryName="GetRows"
set sc=rs.Execute("a")  If $$$ISERR(sc) Do DisplayError^%apiOBJ(sc) Quit
while rs.%Next() { do rs.%Print() }		

And the referenced class is

Class GMECC.DocmanConnect.

Hi, what’s the best way to check if your query return 0 records?

If $$$ISOK(sc) 	
    		{
   	   		 	Set tResult = sqlStatement.%Execute()
   	    
    			WHILE tResult.%Next()
    			{
					set tRecords = tResult.%ROWCOUNT
   		        }

				if (tRecords = "")
				{
					write "no records" 
				}
			}

Client: Northwell Health

Role: Senior Developer

Location: Remote

Duration: 6+ Months

Description:

Team Overview:

The FHIR Platform team is tasked with providing the core infrastructure in providing access to Northwell HIE patient data complying with HL7 FHIR and USCDI standards.

Position Summary:

The primary purpose of this role is to provide technical design, coding, testing and documentation for multiple components in the FHIR solution.

Responsibilities:

• Develop end-to-end solutions, participate in code reviews, unit test and deploy.

Hey Developers, 

See how you can get high-performance relational access using SQL to manage data within your InterSystems products: 

⏯ Using SQL with InterSystems IRIS

Hi,

We need a web-based SQL tool to connect to Ensemble/Cache, that will offer more functionality than what the Management Portal offers.
It needs to be web based, so that we can host it on a machine that has connectivity to the server via the superserver port. We (the devs and support) only have browser access to the environment via Remote Desktops, hence the requirement.

Has anyone gotten OmniDB to connect to Ensemble/Cache? If so, can you please advise on how to configure it?

Any other suggestions for such a tool are also welcome.

Kind regards,
Stefan

Hi folks!

Sometimes we need to import data into InterSystems IRIS from CSV. It can be done e.g. via csvgen tool that generates a class and imports all the data into it.

But what if you already have your own class and want to import data from CSV into your existing table?

There are numerous ways to do that but you can use csvgen (or csvgen-ui) again! I prepared and and example and happy to share. Here we go!

Recently our team have been getting requests to pickup a large amount of data from API nightly (e.g. using ODATA to loop through pages) and placing that into MS SQL databases.

My question is, what is considered the best practice to get data from API and route to external SQL database WITHOUT persisting any messages/traces etc?

At the moment my thinking would be to write a service that triggers an action once a day to an EnsLib.HTTP.OutboundAdapter, then map the HTTP response into a linked table (via method to keep the operation clean that managed the gateway on the way in and out of the method).

Hi,

Is there a way to find the median in Intersystems Cache SQL? I know it is not available as an aggregate function. Also in SQL Server I could try something like: 

SELECT
(
 (SELECT MAX(Score) FROM
   (SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score) AS BottomHalf)
 +
 (SELECT MIN(Score) FROM
   (SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score DESC) AS TopHalf)
) / 2 AS Median

However, there is no PERCENT Keyword in Cache as well. Any suggestions?

Thanks

Hi everyone, 

How do you alter primary key on a table that has data?

Any example much appreciated.

Cheers,

Tom

Hi everyone, how do you run multiple quires?

I have tried couple of different ways, but not working.

 SET sql = 2

    Set sql(1) = "UPDATE QUERY"

    Set sql(2) = "UPDATE QUERY"

    Set sqlStatement=##class(%SQL.Statement).%New()

    Set sc1=sqlStatement.%Prepare(.sql)

    If $$$ISOK(sc1)     {

       Set tResult = sqlStatement.%Execute()    

    }

    else{

        $$$LOGERROR("Failed")

    }

I have a linked procedure class and SQL gateway setup and I can't seem to get any response, status or error from using the stored procedure, I think it must be something big I'm missing to not even get a status.

Can someone see what my problem is please, any help would be very much appreciated.

Class generated by linked procedure wizard:

Include %occInclude /// 
Class dbo.

A question has come up that I am not finding the answer for. Does the daily purge process re-index EnsLib.HL7.SearchTable or other SQL tables?  In looking at the purge process documentation I am not seeing anything that mentions EnsLib.HL7.SearchTable. Do we have to manually constantly re-index tables that we create? For example I created another search table based off of EnsLib.HL7.Search table, will I need to constantly watch this as it grows?

Hi

I'm using an embedded SQL statement with a a cursor-based Embedded SQL query that uses host variables in the where clause, however, what I'm doing doesn't seem to work. Can anyone help?

The code I'm using is

set tMessageName = "AssessmentsMessage"
set tIdentifier = "SectionCode" 
set ID = 0
&sql(
    Declare IDs Cursor For 
    select  %ID
    INTO :ID
    from GMECC_DocmanConnect_Tables.

I have a %Persistent class with properties that are of %SerialObject. I want to add an index to a property of the %SerialObject class.

Is this possible?

Hi!

I'd like to know if there are any issues if an index is inserted into a table without running the %BuildIndices() method.

It's important to note that data inserted before the index is not important for retrieval, so it's not a problem data inserted before the index don't show up in queries.

The reason why I'm asking this is that I'd like to avoid index reconstruction on big tables which I need to inser such index.

I'm using Cache 2018.1.

Thanks,

José

We are trying to track down the source of Orphaned messages and noticed that we are unable to query EnsLib.HL7.Message with any kind of WHERE or ORDER BY clauses in our SQL statement.

I know EnsLib.HL7.Message is a system table, but is there a way we can add additional Indexes to the table to make the query run better/faster without affecting the system?