The ID of the last updated record can be obtained using the SQL function LAST_IDENTITY(). * This function can be used with embedded SQL or ODBC but not with Dynamic SQL, SQL Shell, or the Management Portal's SQL interface.
Here's an example of usage with simple Embedded SQL:
How to read the HL7 V2.5 messages irrespective of message type to Iris database ?
Source system sends HL7 messages of multiple message types to Intersystems through TCP and these messages are to be inserted/read into iris SQL database.
I've asked a lot of questions leading up to this, so I wanted to share some of my progress.
The blue line represents the number of messages processed. The background color represents the average response time. You can see ticks for each hour (and bigger ticks for each day). Hovering over any point in the graph will show you the numbers for that period in time.
This is super useful for "at a glance" performance monitoring as well as establishing patterns in our utilization.
I am inserting rows in a table. This table is appearing in all namespace as I did global mapping.
So once I run insert command from a method, it insert the rows. When I run the same insert command from other namespace, it replace the existing data in table.
Insert command is same in all namespace but the data I m inserting is different.
when I look into the "sql statements" ( sql page of the management portal ) I have lots of old statements with a lot of them frozen. many of them are %sqlcq routines (I believe these are the SLQ's I run when testing/running random SQL queries in the sql page. )
I can clearly see where I can click on an individual query and untick an individual query to unfreeze it. or delete it. but thats a painful slow process. I can't see anywhere to delete/change multiple queries
I need a help in the following error I am getting while running the SQL Query. I am running the SQL Query from different table using inner join. I run this everyday for last 90 days but for few days while extracting the data through this query I am getting this error. Please assist.
SQLException was thrown: Incorrect list format: 0 >= 0_SQLSTATE: S1000
SQLException was thrown: Incorrect list format: 0 >= 0_SQLSTATE: S1000
There is a Link Procedure Wizard option within the Management Portal (System > SQL >Wizards > Link Procedure) which I had reliability issues with so I decided to use this solution instead.
I'm currently running into a very weird issue to where I am trying to connect with a 64 bit version of SQL Server Management Studio (SSMS) to a HealthShare instance. I have created a System DSN using the Drivers (image below) that were downloaded with the Client version of the install and I'm able to successfully connect using my credentials.
Caché Monitor is a database\sql tool primarily for InterSystems Caché but can also connect to MS SQL Server, MS Access and more databases. Within Caché Monitors Server Navigator you see all available Namespaces on your Caché Servers. No need to know the name of the Namespace, no need to configure many many JDBC Connections by hand. Just click on the namespace and see all objects like tables, views, classes and more...
If you are looking to breathe new life into an old MUMPS application follow these steps to map your globals to classes and expose all that beautiful data to Objects and SQL.
This example is going to cram in 4 or 5 different things beyond what was covered in Part 1
Is this a bug? We came across it on an older version of Cache but I've also just tried it in version "Cache for Windows (x86-64) 2017.2.2 (Build 865U)" and got the same result.
I need to select my result into a list and be able to loop through the list when query finished any help appreciated here is where I am
##sql(SELECT %ID INTO :IDArray() FROM MergeHyland.TypeTwoDimesionCollection WHERE GUID = :Key AND EndDate IS NULL)
for I=1;1:$LISTLENGTH(IDArray)
{
w $Data(IDArray),i
}
I have a code block in a BPL. I have the below SQL and code. All variables have been declared and set. When I run the SQL, if there are no values returned, I get 100 for the value of SQLCODE when I perform the FETCH, which is correct. If values are returned, I get a -400 error when I perform the first FETCH. I've investigated, but cannot find the reason for a -400 error. Hoping someone out here understands this and knows what's wrong.
When for any particular reason I need to update a record and don't want to pull the triggers, the keyword %NOTRIGGER can be used. But I've been trying to do the same when I change the row using the object approach, but I can't find it. Anyone knows if it's possible to avoid pulling triggers when working with objects?
Sadly, the utility "DISABLE^%NOTRIGGER" doesn't seem to exist ;-)
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!
I am using ExecDirect() method to get SQL resultset of type %SQL.StatementResult.
now I am fetch a column something like this rs.%Get("Amount") but if amount column is empty then it is return me empty string. If There is any way to set something like this in IRIS rs.%Get("Amount",0.00) if that is possible.
I get the following error when I am trying to create a linked server connection via MS OLE DB Provider for ODBC Drivers in MS SQL Server Management Studio. The message I get is:
The OLE DB provider "MSDASQL" for linked server "IRIS" reported an error. The provider did not give any information about the error. Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "IRIS". (Framework Microsoft SqlClient Data Provider)
In the past, I've created custom SQL operations, but now I had something trivial to do, so I decided to take EnsLib.SQL.Operation.GenericOperation out for a spin. There's no example in the docs, so it was a little tricky. Here's what I ended up doing:
In my external database, I have 'mytable' with two fields 'id1' and 'id2'. Here are the pertinent Business Operation settings:
The FHIR standard establishes a powerful but flexible data model that can smoothly adapt to the complexities of operational healthcare data management. This flexibility comes at the cost of a data model with many tables and relationships, even for simple data such as the patient's record of telephone numbers, addresses, and emails. It would easily require querying 4 different tables. However, FHIR SQL Builder eliminates this problem, allowing you to create visual projections (mappings) in web wizards.
I have an IRIS persistent class with a %Stream property whose value is a JSON object. I'd like to use a SQL trigger to pull some value out of the JSON and persist it in another property, for easy querying and indexing. See below for a minimal example:
Let's say we have two serial classes, one as a property of another:
Class test.Serial Extends %SerialObject
{
Property Serial2 As test.Serial2;
}
Class test.Serial2 Extends %SerialObject
{
Property Property As %String;
}
And a persistent class, that has a property of test.Serial type:
Class test.Persistent Extends %Persistent
{
Property Datatype As %String;
Property Serial As test.Serial;
}
So it's a serial, inside a serial, inside a persistent object.
I'm trying to create an sal connections from one cache system to another. I've created the connection part and I think that is working. When trying to link a table through the link table wizard, I'm getting an error "<MAXSTRING>zCreateOneLinkTable+52^%CSP.UI.SQL.LinkWizardResultPage1.
Is this due to my global block size in the new system not being large enough? It is currently set at 8192.
By replies
.png)
.png)
.png)