Hi

I have two tables: one a local table using cache and the other is via an SQL gateway connection to an MSSQL Server via a 64bit ODBC driver (ODBC Driver 17 for SQL Server).

When I try to join them on a date field I don't get any matches, and I know that there are matches, e.g. running both views separately returns resuls for 30/11/2022.

The following statement returns rows for the first table r, but nothing for the table l

0 5
0 310

Hello,

searching messages in our Message Bank is quite slow, often runs into timeout.

I wanted to perform a tune table on Ens_Enterprise_MsgBank.MessageHeader because this apparently has not been done yet - the Tune Table utility shows no entries for selectivity, etc.

I tried

 w $SYSTEM.SQL.Stats.Table.GatherTableStats("""Ens_Enterprise_MsgBank"".MessageHeader")

and got this error message

1 6
1 417

Hello DC Members,

Please welcome the new video on InterSystems Developers YouTube:

Ten Times Faster Queries with Columnar Storage @ Global Summit 2022

https://www.youtube.com/embed/lcEUPPKU6OY
[This is an embedded link, but you cannot view embedded content directly on the site because you have declined the cookies necessary to access it. To view embedded content, you would need to accept all cookies in your Cookies Settings]

1 0
0 297

This is not an issue in ObjectScript, due to its typeless nature. But it's essential for external programming languages that care a bit more about types of variables.

And in any case, it's still reproducible in ObjectScript. I have table

CREATE TABLE some_table (
        id INTEGER NOT NULL, 
        x INTEGER, 
        y INTEGER, 
        z VARCHAR(50), 
        PRIMARY KEY (id)
)

And data

INSERT INTO some_table (id, x, y, z) VALUES (1, 1, 2, 'z1');
INSERT INTO some_table (id, x, y, z) VALUES (2, 2, 3, 'z2');
INSERT INTO some_table (id, x, y, z) VALUES (3, 3, 4, 'z3');
INSERT INTO some_table (id, x, y, z) VALUES (4, 4, 5, 'z4');

1 11
0 469

Hi Community,

In this video, you will learn about exciting new ways to perform analytics using data in your HL7® FHIR® repository:

InterSystems IRIS FHIR SQL Builder: Sneak Peek @ Global Summit 2022

https://www.youtube.com/embed/lCv5t4785JY
[This is an embedded link, but you cannot view embedded content directly on the site because you have declined the cookies necessary to access it. To view embedded content, you would need to accept all cookies in your Cookies Settings]

2 0
0 373

In Cache 2018, we were using a macro in a query that looked like this:

select $$GetExtraSQL^GetExtra('B',bddtl.odnumb,bddtl.odsnum,bddtl.oddsc1) as "Description", * from sqluser.bddtl

We could save that query as a view, and there was no problem with it.

In IRIS, if we put that query into SQL in the management portal, it still works, but if we save that query as a view, when we try to run a query on that view, we get a big error message:

0 1
0 241

Recently viewed a demo on the new Dynamic Objects in InterSystems (we are still running 2016.1); together with the upcoming IRIS data platform I started thinking about possibilities of building dynamic databases. That is, I can imagine some projects were it might be really nice to store dynamic objects and then run sql queries on them without ever defining the fields of the dynamic objects (i.e. not at storage, but only if you run your sql query).

0 3
0 638

I just wrote up a quick sample to help a colleague load data into IRIS from R using RJDBC, and figured it's worth sharing here for future reference.

Ultimately it was pretty simple, aside from IRIS not liking "." in column names; the workaround is to just rename the columns. Someone better at R than me could probably provide some generic approach. smiley

4 2
1 225
Article
· Nov 14, 2016 14m read
Mastering the JDBC SQL Gateway

As we all know, Caché is a great database that accomplishes lots of tasks within itself. However, what do you do when you need to access an external database? One way is to use the Caché SQL Gateway via JDBC. In this article, my goal is to answer the following questions to help you familiarize yourself with the technology and debug some common problems.

12 2
7 4.1K

Trying to identify which records in the %SYS.Audit table are fails.

Eg, as user "WORKER", I run an attempted a grant, the terminal returns:

SQL> GRANT SELECT ON newschema.patients TO COORDINATOR
[S1000][Iris ODBC][State : S1000][Native Code 112]
[libirisodbc35.so]
[SQLCODE: <-112>:<Access violation>]
[Location: <ServerLoop>]
[%msg: <User WORKER does not have required privileges to grant the privilege(s)>]
[ISQL]ERROR: Could not SQLExecute

but the record in the audit table gives

0 2
0 198

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

Banner image

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.

5 3
0 447

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')

0 2
0 202

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

0 0
0 324

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!

10 6
7 876

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

0 4
0 187

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?

0 1
0 292