i am trying to embed a dynamic SQL into Objectscript code , but the %NEXT() will return 0 , however when i copy the same query and run it in the SQL bit it will give me the result i want the code :
I have been walking through this with a few team members and as such I thought there might be others out there who could use it, especially if you work with HL7 & Ensemble/HealthConnect/HealthShare and never venture out past the Interoperability section.
In the past, I've created custom SQL operations, but now I had something trivial to do, so I decided to take EnsLib.SQL.Operation.GenericOperation out for a spin. There's no example in the docs, so it was a little tricky. Here's what I ended up doing:
In my external database, I have 'mytable' with two fields 'id1' and 'id2'. Here are the pertinent Business Operation settings:
I am experiencing an issue while executing a stored procedure in InterSystems Cache. Here’s the procedure I created
CREATE PROCEDURE Silk.sp_InsertRecord ( IN RecordDate TIMESTAMP, IN UserName VARCHAR(50), IN RecordType INT, IN RecordID VARCHAR(50), IN CategoryID INT, IN ApprovalDate TIMESTAMP, IN FileSize BIGINT, IN WorkstationName VARCHAR(50) ) BEGIN INSERT INTO DummyRecords ( RecordKey, FilePath, RecordDate, UserName,
In ObjectScript you have a wide collection of functions that return some value typically:
set variable = $somefunction(param1,param2, ...)
There is nothing special about that. But there is a set of functions that I classify as LEFT SIDED The specialty of them is that you can use them also on the left of the equal operator as a target in the SET command:
I need help with coming up with a SQL query that pulls only one value. I have a case where two providers share the exact same name. Each has a different NPI number and IdentityTypeId. I tried the below query - output is also below.
I have been trying to track down an issue we are seeing in our TEST environment with Memory usage.
We have Several BP's for years now that take a HL7 message, parse it apart, and make calls to a Custom EnsLib.SQL.OutboundAdapter to have it execute Insert/Select/Update/Delete stored procedures against a MS SQL Database via JDBC connection. We are using Microsoft's JDBC 12.2 driver to do this.
What we are seeing is that IRIS.WorkQueue globals are being defined for these calls but then the IRIS.WorkQueue is not being cleaned up and taking up large amounts of Memory.
In the modern world, the most valuable asset for companies is their data. Everything from business processes and applications to transactions is based on data which defines the success of the organization's operations, analysis, and decisions. In this scenario, the data structures need to be ready for frequent changes, yet in a managed and governed way. Otherwise, we will inevitably lose money, time, and quality of corporate solutions.
In this article, we’ll dive into the inner workings of the database management tool, exploring the architecture and technologies that power it. Understanding how the application functions behind the scenes will give you insight into its design, how it manages databases, tables, and how the API interacts with data.
I am accessing IRIS databases with JDBC (or ODBC) using Python. I want to fetch the data into a pandas dataframe to manipulate the data and create charts from it. I ran into a problem with string handling while using JDBC. This post is to help if anyone else has the same issues. Or, if there is an easier way to solve this, let me know in the comments!
I am using OSX, so I am unsure how unique my problem is. I am using Jupyter Notebooks, although the code would generally be the same if you used any other Python program or framework.
Hello, I need to use IRIS to connect to an MSSQL base. It has to be done via ODBC, I can't use JDBC at this time by client option.
I am trying to use Microsoft Driver libmsodbcsql-13.1.so.9.2
But I can't, my attempts result in: Connection failed. SQLState: () NativeError: [11001] Message:
I have done all DSN configuration, and my configuration is listed in SQL Gateway Connections. I know it's working, because when I run a test with isql I have the information that connects to the bank.
How to add the following to a stored procedure (Cache Studio) 1. Select from a few tables and insert result into a TABLEA. 2. Then select data from the TABLEA, apply some SQL logic, insert results into TABLEB. 3. SELECT * FROM TableA UNION ALL SELECT * FROM TableB
The Certification Team of InterSystems Learning Services is excited to announce the release of our new InterSystems IRIS SQL Specialist exam. It is now available for purchase and scheduling in InterSystems exam catalog. Potential candidates can review the exam topics and the practice questions to help orient them to exam question approaches and content. Candidates who successfully pass the exam will receive a digital certification badge that can be shared on social media accounts like LinkedIn.
I'm testing some functionalities about Foreign Tables and it works smoothly with PostgreSQL database, but I found out an issue with MySQL database, I followed the documentation:
On one of my team's systems, we utilize a business operation with the EnsLib.SQL.OutboundAdapter to make SQL queries to another IRIS system using JDBC. To authenticate the connection, we utilize a user account on the target system.
When running a sql insert query with CONVERT('datetime', '2023-12-12 11:27:00',120) in an insert statement into a table this works on one server and not the other.
FOREIGN TABLES is a rather fresh feature in IRIS (2023.?) So I was motivated to try something new by own hands. Documentation of Foreign Table from File is a good starting point. Also the related promotional video is fine to start with.
I'm executing a query using JDBC on a PostgreSQL db:
SET statement_timeout TO 600000000; COMMIT SELECT * FROM bi_hour
The query is aborted with the following message:
FOUT #5023: Fout in Java Gateway: JDBC Gateway getClob(0,2) errorBad value for type long : active
The column 'blocked_status' contains the value 'active' is of type 'text'. I figure somewhere the SQL Gateway tries to convert the text value into a long but I can't find where, any suggestions?
Instead of building a BP to execute stored procedures just to insert the data, I figured I would give Linked Stored Procedures a try since all I need to do is insert the data. I have proved in our MS SQL Development environment that it is possible, so I through I could just repoint the Linked Stored Procedure
Suppose you have an application that allows users to write posts and comment on them. (Wait... that sounds familiar...)
For a given user, you want to be able to list all of the published posts with which that user has interacted - that is, either authored or commented on. How do you make this as fast as possible?
Here's what our %Persistent class definitions might look like as a starting point (storage definitions are important, but omitted for brevity):
By update.png)
Open Exchange app