SQL

Syndicate content 26 

Order is a necessity for everyone, but not everyone understands it in the same way
(Fausto Cercignani)

Disclaimer: This article uses Russian language and Cyrillic alphabet as examples, but is relevant for anyone who uses Caché in a non-English locale.
Please note that this article refers mostly to NLS collations, which are different than SQL collations. SQL collations (such as SQLUPPER, SQLSTRING, EXACT which means no collation, TRUNCATE, etc.) are actual functions that are explicitly applied to some values, and whose results are sometimes explicitly stored in the global subscripts. When stored in subscripts, these values would naturally follow the NLS collation in effect (“SQL and NLS Collations”).

Last comment 10 June 2016
0 0
1534

views

+ 9

rating

Hello,

We are sometimes asked for examples of how to do 3rd party SQL database selects/updates/inserts/stored procedures from an Ensemble SQL Business Operation.

In the attached file there are 4 sample Ensemble Operations that demonstrate four different types of transactions with an external SQL DB:

1:  The "CheckExists" shows how to use an Operation to do a select statement to see if a given patient MRN exists in a DB
2:  The ExecSP Operation shows how to execute a stored procedure from a BO
3:  the Update Operation does an Update
4:  the Insert Operation does an Insert

Given the samples, one can combine updates/inserts/SPs/selects into a single operation as needed.

 

ensemble_sql_operation_examples_0.zip

Last comment 8 June 2016
0 4
952

views

+ 5

rating

Recently I was asked by a customer with this question. In MS SQL Server, there is a function called host_name() that will return the work station name.

Here is how I would do it in Caché :

With default SQL schema name, in my case SQLUser in SQL (which is equivalent as User in Caché class definition), I have a class called User.dummy, I added a classmethod called hostname and expose it as SQL function host_name:

Last answer 2 June 2016 Last comment 3 June 2016
0 2
611

views

0

rating

In MySQL I have the following table:

CREATE TABLE `info` (
   `created` int(11)
);

And it is linked (via JDBC SQL Gateway) to Cache table mysql.info.  `created` field stores unix timestamp. So when I execute  this SQL in SMP:

SELECT created FROM mysql.info

I receive the following output (which is expected):

created
1435863691
1436300964

But I want to to display `created` field converted to ODBC timestamp format. To do that I call this SQL procedur

Last answer 19 May 2016 Last comment 19 May 2016
0 0
484

views

0

rating

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

 

now                  latest               difference  
-------------------  -------------------  ----------  
2016-05-13 11:05:31  2016-05-13 08:51:16  9945
2016-05-13 11:08:16  2016-05-13 08:51:16  9780    
2016-05-13 11:08:36  2016-05-13 08:51:16  9760 

Last answer 13 May 2016 Last comment 13 May 2016
0 1
355

views

0

rating

Hi,

If you want to import data from a mySQL export file (exported with mysqldump), you will find here a little script that could help.

Only the INSERT commands in the sql file are executed into Caché. Indices are not computed for better performance.
%NOINDEX, %NOCHECK and %NOLOCK are generated on each INSERT line.

Currently, the file can not contain a "),(" pattern inside the values part of the INSERT command. If this is the case, the line is skipped. This feature may be implemented in the extractValuesList method.

Feel free to improve it as needed. Unfortunately it's not guaranty bug free for the moment!

mysql.tools.xml_0.zip

Last comment 13 May 2016
0 2
225

views

+ 2

rating

On the class reference %Library.GlobalCharacterStream is marked as deprecated in favor of  %Stream.GlobalCharacter.

How I can migrate the data from class properties defined as %Library.GlobalCharacterStream to the new one %Stream.GlobalCharacter? 

Does the %Stream.GlobalCharacter have the same SQL support from %Library.GlobalCharacterStream?

Thanks.

Last answer 2 May 2016 Last comment 4 May 2016
0 3
560

views

+ 1

rating

I have two servers in our TEST environment.  One with HSBUS and one with HSREGISTRY.  Let's call them "HSBUS01" and "HSDB01" respectively.

 

Now, I can access HSBUS with my SQL client by using the JDBC driver pointed to hsbus01.full.name.com and port 1972 and specifying a namespace of "HSBUS".  I am, however, unable to access the HSREGISTRY instance via SQL with a similar combination of information.

I have access to the web interface on both servers (/csp/sys/#CSP.Portal.Home.zen).  What screen can I use to compare the relevant settings?  I think it might be a port issue.  Where would I find that?

Last answer 28 April 2016 Last comment 29 April 2016
0 3
208

views

0

rating

I have to disect thousands upon thousands of strings like this:

96842-2-AEV.00001-63561-001958-100728760I-42

I've been able to pull out the first, second and final segments as separated by hyphens, but now I have a predicament where I need to use the second segment, the 2 shown in the example above, in a join to another table.  Problem is Cache apparently doesn't like doing that.  Just the same, I have to find a way.

Here's the code I'm using to pull that segment out into as separate field in the query results:

substring(prov_record_id, charindex('-', prov_record_id) +1, charindex('-AEV.', prov_record_id) - charindex('-', prov_record_id) - 1

Last answer 28 April 2016 Last comment 28 April 2016
0 1
520

views

0

rating

Hello,

 

In my project, I am calling a store proc on third party database. I am using SQL Gateway and ODBC. Everything is working fine until the store proc changed.  In store proc they have changed the return alias name for a column. In Ensemble Snapshot I am not seeing the modified alias name until I restarted the whole Healthshare instance.

 

Here is my question, is there any other way? We do not want  to take the instance down if it happens after we go live.

 

Thanks

Raghu

Last answer 21 April 2016
0 1
0

comments

307

views

0

rating

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? 

 Class Util.MySequence Extends %Persistent [ ClassType = persistent, ProcedureBlock ]
{


Property dummy As %String;

Property myvalue As %Integer [ Calculated, SqlComputeCode = { Set 
{myvalue}=##class(Util.MySequence).CurrentValue()}, SqlComputed ];

ClassMethod CurrentValue() As %Integer
{
        s x=$I(^MySequence)
	Quit x
}

Method myvalueGet() As %Integer [ CodeMode = expression ]
{
..CurrentValue()
}

}

 

Thanks.

 

 

Last answer 19 April 2016 Last comment 19 April 2016
0 1
333

views

0

rating

Presenter: Andreas Dieckow
Task: Apply SQL security to multiple servers in a distributed system
Approach: Provide code samples for using new API calls to apply SQL security statements to multiple instances of our products
 

Description: The requirement that started at all. See examples on how to use this new feature and integrate it into your application by discuss code examples.

Problem: SQL Security is local to the instance and most of time driven by customer application code. That it is only local to the instance and is not automatically going to other instances requires a solution.

Solution: With application code use new API calls to issue SQL security statements that is applied to multiple instances.

 

Content related to this session, including slides, video and additional learning content can be found here.

Last comment 14 April 2016
0 2
115

views

0

rating

Presenter: Anton Umnikov
Task: Run SQL queries quickly on huge amounts of data
Approach: Use a shared-nothing architecture to leverage a cluster of small, cost-effective servers
 

Explains how a shared nothing architecture allows you to leverage a cluster of small, costeffective servers to serve query results that would have taken much longer on a single, big and expensive box. This brings affordable horizontal scalability.

 

Content related to this session, including slides, video and additional learning content can be found here.

0 1
0

comments

173

views

+ 1

rating

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.

0 2
0

comments

124

views

0

rating

If I have defined a class query in one of my classes and I want to use that query from a method of another class, what are the pros and cons of using the %SQL.Statement interface versus the %Library.ResultSet interface?

I believe %SQL.Statement is the newer interface.

So if the old way is:

USER>s rs=##class(%Library.ResultSet).%New("%Library.File:FileSet")
 
USER>s sc=rs.Execute("c:\s\","*.txt")
 
USER>w sc
1
USER>while rs.%Next() {w !,rs.Data("Name")}

...

then the new way is

Last comment 14 March 2016
0 9
809

views

+ 2

rating

Hello! 

I'm trying to run a simple SQL query in atelier but it complains about the manager.  I'm not sure if this is the best way to do it but here is what I got so far:

 



	ZNSPACE "User"
	
	SET domId = 1
	SET flister=##class(%iKnow.Source.SQL.Lister).%New(domId)
	SET myloader=##class(%iKnow.Source.Loader).%New(domId)
 
 
	SET myquery="select top(10) EntityValue, CorpusFrequencyAsRelation, CorpusSpreadAsRelation from %iKnow_Objects.EntityUnique  WHERE DomainId = 1 ORDER BY  CorpusFrequencyAsRelation desc"
	SET idfld="EntityValue"
	SET grpfld="CorpusSpreadAsRelation"
	SET dataflds=$LB("CorpusSpreadAsRelation")

	SET stat=flister.AddListToBatch(myquery, idfld, grpfld, dataflds)	
		IF stat '= 1 {WRITE "The lister failed: ",$System.Status.DisplayError(stat) QUIT }
	SET stat=myloader.ProcessBatch()
		IF stat '= 1 {WRITE "The loader failed: ",$System.Status.DisplayError(stat) QUIT }


And the error I get

Last comment 14 March 2016
0 7
0

answers

204

views

+ 1

rating

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. Each call can take seconds where the message input is taking milliseconds. This starts to impact the operation queue as the soap calls can be blocked by the locks. The Ensemble operation is waiting on the web service to return.

Last comment 4 March 2016
0 5
0

answers

374

views

+ 1

rating

I am pleased to announce the next 2016.2 field test kit, 2016.2.0.595.0.

It may look like a slow week, with less than fifty changes having been checked in, but this kit includes the following fixes to problems found by you, the ones running the kits in the field:

  • ALE2845, which fixes the cachejdbc.jar version
  • JN1637, which fixes an issue that blocked debugging Atelier when used in conjunction with Kerberos
  • DLP3508, which fixes a JSON issue with $compose()

The rest of the fixes have been spread among different areas of the product, chief among them DOCUMENT Data Model, SQL and Atelier.

Please download the kit and give it a try; the latest field test of 2016.2 is available HERE.  And, as always, we welcome your feedback.

Steve Glassman, Director of QD

0 3
0

comments

136

views

+ 3

rating

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...

There is a beta build available with some new features: A main new feature in this build is called Query Cloud. With this feature you can write SQL Statements across multiple Caché Servers; Namespace and combine (SQL JOIN!) this data with other datasources like SQL Server; MS Access or simple CSV files.

Last comment 18 February 2016
0 1
1395

views

+ 3

rating

Here is my original query:

SELECT EventType, InitiatedAt, COUNT(*) as cnt
FROM HS_IHE_ATNA_Repository.Aggregation
WHERE EventType = 'LOGIN'
AND LocalDateTime > '2016-02-16 11:00:00'
GROUP BY EventType, InitiatedAt

This gives me data like this:

LOGIN%SYSTEM69918
LOGINOTHER39


However, I need to get the data back as two columns with all but the last concatenated and delimited, more like this

Last comment 17 February 2016
0 4
0

answers

799

views

0

rating

What do you do if you want to have the ID field have a meaningful name for your application? 

Sometimes it comes to pass that when you're making a new table that you want to have the unique row identifier (a.k.a. IDKEY) to be a field that has a name that is meaningful for your data.  Moreover, sometimes you want to set this value directly.  Caché fully supports this functionality and it works   Suppose you have a class Test.Kyle.  The data will be stored like so:

^Test.Kyle(IDKEY)=$LB("",Field1,Field2,...,Fieldn)

Any other field can be unique, but the IDKEY is the one that is the subscript for all the data.  There are two ways to create a named IDKEY.

  1. Property KyleID as %Integer [Identity];
  2. Property KyleID as %Integer;

             Index IDKEYIndex on KyleID [IDKEY]

Last comment 3 February 2016
0 5
241

views

+ 3

rating

Attached to this post is a PDF document outlining some of the key enhancements included with 2016.2. I will be giving a WebEx session that is open to all tomorrow at 11 AM EST. Once the WebEx is over I will be adding a link to the recording for those who cannot attend.

2016.2 Field Test Launch
Tuesday, February 2, 2016
11:00 am  |  Eastern Standard Time (New York, GMT-05:00)  |  30 mins
 
Meeting number:    747 673 229

Join!

Join by phone
Call-in toll-free number: 1-866-8576852  (US)
Call-in number: 1-636-6925389  (US)

You can look up global numbers by country for those dialing in from outside the US here:

https://www.tcconline.com/listNumbersByCode.action?confCode=5568713526


Conference Code: 556 871 3526

Here is the link to the recording

0 6
0

comments

194

views

+ 4

rating