.png)
I tryinfg to alter a column definition data type with a user that seems that doesnt have priviledges, but the user has this priviliedge.
Documentation:
Privileges and Locking
SQL
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
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
Say I have a property in a persistent class that stores list of colours and I would like to query that field and return a list and be able to loop that list to get individual colours how will l go about achieving this I have tried something like this but its not working as expected
&sql(SELECT colour INTO :colourList FROM favouritecolours)
While (SQLCODE = 0)
{
for i=1:1:$LENGTH($P(colourList,","))
{
set fvalue=$P(colourList,",",i)
write "the first"_fvalue,i,
}
}
Last answer 3 June 2019
End users sure love Excel tables. So, how can you deliver the second to the first? Read this post to find out.
Last comment 1 June 2019
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
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
Have some free text fields in your application that you wish you could search efficiently? Tried using some methods before but found out that they just cannot match the performance needs of your customers? Do I have one weird trick that will solve all your problems? Don’t you already know!? All I do is bring great solutions to your performance pitfalls!
As usual, if you want the TL;DR (too long; didn’t read) version, skip to the end. Just know you are hurting my feelings.
Last comment 10 May 2019
Hi! I've been fiddling with linked tables to get data from other servers, and I encountered a problem that I'm curious about. Maybe I'm not using these tools as intended or there's more going on, so I'm asking here.
I'm running a query on linked table A, something simple like this:
select name from A where id = 5983658923646
And I get this error:
[SQLCODE: <-400>:<Fatal error occurred>]
[%msg: <>]
Last answer 8 May 2019 Last comment 10 May 2019
Hi!
I was trying to create a query that can be exposed as a stored procedure (function actually) that would return a resultset with a random number of columns.
Unfortunately, it seems that unless I specify the ROWSPEC annotation on the Query method, I won't get any columns exposed. I was hoping to implement QueryNameGetInfo method and specify the names and number of columns I would be returning dynamically. But it seems that GetInfo information is simply ignored.
Here is my code:
Last answer 8 May 2019 Last comment 10 May 2019
Cache version: Cache for Windows (x86-64) 2017.2.1 (Build 801_3U)
Good Afternoon,
I have a co-worker who is trying to run the below query via ODBC. The issue is that the query appears to be running extremely slow (nearly 2 hours).
Last answer 10 May 2019
Class ICT.Experiments.A Extends %Persistent
{
Property Name As %String;
Property Collection As list Of ICT.Experiments.B;
Property Collection2 As array Of ICT.Experiments.C;
}
Class ICT.Experiments.B Extends %Persistent
{
Property Name As %String;
}
Class ICT.Experiments.C Extends %Persistent
{
Property Name As %String;
}I have the classes above and I can select columns from the array collection by using two joins as follows:
Last answer 19 April 2019 Last comment 24 April 2019
Hello all,
I am still learning CACHE and have below question.
In one of my ZEN method i am creating a table using
Last answer 23 April 2019 Last comment 23 April 2019
Hello,
I am trying to run a simple SELECT STATEMENT in USER NAMESPACE.
SELECT * FROM projectname.table
What is the syntax if i need to get data for a table in another namespace eg. SAMPLES., SYS
SELECT * FROM SAMPLES.projectname.table is not working.
Thanks in advance.
Jimmy Christian.
Last answer 22 April 2019 Last comment 23 April 2019
Referencing this post:
https://community.intersystems.com/post/producing-json-sql
I'm not sure how to actually interact with the result set I get from doing something like this. I want to return something like:
[{"field1":1, "field2":2}, {"field1":2, "field2":10}]
I'm finding it very difficult to get it in this format, since %Print appends a newline onto the end of the {} object it prints.
Here's the closest I've gotten:
Last answer 13 April 2019 Last comment 16 April 2019
We use a lot of external MS SQL calls to look up numerous things when it comes to HL7. I am running into an issue where the outbound calls are queued but the message is sent onto the next process before it gets a response. How do I get it to wait till the response gets back from the MS SQL call before sending it onto the next process?
Below is a screen shot of what I am trying to explain...Select Research Study executes at 8:33 the message is sent to the next process at 8:34, but the response of the MSSQL call doesn't come till 9:31.
Last answer 13 April 2019 Last comment 16 April 2019
Hello,
When i click on the menu to run the Data import wizard from MP, i receive following CSP error
| <UNDEFINED>zOnPageHEAD+229^%cspapp.exp.utilsqleximwizardcontent.1 *schemaname : CSP Error |
It is happening for all the namespaces. Looks like some permission issue. Same issue with Data Export wizard. Help to resolve this will be appreciated.
I am using
Cache for Windows (x86-64) 2017.2.2 (Build 865_0_18763U)
Thanks,
Jimmy Christian.
Last answer 12 April 2019 Last comment 12 April 2019
Hi, I have used CSP to exec SQL selects from any own NAMESPACE. But in our servers we have many SQL GATEWAY CONNECTIONS.
I'd like to create a CSP page that could use these gateway to exec SQL using these gateway connections, only Administrators will use that page to launch many select at many dsn. I'm not sure if we must deploy that CSP on %SYS namespace and how to use DSN(SQL Gateway connections) that are defined on server.
Anoyone has made that?
For example:
DSN CLIENT ONE
DSN CLIENT ONE
CSP Webpage:
TEXTBOX: Introduce your select:
From time to time we develop an Ensemble Production with simple SQL Inbound data from external databases, we need to develop a few new classes. There are at least:
Last answer 10 April 2019 Last comment 10 April 2019
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
I use Documatic a lot to generate class documentation from comments embedded in the code. Is there a good way to create documentation for SQL views as well? Ideally I want to document each column in the view with HTML markup similar to how I document each method of a class with Documatic.
Last answer 4 April 2019 Last comment 3 April 2019
Hi all,
In order to prioritize functional requirements for evolving our SQL developer experience, we have prepared a short survey to better understand how you’re currently using and, in an ideal world, would like most to interact with our SQL platform.
Last comment 1 April 2019
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
Global Summit 2018, InterSystems IRIS Experience, Sharding, SQL, Video, Global Summit, InterSystems IRIS
Hi Community!
Please welcome a new video on Developer Community YouTube Channel:
Getting Sharded with InterSystems IRIS
Hi I've got a service setup to query a sql server via ODBC.
I've got all the parameters etc setup and everything is ok
My Query is
Last comment 22 March 2019
In my earlier article on Adopting Bitmaps I described the technique already.
Now you can find a code example also on Open Exchange.
