Error:

    [SQLCODE: <-400>:<Fatal error occurred>]
    [Cache Error: <<SUBSCRIPT>%0AmEdun+4^%sqlcq.HSREGISTRY.cls966.1 ^||%sql.temp(1,"")>]
    [Location: <ServerLoop - Query Fetch>]
    [%msg: <Unexpected error occurred:  <SUBSCRIPT>%0AmEdun+4^%sqlcq.HSREGISTRY.cls966.1 ^||%sql.temp(1,"")>]

Here is an example of a query that gives the error:

    SELECT COUNT(DISTINCT Criteria) as Relevance FROM HS_IHE_ATNA_Repository.Aggregation
    WHERE EventType = 'CROSS GATEWAY QUERY'

The content of the actual field is XML, but the query simply returns a number.

I have a class which defines a property as array of %String. Is it possible to index values of this property and use this property in SQL?

I have tried 'Index idx On prop(ELEMENTS)' and then a select from the generated collection table, but this is still orders of magnitude slower than queries to the containing class.

Is there a tool to get the slowests queries ?

In writing some code on:

Cache for Windows (x86-64) 2015.1 (Build 429U) Fri Feb 13 2015 14:37:23 EST

I noticed an unexpected "ROLLFAIL" error when a Unique Index fail was generated in a %Save() call.

The object SHOULD fail to save, since the Unique criteria is not met, but I didn't expect to see a ROLLFAIL error as the "Last Error".

When running the following class code:

Class BUG.Test Extends %Persistent
{

Property Field1 As %String;

Property Field2 As %String;

Index idx1 On Field1 [ Unique ];

ClassMethod Test()
{
    kill ^BUG.TestD
    kill ^BUG.TestI

    ; Should work fine
    set record1 = ##class(BUG.Test).%New()
    set record1.Field1 = "Bosco"
    set record1.Field2 = "Jones"
    set sc = record1.%Save()
    if $$$ISERR(sc) {
        do $System.OBJ.DisplayError(sc)
    } else {
        write !,"Save OK ID: "_record1.%Id()
    }

    ; Should work fine
    set record2 = ##class(BUG.Test).%New()
    set record2.Field1 = "Waffles"
    set record2.Field2 = "Syrup"
    set sc = record2.%Save()
    if $$$ISERR(sc) {
        do $System.OBJ.DisplayError(sc)
    } else {
        write !,"Save OK ID: "_record2.%Id()
    }

    ; Should fail on duplicate "unique" value for Field1
    set record3 = ##class(BUG.Test).%New()
    set record3.Field1 = "Bosco"
    set record3.Field2 = "Jones"
    set sc = record3.%Save()
    if $$$ISERR(sc) {
        do $System.OBJ.DisplayError(sc)
    } else {
        write !,"Save OK ID: "_record3.%Id()
    }
}

I would like to integrate SQL into our ObjectScript routines in order to illustrate how SQL can be used to minimize development time. However, there are two questions I have.

1. How does one iterate through the results of a SELECT statement?
2. How does one access a Table in another namespace?

I'm trying to use the standard TSQL DECLARE syntax but I get the following error:

[%msg: < IDENTIFIER expected, @ found^  DECLARE @>]

How do I declare a variable?  For instance:

i have this error

DrawTableError : <MAXSTRING> zDrawTable+349^%CSP.Util.TablePane.2

perhaps i have the possibility of Deleting all history queries, but i think better to check the table where last queries were stored and solve it by deleting wrong registries. anybody knows where is the table 'QueryHistory'.

I get this on some queries in some namespaces.  For instance, this query:

It works fine in HSBUS but in HSREG it throws the error.

Server closed communication device

Does anyone know what would cause this? Would it log something more useful somewhere?

Since now i have been working from external connection but i want to work with the SQL utility of Management Portal

i dont know how to do with several instructions like in other editors like this example

update Prod.Articulos set Alto = 1646, Ancho = 16, Fondo =  80 where CodigoNum = '100' and Empresa = 'CO'
update Prod.Articulos set Alto = 1646, Ancho = 16, Fondo =  400 where CodigoNum = '101' and Empresa = 'CO'
update Prod.Articulos set Alto = 1646, Ancho = 16, Fondo =  400 where CodigoNum = '102' and Empresa = 'CO'

Running TuneTable accounts among other the parameter named Block Count. In documentation, we see that this is an approximate numbers of 2K-blocks in which SQL-maps are stored. Databases in recent Cache doesn't support 2K-physical blocks so SQL-blocks are not physical blocks as it seems. So two questions:

- what are these blocks?

- how knowledge about count of blocks can help in SQL optimization?

Thanks for intelligent answer to stupid questions!

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.

Selectivity

First, let's take a quick look at Selectivity. Selectivity is meta information about the values in one column in a table.

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.

Here is the query used:

SELECT
    mh.name                                  AS MessageType,
    COUNT(mh.name)                           AS MessageCount,
    CAST(AVG(ResponseTime) AS DECIMAL(5, 2)) AS AvgResponseTime
FROM
    (
     SELECT
            li.SessionId,
            li.Name,
            DATEDIFF(s, MIN(li.TimeCreated), MAX(lo.TimeCreated)) AS ResponseTime
       FROM
            (
             SELECT
                    SessionId,
                    name,
                    TimeCreated
               FROM
                    ens.messageheader h1,
                    HS_Message.XMLMessage m1
              WHERE
                    h1.MessageBodyId = m1.ID
                AND h1.TimeCreated > DATEADD(hh, -1, GETUTCDATE())) li
       JOIN
            (
             SELECT
                    SessionId,
                    TimeCreated
               FROM
                    ens.messageheader h2,
                    HS_Message.XMLMessage m2
              WHERE
                    h2.MessageBodyId = m2.ID
                AND h2.TimeCreated > DATEADD(hh, -1, GETUTCDATE())) lo
         ON
            li.SessionId = lo.SessionId
   GROUP BY
            li.SessionId) mh
WHERE
    mh.name LIKE '%REQUEST'
GROUP BY
    mh.name

This post is the direct result of working with an InterSystems customer who came to me with the following problem:

SELECT COUNT(*) FROM MyCustomTable

Takes 0.005 seconds, total 2300 rows.  However:

SELECT * FROM MyCustomTable

Took minutes.  The reason for this is subtle and interesting enough for me to write a post about.  This post is lengthy, but if you scroll to the bottom I'll write a quick summary, so if you've gotten this far and think you've already read enough, scroll to the end to get the main point.  Check for the sentence in bold.

Hi All,

            I have a general query in regards to developers experience on extracting data from cache databases and the most efficient way to do so. I work with a number of clients who have applications with cache databases and require the data off the host system and onto data warehouse platforms for research and analysis. Often they require the data in source state which means the extracts are often simply a table scan of the entire database table without any aggregation or manipulation.

This tells me that there is no timezone offset on this table/field:

So why is TimeCreated in this resultset 4 hours less than OneMinuteAgo when I'm clearly requesting only records with a TimeCreated greater than OneMinuteAgo?

I have a list of about 100 MPI IDs that I would like to run a report on.  I want to list times that any data for these patients were accessed.  Currently in "Managed Reports" we have a "Disclosure Report" which I think was a custom development effort, but it is per-patient.

I have a SQL query for the ATNA log but I'm not confident in its accuracy, so I thought I'd reach out and see how other Information Exchange's might get this data.

Hello,

I need to create a query that return the amount of heath related information from a patient. 

I created one using the Analitics but, once our Analitcs database is update once a day this information is not reliable during the current query day.

Part of my Analitcs query is shown below. Where can I find the equivalent tables in Health Share? Any help is appreciate.

Tanks In Advance.

--Analitcs Query

SELECT patient FROM  HSAA.AdvanceDirective union all
SELECT patient FROM  HSAA.Allergy union all
SELECT patient FROM  HSAA.Appointment union all
SELECT patient FROM  HSAA.

I experience this constantly with Cache SQL.  Especially when querying the ATNA log.

SELECT TOP 400000 * FROM HS_IHE_ATNA_Repository.Aggregation ORDER BY ID DESC

That took 12 seconds.  I then upped the number to 500,000 and it took 185 seconds.

Shouldn't the execution time scale proportionately?

If I run the 500,00 query again it takes 2.4 seconds.

I have to connect to an external database and I'd like to use the JDBC SQL Gateway.

I followed this tutorial [1] to create a JDBC SQL Gateway and after configured it works as expected, but I don't know

how use this connection into source code. Maybe these other tutorial is related [2] and [3].

[1] http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY…

[2] https://community.intersystems.com/product-documentation/intersystems-p…

[3] http://docs.intersystems.com/latest/csp/documatic/%25CSP.

I have a class with a string property with DISPLAYLIST and VALUELIST parameters specified.

When I query this class via ODBC I receive logical value as it is and not a display value.

Is there any way (without stored procedure) to get display value?

I need to do an UPDATE via SQL and I would like the statement to return the `ID` column of each row that is updated.  MS SQL has an "OUTPUT" statement, but I don't see anything similar in Cache.  Is there a way to do this?

I have setup an async reporting mirror member with Read only access.  My problem is that if I try to do any sql reporting against that data I am getting errors.  I am sure that this is because the DB is read only, but I had assumed that setting up a reporting mirror would handle this.

I there a setting or mapping  I am missing?

I have been using the query below and it was working fine but now it's giving a SQL error. There were no changes made that could cause this to stop working. There is no Field 'APPLICATIONID' in the table.

QueueSQL=select distinct (convert(char(5),SkillsetID)+'='+Skillset) from iagentbySkillsetStat where ApplicationID > 10000
QueueMappingSQL=SELECT DISTINCT (convert(char(5),SkillsetID)+'='+Skillset), SkillsetID FROM iagentbySkillsetStat iagentbySkillsetStat WHERE (iagentbySkillsetStat.ApplicationID>10000)

DB- Intersystems Cache

Error details for the log files are below.

14/09/2016 11:20:05 a.m. > ERROR - SQL Error '[Cache ODBC][State : S1000][Native Code 29]
[C:\xlink\xlink Compiled Package\Xlink Application\Isps_Ul.exe]
[SQLCODE: <-29>:]
[Cache Error: <errdone+2^%qaqqt>]
[Details: ]
[%msg: < SQL ERROR #29: Field 'APPLICATIONID' not found in the applicable tables^select distinct ( convert ( char ( 5 ) , SkillsetID ) + :%qpar(1) + Skillset ) from iagentbySkillsetStat where ApplicationID >>]'

Hello Fellow Cache Developers:  

Has anyone ever created an index on values of a list property?   If so, would you be willing to share an example?

Also, feel free to offer input and suggestions regarding use of indexes on List values.

I try it with the COLLATE instruction, but doesn't work.

Any ideas ?

Tkanks.

Date range queries going too slow for you?  SQL Performance got you down?  I have ANOTHER weird trick that SQL Developers don't want you to know!*

I have a NewBie Question.

I have been playing around with "Basic CLass Queries."

I  have defined a very simple "Basic Class Query." (see below)

However, I cannot find in the I/S documentation how to execute this query.

Any help is appreciated.

Class LastName.BasicClassQuery Extends LastName.Person
{
Query Display() As %SQLQuery (ROWSPEC="ID:%Integer,Name:%String", CONTAINID = 1)
{
SELECT %ID, Name FROM LastName.Person
 ORDER BY DOB
}
Storage Default
{
<Type>%Library.CacheStorage</Type>
}
}

We are creating a package (written in Caché Object Script) that will provide access to an external DB (MySQL). Because applications that use our package will be run from machines with various, potentially unexpected, operating systems, we’d like to establish a connection to the external DB without using DSNs (we’ve heard that setting up DSNs on certain non-Windows machines can be cumbersome and problematic).

Does anyone know of  way to create a connection to an external DB without using a DSN?

I want to override the Get and Set methods of a class property.  The class maps to a pre-existing global. The property is defined like so:

Property Invalid As %Library.Boolean;

with the property mapping to a node like ^GLOBAL(Code,"INVALID")=1
Code is a property in the same class.

The value can be 0 or 1 or the node might not exist. When it doesn't exist I want the value of the SQL field to come out as 0 (false).

i'm looking for a way to display a list of tables in an application.  I need a Table or Schema API, and I can't find one.  I found this:

http://docs.intersystems.com/latest/csp/docbook/%25CSP.Documatic.cls?PA…

with a google search, but I'm not sure if I can even use this (it says that SQL name is "TABLES"), or how to use it.

Is there a way to get at the table names available in a namespace?

Thanks,

Laura