SQL is a standard language for storing, manipulating and retrieving data in relational databases.
Hi,
My routine contains embedded SQL including this:
&sql(DECLARE C1 CURSOR FOR
The error "SQLCODE=-52 : Cursor C10 already declared" comes up on compilation in Studio. Does anyone know a way around it please?
Bob
On the Latest GlobalSummit 2022, InterSystems Introduced Cloud SQL. So, you may have lightweight InterSystems IRIS with access to SQL only. Well, what if you would still need some Interoperability features in the cloud as well? There are various solutions on the market nowadays, which offer a bunch of integration adapters out of the box and can be extended with support from the community. Some time ago, I've implemented an adapter for the Node-RED project, which can be deployed manually everywhere you want. Now I would like to introduce a new integration with my recent discovery, n8n.io
n8n.io is a workflow automation platform, that supports over 200 different integrations out of the box and from a community, and now including InterSystems IRIS.
How can we insert into a table using a stored procedure?
Table structure :
CREATE TABLE SampleHospital (
Hospital VARCHAR(50),
Location VARCHAR(50),
UserName VARCHAR(50),
UserFullName VARCHAR(70),
LastLogin TIMESTAMP,
LoginCount int)
Procedure Name:
call Custom_MENS_Other.samplesp('2021-11-02','2021-11-04','H001')
Hello guys,
Does anyone know what is a process status IPQW ?
We get it in jobs that do some SQL..
Thanks in advance.
Yaron Munz
Can someone help me on how the alter a table in Management table, I have tried the SQL and MYSQL way but it does not alter the table.
Thank you.
Hi,
I am trying to input an entire xml into a sql server table. I am doing this via an operation in Ensemble.
I keep getting the following error
[SQL Server]XML parsing: line 0, character 0, unrecognized input signature
I have setup the table with a column parameter XML.
Here is the request object that is being sent to the store procedure
<reqSQL xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:s="http://www.w3.org/2001/XMLSchema">
<SQLScript>
{ call dbo.PopulateDataTable(?,?,?,?,?,?,?)}
</SQLScript><lstInputParams><lstInputParamsItem>
test
</lstInputParamsItem><lstInputParamsItem>
666666
In MS SQL Server i can do this:
SELECT
Category, CrewNumber, MedicalCertificationDate, Seat, SeatbeltUsed, Sex, ShoulderHarnessUsed, ToxicologyTestPerformed, childsub
INTO #tempfemale
FROM Aviation.Crew
WHERE Sex = 'F'
In this article you will have access to the curated base of articles from the InterSystems Developer Community of the most relevant topics to learning InterSystems IRIS. Find top published articles ranked by Machine Learning, Embedded Python, JSON, API and REST Applications, Manage and Configure InterSystems Environments, Docker and Cloud, VSCode, SQL, Analytics/BI, Globals, Security, DevOps, Interoperability, Native API. Learn and Enjoy!
Hi!
I have a question regarding SQL insert/update from the mananger portal's SQL window.
I am trying to do an insert with a value that contains multiple spaces ($32) between two words. This is needed for a comparative reason. But the spaces are automatically trimmed away all the time. How should I write to keep these?
Example:
Insert into TableA
(MyColumn) Values('xxxx yyyy')
then the spaces are trimmed away and it becomes: 'xxxx yyyy'
But I want to keep the spaces. How do I get around the problem?
Regards,
Michael
Hi ,
When I use jdbc driver to query the column info ,the "REMARKS" field always show the same as the "COLUMN_NAME" field.
When I use the sql "select * from INFORMATION_SCHEMA.columns a where a.table_name='some table name ' " to query columns info,there has a "DESCRIPTION" field ,the value is some comment for the current field.
So is there anyway to return the description to the jdbc remarks field? And is there anyway to update the desciption or remarks field? Or is there some other way that I don't know to manage column comment info?
Hi,
I encountered a strange issue I just can’t solve and I am running out of ideas. After reading the relevant passages of the documentation, asking the community is my last resort.
The task itself is not complicated at all. I am using an EnsLib.SQL.OutboundAdapter to connect to a MS SQL Server (12.0.6433.1) and execute a simple query. Said query is using a temporary table from which I just can’t select any data afterwards. All I get is this error message: <Ens>ErrNoSQLColumns.
I reduced the statement for debugging to this query:
DECLARE @result AS TABLE(
CaseID varchar(50),
I running a query and get the results inside a result set. Now I have to iterate through the result set many times. From the doc I've seen only the Next() method. Is there a way to reset the cursor? Otherwise what is a good data structure to save multiple rows of a table?
My code in this case is something like this:
set sql = "SELECT * FROM MY_TABLE WHERE X= '"_Y_"'"
set status = ..Adapter.ExecuteQuery(.rs, sql)
// somehow iterate the rs more than one time
Hello, new user here ! My admin granted me all access to the domain I'm working on. Nonetheless, I'm unable to perform simple SQL queries such as creating a table or an index.
Kindly help me on the below points
1)Concatenate the Date column and Time column.
2)Get the current date only without time.
3)Find the current age using DOB column.
Hi
I'm trying to add multiple rows at a time to a SQL table for a persistent object, but I can't seem to get the syntax correct. When I try the below it doesn't work.
INSERT INTO Sample_Table.Accounts (Account, AccountName, Bank)
VALUES
('123456', 'AccountNameOne', 'BANK1'),
('654321', 'AccountNameTwo', 'BANK2')Thanks
Peter
Hi, we are trying to run a dynamically generated Oracle Insert SQL script using Do $SYSTEM.SQL.DDLImport("Oracle",""," sql.txt",[Error Log]) to insert multiple rows into a Linked Table within HealthConnect to an Oracle database.
We have tried using statements that are accepted within Oracle but not when using Intersystems DLL along the lines of :
An interesting pattern around unique indices came up recently (in internal discussion re: isc.rest) and I'd like to highlight it for the community.
As a motivating use case: suppose you have a class representing a tree, where each node also has a name, and we want nodes to be unique by name and parent node. We want each root node to have a unique name too. A natural implementation would be:
I'm getting wrapped around the axle with CAST and CONVERT and can't seem to find a way to do this (short of adding a stored procedure wrapping $zdt($zdth(posix,-2),3), which I'm refusing to do on principle, because there has to be some way to make this work).
Any ideas?
I am calling a stored procedure over an ODBC connection and every time I call it there are several warnings written to the log event {Found no Parameter 1 (used as 1) for query}.I seem to be getting this on every query executed and that seems to happen a number of times the query parameters are per query and its filling up my disc.
1) Is there a way to suppress these warnings as the query seems to be executed and data written to the database?
Hi,
I'm trying to load some data into IRIS using LOAD DATA in the IRIS terminal however I am getting blocked by this error. I have read the documentation on this page trying to set up a gateway connection but get the same Connection cannot be established error.
This is what is in the %Java Server activity log
Say I have a persistent class in IRIS with an optional property EmailOptIn:
Class Person Extends %Persistent
{
Property Name As %String;
Property EmailOptIn As %Boolean;
}
I later realize that I'm doing a lot of null-checking on this property where I shouldn't need to. The solution is to make this a required property:
Class Person Extends %Persistent
{
Property Name As %String;
Property EmailOptIn As %Boolean [ Required ];
}
Hey Community,
I have an scenario, please advise or suggest possible ways to solve it.
So i have a persistent class to test results of a diagnostic laboratory. The properties are
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
I have a table with a Varchar(max) column that I have created via the HealthShare SQL portal, and I see that in it's underlying class that column corresponds to a %Stream.GlobalCharacter
When I try to do a text search on that column (referenced as cd.Code in the example below) I get an error
select *
from DevTools_CodeAnalysis_tables.Code cd
inner join DevTools_CodeAnalysis_tables.MethodData mtd
on charindex( mtd.qualifiedRef, cd.Code) > 0It produces the error
Scalar function CHARINDEX (arg2) not supported for stream fields
How do I search it?
Hi all
I'm struggling to insert into a table, and it is baffling me. The insert statement is now very simple (I was using a complex INSERT SELECT statement but I'm trying to narrow down the problem).
Insert statement:
INSERT INTO Phu_Replay_Schema.ReplayMessageModel (Completed, MessageHeaderId, NewHeaderId, NewTargetName) VALUES (true, 3616, null, 'Router_ReplayHL7')
Hey Community,
Don't miss the latest videos on InterSystems Developers YouTube channel:
In the good old days (tm) determining the size of the data, streams, and indices for a class/table was easy - you just ran %GSIZE and check D, S, and I globals respectively.
However, nowadays sharding, optimized global names, and indices in separate globals produce %GSIZE output looking like this:
Thanks to @Yuri Marx we have seen a very nice example for DB migration from Postgres to IRIS.
My personal problem is the use of DBeaver as a migration tool.
Especially as one of the strengths of IRIS ( and also Caché) before is the availability of the
SQLgateways that allow access to any external Db as long as for them an access usinig
JDBC or ODBC is available. So I extended the package to demonstrate this.
I would like to capture any NACK's that is sent back to the Operation. The Operation is already setup to "Save Replies/IndexNotOK's", but I would like to see if we can query Cache and pull those NACK's into an extract.
Is this possible?
Thanks
Scott
Hi Guys,
So I've been following this guide in using a %Library.ResultSet with a ClassName / QueryName as described in the first example.
https://docs.intersystems.com/irislatest/csp/documatic/%25CSP.Documatic…
The code I've got so far doesn't work and is as follows:
set rs=##class(%ResultSet).%New()
set rs.ClassName="GMECC.DocmanConnect.Tables.vwNewGPs"set rs.QueryName="GetRows"set sc=rs.Execute("a") If$$$ISERR(sc) Do DisplayError^%apiOBJ(sc) Quitwhile rs.%Next() { do rs.%Print() } And the referenced class is