SQL

Syndicate content 26 

As I was going though and trying to figure out why our CACHE.dat has increased in size over the past 18 days, I found that EnsLib_HL7.Message is still retaining messages dating back to 2014 even though we have our purge set to 10 days. Has anyone else experienced this?

Thanks

Scott Roth

The Ohio State University Wexner Medical Center

Last answer 30 January 2018
0 2
0

comments

107

views

0

rating

Hi,

My doubt is about the SQL Query in Caché:

I don't want to take all results from the table and orgainze them manually, for example: I have a table with 50 records, but I only want to select 10 records, being from tenth until the twentieth and this without knowing their IDs.

So, how can I do this, without losing performance, there is any way?

 

NOTE: In other SQL languages I can do, for example, SELECT * FROM extbl Limit 10 OFFSET 10

 

Thanks,

Andrei L. Nenevê

Last answer 24 February 2017 Last comment 25 January 2018
0 2
951

views

+ 1

rating

I'm trying to create an sal connections from one cache system to another. I've created the connection part  and I think that is working. When trying to link a table through the link table wizard, I'm getting an error "<MAXSTRING>zCreateOneLinkTable+52^%CSP.UI.SQL.LinkWizardResultPage1.

Is this due to my global block size in the new system not being large enough? It is currently set at 8192.

Thank you

Mike

Last answer 16 January 2018 Last comment 16 January 2018
0 2
156

views

0

rating

Hi -

I'm creating a custom report in Health Insight via SQL and one of the requirements says that I must include patients who are >= 13 years old (which is easy) but I also need to include patients who WILL be 13 years old as of December 31st of current year that the report is ran in.

 

I see that there is an Age column and an AgeInMonths column in HSAA.Patient. and of course a DOB column. I'm wondering if it is possible to do this purely using SQL (and SQL functions) or if I will have to incorporate a COS function to achieve this requirement.

 

Any suggestions?

 

Thanks,

Blake

Last answer 16 January 2018 Last comment 28 December 2017
0 2
728

views

0

rating

I have a database which needs to be synced to another system and considering to use the SQL Inbound adapter to 'watch' the database. The other system, however, has a bulk API so I'd prefer to send multiple SQL rows in a single message to that system. The SQL Inbound adapter seems to trigger a single call (to a process or business operation) per SQL-row. However, the documentation here: http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=... in example 5 seems to suggest that if I exclude a KeyFieldName I get multiple rows in my EnsLib.SQL. snapshot and should be able to send multiple rows to the bulk API down the line. Is this a correct interpretation of the documentation?

Last answer 8 January 2018
0 1
0

comments

165

views

0

rating

Recently viewed a demo on the new Dynamic Objects in InterSystems (we are still running 2016.1); together with the upcoming IRIS data platform I started thinking about possibilities of building dynamic databases. That is, I can imagine some projects were it might be really nice to store dynamic objects and then run sql queries on them without ever defining the fields of the dynamic objects (i.e. not at storage, but only if you run your sql query). If there is any system where this might be possible it is InterSystems Cache.  Of course, Ithis might not be a good idea for an entirety of reasons, but one can still dream, so please bear with me :P.

So the core idea is to host an intersystems REST API that acepts any JSON message, converts it to a dynamic object and store it in Cache. Then I'd like to run a sql query on the stored objects to see what I actually received.

So here are my questions

Last answer 13 December 2017
0 1
0

comments

255

views

0

rating

Hi, I'm a student, weak programming. I ask for your help, I write a program in C #, there are two tables, but I do not understand how to organize the connection between them many to many. Table in DataGridView1 from DataSet1 on Form1 key connection in DataGridView1 from DataSet1 to Form3

I do this on Form1

private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e) 
{
  if (e.RowIndex >= 0)
  {
    DataGridViewRow row = this.dataGridView1.Rows[e.RowIndex];
    Form1.IDfor3form = row.Cells[1].Value.ToString(); // By the Bilet column
    Form3 c = new Form3();
    c.Owner = this;
    c.Show();
  ​}
}

and on Form

Last comment 12 December 2017
0 1
0

answers

221

views

0

rating

Hello,

I'm working on integrating a Cache database into an existing .NET project of mine.  I'm trying to execute certain queries utilizing the Cache ODBC driver.  The first error I ran into was "[S1000] [Cache ODBC][State : S1000][Native Code 417] Access Denied".  The part that's throwing me for a loop though is that the application ran ~200 successful queries before I started getting that.  After some of those Access Denied errors, they turned into "[Cache ODBC][State : S1T00][Native Code 450] Request timed out due to user timeout".  This was all with the regular (2.5?) driver.

I then started using the 3.5 driver.  But, the same scenario played out again.  The only difference being the State: from S1000 to HY000.  These then also turned into "Request timed out due to user timeout" with a state of HYT00 instead of S1T00

Last answer 21 November 2017 Last comment 22 November 2017
0 4
1826

views

0

rating

I have a query that I am attempting to run, but inner query is being rolled up into the overall query - per the optimizer (and jobexam observation).  While I know that if the inner query runs first, I will eliminate a number of records (making the result set smaller, and return quicker).

 

Just an example of what I am attempting to do:

select Account.Name, Account.State, Transaction.Amt, Transaction.Date, Transaction.Service
from Transaction 
left join Account 
    on Account.Id = Transaction.Account  
where Account.Id in (
    Select Account.Name
     from Account
     where Account.Type is not null
     and Account.Id>123456789
     and Account.Id<=323456789
)
and Transaction.Date >= ?
and Transaction.Date <= ?

 

Index on 'Transaction.Date, Transaction.Account

Last answer 20 November 2017 Last comment 20 November 2017
0 0
158

views

0

rating

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
}
Last answer 6 November 2017 Last comment 5 November 2017
0 1
257

views

0

rating

The Art of Mapping Globals to Classes (3 of 3)

 

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:

 

The Art of Mapping Globals 1

The Art of Mapping Globals 2

 

This example is going to show you how to map a classic parent-child structure.

Last comment 2 November 2017
0 7
765

views

+ 8

rating

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)]

 

No queries work... any help? stumped over here.

Last answer 21 September 2016 Last comment 2 November 2017
0 1
584

views

0

rating

Greetings to all!!! Suppose there is a table Mother (ID, Name) and Child (ID, Name, Mother), Mother in the table Childis a relationship. Let's say the task is to deduce the names of all the children whose their moms' names start with the letter 'A', I can do this in two ways in sql, and I can not understand the difference, the pros and cons that when to use:

1) SELECT Child.Name FROM Mother INNER JOIN Child ON Mother.ID = Child.Mother WHERE Mother.Name LIKE 'A%'
2) SELECT Child.Name FROM Child WHERE Child.Mother->Name LIKE 'A%'

Last answer 26 October 2017
0 1
0

comments

201

views

0

rating

We are trying to convert some of our SQL Service Integration Service jobs from Visual Studio to Ensemble. If we execute a Stored Procedure within SQL Server Management Studio it is returning approx 12,000 rows. However when Ensemble executes the same Stored Procedure it is only returning 250 rows.

Is there a limitation to EnsLib.SQL.Snapshot?

This is how we are calling the Stored procedur

Last answer 19 October 2017 Last comment 20 October 2017
0 1
230

views

0

rating

Hi,

I have a class with around 400k lines and 60 columns. Class storage is Cache SQL storage (Mapped from a global).

 I want to create multiple indices on certain fields.

I am familiar with two approaches:

1. Create a new map (Index type) on a pointer global.

2. Create a bitmap index

Which approach is more recommended to be used in the case I described? If there are any other approaches, I will be happy to hear.

Thanks :)

 

Last answer 11 October 2017 Last comment 19 October 2017
0 2
341

views

0

rating

The newer dynamic SQL classes (%SQL.Statement and %StatementResult) perform better than %ResultSet, but I did not adopt them for some time because I had learned how to use %ResultSet. Finally, I made a cheat sheet, which I find useful when writing new code or rewriting old code. I thought other people might find it useful.

First, here is a somewhat more verbose adaptation of my cheat sheet

Last comment 18 October 2017
0 12
1185

views

+ 7

rating

HI,

I have a query like below but its syntax is not accepted by Cache.

I would like to perform a left join on two tables first and then make a inner for this result with another table.

Given the constraint that we only allow one SELECT in the query, it is possible to achieve this semantics ?

Thank for your help.

 

Select * 
FROM ( sample . employee e 
LEFT JOIN  sample . company c 
on c . id = e . id ) g
JOIN sample . vendor v
on v . %id = g . attr 

Last answer 14 October 2017 Last comment 15 October 2017
0 1
419

views

0

rating