SQL

Syndicate content 29 

Hi guys!

As you know there are two (at least) ways to get the stored value of the property of InterSystems IRIS class if you know the ID of an instance (or a record).

1. Get it by as a property of an instance with "Object access":

ClassMethod GetPropertyForID(stId As %Integer) As %String

{

set obj=..%OpenId(stId)

return obj.StringData

}

2. Get it as a value of a column of the record with "SQL access":

Last answer 29 June 2019 Last comment 11 days ago
0   0 4
113

views

0

rating

I have a custom process that is parsing HL7 and inserting it into a table. Periodically the inserts fail with # due to error: ERROR #5803: Failed to acquire exclusive lock on instance of.... 

Traditional databases would wait until the lock is removed then do the insert, but cache fails. I'm sure it's my coding approach.

How can I work around this? A Try/Catch loop?

Thanks in advance.

Last answer 6 June 2019 Last comment 6 June 2019
0   0 4
104

views

0

rating

Hello all,

I have a Recordset object  which contains data from a table "XYZ". 

Currently i use this object to extract data using  %Get(COL1,COL2...) in a loop and than pass it to a function which inserts the data into another dynamically created  Table "ABC"  for each record. This takes a lot of time when 100's of records.

Is there a way i can directly copy a RecordSet to a dynamic table without looping through..?

Something like copy Recordset (COL1,COL2..)--> "ABC"

Thanks,

Jimmy

Last answer 3 June 2019 Last comment 3 June 2019
0   0 2
78

views

0

rating

Hi everyone,

Im new  in cache, i came from Java and im missing some features that i couldn't find in the documentation,  I hope you can help me with this questions.

Just a brief introducction: 

- Im in a project with old cache version, so saddly i can't use Eclipse + Atelier, so im using Studio.

- Currently im in a project with persistent classes, we want to turn apart the globals and focus on tables. 

The questions:

Last answer 21 May 2019 Last comment 21 May 2019
0   0 5
182

views

0

rating

Hi All,

I have two tables LB_TestSet and LB_Transfer

LB_Transfer has a list if TestSet row IDs, so i am doing the following join

SELECT 
LBTS_RowID,
,LBTS_CollectedDate
FROM SQLUser.LB_TestSet 
JOIN SQLUser.LB_Transfer ON ($LISTBUILD(LBTS_RowID) %INLIST LBTR_TestSetList)
WHERE LBTS_CollectedDate BETWEEN '2019-01-01' AND  '2019-05-10'

 

Without the date filter the query returns data, but if i add the date filter no data is returned

I also tried using %INTERNAL, %EXTERNAL TO_DATE('2009-01-01','YYYY-MM-DD')

Last answer 21 May 2019
0   0 3
0

comments

152

views

0

rating

Hello everyone!
I need to have a ResultSet of type % SQL.Statement show its contents when it is trafficked in a message property by Business Process.
I tried to use the % XML.DataSet type that inherits properties of type % XML.Adaptor, but did not work.
Is there any other way to traffic as an object, other than within a Stream?

Note: I can not traffic Streams and I will not be able to use Correlate in this case.

Last answer 3 April 2019 Last comment 5 April 2019
0   0 2
56

views

0

rating

Hi! We have received a request to create a new rule on CachéQuality to identify when a developer uses double quotes (" ") within any SQL statement.

We have been asked many times about SQL validation rules, and we would like to open a debate to allow everyone discuss what would you like to be checked on a SQL statement.

Current examples are for basic situations:

Last answer 1 April 2019 Last comment 28 March 2019
0   0 3
188

views

0

rating

I am having an intermittent issue that when I make a call to MSSQL from a BPL that the response does not come back in the amount of time required. Since the call from the BPL is synchronous I tried changing the timeout to 60 but it has not helped (see below). Is there anyway to guarantee that the call waits long enough for a response before continuing on?

 

Thanks

Last answer 20 March 2019 Last comment 21 March 2019
0   0 3
82

views

0

rating

Hi Community!

There are two general ways to execute arbitrary SQL  in serverside ObjectScript code: EmbeddedSQL and ObjectScript SQL a.k.a. Dynamic SQL.

E.g. if we want to get the value of the property of instance with a certain ID using SQL we can do:

&sql(SELECT Name INTO :name FROM Sample.Person WHERE ID=1)

write name

Same result with %SQL.Statement:

set rs=##class(%SQL.Statement).%ExecDirect(,"SELECT Name as name FROM Sample.Person where ID=1")
  do rs.%Next()
  write rs.name

Last answer 7 March 2019
+ 1   0 3
0

comments

294

views

+ 1

rating

I have a problem,ExtentSize,Selectivity etc is stored in class definition.

I have multiple namespaces with globals maped and only one class definition for All, for example Orders.

Order definition is only one,mapped to main namespace,but in different namespaces have different ExtentSize etc.

Last answer 1 March 2019 Last comment 2 March 2019
0   0 3
84

views

0

rating

SetIdentityInsert call controls the ability of the user to specify a value for the IDENTITY property when saving a new object, a value for the IDENTITY column or an explicit ROWID value in an SQL INSERT. If IDENTITY_INSERT is false and the user specifies an explicit IDENTITY or ROWID value when saving a new object or inserting a new ROW then an error condition is reported.

Setting takes effect immediately and lasts for the duration of the process or until SetIdentityInsert is called again.

My question is how can I change this setting system-wide?

Last answer 22 February 2019 Last comment 22 February 2019
0   0 2
74

views

0

rating

Problem

I got a quick answer from this forum yesterday so I'm going to try my luck again today!  I've hit an error in another table when trying to extract through the Cache ODBC driver, but this one gives me less details and I'm struggling to pinpoint what might be causing the error.

The table I am trying to extract is called REF_TABLE_ONE.

Here's the Error:

Last answer 31 January 2019 Last comment 1 February 2019
0   0 3
141

views

0

rating

I have a persistent class.

I want to store one of the properties there as a stream or a string depending on a size.

99% of values would be strings (less than $$$MaxStringLength characters) so I don't want to store everything as streams.

What do you think of this approach?

What's the best architecture to implement in this situation?

Last answer 17 January 2019 Last comment 18 January 2019
0   0 2
128

views

0

rating

A very simple SQL

SELECT field1 F1, count(field2) CntF2
FROM GCT.PA_Data
where field1 is not null
group by field1

gives an error back:

 [SQLCODE: <-461>:<Communication link failure>]

  [%msg: <Connection 'TRAKCARELIVE': Execute: SQLState: (HY000) NativeError: [10053] Message: [Cache ODBC][State : HY000][Native Code 10053] [c:\intersystems\ensemble\bin\cache.exe] Software caused connection abort> ]

Last answer 12 December 2018 Last comment 14 December 2018
0   0 4
127

views

0

rating

Hello,

I'm looking to find if there is a datatype convert equivalent in Object Script to SQL convert function. Have a VarBinary string coming in from source application (which is really performing a SQL dump). The source application uses the standard SQL convert function to convert from varchar to varbinary on their side.

I know &sql(Convert()) should work in Object Script, but am wondering if there is a better way of doing this. 

Getting data in via flat file (Record Map), then using data transform to transpose this data to SDA3. 

Last answer 15 December 2018 Last comment 17 December 2018
0   0 3
132

views

0

rating

Hello,
I need help in health insight. I am trying to generate reports on deep see but i am not able to pull in the patient ids as these are our requirements for the project. Can anyone help me in unlocking this feature.
Can anyone help me with link to correct documentation on how to access the edge gateways of multiple facilities to access the clinical data on sql explorer.

Last answer 28 November 2018 Last comment 25 October 2018
0   0 3
177

views

0

rating

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,

Last answer 3 December 2018
0   0 3
0

comments

138

views

0

rating

When using the JSON_OBJECT() function in Caché SQL on a %String property that contains JSON syntax, it converts the %String into a JSON object instead of escaping it as a string literal. How can I prevent this? (without ridiculous hacks like "add a space to the beginning of the value" as we don't always know which properties will contain these values and I certainly don't want to have to check for nulls and add/remove a space every single place this value is used in the application)

I don't want these strings automatically marshalled into JSON objects.

For example:

Last answer 20 November 2018 Last comment 28 November 2018
0   0 3
214

views

0

rating

Greetings all,

I have a quick SQL question.  I am working with a class, which has a calculated property, which in turned indexed.
The calculation for this property is created based on another property value via: 
SqlComputeOnChange = attributes  - so basically every time I add a property attribute to my class, my new property 
is calculated based on some SqlComputeCode which calls some class method.

That initial setup works as designed.

Last answer 16 November 2018
0   0 2
0

comments

102

views

0

rating

Hi,

Most of my classes are mapped from Globals. I want to access Cache classes from a BI software through ODBC connection.

'Last update' information does not exist in most of the classes. My question is whether there is a 'last update' timestamp that is automatically generated for each line in classes I can extract to external systems?

Thanks

Last answer 10 November 2018 Last comment 12 November 2018
0   0 2
269

views

0

rating

Hello,

I have been trying to pull data through a linked server in SSMS from an InterSystem Cache Database for a while, this is to enable us to join the data to other source systems in our Data Warehouse.

I have set up an ODBC connection and a linked server to the database and can execute queries through OPENQUERY in Management studio, but the data is huge (> 100million rows). So when I execute a SQL query with a WHERE clause the query just spins.

Last comment 1 November 2018
0   0 2
218

views

0

rating