New post

#SQL

14

SQL is a standard language for storing, manipulating and retrieving data in relational databases.

All time

What is the recommended approach to handle the following scenario: Large number of HL7 messages are serviced via Ensemble. Those messages are then transformed and routed to an Operation which calls a web service which does a number of different activities. If will do address normalization, location geo coding, MPI lookup and resolution, and insert into a SQL Server database to stage for an analytics product. Unfortunately all of this work takes time and the first thing that happens in the web service is a lock taken out on the Patien in SQL Server.

1 2
0 685
Question
· Nov 5, 2017
How to avoid dirty read

I get two methods below: I would run both methods concurrently.

However, the "testRead" would always read the uncommitted results from "testInsert".

Anyway to avoid that? Thanks.

ClassMethod testInsert()
{
  &sql(START TRANSACTION ISOLATION LEVEL READ COMMITTED, READ WRITE)
  &sql(insert into Test.Table(AttrA,AttrB,AttrC,AttrD) values(1,2,3,4))
  hang 15
  &sql(ROLLBACK)
}

ClassMethod testRead()
{
  &sql(START TRANSACTION ISOLATION LEVEL READ COMMITTED)
  &sql(select count(*) into :ans from Test.Table)
  &sql(COMMIT)
  w !,ans
}
0 5
0 680

DataGrip is a multi-engine database environment targeting the specific needs of professional SQL developers, DataGrip makes working with databases an enjoyable and productive experience.

To work with InterSystems IRIS from DataGrip you'll need to add InterSystems JDBC driver first (once per DataGrip) and after that add all your InterSystems IRIS connections.

Part 1: Add InterSystems IRIS JDBC Driver

1. Go To File → DataSources

1 0
0 676

I came across How to import a tab separated text file into a SQL table programmatically?, which appears to indicate that I can pass the filename and all records will be imported. However, when I put use the Import method only a single record gets imported. However, all records are imported if I use the management console to import the file by selecting it and choosing the options.

Has anyone ran into this in the past?

0 3
0 666

Hello! So, my knowledge on the Cache database is extremely limited, and I was hoping I could find some assistance here. I'm connecting to the DB via ODBC. The table(s) I'm interested in are named as such nameYYYYMMDD. So each day, a new table is created with logs. We'd like to grab these records each day, for the previous day's logs.

0 2
0 666

I have a serial object:

Class EmbedObj Extends %SerialObject

which is stored as a property of another object

Class ContainerObj Extends %Persistent

Property InnerObj As EmbedObj;

Property Foobar As %String;

Question:

From within the context of an instance of EmbedObj, how can I navigate to the containing instance of "ContainterObj" and find that value of its Foobar property?

0 8
0 652

I have a table, with autoincremented id

CREATE TABLE users (
    id SERIAL NOT NULL,
    name VARCHAR(30) NOT NULL,
    PRIMARY KEY (id)
)

I can add a new item there with an explicit id 

INSERT INTO users (id, name) VALUES (2, 'fred')

And while my id is autoincremented, I can omit it

INSERT INTO users (name) VALUES ('ed')

So, this time, I don't know the id, and I want to somehow get it.

I could do it with LAST_IDENTITY() function, but it just uses %RowID, and have no relation to the primary id

0 11
0 645

This is a bit weird. I'm trying to use `DATEDIFF()` to calculate the time since the last message on an interface. For some reason, when no messages are received, the number steadily decreases. This is the opposite of what should happen. Here is my current query and a few sequential result sets:

SELECT getDate() as now, max(TimeCreated) as latest, DATEDIFF (s,getdate(), max(TimeCreated)) as difference
FROM EnsLib_HL7.Message
0 4
0 651

I am working on a BPL to take data from a MS SQL database and create an HL7 Materials Message for our EMR. I have done this plenty of times in the past however I am running into an error.

"Remote Gateway Error: JDBC Gateway SP execute(0) error 0: Access to the remote server is denied because no login-mapping exists."

What is confusing is that this BPL doesn't differ from any of my other BPLs in connecting to MS SQL Server. I know I am missing something..

0 3
0 648
Article
· Nov 8, 2016 4m read
Introduction to Outlier Selectivity

Beginning in Caché 2013.1, InterSystems introduced Outlier Selectivity to improve query plan selection involving fields with one atypical value.

In this article, I hope to use an example 'Projects' table to demonstrate what Outlier Selectivity is, how it helps SQL performance and a few considerations for writing queries.

3 1
0 629
Question
· Apr 19, 2016
SQL Sequence

Does Caché support SQL CREATE SEQUENCE as in PostgreSQL?

If not, what would be the best alternative? Create my own sequence logic as the example bellow?

0 4
0 645

Hi!

I was trying to create a query that can be exposed as a stored procedure (function actually) that would return a resultset with a random number of columns.

Unfortunately, it seems that unless I specify the ROWSPEC annotation on the Query method, I won't get any columns exposed. I was hoping to implement QueryNameGetInfo method and specify the names and number of columns I would be returning dynamically. But it seems that GetInfo information is simply ignored.

Here is my code:

1 5
0 632

Hi all,

I have an operation which uses the SQL outbound adapter to query a data source set up using a system DSN on the server which uses the Intersystems ODBC35 driver (this is a cache db hosted elsewhere).

Whenever Ensemble runs the following query:

SELECT lnkdid,
c_fastsearch_code,
c_drugfull
FROM JAC.drug_basic
WHERE c_drugfull LIKE 'Para%'

No data is returned, however running the same query via SQL Server Management Studio, all expected rows are returned.

Also if I run the following via Ensemble:

0 7
0 630

Apologies if this isn't the correct forum but here goes..

I'm accessing an Intersystems database from MS SQL Server using Intersystems ODBC driver and using OPENQUERY statement to read metadata of tables using

SELECT * FROM OPENQUERY([<linked server name>], 'SELECT * FROM INFORMATION_SCHEMA.COLUMNS')

but I get an error message saying INFORMATION_SCHEMA.COLUMNS cannot be found.

Intersystems SQL documentation does reference this table so is there some other way to access this table or is it maybe a permissions issue?

0 3
0 625

I am trying to use %INLIST in SQL query using a cursor and the query fails to return results. It appears that the variable I use against %INLIST returns an empty string. All the examples I have seen use result sets and build the query as a string. Is it possible to use %INLIST in a cursor-based query? Below is a a snippet of the code I am using:

Property mylist as %String (MAXLEN="") [InitialExpression = "route1, route2, route3"];

set routeList = $LISTBUILD(mylist)

&sql(DECLARE MyReport CURSOR FOR

SELECT

ProcessStartDate,

0 3
0 620

Hi everyone

Is there any way to change a class definition (especifically a query definition during the compilation time)?
The idea is:
I have an abstract class with a parameter where I will define the ROWSPEC of a query and some methods to populate e temporary table
The implementation class will override the parameter, specifying the ROWSPEC of this implementation, and the methods will populate the rows in the same format as the ROWSPEC.

0 4
0 619

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:

0 12
0 605

In this article, we will establish an encrypted JDBC connection between Tableau Desktop and InterSystems IRIS database using a JDBC driver.
While documentation on configuring TLS with Java clients covers all possible topics on establishing an encrypted JDBC connection, configuring it with Tableau might be a little bit tricky, so I decided to write it down.

2 3
2 553

Morning guys I have a SQL operation that I use to read data and results to a record map. But seems to have trouble saving the objects to a record map as I get this message

ERROR ErrException:
zInsertObject+2^%Library.RelationshipObject.1 -- logged as '-' number - @''

please help here is my code let me know if I missing anything thank you in advance

0 2
0 607

Case description

Let’s imagine that you are a Python developer or have a well-trained team specialized in Python, but the deadline you got to analyze some data in IRIS is tight. Of course, InterSystems offers many tools for all kinds of analyses and treatments. However, in the given scenario, it is better to get the job done using the good old Pandas and leave the IRIS for another time.

4 3
2 544