#SQL

Hi All,

I am trying to execute my SQL query using ExecDirect() method and it returns a ResultSet. No doubt it works fine after this I am trying to fetch each value using a loop code below. But before that, I am also checking for %SQLCODE = 100 which is for an empty resultset I think. However, It is not working as desired. In the case of an empty resultset also I am getting %SQLCODE = 0 until result.%Next() is called. Also, %ROWCOUNT is giving 0 in a case where my query is return 1 result row. I am so confused about this.
while resultset.%Next() {

// code

}

Ran into an issue with our upgrade from HealthShare Health Connect 2018.1.3 to IRIS HealthShare Health Connect 2022.1, that I thought I would reach out about, I already have a ticket open with WRC and started a chat on Discord. We have a couple of MS SQL tables that I have setup as Linked Tables within Cache to query NPI, Patient Account Information, Visit information, etc.

How to read the HL7 V2.5 messages irrespective of message type to Iris database ?

Source system sends HL7 messages of multiple message types to Intersystems through TCP and these messages are to be inserted/read into iris SQL database.

Hello,

Do you know if there is any way to disable the automatic tuning IRIS is doing every time a class is altered? It just takes too long in our case and is holding the system, so I'd like to tune the tables when I decide it (again).

I've seen in the documentation that for 2023 version there is an option present in the backend. But not for 2022, so I assume a flag is needed somewhere.

Many thanks!

Hi,
just want to know if indexes in IRIS / SQL are autobuilding.

I define an index in a table and then i costantly do a lot of insert/delete in that table. Will i face index problem? Do i need to rebuild it often?

Thanks

The SQL editor in the contest environment promises these features
InterSystems SQL Reference.  

But this is the reality:

I see no chance to have a different user.
Or did I miss something?
Or is this just fiction from ChatGPT ?

Assuming I have an SQL table with data, is it possible to get DML export (INSERT statements for this data)?

looking into tenants  I see this:

But on the main page, I have 2 deployments that I can work with  !! 

What is going on ??

I have  an accepted tenant and want to share my deployment.

how is this possible?

On an IRIS system, we expect the default string collation for SQL columns to be SQLUPPER. This means WHERE conditions will be case-insensitive. However, when I make a WHERE condition on a SQL procedure that returns a string, it's case sensitive.For example:

Class Sample.Person Extends %Persistent
{

Property Name As %String;
ClassMethod Test() As %String [ SqlProc ]
{
    return "Abe Lincoln"
}

} 

If I query select * from Sample.Person where Name = 'abe lincoln', it will return results where name = "Abe Lincoln". If I query select * from Sample.Person where Sample.

Hi developers!

What is the way using SQL to list all the tables available in the current database/namespace?

Most of the databases with SQL access offers the DATE_TRUNC function or analogs. This helps to easily truncate a date by any part while keeping the DateTime in a valid format

In some testing scenarios, I need to clone the database. While I use only SQL access.

I need all data to be cloned, not mapped from the original database, and a separate database to be accessed.

Is there any way to do so?

Something like

CREATE DATABASE [Database to create]
WITH TEMPLATE [Database to copy]

In the documentation  Data Types | Caché SQL Reference | Caché & Ensemble 2018.1.4 – 2018.1.8 (intersystems.com)

it says that "by default, Caché establishes a system-wide ODBC VARCHAR maximum length of 4096; this ODBC maximum length is configurable"

Per instructions, I went to SMP -> System -> Configuration -> General SQL Settings, where most of the fields from  $SYSTEM.SQL.CurrentSettings()

appear. That one does not.

How does one change it, and can it be increased?

Thanks!

[Cross-posted from https://groups.google.com/g/intersystems-public-cache/c/eRFeORb_sb0]

We are migrating to new servers, and I need to copy the information from Business Partner's over to the new server. Is this information in a Global or SQL table somewhere that I can export and import into the new IRIS environment?

Thanks

Scott

Hi Community,

I am trying to save data into SQL table but each entry  getting saved twice in the SQL table. Is there any reason data is saving Twice in the SQL table 

I have created a %Persistent class for the fields

Please find the business operation below

Class Patient.DBOperation Extends Ens.BusinessOperation

{

Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";

Property Adapter As EnsLib.SQL.OutboundAdapter;

Parameter INVOCATION = "Queue";

Method OnMessage(pInput As Patient.PatientDBReq, pOutput As Ens.Response) As %Status
{
    Set Entry=##class(Patient.PatientDBReq).%New()
    Set Entry.

I need a stored procedure where I can execute multiple delete from statements. 

This will work to create a stored procedure, but when I add in other delete from, get error invalid sql statements when try to create

CREATE PROCEDURE DeleteSpecimenTwo
  (IN Specno VARCHAR(50))
BEGIN
DELETE FROM PCONT WHERE PSPECNO = :Specno;
END;

This doesn't work

CREATE PROCEDURE DeleteSpecimenTwo
  (IN Specno VARCHAR(50))
BEGIN
DELETE FROM PCONT WHERE PSPECNO = :Specno;

DELETE FROM PSPEC WHERE PSPECNO = :Specno;
END;

Any suggestions

Thanks

Louie

Is it possible to retrieve the SQL-Executing process ID (PID) from a process spawned from a UNION %PARALLEL? I have a value set in the SQL-Executing process that I need to replicate in the UNION %PARALLEL spawned process(es). The SQL-Executing process writes the value to a global subscripted by its PID so determining the SQL-Executing PID from the spawned process is what I need. I expected $ZPARENT from the spawned process would hold the SQL-Executing PID but the PID returned is different.

With thanks,

Michael Hill

Hi guys,

I'm working on a project with my client.
They have a visit table which has about 7,000,000 records. The table is used in a random search page witch holds 20+ conditions to be combined.
The table is defined as below:

CREATE TABLE SQLUser.

Hi,

      Can anyone please help me how to use regular expression/wild character on a sql query  for a pattern like C00.0 to C00.9, C01.0 to C01.9  and so on until C77.0 to C77.9  .

Thanks for your help

Thanks

Jude

Hi All,

I am currently using D $SYSTEM.SQL.Shell() command or management portal to retrieve the data from database. I just learned we can also run cache table sql queries in MS access. MS access already installed on my machine. 

I need to ask how I can connect my database of USER or sample namespace in MS access to run these SQL queries.

Thanks in advance!!

Hi All,

I am a beginner in cache. I need to extract data from multiple tables (4-5) tables based on an SQL query . The data is more than 9 lakhs of records (i checked using count). The total number columns is 16. 

When I am running this SQL query through management portal, I am getting time out exception. Could you please suggest how I can retrieve those records. Which way will be good (Dynamic query using %SQL.Statement or Embedded SQL (not known to me) or Writing to a global will help or not).

Hi Guys

I've been using the example code %SQL.ExportMgr taken from here (an example from the intersystems community) an am having a few problems with it.

The code I have is below, and I'm having the following problems:

• Regardless of the dateformat property I use (e.g.  Set mgr.DateFormat = 1),  date data is always exported with format in YYYY-MM-DD
• Regardless of the timeformat property I use (e.g.     Set mgr.

Hi everyone.

I have a use case where I'm using embedded SQL within a Business Process to interact with a SQL table. However, when it comes to deployment into our production, the table wont form part of the deployment package created from the production.

Beyond manually creating the table on the production system, is there a standard way of ensuring that a table needed for a class is created during deployment?

I have this code:

SELECT id,room,client->name,functionary->name FROM rooms ORDER BY id

What i do to access the "client" name?

set rs = $SYSTEM.SQL.Execute("SELECT id,room,client->name,functionary->name FROM rooms ORDER BY id")

zw rs.%Get("name")
"return functionary name"

Hello!

I'm new to the system I'm working on, and I have a part of a statement that needs to grab the previous month.

....and MONTH(TX3.date_of_service)= MONTH(dateadd(mm,-1,GETDATE()))

However, when it hits January, I'm left with month 0.

Does anyone know a workaround to grab December of the previous year, while still functioning normally for the rest of the current year.

Thank you! 

Is there a way I can call %Save() on a %Persistent object and at the same time prevent indices from being built?

Hello,
I tested the query on the Config.ConfigFile table of the Namespace %SYS, it returned 3 lines and a fatal error:

SELECT
*
FROM Config.ConfigFile

I tested the same query on other IRIS instances (including a 2021.1 instance) and got the same error.
Is it a bug in IRIS or is there something wrong with the installations?
Anyone who can test and leave in the answers if they got the same result, I would be grateful.
Thank you for your attention.

I am trying to migrate our SQL Connections from HealthShare Health Connect 2018.1.3 to IRIS HealthShare Health Connect 2022.2 using the Data Export/Import Wizard through the Management Portal. I am able to export the data fine, but I am running into issues trying to Import it into 2022.2 using the Data Import Wizard. When I run through the steps it is not importing everything from the txt file I created from 2018.1.3. I would rather not have to rekey all the connections if possible.

Here is the Background Task Error Log

Is there a way around this Validation error?

I have a table, with autoincremented id

CREATE TABLE users (
    id SERIAL NOT NULL,
    name VARCHAR(30) NOT NULL,
    PRIMARY KEY (id)
)

I can add a new item there with an explicit id 

INSERT INTO users (id, name) VALUES (2, 'fred')

And while my id is autoincremented, I can omit it

INSERT INTO users (name) VALUES ('ed')

So, this time, I don't know the id, and I want to somehow get it.

I could do it with LAST_IDENTITY() function, but it just uses %RowID, and have no relation to the primary id