#Databases

Kindly help me on the below points

1)Concatenate the Date column and Time column.

2)Get the current date only without time.

3)Find the current age using DOB column.

Hi team,

I'll start with an apology as I am trying to wrap my head around the architecture of how InterSystems IRIS database management works. I am attempting to connect to the platform remotely through say a JDBC or ODBC connection in order to run queries, searches (through SQL statements) on my laptop and was trying to understand whether this would be possible? It is possible to setup an inbound client connection and wanted to better understand the architecture of how the database association works for IRIS database management. Does it use it's own internal SQL database or are we able to connect to our own database and which databases are certified to run against the platform?

Hello everyone,

I want to restore a database from an external backup with the journal files.

The manual says I should (in short):

1. Stop journaling with ^JRNSTOP
2. Restore the database file IRIS.DAT
3. Run the journal restore utility with ^JRNRESTO
4. Restart journaling ^JRNSTART

Since I want to restore one database only step 1 would disable journaling for the whole instance, not only for the database to be restored. As I understand, it would be impossible to restore a different database if there occurs a problem while journaling is disabled during the recovery process?

Can I please check if anyone know why the data base resource is created differently when creating a namespace after specifically selecting it to use the default resource %DB_%DEFAULT?

I created a namespace and when creating the DB I selected the option to use the default resource

When the namespace creation was complete , database is using a new resource instead of the default. 

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.

Hey Developers,

Join us for a discussion guide of current operational tasks to reduce storage and an overview of new features coming in InterSystems HealthShare 2021.2, including stream, database, and journal compression abilities:

⏯ InterSystems HealthShare Storage Footprint Reduction

This formation, accessible on my GitHub, will cover, in half a hour, how to read and write in csv and txt files, insert and get inside the IRIS database and a distant database using Postgres or how to use a FLASK API, all of that using the Interoperability framework using ONLY Python following the PEP8 convention.

This formation can mostly be done using copy paste and will guide you through everystep before challenging you with a global exercise.
We are available to answer any question or doubt in the comment of that post, on teams or even by mail at lucas.enard@intersystems.com .

I am sure I came across this in the past with Cache and just saw this again in IRIS.

When rebuilding or swapping a DAT file for a database it retains the Resource of the DAT file, not the Resource of the Database it is being used for.

For instance, if I have a local Database called APP with a resource %DB_APP and I want to refresh the data from another Database called TEST that has a Resource %DB_TEST I can just copy the DAT file from the TEST folder to the APP folder.

No Database settings are changed and it is all done with either IRIS down or the databases dismounted.

Hi 

Does anyone know where the user generated code resides, the stuff one might write for a production,  I presume it lives in a table somewhere within a database?

Basically, I want to do some dependency analysis of what I've written - find out what's in use, how it's referenced, etc. I know  I can export it to an XML file and work on that, but direct access on a table would be quicker for me.

Cheers

Hi everyone, 

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

Any example much appreciated.

Cheers,

Tom

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.MrnLookup [ Not ProcedureBlock ]
{ ClassMethod MrnLookup(pp01 As %String(MAXLEN=8), ByRef mrn As %String(MAXLEN=20)) As %Integer [ ProcedureBlock = 1, ReturnResultsets, SqlName = MrnLookup, SqlProc ]
{
    if '$isobject($g(%sqlcontext)) s %sqlcontext=##class(%Library.ProcedureContext).%New() s RETURNVALUE=$g(RETURNVALUE)
 s pp01=$g(pp01)
 s mrn=$g(mrn)
 s hdbc=$$GetConnection^%apiGTW("MRNLookup")
 i hdbc="" s %msg=$$$ERROR($$$GTWCConnectionError) s %msg=$$GetErrorText^%apiOBJ(%msg) g setError
 s QHandle=$zf(-5,%SQLGateway,5,hdbc)
 i QHandle="" s %msg=$$$ERROR($$$GTWCAllocationError) s %msg=$$GetErrorText^%apiOBJ(%msg) g setError
 s err=$zf(-5,%SQLGateway,$s(%SQLGateway("-uv"):37,1:3),QHandle,"{?=call dbo.""MrnLookup;1""(?,?)}")
 i err g getError
 s err=$zf(-5,%SQLGateway,62,QHandle,1,4,1,4,10,0,10)
 s err=$zf(-5,%SQLGateway,62,QHandle,2,1,1,12,8,0,8)
 s err=$zf(-5,%SQLGateway,9,QHandle,$lb(pp01),2)
 i err g getError
 s err=$zf(-5,%SQLGateway,62,QHandle,3,2,1,12,20,0,20)
 s err=$zf(-5,%SQLGateway,9,QHandle,$lb(mrn),3)
 i err g getError
 s err=$zf(-5,%SQLGateway,4,QHandle)
 i err g getError
 s %ROWCOUNT=$zf(-5,%SQLGateway,31,QHandle)
    set tConnection = ##class(%Library.SQLGatewayConnection).%New() set tConnection.DLLHandle = %SQLGateway, tConnection.ConnectionHandle = hdbc
    set rrs = ##class(%SQL.Gateway.ODBCResultSet).%New(tConnection,QHandle)
    while $Isobject(rrs) {
        if rrs.%GetMetadata().columnCount do %sqlcontext.AddResultSet(##class(%ResultSet.Static).%New(rrs))
        set rrs = tConnection.getNextResultSet(QHandle)
    }
 s RETURNVALUE=$li($zf(-5,%SQLGateway,22,QHandle,1))
 s mrn=$li($zf(-5,%SQLGateway,22,QHandle,3))
setError ;
 If ($g(%sqlcontext)'=$$$NULLOREF) {
 Set %sqlcontext.%SQLCODE = $g(err)
 Set %sqlcontext.%Message = $g(%msg)
 Set %sqlcontext.%ROWCOUNT = $g(%ROWCOUNT)
 }
  s err=$zf(-5,%SQLGateway,16,QHandle)
 QUIT $g(RETURNVALUE)
getError ;
 s %msg=$li($zf(-5,%SQLGateway,12,hdbc,QHandle)) g setError
} }

Hi Community,

This post is a introduction of my openexchange iris-globals-graphDB application.
In this article I will demonstrate how to save and retrieve Graph Data into InterSystems Globals with the help of Python Flask Web Framework and PYVIS Interactive network visualizations Library

Recommendation

• Read related documentations Using Globals
• Introduction to the Native SDK
• PYVIS Interactive network visualizations Liabrary

Step1 : Establish Connection with IRIS Globals by using python native SDK

 #create and establish connection
  if not self.iris_connection:
         self.iris_connection = irisnative.createConnection("localhost", 1972, "USER", "superuser", "SYS")
                                     
  # Create an iris object
  self.iris_native = irisnative.createIris(self.iris_connection)
  return self.iris_native

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é

Hi Developers!

Here're the technology bonuses for the InterSystems IRIS Globals Contest 2022 that will give you extra points in the voting:

• Data-model implementation - 5
• Functional index implementation - 5
• Data Move usage - 3
• Embedded Python - 4
• Docker container usage - 2 
• ZPM Package deployment - 2
• Online Demo - 2
• First Article on Developer Community - 2
• Second Article On DC - 1
• Code Quality pass - 1
• Video on YouTube - 3

See the details below.

Hello,

Can anyone help on the below point? It will very helpful for me.

Copy exact same data from IRIS Instance 2019.1 to IRIS 2021.1 with the Identity column from the IRIS database.

Thanks,

Harshdeep Acharya

I am using the 

import intersys.pythonbind3 as pyb

conn     = pyb.connection()
conn.connect_now(...)
    
db      = pyb.database(conn)
qry        = pyb.query(db)
obj        = pyb.object(db)

for connection to the cache instance

However when I run this class method, The error message  indicates the method does not exist:

print(db.run_class_method("SYS.Database","GetStatus",[]))

intersys.pythonbind3.cache_exception: file=intersys/pythonbind3.c line=3034 err=-1000 message=<METHOD DOES NOT EXIST> 148 runMtdLow+22^%SYS.BINDSRV GetStatus,SYS.Database

Dear people,

I (really) spent hours on finding the maximum size a Global is allowed to be (for Windows, if that matters). All I seem to run into are database sizes (derived from a max number of blocks and block size), but I refuse to believe that is correct because too small to be realistic.  

It will  undoubtedly my fault that I don't understand or misread the concepts of "database" vs Globals, envisioning files as such while one set of functional Globals in one file (?

Found one interesting behaviour in one system with Ensemble. Some Request class has a property with type %XML.CharacterStream by design, this class is the heaviest request in the system, and with profiling journal files, it got about 40% of the file. When I counted all the sizes of such streams per one day and found that the real stored data is three times less. After deep inspecting the journal file, I found the first save of stream data, then removing previously stored data, and saving again and there were no reasons to change the content of the stream, it should be stored only once.

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.

Consider a Natural Key with an Identity (Serial) field.

I cannot seem to acquire the generated value after persisting my entity. That is, the returned entity by Spring Data's "save" does not have the generated value.

The value is generated by the database, and I can query it after repository.save(entity).

I have done some testing and created a Github repo with it...

https://github.com/jesjobom/iris-hibernate-key-test
Am I missing something?

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 Community,

This post is a introduction of my openexchange iris-python-apps application. Build by using Embedded Python and Python Flask Web Framework. 
Application also demonstrates some of the Python functionalities like Data Science, Data Plotting, Data Visualization and QR Code generation.

 Features

•  Responsive bootstrap IRIS Dashboard

•  View dashboard details along with interoperability events log and messages.

•  Use of Python plotting from IRIS

•  Use of Jupyter Notebook

•  Introduction to Data Science, Data Plotting and Data Visualization.

•  QR Code generator from python.

Hello Programmers,
I'm trying to use Intersystems IRIS Database with
Quarkus, but I'm getting problems making it work
does anyone knows how to set up the Intersystems IRIS Database
on Quarkus ?

I did that on my pom.xml  and also I added the jar
hibernate-iris-1.0.0.jar and intersystems-jdbc-3.2.0.jar but it's not working 

<dependency>
            <groupId>com.intersystems</groupId>
            <artifactId>intersystems-jdbc</artifactId>
            <version>3.2.0</version>
            <scope>system</scope>
            <systemPath>${project.basedir}/src/main/resources/intersystems-jdbc-3.2.0.

Hi, the class %Library.Storage has several methods such as  %BuildIndices(pIndexList As %RawString = "" ...), %ValidateIndices(idxList As %List = ""...), ... which take as a parameter (amongst others) a list of indices (in the example, the signature of the list is not the same but I guess it does not matter).

My question is: can get this list in code ?

Hi Folks,

I created a persistent class as below 

Class myclass.DataBase Extends %Persistent
{

Property ID As %String;

Property Skill As list Of myclass.SerialTablelist;
}

and Created another Serial class as 

Class myclass.SerialTablelist Extends %SerialObject
{

Property PSkill As %String;

Property OSkill As %String;
}

Now I will save the id as below

do rs.Prepare("Insert into myclass.DataBase(ID)VALUES(?)")
do rs.Execute(ID)

I am facing issue with the AS LIST OF 

Could any one guide me how to save the Skill (PSkill ,OSkill ) using resultset (I am able to )

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

Astronomers’ tools

5 years ago, on December 19, 2013, the ESA launched an orbital telescope called Gaia. Learn more about the Gaia mission on the official website of the European Space Agency or in the article by Vitaly Egorov (Billion pixels for a billion stars).

However, few people know what technology the agency chose for storing and processing the data collected by Gaia. Two years before the launch, in 2011, the developers were considering a number of candidates (see “Astrostatistics and Data Mining” by Luis Manuel Sarro, Laurent Eyer, William O’Mullane, Joris De Ridder, pp. 111-112):

• IBM DB2,
• PostgreSQL,
• Apache Hadoop,
• Apache Cassandra and
• InterSystems Caché (to be more precise, the Caché eXTreme Event Persistence technology).

Comparing the technologies side-by-side produced the following results (source):

The first four will probably sound familiar even to schoolchildren. But what is Caché XEP?

Hey Developers,

It's time to announce the winners of the InterSystems Datasets contest! Are you ready?

A storm of applause goes to these developers and their applications:

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