The forth in the trilogy, anyone a Hitchhikers Guide to the Galaxy fan?
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.
If the above does not sound familiar to you please start at the beginning with the following:
Looking to breathe new life into an old MUMPS application? Follow these steps to map your existing globals to classes and expose all that beautiful data to Objects and SQL.
Date range queries going too slow for you? SQL Performance got you down? I have one weird trick that might just help you out! (SQL Developers hate this!)*
If you have a class that records timestamps when the data is added, then that data will be in sequence with your IDKEY values - that is, TimeStamp1 < TimeStamp2 if and only if ID1 < ID2 for all IDs and TimeStamp values in table - then you can use this knowledge to increase performance for queries against TimeStamp ranges. Consider the following table:
As we all know, Caché is a great database that accomplishes lots of tasks within itself. However, what do you do when you need to access an external database? One way is to use the Caché SQL Gateway via JDBC. In this article, my goal is to answer the following questions to help you familiarize yourself with the technology and debug some common problems.
Given a complex method flagged with [ SqlProc ] so it is available as an SQL stored procedure, what's the best way to report a non-system error detected in that method - say, for example, an error %Status - so that the SQL query calling it fails descriptively? Is it best to create and throw an exception, or are there special % variables involved (like in a trigger)? I haven't been able to find an answer in the documentation.
Class Queries in InterSystems IRIS (and Cache, Ensemble, HealthShare) is a useful tool that separates SQL queries from Object Script code. Basically, it works like this: suppose that you want to use the same SQL query with different arguments in several different places.In this case you can avoid code duplication by declaring the query body as a class query and then calling this query by name. This approach is also convenient for custom queries, in which the task of obtaining the next row is defined by a developer. Sounds interesting? Then read on!
Presenter: Anton Umnikov Task: Identify your slowest SQL queries and tune them for better performance Approach: Use InterSystems’ query profiling and analysis tools. Discuss how system configuration can affect performance
This session will show you how you identify the weakest link in your application SQL and introduce you to the fine art of tuning those queries. To do this we will take a look at InterSystems query profiling and analysis tools, as well as how system configuration can impact SQL performance.
Problem: Obscurity on how our SQL engine works
Content related to this session, including slides, video and additional learning content can be found here.
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.
The Caché System Management Portal includes a robust web-based SQL query tool, but for some applications it’s more convenient to use a dedicated SQL client installed on a user’s PC.
SQuirreL SQL is a well known open source SQL client built in Java, which uses JDBC to connect to a DBMS. As such, we can configure SQuirreL to connect to Caché using the Caché JDBC driver.
Have some free text fields in your application that you wish you could search efficiently? Tried using some methods before but found out that they just cannot match the performance needs of your customers? Do I have one weird trick that will solve all your problems? Don’t you already know!? All I do is bring great solutions to your performance pitfalls!
As usual, if you want the TL;DR (too long; didn’t read) version, skip to the end. Just know you are hurting my feelings.
This is a sample Ensemble/Health Connect production which demonstrates how to receive an HL7 order (ORM) inbound from a file, extract fields (in this case, basic demographic information), and insert those into a table in an external SQL database via ODBC.
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.
If the above does not sound familiar to you please start at the beginning with the following:
So just this morning we started to get this error from all of our SQL-client based tools that connect to our Caché SQL via ODBC:
[%msg: <Error instantiating cached query class in Prepare(): <METHOD DOES NOT EXIST>Prepare+41^%SYS.SQLSRV *%New,%sqlcq>] (State:S1000, Native Code: 190)]
As Bill has mentioned earlier in his post, we have carefully reviewed the JSON capabilities and made some adjustments to ensure they deliver the best benefit to you. In this post, I am going to describe the modifications in more detail and provide guidance for you to understand the implication for your code base.
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
I have been following the online Zen Quickstart Tutorial using the lastest release documentation. In addition to playing around with the styling and making a few minor functionality tweaks, I wanted to add an additional column that shows a count of the number of phone numbers for that Contact (as shown in the image below).
Embedded SQL is a tool that allows us to execute SQL statements in Caché Object Script. For example, to select the name of a person with a particular SSN from the Sample.Person class we can do the following:
So, I played with row level security and was unable to find a way to get to work it dynamically. I want to determine if the user can access the row in runtime based on a state of external system, but it seems that row level access is calculated during INSERT/UPDATE and stored in %RLI index. Is there a way to achieve runtime access calculation?
The purpose of this post is to ask for everyone's thoughts and input around the use of transient, SQLCalculated/Computed properties within persistent classes.
This approach allows for extra data values needed in SQL queries to be available without having to join to other tables.
Very simple/basic example to illustrate the topic:
Let's say I have a persistent class named ICDAutoCodeDefn to hold ICD Auto-Coding definitions, such as: