#SQL

I have a MySQL server with "posts" table.

I also have a Caché server with "downloadedposts" table. 

They are connected from Caché to MySQL via SQL Gateway

I want to keep Caché table synced with MySQL one  (MySQL "posts" table is a master copy), so periodically Caché queries MySQL server and downloads data. So far so good, and if a record appears or changes in MySQL table, Caché downloads the changes.

The problem I'm encountering is that sometimes rows would be deleted from  MySQL "posts" table.

How do I synchronize deletions?

Recently I have been posting some updates to our JSON capabilities and I am very glad that so many of you provided feedback. Today I would like to focus on another facet: Producing JSON with a SQL query.

Hi -

I'm trying to rewind a cursor back to the first row after looping part of the way through the implied result set, but I'm not finding a way to make this happen, is there some such iterator variable or directive that I can leverage to accomplish this?

I could code around it by pulling identifiers and/or values into a local array, and then hand code up an iterator over my local results copy, but this feels like a "redesigning of a wheel" approach, and I thought I would check before I start down this path.

Thanks

Introduction

The field test of Caché 2016.2 has been available for quite some time and I would like to focus on one of the substantial features that is new in this version: the document data model. This model is a natural addition to the multiple ways we support for handling data including Objects, Tables and Multidimensional arrays. It makes the platform more flexible and suitable for even more use cases.

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

Hello, is there a way to reset all of the Sample Classes back to their "Installed State."? In other words, delete all the other classes I have put into the samples namespace and leave only those classes that were there when I installed the instance.?

-thank you

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.

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:

/// 
Class User.Dummy Extends %Persistent [ ClassType = persistent, DdlAllowed, ProcedureBlock, SqlRowIdPrivate, SqlTableName = dummy, StorageStrategy = "" ]
{
Property f1 As %Library.

I have an sql that is refusing to play fairly.

I have

• tuned affected tables
• made sure I'm on the latest version (2016.1)
• got enough global buffers
• tried forcing the sql to use outliers correctly

Still nothing.

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.

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.

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.

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.

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.

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

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.

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.

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.

Presenter: Kyle Baxter
Task: Upgrade to a new version of Caché without concerns about slowing existing SQL queries
Approach: Use the new Frozen Plans feature in Caché
 

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

Stream datatypes are not displayed when you execute SQL (in SMP for example).

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:

USER>s oStmt=##class(%SQL.Statement).%New()
 
USER>s sc=oStmt.

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.

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.

In MSSQL I think you can do something like this:

select *
from HS_IHE_ATNA_Repository.COLUMNS
where TABLE_NAME='Aggregation'

How can I do this in Cache SQL?

[%msg: < Table 'HS_IHE_ATNA_REPOSITORY.COLUMNS' not found>]

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.

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

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:

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

I tried this:

SELECT EventType + ';' + InitiatedAt as k, COUNT(*) as cnt
FROM HS_IHE_ATNA_Repository.

Looking at a property defined as follows:

Property SystemTime As %Library.TimeStamp [ SqlComputeCode = {Set {*}=$ZDATETIME($NOW(),3,1,0)}, SqlComputed ];

The documentation talks about using SqlComputed and SqlComputeCode with SqlComputeOnChange (specifying which events will trigger computation), and about using them with the Calculated keyword (so it's always computed). I don't see any specific explanation of the case above, though, when neither SqlComputeOnChange nor Calculated is specified.

Hi,

Do you know how to export SQL errors to a text file from a terminal session in Caché?

Thanks

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