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.ParisConnecMessagetSettings
    Where  MessageName = :tMessageName
    and Identifier = :tIdentifier
)

&sql(Open IDs) For {
   &Sql(fetch IDs) If SQLCODE Quit // SQLCODE = 100, now rows found
       w !, ID 
       If $$$ISERR(tSC) Quit
} &Sql(Close IDs)

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?

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

thank you very much.

How do I use the LOAD DATA command as described here.

I have the following LOAD DATA statement:

LOAD DATA FROM FILE 'E://Temp/Values.txt'

into PARIS.UAGU_MNDOOUT1 (AHSU_ID, AHSU_UAG_ID)

Where do I run it? It doesn't work in the SQL Gateway, in the terminal, in an embedded SQL command.

What am I missing?

Hi Community, 

New video is already on InterSystems Developers YouTube:

⏯ Poor Man's Analytics with Rich SQL

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

using (var mdscmd = new CacheCommand(_sqlText, MyConnection) { CommandType = CommandType.Text }) {

}

Thanks

Hello everyone!

I am writing a SQL CALL (using JDBC) to a stored procedure that outputs a structured object (Oracle Object).

Since Caché 2017 the SQL engine has included new set of statistics. These record the number of times a query is executed and the time it takes to run.

This is a gold mine for anyone monitoring and trying to optimize the performance of an application that includes many SQL statements but it isn’t as easy to access the data as some people want.

This article and the associated sample code explains how to use this information and how to routinely extract a summary of daily statistics and keep a historic record of the SQL performance of your application.

What is recorded?

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.

Use case: small in-house hospital systems that query patient demographics via SQL.  The new PAS being implemented in the near future will only support query/response via HL7.  SQL access is available but intended for reporting so up to 24 hours behind.

Probably the easiest option is to set up a database in Ensemble and keep it up to date via standard ADT feed.  This is going to have quite a large footprint and has a risk of getting out of sync.

Another option would be to proxy the SQL queries in Ensemble and translate them to HL7 QRY messages.

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.

Hello, first question for me:

Is there a way to use a class Parameter in embedded SQL without having to declare a local variable, in a similar way to how it is done with Property by preponing i% as described here. See my example:

Class Test.Test Extends %RegisteredObject
{ 
Parameter MyParam = "1";  
Property MyProperty;  
(...)
Method PerformQuery() {
    set myParamLocalVar = ..#MyParam //this is what I would like to avoid
    &sql(SELECT * FROM MY_TABLE
        WHERE MY_PROPERTY = :i%MyProperty
        AND MY_PARAM = :myParamLocalVar)
    (...)
}
}

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. 

The EDI is a PUBSUB that generates X12 834s for various vendors.  The SQL Batch Service is running a modified code that executes a stored procedure.

I'm running SQL reports on some HL7 messages and need to report the raw content of both the outbound message and the ACK response.

When looking in a Visual Trace at an I/O (Ens.Util.IOLog), under the Header tab it lists "InObject" and "OutObject" as each having values - in the example I'm looking at, the OutObject value is the EnsLib.HL7.Message ID I need for the outbound message, and the InObject value is the EnsLib.HL7.Message ID I need for the ACK response. My thought was that I could query that class via SQL and do two joins on the EnsLib_HL7.

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?

SQL gateway. 'Locking' problem.

while debugging, is there in Caché a command to 'unlock' the instance of a class ?

close class, and kill class is not enough.

same problem in %Activate link

for each debug I have to exit (Halt) the terminal, 

make debug in C#

coming back , and re-load again in Caché.

Regards,

Emanuel

Hi, Community,

This post will demonstrate how to display data on the web by using Embedded Python , Python Flask Web Framework and Jquery datatable

We will display processes from %SYS.ProcessQuery table.

Step 1: Add table to HTML page and write below javascript code to display passed data from app.py :

HTML

  <table id="myTable" class="table table-bordered table-striped">                 
   </table>

Javascript

  <script>
    
$(document).ready(function() {
      // parse the data to local variable passed from app.py file
      let my_data = JSON.parse('{{ my_data | tojson }}');
      let my_cols = JSON.parse('{{ my_cols | tojson }}');

      $('#myTable').DataTable( {
          "data": my_data,
          "columns": my_cols,"} );
     } );

Hi developers!

As you probably noticed in IRIS 2021 the names of globals are random.

And if you create IRIS classes with DDL and want to be sure what global was created you probably would want to provide a name.

And indeed you can do it. 

Use WITH %CLASSPARAMETER DEFAULTGLOBAL='^GLobalName' in CREATE Table to make it work. Documentation. See the example below:

Hey developers!

Sometimes we need to insert or refer to the data of classes directly in globals.

And maybe a lot of you expect that data structure of global with records is:

^Sample.Person(Id)=$listbuild("",col1,col2,...,coln).

And this article is a heads up, that this is not always true, don't expect it as granted!

Hey Community,

See how cubes are constructed for use in business intelligence, and learn about SQL and MDX query languages. Physical and virtual cubes are used in InterSystems IRIS® Business Intelligence and Adaptive Analytics:

⏯ Introduction to Cubes in Business Intelligence

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,

Management Portal SQL Interface

ERROR #5540: SQLCODE: -104 Message: Field 'QT.Fehlermeldung.parameter' (value '<params wert=\"\" feldName=\"Modul.name:...') failed validation

I've checked the syntax with some public tools with adjusted Tablename "Fehlermeldung" -> `Fehlermeldung`. It seems to be valid, but cache does not accept it.

INSERT INTO "Fehlermeldung" VALUES (1001021,'qsDataFieldOutOfRange','10','Der Wert ''<wert>'' des Datenfeldes <feldName> \"<feldBezeichnung>\" ist <artDerAbweichung> als <feldBound>.','<params wert=\"\" feldName=\"Modul.name:Bogen.name:Feld.name\" feldBezeichnung=\"Feld.bezeichnung\"  artDerAbweichung=\"Ein Wert aus [größer, kleiner]\" feldBound=\"Minimal- oder Maximalwert des Feldes je nach Wert in artDerAbweichung\" />','','Leistungserbringer','','','Leistungserbringer','Als Template-Meldung kann diese auch auf modifizierte Daten der DAS zutreffen.','')

Hi everyone, i cant understand what is wrong in my query: 

ALTER TABLE MyNamespace.MyTable ALTER COLUMN CurrentColumnName RENAME NewColumnName

I just want to rename column name using sql.

I could not find any life example using this syntax.

SQLCODE for this query is -25:

-25 Input encountered after end of query

Quote from documentation:

• Rename the column using the syntax ALTER TABLE tablename ALTER COLUMN oldname RENAME newname. Renaming a column changes the SQL field name. It does not change the corresponding persistent class property name.

In this article I will explain the usage of %SQL_Diag.Result and %SQL_Diag.Message table along with all-new LOAD DATA functionality.

It is recommended to go through LOAD DATA documentation first. 

After successful operation LOAD DATA insert one record in %SQL_Diag.Result table and details are inserted in %SQL_Diag.Message table

Below is the basic command when table is already created and source file does not contain header row. 

LOAD DATA FROM FILE 'C://TEMP/mydata.txt' 
INTO MyTable

The file name must include a .txt or .csv (comma-separated values) suffix and both source and target have the same sequence of data columns.

Loading from File Source: Header

We recently encountered an Embedded SQL issue while upgrading to IRIS 2021.1, and thought the issue and workaround might be interesting to share.

Key takeaway:  Host variables in an ORDER BY clause of an embedded SQL query that is inside of a method don't work as expected. IRIS versions starting with 2020.1 are affected. As a workaround, add the host variable to the Method's PublicList list and "new" them so the embedded query has access to them.

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%'

Application to import 12 Datasets along with 43 tables dynamically by using  LOAD DATA command which loads data from a source into an IRIS SQL table. 
List of Datasets