#SQL

Hi guys!

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":

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.

Hi Community,

In this article, I listed 5 useful SQL functions with explanations and query examples 👇🏻

These 5 functions are

• COALESCE
• RANK
• DENSE_RANK
• ROW_NUMBER
• Function to Get Running Totals

So Let us start with COALESCE function

Hi Community!

There are two general ways to execute arbitrary SQL in serverside ObjectScript code: EmbeddedSQL and ObjectScript SQL a.k.a. Dynamic SQL.

E.g. if we want to get the value of the property of instance with a certain ID using SQL we can do:

&sql(SELECT Name INTO :name FROM Sample.Person WHERE ID=1)

write name

Same result with %SQL.Statement:

set rs=##class(%SQL.Statement).%ExecDirect(,"SELECT Name as name FROM Sample.Person where ID=1")
  do rs.%Next()
  write rs.name

select * from A left join B on A.x = B.x inner join C on B.y = C.y

does Cache SQL treat the join path: LEFT_JOIN(A,INNER_JOIN(B.C)) also a valid candidate path?

Thanks.

In MySQL I have the following table:

CREATE TABLE `info` (
   `created` int(11)
);

And it is linked (via JDBC SQL Gateway) to Cache table mysql.info. `created` field stores unix timestamp. So when I execute this SQL in SMP:

SELECT created FROM mysql.info

I receive the following output (which is expected):

I get this on some queries in some namespaces. For instance, this query:

It works fine in HSBUS but in HSREG it throws the error.

Server closed communication device

Does anyone know what would cause this? Would it log something more useful somewhere?

Hi guys,

My client has a requirement to add a column of random numbers to the query result.

I wrote a function as below:

Class Utils.SqlUtility Extends %RegisteredObject
{

ClassMethod GetSomeNumber(intInput As %Integer) As %Integer [ SqlName = GetNumber, SqlProc ]
{
Return $R(intInput)
}

}

But in the returned sql result, every row share the same value, as below,

SELECT Utils.GetNumber('456'),
ID, Citizenship, DOB, FirstName, Gender, IDNumber, LastName, PatientNumber, PhoneNumber
FROM CDR.Patient

Nowadays so much noise around LLM, AI, and so on. Vector databases are kind of a part of it, and already many different realizations for the support in the world outside of IRIS.

Why Vector?

• Similarity Search: Vectors allow for efficient similarity search, such as finding the most similar items or documents in a dataset. Traditional relational databases are designed for exact match searches, which are not suitable for tasks like image or text similarity search.
• Flexibility: Vector representations are versatile and can be derived from various data types, such as text (via embeddings like Word2Vec, BERT), images (via deep learning models), and more.
• Cross-Modal Searches: Vectors enable searching across different data modalities. For instance, given a vector representation of an image, one can search for similar images or related texts in a multimodal database.

And many other reasons.

So, for this pyhon contest, I decided to try to implement this support. And unfortunately I did not manage to finish it in time, below I'll explain why.

When working with a large query executed though an ODBC connection what is the best way to allow the paging of the results at the client side. I have tried some methods using %VID and similar methods, but these really don't seem to work as the value returned is related to the ID of the data and not the position in the results set. What would be ideal is if the value seen in the management portal when you check of "Row Number" was available to external queries through ODBC. I have not seen a way to return this however.

Cache ODBC State S1000 Native Code 400 Illegal Value

This is my query:

select DateTijdSec from
GLPPatTcActie
where pnr = '27085070017' and LTestId->Makey='BLA' and VerzamelDatTijd < '2021-03-04-2021 09:04' and glpactieid->makey in ('TAV','TMA') order by DateTijdSec desc

Most likely there is a wrong date time in the table, how can i get the data?

Good Morning,

I have this question for a long time. When I make an insert by SQL from an external application in the fields of type %String, if they are empty, it writes the character $c(0) in the global.

Is there a way so that if an Insert is received in a field of type% String with null value instead of the $ c (0) it leaves it empty?:

Caché2010

Caché2016

With the release of InterSystems IRIS Cloud SQL, we're getting more frequent questions about how to establish secure connections over JDBC and other driver technologies. While we have nice summary and detailed documentation on the driver technologies themselves, our documentation does not go as far to describe individual client tools, such as our personal favourite DBeaver. In this article, we'll describe the steps to create a secure connection from DBeaver to your Cloud SQL deployment.