#SQL

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

}

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? How do tables get index, is there some kind of mechanism that automatically does it, or are we responsible for indexing tables ourselves?

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?

Why when I use a SQL on the Cache, the condition between "the expression" and "the expression", not permite to me, delete the all ID of this data class?

Could anyone please explain why Caché has a %STARTSWITH function while it supports ANSI SQL "LIKE"?

thank you very much.

I'm new to cache, come from an oracle and sql server background. In oracle and sql server I could write basically a stored procedure like script and pass it in as text to the command to execute.

Example:

Below would be the _sqltext

DECLARE @Id INT;

select id into @Id from something;

if @Id = 9

BEGIN

do something

END

The _sqltext would work in sql server, what would be the equivalent for cache for it work?

I get a generic error when I try it with cache

I want to have a script that can run from the usual unix, linux, or aix command line. It has to be able to get into an irissession and use set statements to get data using sql. It seems like I'm in a catch-22. When I use the irissession SERVER command at the command line, I can't run a script. When I put the irissession SERVER command in a script, it won't run anything in the script after that.

My goal for this script is to get this information and put it into a file which I can then parse.

I have a SQL query that I want to run against MS SQL from cache ensemble using the SQL outbound adapter. If I run this query direct from MS SQL Studio it take about 7 seconds MAX and returns about half a million rows. The row only contains one column it is a number all same size and if I run this query in the production in ensemble it takes for ever the production finishes without getting the response back. The same query run in cache outside the ensemble environment writing results to a file it returns results taking about 3minutes to complete.

I am experiencing a problem with an EDI process that uses a SQL Batch Service to connect to our DEV environment. However, when we point the EDI service to our TEST server, it errors out.

I have checked every single property on our TEST and DEV servers as well as the properties on the associated tables. They are identical. Nothing has changed in the SQL either.

I can refer to the TracerName for a given ImageFile object with the following syntax: obj.Study.Injection.GetAt(obj.InjKey).RadioTracer.TracerName

Is it possible to write an SQL statement to search the PET.ImageFile table to find a match based on the RadioTracer.TracerName?

Hi Community,

We need to export last 5 days Ens.MessageHeader data for a specific messages into file. I have written custom code to execute SQL query in object script.

Could somebody help me to iterate resultSet and write the data to text file.

Hi folks!

Consider I have a class "X.Y.Z"

What is the SQL table name for it? How can I obtain it via ObjectScript?

A quick search doesn't show any methods and properties. Documentation is a bit "wrong" here saying that the SQL table name is the same. It will be at least 'x_y.z'.

Any help is very appreciated!)

How to execute it from the command line? I am able to use SQL in Management Portal, but I need to do it in Unix

select * from Ens_Util.Log where ConfigName='SFTPTo.HQ.SAFETRACE.DFT' AND Text LIKE '%Put Stream%' and TimeLogged LIKE '%2021-10-16%'

Problem to Solve: When an HL7 message hits a particular Process, I need to use the placer order number therein to query the messages that came in over the last 24 hours on a particular Service and grab discrete pieces of data that came in the latest message that has that placer order number.

The code I'm (currently) using looks like this: