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:
I've setup ODBC connection so I can access Cache data within SQL Server.
I want to be able to write SQL queries for internal monitoring purposes, similar to what's possible with SQL Server. Specifically I want to be able to check mirroring status (i.e. check which is the current primary mirror member), check the status of any Ensemble productions (started/stopped), check the status of business hosts etc. I want to do all of this from SQL Server to go with our other system monitoring solutions.
Hello community! I have to work with queries using all kinds of methods like embedded sql and class queries. But my favorite is dynamic sql, simply because of how easy it is to manipulate them at runtime. The downside to writing a lot of these is the maintenance of the code and interacting with the output in a meaningful way.
I'm having trouble accessing the snapshots EnsLib.SQL.Snapshot in %Library.ListOfObjects that are returned from method ExecuteProcedure in EnsLib.SQL.OutboundAdapter. The Microsoft SQL stored procedure I am executing returns multiple resultsets.
The issue I am having is that my code works fine when executed in the business operation (commented out in the code below) but when it is executed in the business process it errors. Any ideas as to why this happens? The error I get is:
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":
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)
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"
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,
}
}
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')
CREATE TRIGGER "SQLUser".SYM_ON_U_FOR_SYM_CHNNL_125123 AFTER
UPDATE
ON "SQLUser"."sym_channel" REFERENCING OLD ROW AS OLD NEW ROW AS NEW FOR EACH ROW
DECLARE @var_row_data VARCHAR(16336);
WHEN
(
1 = 1
and 1 = 1
)
-- I will use OLD/NEW alias here set @var_row_data = ''; LANGUAGE sql insert into mytable (id, data) values(1, @var_row_data);
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.
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:
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.
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:
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.
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.
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.
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.
By update
Open Exchange app